how to create a user?

[trogen]

How do I create a new user in Oracle who has only read rights to a certain database?
An sql example would help.
Thanks.

[sreddy]

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....

[bkanti]

Step 1.
Get all the tables the user wants read-only access.
Step 2.
Create a role and assign read only access for the tables above to the role
Step 3.
Create an user and assign the role created in step 2 to the user

[akkerend]

Don't forget to grant CREATE SESSION privilege to the user or the role.

[trogen]

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.
Thanks.

[sreddy]

1) Create user XYZ with 'CREATE SESSION' privilege
<font face=courier>
create user XYZ profile DEFAULT identified by *****
default tablespace USERS
temporary tablespace TEMP
account unlock;

grant 'CREATE SESSION' to XYZ ;
</font>
2) Create role READONLY
<font face=courier>
create role READONLY ;
</font>
3) Run the following script
<font face=courier>
set echo off
set feedback off
set heading off
spool temp.sql
select 'GRANT SELECT ON '||owner||'.'||object_name||' to READONLY ;'
from dba_objects
--from user_objects
--from all_objects
where object_type='TABLE'
and owner not in ('SYS','SYSTEM')
/
spool off
--@temp.sql
/

set echo on
set feedback on
set heading on
</font>
4) grant created role to user XYZ
<font face=courier>
grant READONLY to XYZ;
</font>

***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]