Oracle - How to create a readonly user

Question

It's possible create a readonly database user at an Oracle Database? How?

Answer 1

A user in an Oracle database only has the privileges you grant. So you can create a read-only user by simply not granting any other privileges.

When you create a user

CREATE USER ro_user  IDENTIFIED BY ro_user  DEFAULT TABLESPACE users  TEMPORARY TABLESPACE temp; 

the user doesn't even have permission to log in to the database. You can grant that

GRANT CREATE SESSION to ro_user 

and then you can go about granting whatever read privileges you want. For example, if you want RO_USER to be able to query SCHEMA_NAME.TABLE_NAME, you would do something like

GRANT SELECT ON schema_name.table_name TO ro_user 

Generally, you're better off creating a role, however, and granting the object privileges to the role so that you can then grant the role to different users. Something like

Create the role

CREATE ROLE ro_role; 

Grant the role SELECT access on every table in a particular schema

BEGIN   FOR x IN (SELECT * FROM dba_tables WHERE owner='SCHEMA_NAME')   LOOP     EXECUTE IMMEDIATE 'GRANT SELECT ON schema_name.' || x.table_name ||                                    ' TO ro_role';   END LOOP; END; 

And then grant the role to the user

GRANT ro_role TO ro_user; 

Answer 2

create user ro_role identified by ro_role; grant create session, select any table, select any dictionary to ro_role;