| 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
|
|
|
|
| Re: read only user [message #183376 is a reply to message #183347] |
Thu, 20 July 2006 10:48  |
 |
Littlefoot
Messages: 21823
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
|
|
|
|
| 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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
