Re: Grant select on all tables of other user

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 01 Apr 2005 15:55:24 -0800
Message-ID: <1112399509.622532@yasure>

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 may not want to but there is No alternative.

If for multiple users you would create a role and grant SELECT to the role and then the role to multiple users. But there is no concept of grant privileges to a schema's objects in Oracle. Something for which I, and anyone concerned with security, should be very grateful.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Apr 01 2005 - 17:55:24 CST