How do I create a new user in Oracle who has only read rights to a certain database?
An sql example would help.
I guess its may be little big task to create and run scripts with 'SELECT' Privileges on all of the tables if your database is too big.
If its not big enough, could spend 5 to 10 minutes and create a role READONLY_USER and give all the select tables privileges to that role and assign that role to the user you wanted.
Not sure, your intention was little more simplified task or this is what you wanted....
Get all the tables the user wants read-only access.
Create a role and assign read only access for the tables above to the role
Create an user and assign the role created in step 2 to the user
Don't forget to grant CREATE SESSION privilege to the user or the role.
Ok, whats the sql statement that would allow me to create a user to have full access to a certain database, but only to that database. No other tables would be accessable by that user.
1) Create user XYZ with 'CREATE SESSION' privilege
create user XYZ profile DEFAULT identified by *****
default tablespace USERS
temporary tablespace TEMP
grant 'CREATE SESSION' to XYZ ;
2) Create role READONLY
create role READONLY ;
3) Run the following script
set echo off
set feedback off
set heading off
select 'GRANT SELECT ON '||owner||'.'||object_name||' to READONLY ;'
and owner not in ('SYS','SYSTEM')
set echo on
set feedback on
set heading on
4) grant created role to user XYZ
grant READONLY to XYZ;
***Uncomment to make spooled script to run.
***Modify it per your requirement including/excluding owners.
***You can USE ALL_OBJECTS/USER_OBJECTS views instead of DBA_OBJECTS whichever is appropriate for your situation.
*** OWNER is not valid column in user_objects view
[Edited by sreddy on 01-26-2001 at 02:21 PM]
Click Here to Expand Forum to Full Width