Re: Grant select on all tables of other user

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Sat, 02 Apr 2005 20:08:21 GMT
Message-ID: <VYC3e.785$uw2.631@twister.nyroc.rr.com>

Anurag Varma wrote:
> Rene Nyffenegger wrote:
>

>>>> OK, but this is not what I would like to do. This way you have to 
>>>> specify grants for each table. If a new table is added, a new SELECT 
>>>> grant has to be given. Would it be possible to give this grants 
>>>> automatically each time a new table is added ?
>>>>
>>>
>>> You can do this using a schema trigger.
>>> If you want to see an example code of schema triggers ... try:
>>> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1380003556171 
>>>
>>>
>>> Anurag
>>
>>
>>
>>
>> Anurag,
>> That was my first thought as well. But when I tried it, I got an error:
>>
>>
>>
>> create user          other_user identified by        other_user 
>> default tablespace   data temporary tablespace temp;
>>
>> grant create session to other_user;
>>
>>
>> create or replace trigger trg_grant after   create on schema
>> begin
>>   if ora_dict_obj_type = 'TABLE' then
>>     execute immediate 'grant select on '             ||       
>> ora_dict_obj_owner || '.' || ora_dict_obj_name ||
>>       ' to other_user';
>>     grant_read(ora_dict_obj_name, ora_dict_obj_owner);
>>   end if;
>> end;
>> /
>>
>> create table zebra(fisch number);
>>
>> ERROR at line 1:
>> ORA-00604: error occurred at recursive SQL level 1
>> ORA-30511: invalid DDL operation in system triggers
>> ORA-06512: at line 3
>>
>>
>>
>> So, did you have something else in mind?
>>
>> Rene
>>
>>

>
> Rene,
>
> Something like this might work (as tested briefly by myself). I hope it
> does not wrap:
>
> ***************************************************************************************
>
> drop trigger trg_grant;
> drop table crtab;
>
> CREATE OR REPLACE package grantpkg
> AS
> procedure sel (in_obj in varchar2, in_user in varchar2);
> end grantpkg;
> /
> show errors
>
> CREATE OR REPLACE package body grantpkg
> AS
> procedure sel (in_obj in varchar2, in_user in varchar2)
> is
> begin
> execute immediate 'grant select on '||in_obj||' to '||in_user;
> end sel;
> end grantpkg;
> /
> show errors
>
> create or replace trigger trg_grant
> after create on schema
> declare
> l_sysevent varchar(25);
> v_jobno number;
> begin
> select ora_sysevent into l_sysevent from dual;
> if ( l_sysevent = 'CREATE' )
> then
> dbms_job.submit(v_jobno, 'begin grantpkg.sel( '''||
> ora_dict_obj_name ||''', ''TEMPUSER''); end;',sysdate);
> end if;
> end;
> /
> show errors
>
> create table crtab (a number);
>
> insert into crtab values (1);
>
> commit;
>
>
> ***************************************************************************************
>
>
> Thanks,
> Anurag

Note: the above example would require refining to only issue the grant if the create is on a "TABLE". There might be other gotchas too...

Thanks,
Anurag Received on Sat Apr 02 2005 - 14:08:21 CST