Jaap W. van Dijk wrote:
> 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.
Not in any version with any trigger type.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Apr 02 2005 - 13:42:20 CST
