How to Grant READ ONLY Access on Schema Using ROLE in Oracle

June 3, 2021

How to Grant READ ONLY Access on Schema Using ROLE in Oracle

This is a very frequent and common request by developers to grant READ ONLY access to specific application schema. To fulfill this request either we can grant SELECT privileges on all tables to requestor which are owned by specific application schema or create a ROLE and grant SELECT privileges on all tables which are owned by specific application schema to newly created ROLE and grant access on ROLE to the requested user/s.

In this demonstration, we will perform both options. Below is the list of requirements:

⇒ GRANTEE: TESTUSER1, TESTUSER2, TESTUSER3 (User who needs access to schema’s object).
⇒ OWNER: USER_GRANT (Object’s owner on which access is require)
⇒ ROLE: USER_GRANT_READ_ONLY (Create new READ ONLY ROLE)

OPTION 1: Grant SELECT privileges on all tables to requestors who are owned by specific application schema. To fulfill this option, we will prepare a dynamic query or also we will prepare one stored procedure.

1.1.1. Prepare dynamic query for select privileges: Below command creates one spool file READ_ONLY_TEST.sql which will contain all SELECT privileges of USER_GRANT for TESTUSER1.

SET LINES 333 PAGES 222
SET FEEDBACK OFF
SET HEADING OFF
set TERMOUT off
spool READ_ONLY_TEST.sql
SELECT 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' TO TESTUSER1;'  FROM DBA_TABLES WHERE OWNER='USER_GRANT';
spool off;

1.1.2. Execute READ_ONLY_TEST.sql: Open file READ_ONLY_TEST.sql in the editor and remove first line (select command) and last line (spool off;) and save then execute it.

SET FEEDBACK ON
SET HEADING ON
SET TERMOUT ON
SET ECHO ON
spool OUTPUT_READ_ONLY_TEST.sql
@READ_ONLY_TEST.sql
spool off;

1.1.3. Validate access: Open a new terminal and connect to user TESTUSER1 then validate access.

SQL> CONN TESTUSER1/XXXXXXXX
Connected.
SQL> SHOW USER
USER is "TESTUSER1"
SQL> SELECT COUNT(*) FROM USER_GRANT.TAB_SELECT;

  COUNT(*)
----------
        49

Optionally we can also create one procedure that will work the same as above but here it would be one-time activity and whenever in the future we have a request for SELECT access for any OWNER, any USER, we have to pass as first argument ‘OWNER’ and second argument ‘GRANTEE’. Follow the below steps:

1.2.1. Create a procedure: Create procedure READ_ONLY_PROC.

SQL> CREATE OR REPLACE PROCEDURE READ_ONLY_PROC(
USERNAME VARCHAR2,
GRANTEE VARCHAR2)
AS
BEGIN
FOR D IN (
SELECT OWNER, TABLE_NAME
FROM DBA_TABLES
WHERE OWNER = USERNAME)
LOOP
EXECUTE IMMEDIATE
'GRANT SELECT ON '||D.OWNER||'.'||D.TABLE_NAME||' TO ' || GRANTEE;
END LOOP;
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15

Procedure created.

1.2.2. Execute procedure READ_ONLY_PROC: To execute procedure pass arguments as below.

EXEC READ_ONLY_PROC(‘OWNER‘,’GRANTEE‘);

SQL> EXEC READ_ONLY_PROC('USER_GRANT','TESTUSER2');

PL/SQL procedure successfully completed.

1.2.3. Validate access: Open a new terminal and connect to user TESTUSER2 then validate access.

SQL> CONN TESTUSER2/XXXXXXXX
Connected.
SQL> SHOW USER
USER is "TESTUSER2"
SQL> SELECT COUNT(*) FROM USER_GRANT.TAB_PROC;

  COUNT(*)
----------
       168

OPTION2: Create a ROLE and grant SELECT privileges on all tables which are owned by specific application schema to newly created ROLE and grant access on ROLE to the requested user/s.

2.1. Create a role: Create role USER_GRANT_READ_ONLY.

SQL> CREATE ROLE USER_GRANT_READ_ONLY;

Role created.

2.2. Grant SELECT access to ROLE:

Grant SELECT access of all TABLES which are owned by USER_GRANT to newly created ROLE.

SQL> BEGIN
FOR D IN (SELECT * FROM DBA_TABLES WHERE OWNER='USER_GRANT')
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON USER_GRANT.' || D.TABLE_NAME || ' TO USER_GRANT_READ_ONLY';
END LOOP;
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

2.3. Access on ROLE to USER: Grant access on ROLE to the requestor.

SQL> GRANT USER_GRANT_READ_ONLY TO TESTUSER3;

Grant succeeded.

2.4. Validate privilege on ROLE.

SQL> COL GRANTEE FOR A15
SQL> COL GRANTED_ROLE FOR A21
SQL> SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='TESTUSER3';

GRANTEE         GRANTED_ROLE
--------------- ---------------------
TESTUSER3       USER_GRANT_READ_ONLY

1 row selected.

2.5. Validate access: Open a new terminal and connect to user TESTUSER3 then validate access.

SQL> CONN TESTUSER3/XXXXXXXX
Connected.
SQL> SHOW USER
USER is "TESTUSER3"
SQL> SELECT COUNT(*) FROM USER_GRANT.TAB_ROLE;

  COUNT(*)
----------
        11

Click here to knoe more about How to Find Role Creation Date in Oracle.

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DATABASE, ORACLE, ROLE, SCRIPTS, SECURITY

How to Grant READ ONLY Access on Schema Using ROLE in Oracle

Related Articles