OraFAQ Forum: Security » read only user

Home » RDBMS Server » Security » read only user

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

read only user [message #183347]

Thu, 20 July 2006 08:05

lilyjohn
Messages: 1
Registered: July 2006

Junior Member

I have 1 users(user x) who want to give another user(user y) read only access to his schema object.
Is there a way where i can give another oracle users read only access to all the
objects in his schema or to create a read only user. The oracle user (y)should be
given the permissions to execute "select" queries only.The oracle user should
not be able to drop his objects (tables/views/synonyms) and he should be able to
view all the objects when he executes "select * from tab".
User x has default tablespace xtbl
1)Create user y identified by password
Default tablespace xtbl
2)grant connect to user y

Report message to a moderator

Re: read only user [message #183376 is a reply to message #183347]

Thu, 20 July 2006 10:48

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

There's no way to grant such a privilege directly to schema - you'll have to grant select for each table separately.

In order to make it possible for user_y to see something when issuing "SELECT * FROM TAB", create private synoynms in user_y's schema for all user_x's objects.

[EDIT]

If you don't want to allow this user to drop his objects, revoke these privileges from it (if they were granted, of course; check them querying USER_ROLE_PRIVS, USER_SYS_PRIVS, USER_TAB_PRIVS, USER_TAB_PRIVS_RECD):

REVOKE DROP ANY TABLE FROM user_y;
REVOKE DROP ANY VIEW FROM user_y;
etc.

[Updated on: Thu, 20 July 2006 10:56]

Report message to a moderator

Re: read only user [message #193438 is a reply to message #183347]

Sat, 16 September 2006 15:05

kimant
Messages: 201
Registered: August 2006
Location: Denmark

Senior Member

You could achieve this by

1) Grant select on all tables, views etc. one by one
2) Create a trigger (alternatively a job) which grants select then a new table/view etc. are created.

Br
Kim

Report message to a moderator

Re: read only user [message #194689 is a reply to message #193438]

Sun, 24 September 2006 03:10

aorehek
Messages: 52
Registered: August 2006

Member

Try this on schema, where your tables are. Change YOUR_USER with correct user.

spool grant_select.sql
select 'grant select on ' || table_name || ' to YOUR_USER ;' from user_tables;
spool off
@grant_select

Report message to a moderator

Previous Topic:	audit program activity
Next Topic:	column programm in audit trail?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 16 16:01:52 CDT 2025