Re: Grant select on all tables of other user

On Sat, 02 Apr 2005 12:43:35 GMT, Anurag Varma <avdbi_at_hotmail.com> wrote:

>Matthias Hoys wrote:
>> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
>> news:1112398616.594650_at_yasure...
>>
>>>Matthias Hoys wrote:
>>>
>>>
>>>>A developer asked me this question :
>>>>Is it possible to create a user (a read-only user) which has by default
>>>>SELECT rights on all tables of another user ? So something like the
>>>>SELECT ANY TABLE privilege but restricted to only one schema.
>>>>
>>>>Any ideas ? Db is Oracle 9.2.0.5.0.
>>>
>>>CREATE USER readonly
>>>IDENTIFIED BY readonly
>>>TEMPORARY TABLESPACE temp
>>>QUOTA 0 ON system;
>>>
>>>GRANT create session TO readonly;
>>>GRANT select ON <schema_name.table_name_1> TO readonly;
>>>GRANT select ON <schema_name.table_name_2> TO readonly;
>>>GRANT select ON <schema_name.table_name_n> TO readonly;
>>>--
>>>Daniel A. Morgan
>>>University of Washington
>>>damorgan_at_x.washington.edu
>>>(replace 'x' with 'u' to respond)
>>
>>
>> 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

You cannot perform an object grant in a DDL-trigger (at least not in Oracle 8, maybe it changed in a later version).

Jaap. Received on Sat Apr 02 2005 - 13:11:27 CST