?
Solved

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

Posted on 2008-02-06
9
Medium Priority
?
3,002 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:ElishaMoshe
9 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 20833695
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
 
LVL 9

Expert Comment

by:joebednarz
ID: 20833815
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
 
LVL 9

Expert Comment

by:joebednarz
ID: 20833873
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:ElishaMoshe
ID: 20840633
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
 

Author Comment

by:ElishaMoshe
ID: 20840692
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
 
LVL 9

Expert Comment

by:joebednarz
ID: 20840948
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
 
LVL 9

Expert Comment

by:joebednarz
ID: 20840984
...sorry...

not_found := true;
FOR rec ...
   not_found := false;
   EXIT;
END LOOP;
0
 
LVL 6

Expert Comment

by:jwittenm
ID: 20851236
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
 

Accepted Solution

by:
ElishaMoshe earned 0 total points
ID: 20865314
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question