Trigger Question

From: Scott Canaan <>
Date: Thu, 19 Feb 2009 16:21:11 -0500
Message-ID: <>

   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'))






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


pragma autonomous_transaction;


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

                     ' to test_user2';



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  

Thank you,  

Scott Canaan '88 (

(585) 475-7886

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

Received on Thu Feb 19 2009 - 15:21:11 CST

Original text of this message