Trigger Question
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-lReceived on Thu Feb 19 2009 - 15:21:11 CST