Re: Grant select on all tables of other user

"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message news:d2m9c0$kum$1_at_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/

What does "grant_read(ora_dict_obj_name,ora_dict_obj_owner)" do ? Received on Sat Apr 02 2005 - 08:50:11 CST