Re: Grant select on all tables of other user

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Sat, 2 Apr 2005 14:14:24 +0000 (UTC)
Message-ID: <d2m9c0$kum$1@klatschtante.init7.net>

>>
>> 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 Nyffenegger
  http://www.adp-gmbh.ch/

Received on Sat Apr 02 2005 - 08:14:24 CST