How-to: Create an Oracle Read-Only user on an Oracle 11g Server

I recently had a situation where I had to create an Oracle read-only user on an Oracle 11g Database. Mainly for compliance reasons but I won’t bore you with that detail… I used Oracle 11G to set this up. Here are some commands I found helpful in configuring this read-only setup. I am not a DBA, but this should help anyone who is looking to create a read only user in Oracle 11g.

To create read only user:


create user read_only_user identified by <password>;


To List all tables:


select table_name from user_tables;


To create a read-only role


create role read_only_role;


To grant select privileges to the role:

grant select on TABLENAME to read_only_role;


To Grant role to user:


grant read_only_role to read_only_user;


To create a public synonyms for read_only_user for table owned by another schema:
As Privileged ID:


create public synonym TABLENAME for schema.TABLENAME;


To creating a local synonym for read_only_user for a table owned by another schema:
As user:


create synonym TABLENAME for schema.TABLENAME;


Happy SQLing!