Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> Identify 'implicit compile' SQL in DDL trigger?
        
(Message started by: Pete Finnigan on Nov 16^th, 2009, 2:51pm)

Title: Identify 'implicit compile' SQL in DDL trigger?
Post by Pete Finnigan on Nov 16^th, 2009, 2:51pm

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.

Title: Re: Identify 'implicit compile' SQL in DDL trigger
Post by Pete Finnigan on Nov 17^th, 2009, 9:49pm

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.

Title: Re: Identify 'implicit compile' SQL in DDL trigger
Post by Pete Finnigan on Nov 18^th, 2009, 8:38am

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.

Title: Re: Identify 'implicit compile' SQL in DDL trigger
Post by Pete Finnigan on Nov 18^th, 2009, 12:27pm

Hi Darren,

Nice question. I have a couple of comments. First Chet Justice has created a poor mans database vault that may have something better you can use -http://www.petefinnigan.com/weblog/archives/00001253.htm

Second the error 4045 only traps implicit recompiles or revalidations so its useful in this context. You only want to trap explicit compiles as i understand from above. You could there for add a exception/when clause for 4045 in your trigger and allow processing to continue if its trapped. You could also log the capture of 4045 in the trigger or create an error trigger?

hth

cheers

Pete

Title: Re: Identify 'implicit compile' SQL in DDL trigger
Post by Pete Finnigan on Nov 18^th, 2009, 1:09pm

Hi Pete

The ORA-04045 error is occuring as a consequence of the trigger raising an exception. Nothing I can do in the trigger to trap for it.

Just had a look at the link you provided, and that is basically built on the same principal, trap the DDL before it runs. Looking through the code it appears that Chet will have the same issue though.

Title: Re: Identify 'implicit compile' SQL in DDL trigger
Post by Pete Finnigan on Nov 18^th, 2009, 3:23pm

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

Title: Re: Identify 'implicit compile' SQL in DDL trigger
Post by Pete Finnigan on Nov 19^th, 2009, 12:30am

on 11/18/09 at 08:38:11, 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.

Title: Re: Identify 'implicit compile' SQL in DDL trigger
Post by Pete Finnigan on Nov 19^th, 2009, 8:47am

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

Title: Re: Identify 'implicit compile' SQL in DDL trigger
Post by Pete Finnigan on Nov 19^th, 2009, 9:01am

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.

Title: Re: Identify 'implicit compile' SQL in DDL trigger
Post by Pete Finnigan on Nov 19^th, 2009, 9:34am

Hi Darren,

One final point, you could restrict ALTER USER by use of a trigger. See my blog entry from last year on this - http://www.petefinnigan.com/weblog/archives/00001198.htm

cheers

Pete