RE: Trigger Question

you cannot grant directly out of the trigger, but you could submit a job that does
this for you. Set the interval to null and the job will disappear after execution.
but take care, trigger should not fire to often as this might fill up the job queue
intolerably.  

snippet:  

declare
  v_job binary_integer;
begin
  dbms_job.submit(v_job,'grant_select(''' || ora_dict_obj_owner || ''',''' || ora_dict_obj_name || ''');'); end;
/
 

HTH Mathias

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Scott Canaan Sent: Thursday, February 19, 2009 10:21 PM To: oracle-l_at_freelists.org
Subject: Trigger Question

   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