Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Welcome, Guest. Please Login.
Oct 15th, 2019, 7:08am
News: Welcome to Pete Finnigan's Oracle security forum
Home | Help | Search | Members | Login
   Pete Finnigan's Oracle Security Forum
   Oracle Security
   Oracle Security
(Moderator: Pete Finnigan)
   Identify 'implicit compile' SQL in DDL trigger?
« Previous topic | Next topic »
Pages: 1  Reply | Notify of replies | Send Topic | Print
   Author  Topic: Identify 'implicit compile' SQL in DDL trigger?  (Read 9970 times)
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
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: male
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: male
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 elaborateSmiley
 
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 Wink
 
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: male
Posts: 309
Re: Identify 'implicit compile' SQL in DDL trigger
« Reply #3 on: Nov 18th, 2009, 12:27pm »
Quote | Modify

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
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: male
Posts: 309
Re: Identify 'implicit compile' SQL in DDL trigger
« Reply #4 on: Nov 18th, 2009, 1:09pm »
Quote | Modify

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.
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: male
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: male
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: male
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: male
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
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
Re: Identify 'implicit compile' SQL in DDL trigger
« Reply #9 on: Nov 19th, 2009, 9:34am »
Quote | Modify

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
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
Pages: 1  Reply | Notify of replies | Send Topic | Print

« Previous topic | Next topic »

Powered by YaBB 1 Gold - SP 1.4!
Forum software copyright 2000-2004 Yet another Bulletin Board