Author |
Topic: Identify 'implicit compile' SQL in DDL trigger? (Read 11567 times) |
|
Pete Finnigan
PeteFinnigan.com Administrator
Oracle Security is easier if you design for it
View Profile | WWW | Email
Gender: 
Posts: 309
|
 |
Identify 'implicit compile' SQL in DDL trigger?
« on: Nov 16th, 2009, 2:51pm » |
Quote | Modify
|
Question
--------
Is there a way to identify an 'implicit compile' within a DDL trigger. to prevent the ORA-04045 error?
Background
----------
In normal operations, if a user has 'execute' permissions on a package and that package/body becomes invalid, an implicit recompile is performed under the covers before it is run.
However, I am blocking 'ALTER' statements on this package via a DDL trigger to a certain user with DBA role. This results in a ORA-04045 error.
I have looked at all options in the USERENV context but cannot identify anything to give the SQL away as an implicit compile.
The below test case recreates the error.
Code:
-- create user u1
create user u1 identified by u1
/
-- create package
create or replace package u1.p1 as
procedure go;
end;
/
create or replace package body u1.p1 as
procedure go as
begin
null;
end;
end;
/
-- create trigger
CREATE OR REPLACE TRIGGER U1.T1
AFTER ALTER
ON database
declare
dummyCnt pls_integer;
sqlText ora_name_list_t;
BEGIN
IF ( sys_context('USERENV','SESSION_USER') in ('SYS', 'U1', 'SYSTEM') ) THEN
RETURN;
END IF;
if ( ora_dict_obj_name = 'P1' ) then
dummyCnt := ora_sql_txt(sqlText);
raise_application_error(-20101, 'FAIL!!!!!! sql:' || sqlText(1), true);
end if;
END;
/
-- create user u2
create user u2 identified by u2
/
grant dba to u2
/
-- now log on as user u2 and run the below
begin u1.p1.go(); end;
/
--PL/SQL procedure successfully completed.
-- as u1, recreate the header
create or replace package u1.p1 as
procedure go;
end;
/
-- now log on as user u2 and run the below
begin u1.p1.go(); end;
/
--begin u1.p1.go(); end;
--*
--ERROR at line 1:
--ORA-04045: errors during --recompilation/revalidation of U1.P1
--ORA-20101: FAIL : sessionUser:U2
--ORA-06512: at line 10
--ORA-06508: PL/SQL: could not find program unit being called
--ORA-06512: at line 1
-- tidy up
drop user u1 cascade
/
drop user u2 cascade
/
|
|
The 4045 error description is...
Code:
04045, 00000, "errors during recompilation/revalidation of %s.%s"
// *Cause: This message indicates the object to which the following
// errors apply. The errors occurred during implicit
// recompilation/revalidation of the object.
// *Action: Check the following errors for more information, and
// make the necessary corrections to the object.
|
|
|
|
 IP Logged |
Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
|
|
|
Pete Finnigan
PeteFinnigan.com Administrator
Oracle Security is easier if you design for it
View Profile | WWW | Email
Gender:
Posts: 309
|
 |
Re: Identify 'implicit compile' SQL in DDL trigger
« Reply #1 on: Nov 17th, 2009, 9:49pm » |
Quote | Modify
|
In my XE environment I had to make a couple of changes to the script to make sure the package became invalid.
Code:
create procedure dummy is
begin
null;
end;
/
-- create package
create or replace package u1.p1 as
procedure go;
end;
/
create or replace package body u1.p1 as
procedure go as
begin
dummy;
end;
end;
/
drop procedure dummy;
create procedure dummy is
begin
null;
end;
/
select * from user_objects where status != 'VALID';
|
|
Amending the trigger to
Code:
CREATE OR REPLACE TRIGGER U1.T1
AFTER ALTER
ON database
declare
dummyCnt pls_integer;
sqlText ora_name_list_t;
v_text varchar2(32000);
BEGIN
IF ( sys_context('USERENV','SESSION_USER') in ('SYS', 'U1', 'SYSTEM') ) THEN
RETURN;
END IF;
if ( ora_dict_obj_name = 'P1' ) then
dummyCnt := ora_sql_txt(sqlText);
for i in 1..dummycnt loop
v_text := v_text ||'/n'||sqltext(i);
end loop;
raise_application_error(-20101, 'FAIL!!!!!! sql:' || v_text, true);
end if;
END;
/
|
|
The SQL that was executed was :
"ALTER PACKAGE "U1"."P1" COMPILE BODY REUSE SETTINGS"
You don't say the motivation for preventing ALTER on the object. I'm guessing that it is to prevent it being recompiled with different conditional compilation flags (interesting attack vector I hadn't really considered).
The REUSE SETTINGS are the key to keeping the same conditional compilation settings, so as long as the SQL ends with those two words,you should be okay to allow it.
|
|
IP Logged |
Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
|
|
|
Pete Finnigan
PeteFinnigan.com Administrator
Oracle Security is easier if you design for it
View Profile | WWW | Email
Gender:
Posts: 309
|
|
Re: Identify 'implicit compile' SQL in DDL trigger
« Reply #2 on: Nov 18th, 2009, 8:38am » |
Quote | Modify
|
Quote:You don't say the motivation for preventing ALTER on the object. I'm guessing that it is to prevent it being recompiled with different conditional compilation flags (interesting attack vector I hadn't really considered).
|
|
Nothing so elaborate
My motivation... A poor mans data vault I guess (this is on 9i btw).
A vendor has specified that the user used by their app server user must have DBA role. The user/pass is also well known as it is hardcoded in places...
After much arguing/discussion, the decision to proceed with this was made (from higher up of course).
Its a wonderful setup as Im sure you will agree 
I am trying to prevent this user from being able to explicitly compile (among other things) objects, and thought simply trapping the DDL was sufficient.
However, Oracle runs implicit compiles under the covers. Even for users that dont have the ALTER privilege.
I am trying to identify, and ignore, these implicit statements.
The above code is purely a test case to expose what is going on, and should not be taken as production code.
|
|
IP Logged |
Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
|
|
|
Pete Finnigan
PeteFinnigan.com Administrator
Oracle Security is easier if you design for it
View Profile | WWW | Email
Gender:
Posts: 309
|
|
Re: Identify 'implicit compile' SQL in DDL trigger
« Reply #5 on: Nov 18th, 2009, 3:23pm » |
Quote | Modify
|
Hi Darren,
Hmmmm. So the 4045 is a sort of red-herring. its valid in that it is an implicit compile (in this case). Does an explicit compile also include 4045 in the error stack? - you have the trigger for ALTER so it should fire for an explicit compile?
Also have you run a trace comparing an explicit compile with an implicit compile? - to see if there are any identifiable differences in the recursive SQL that may help us identify if the compile is implicit or explicit?
You could also check the object status, to see if its INVALID but then you should use a BEFORE trigger not an AFTER one, although because the ALTER fails the status shouldremain INVALID in either case?
BUT an explicit compile can also be done on an INVALID object so this makes it complex. I think the best next step would be to trace both cases and see if the recursive SQL is different in terms of actual SQL, parents of SQL or actual data manipulated (meta data).
cheers
pete
|
|
IP Logged |
Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
|
|
|
Pete Finnigan
PeteFinnigan.com Administrator
Oracle Security is easier if you design for it
View Profile | WWW | Email
Gender:
Posts: 309
|
|
Re: Identify 'implicit compile' SQL in DDL trigger
« Reply #6 on: Nov 19th, 2009, 12:30am » |
Quote | Modify
|
on Nov 18th, 2009, 8:38am, darren turland wrote:
I am trying to prevent this user from being able to explicitly compile (among other things) objects, and thought simply trapping the DDL was sufficient.
|
|
Frankly, if you put in place a mechanism that allows an implicit compile, but not an explicit one, I'd just create a dummy function that I can call so I can compile it implicitly just by calling the function. There may even by a function that already does the job.
Not really sure why you need to prevent an ALTER. Any code change has to go through a CREATE OR REPLACE, not an ALTER.
If you are worried about locking, cascading invalidations or losing session state, I'd follow on from Pete's comment and see if you can check whether it is INVALID. If it is, then allow the compile as no-one will have an active session state for an INVALID package.
|
|
IP Logged |
Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
|
|
|
Pete Finnigan
PeteFinnigan.com Administrator
Oracle Security is easier if you design for it
View Profile | WWW | Email
Gender:
Posts: 309
|
|
Re: Identify 'implicit compile' SQL in DDL trigger
« Reply #7 on: Nov 19th, 2009, 8:47am » |
Quote | Modify
|
Good points Gary, thanks. You make a good point about ALTER in relation to code and the much more dangerous use of ALTER with your vendors DBA account is that this account can be used to change the SYS or any other users password. Even worse is that this account could be used to make structural changes to the database if it has the DBA role.
My reaction would be to not try and find a code based solution. You cannot replicate DV (I assume you cannot install DV from a budget point of view?) by yourself as you cannot put VPD on the dictionary as Oracle have done with DV; you can go quite a qay to replicate it which is what Chet has tried to do but its never going to go far enough. The biggest issue with DV is that its easy to bypass if you have OS access. I would hope your vendor does not also have OS access?
My view would be three fold:
1) try and persuade management that they must push this vendor to reduce the privileges of its schema accounts. Most vendors are doing this nowadays, its much less common to see schema accounts that "insist" they are DBA
2) do some of what you are doing but its going to be difficult to prevent them abusing the database if they want to
3) much better put audit in place and watch what they do for two reasons. 3.1) you can look for abuse and use that to force (1) to be done. 3.2) you can help them do (1) by showing what system privileges their account really uses. put audit on all system privileges and key system packages that they may use.
cheers
Pete
|
|
IP Logged |
Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
|
|
|
Pete Finnigan
PeteFinnigan.com Administrator
Oracle Security is easier if you design for it
View Profile | WWW | Email
Gender:
Posts: 309
|
|
Re: Identify 'implicit compile' SQL in DDL trigger
« Reply #8 on: Nov 19th, 2009, 9:01am » |
Quote | Modify
|
GAMYERS comments got me thinking... There is actually little damage to be done with an 'ALTER' on packages, etc. OK , the status can be changed and recompiles done, which will change last_ddl times, but not really much else.
As stated changes to the code will only be possible via CREATE OR REPLACE.
I think I was trying to be over zealous and tighten things up way too much.
So I will allow the ALTER and put audit in place to trap what is actually going on.
Thanks for the input. Much appreciated.
|
|
IP Logged |
Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
|
|
|
|
PFCLScan
PFCLObfuscate
PFCLCode
PFCLForensics
PFCLReselling
PFCLATK
PFCLCookie
PFCLTraining
PFCLServices
PFCLConsulting
PFCLCustom
PFCLCloud
PFCLUserRights
PFCLSTK
PFCLSFTK
PFCLSEO