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.
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
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.
97 Total Views, 4 Views Today
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?
Hello and welcome to DBsGuru,
DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training.
Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.
Thanks for the visits!
Share Learn Grow!