It can be quite common to create a “Read Only” database user account in an Oracle database. To do this is pretty simple using the principle of least privilege:
CREATE USER READ_ONLY IDENTIFIED BY "password";
GRANT CREATE SESSION TO READ_ONLY;
Expected output:
SQL> CREATE USER READ_ONLY IDENTIFIED BY "password";
User created.
SQL> GRANT CREATE SESSION TO READ_ONLY;
Grant succeeded.
SQL>
Depending on the Oracle Version, it’s recommend to use “READ” object privilege new in 12.1.0.2 instead of “SELECT” to prevent locking. See the following blog post for more info:
New READ Object Privilege in 12cR1
PLEASE NOTE: For anyone using Oracle Version prior to 12.1.0.2, replace the object privilege “READ” with “SELECT“.
To read specific tables for a schema:
SET HEADING OFF
SET PAGES 9999 LINES 400
SPOOL TABLE_GRANTS.sql
SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'
FROM DBA_TABLES
WHERE OWNER IN ('ZEDDBA')
ORDER BY 1;
SPOOL OFF
@TABLE_GRANTS.sql
!rm TABLE_GRANTS.sql
Expected output, where you can see the user “READ_ONLY” able to select from the table owned by user “ZEDDBA“:
SQL> SET HEADING OFF
SQL> SET PAGES 9999 LINES 400
SQL> SPOOL TABLE_GRANTS.sql
SQL> SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'
2 FROM DBA_TABLES
3 WHERE OWNER IN ('ZEDDBA')
4 ORDER BY 1;
GRANT READ ON ZEDDBA.DEMO TO READ_ONLY;
SQL> SPOOL OFF
SQL> @TABLE_GRANTS.sql
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.
Grant succeeded.
SP2-0734: unknown command beginning "SQL> SPOOL..." - rest of line ignored.
SQL> !rm TABLE_GRANTS.sql
SQL> conn read_only/password
Connected.
SQL> SELECT * FROM ZEDDBA.DEMO;
Read Only example.
SQL> exit
To read all tables in the databases:
GRANT READ ANY TABLE TO READ_ONLY;
Expected output, where you can see the user “READ_ONLY” able to select from the table owned by user “ZEDDBA“, even after the revoke and the user “ZEDDBA2“, which hasn’t been explicitly referenced:
SQL> conn zeddba/zeddba
Connected.
SQL> REVOKE READ ON ZEDDBA.DEMO FROM READ_ONLY;
Revoke succeeded.
SQL> GRANT READ ANY TABLE TO READ_ONLY;
Grant succeeded.
SQL> conn read_only/password
Connected.
SQL> SELECT * FROM ZEDDBA.DEMO;
COMMENTS
--------------------------------------------------------------------------------
Read Only example.
SQL> SELECT * FROM ZEDDBA2.DEMO;
COMMENTS
--------------------------------------------------------------------------------
Read Only example.
SQL>
To grant access to dictionary tables:
GRANT SELECT ANY DICTIONARY TO READ_ONLY;
Expected output, where you can see the user “READ_ONLY” able to select from dictionary i.e. table starting with DBA and V$:
SQL> conn zeddba/zeddba
Connected.
SQL> GRANT SELECT ANY DICTIONARY TO READ_ONLY;
Grant succeeded.
SQL> conn read_only/password
Connected.
SQL> SELECT TABLE_NAME
2 FROM DBA_TABLES
3 WHERE OWNER = 'ZEDDBA';
TABLE_NAME
--------------------------------------------------------------------------------
DEMO
SQL> SELECT NAME FROM V$DATABASE;
NAME
---------
ZEDDBA
SQL>
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)