Capturing "ORA-01031: insufficient privileges" in a DDL trigger

Hi,

I am using Oracle 9.2.0.1.0.

I have a BEFORE DDL trigger on DATABASE.
If I issue a DDL without having the proper priviliges to execute it,
the code in the BEFORE  trigger still runs and only then do I get the error message.

I would like to know if there is a way to catch the error: "ORA-01031: insufficient privileges" in the trigger
so that if it occurs the triggers code will not be executed?

Thanks.
-- A log message table
CREATE TABLE LOG_MSG (message NUMBER);
 
Table created.
 
-- Just a procedure to log messages from the trigger
create or replace PROCEDURE log_msg(p_msg VARCHAR2) IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   insert into log_msg values (p_msg);
   COMMIT;
END log_msg;
 
Procedure created.
 
-- Create the DDL trigger
create or replace trigger my_trig before ddl on database
begin
log_msg('trigger still executed...');
end;
 
Trigger created.
 
-- Now execute a ddl you do not have priviliges to run:
SQL>  create view my_view as select *
  2   from my_tab;
 create view my_view as select *
             *
ERROR at line 1:
ORA-01031: insufficient privileges
 
-- Now check the log table
SQL> select message from yoni_msg;
 
MESSAGE
-----------------------------------------------------------------------
trigger still executed...

Open in new window

ElishaMosheAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oleggoldCommented:
You need to make exception with the ora err_code of this message,e.g:

exception when insufficient_privileges then
null;
end;

Open in new window

0
joebednarzCommented:
There are some attributes you can use to help, try something like this:

create or replace trigger my_trig before ddl on database
DECLARE
  CURSOR get_privs( c_owner VARCHAR2, c_table VARCHAR2)
  IS
  SELECT owner, table_name FROM dba_tables
  WHERE owner = c_owner AND table_name = c_owner;
 
  not_found BOOLEAN;
begin

  IF ora_dict_obj_type = "TABLE' THEN
     FOR rec IN get_privs( ora_dict_obj_owner, ora_dict_obj_name ) LOOP
        BEGIN
           SELECT privilege INTO dummy FROM dba_tab_privs
              WHERE owner = rec.owner AND table_name = rec.table_name
              AND grantee = ora_login_user;
           not_found := FALSE;
        EXCEPTION WHEN NO_DATA_FOUND THEN
           not_found := TRUE;
           EXIT;
        END;
     END LOOP;
     IF not_found THEN
         -- priv not there for the user... don't process
         log_msg( 'tried to create, but no privs...');
     ELSE
         -- priv there, log and continue
         log_msg( 'created table...' );
     END IF;
   END IF;
END;

... NOTE:  Not fully tested... but it should work.
0
joebednarzCommented:
oops... already see one error:

variable "dummy" was not declared.  Should be:

dummy dba_tab_privs.privilege%TYPE;

... and just so you know, these are system attributes available to you when you write triggers:

ORA_DICT_OBJ_TYPE
ORA_DICT_OBJ_OWNER
ORA_DICT_OBJ_NAME
ORA_LOGIN_USER

... to name a few.  Try here http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg14evt.htm for a complete list of system attributes
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ElishaMosheAuthor Commented:
Hi Oleg,

Thanks for your suggestion.

Tried adding an exception clause to the trigger as you suggested:
But as you can see, the trigger body is still executed.



create or replace trigger my_trig before ddl on database
  insufficient_privileges EXCEPTION;
  PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);
begin
  log_msg('trigger still executed...');
exception
  when insufficient_privileges then
    log_msg('NO PRIVILIGE');
end;
 
SQL>  create view my_view as select *
  2   from my_tab;
 create view my_view as select *
             *
ERROR at line 1:
ORA-01031: insufficient privileges
 
SQL> select message from yoni_msg;
 
MESSAGE
-----------------------------------------------------------------------
trigger still executed...

Open in new window

0
ElishaMosheAuthor Commented:
Hi Joe,

Thanks for your suggestion.

What you suggest might work if only had to check "tab" privs,
but how can I deal with a case in which the user lacks a "sys" privs,
such as "create view" in my example?

P.S: Thanks for the tip regarding system attributes, I have been using them quite extensivly in my triggers.
0
joebednarzCommented:
Well, I'd say the trigger needs to be revised:

DECLARE
  sql_text ora_name_list_t;
  stmt VARCHAR2(2000);

BEGIN
IF ora_sysevent = 'CREATE' THEN
   n := ora_sql_txt(sql_text);
   FOR i IN 1..n LOOP
     stmt := stmt || sql_text(i);
   END LOOP;

   this_priv := SUBSTR( stmt, 1, INSTR( stmt, ' ', 1, 2 ) - 1);  -- looks at incoming statement at finds second "space character" for instance 'CREATE TABLE' or 'CREATE VIEW'

   any_priv := REPLACE( this_priv, ' ', ' ANY ');  -- this

   not_found := false;
   FOR rec IN (SELECT 'X' FROM dba_sys_privs
                        WHERE privilege IN ( this_priv, any_priv )
                        AND grantee = ora_login_user) LOOP
      not_found := TRUE;
      EXIT;
   END LOOP;

   IF not_found THEN
      log_msg( 'cannot create...' );
   ELSE
      log_msg( 'creating whatever...' );
   END IF;
END IF;

As with previous post, not sure what user context the trigger will run under, so you'll have to experiment yourself... i.e., you may need to use USER_SYS_PRIVS instead of DBA_SYS_PRIVS
0
joebednarzCommented:
...sorry...

not_found := true;
FOR rec ...
   not_found := false;
   EXIT;
END LOOP;
0
jwittenmCommented:
Put the code for log_msg in the trigger to avoid having it executed.  Or remove the 'pragma autonomous transaction'.  The purpose of the pragma is to do just what you DON'T want the trigger to do which is to execute that piece of code regardless of whether the trigger executes sucessfully.
0
ElishaMosheAuthor Commented:
Hi,

Eventually found a bit of a different solution:
The code in the BEFORE DDL trigger still runs,
but now I've added an AFTER SERVERERROR trigger to "undo" the effects of the BEFORE trigger...

Not the prettiest solution, but it will do...

Thank you all for your help!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.