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

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


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

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

fifteen − 1 =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

NOTE: Excuse us for spammer/promoter i.e don't join the group for spam, will be kicked off without warnings.

Thanks.
Team DBsGuru.

Share Learn Grow!

Welcome to DBsGuru! We wish you a very healthy day, hope and pray things to go in a good way for all of humanity. Stay safe!

We encourage technology experts to contribute share technical knowledge in form of writing technical articles/blogs, SQL commands for daily usage (basic to a high level), Carrier guidance on any technology, and become an author.

We have a ready platform for you with no profit no loss (as of now, in the future you may also earn revenue) if you are ready to contribute to writing articles, click on the registration link and the article will be published as an individual contributor on your name.

Click here for registration

Thanks,
Team DBsGuru
We Commit We Deliver