Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Grant select on all tables of other user

Re: Grant select on all tables of other user

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Sat, 02 Apr 2005 12:43:35 GMT
Message-ID: <Xrw3e.709$uw2.46@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 Received on Sat Apr 02 2005 - 06:43:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US