Trigger Question
- From: "Scott Canaan" <srcdco@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Feb 2009 16:21:11 -0500
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@xxxxxxx)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put
into it." - Tom Lehrer.
Other related posts:
