Home » RDBMS Server » Security » read only user
read only user [message #183347] Thu, 20 July 2006 08:05 Go to next message
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

Re: read only user [message #183376 is a reply to message #183347] Thu, 20 July 2006 10:48 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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 Go to previous messageGo to next message
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
Re: read only user [message #194689 is a reply to message #193438] Sun, 24 September 2006 03:10 Go to previous message
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
Previous Topic: audit program activity
Next Topic: column programm in audit trail?
Goto Forum:
  


Current Time: Sun Dec 04 10:35:55 CST 2016

Total time taken to generate the page: 0.13056 seconds