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)
Zed DBA's Oracle Blog 