Oracle - How to create a readonly user

28

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

This question is tagged with oracle readonly

~ Asked on 2011-09-21 15:27:53

The Best Answer is


50

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;

~ Answered on 2011-09-21 15:37:44


19

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

~ Answered on 2013-08-07 05:23:28


Most Viewed Questions: