Trigger Question

From: Scott Canaan <srcdco_at_rit.edu>
Date: Thu, 19 Feb 2009 16:21:11 -0500
Message-ID: <D0A642D37DE30842AA667A9AFFE3951A05F48186_at_svits11.main.ad.rit.edu>


   I have a question on how to grant select access to a table as part of the table create process, inside the database. I was hoping to use a system trigger (after create on schema). In testing, I created the following trigger and stored procedure:  

create or replace trigger test_create_trig after create on schema

when (ora_dict_obj_type in ('TABLE','VIEW'))

declare

begin

   grant_select(ora_dict_obj_owner,ora_dict_obj_name);

end;

/
 

create or replace procedure grant_select (obj_owner varchar2, obj_name varchar2)

 as

pragma autonomous_transaction;

begin

   execute immediate 'grant select on ' || obj_owner || '.' || obj_name ||

                     ' to test_user2';

end;

/
 

When I try to create a table as test_user, which I want select permission on for test_user2, I get the following error stack:  

SQL> create table test2 (a number);

create table test2 (a number)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04020: deadlock detected while trying to lock object TEST_USER.TEST2

ORA-06512: at "TEST_USER.GRANT_SELECT", line 4

ORA-06512: at line 5    

SQL>      The request is to have all tables (and views) owned by one user to be readable by another user, but not by giving "select any table" to the other user. I granted all of the existing tables and views, but want to automatically be able to grant future ones as they are created. It seems like there should be a way to do this and it shouldn't be too hard, without relying on the developers to remember to put the grants in their code. I can't believe I'm the first one to have this issue.  

We are using Oracle 10.2.0.4.  

Thank you,  

Scott Canaan '88 (Scott.Canaan_at_rit.edu)

(585) 475-7886

"Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer.  

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 19 2009 - 15:21:11 CST

Original text of this message