"Anurag Varma" <avdbi_at_hotmail.com> wrote in message
news:Xrw3e.709$uw2.46_at_twister.nyroc.rr.com...
> 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
Indeed something I could use.
Thanks !
Matthias
Received on Sat Apr 02 2005 - 08:47:12 CST
