HOW TO GRANT THE SELECT PRIVILEGE ON A VIEW TO THIRD PARTY USERS

Users and Objects for the DEMO

-- To drop DEMO users if needed

/*
CONNECT / AS SYSDBA;
DROP USER TBOW CASCADE;
DROP USER VWOW CASCADE;
DROP USER GR1 CASCADE;
*/

-- A table owner

CREATE USER TBOW IDENTIFIED BY DEMO; 
GRANT UNLIMITED TABLESPACE TO TBOW;
GRANT CONNECT, CREATE TABLE TO TBOW;
GRANT SELECT ON DBA_TAB_PRIVS TO TBOW;

-- A view owner

CREATE USER VWOW IDENTIFIED BY DEMO; 
GRANT CONNECT, CREATE VIEW TO VWOW;
GRANT SELECT ON DBA_TAB_PRIVS TO VWOW;
GRANT SELECT ON DBA_SYS_PRIVS TO VWOW;

-- Third party user to access a view

CREATE USER GR1 IDENTIFIED BY DEMO; 
GRANT CONNECT TO GR1;
GRANT SELECT ON DBA_TAB_PRIVS TO GR1;

-- Table for the DEMO

CONNECT TBOW/DEMO
CREATE TABLE TBOW.DUMMY_TABLE(COL1 NUMBER);
INSERT INTO TBOW.DUMMY_TABLE VALUES(1);
COMMIT;

-- Table content

SQL> SELECT * FROM TBOW.DUMMY_TABLE;

      COL1
----------
         1

As Table Owner(TBOW) grant the SELECT privilege on the table to View Owner(VWOW) with specifying WITH GRANT OPTION clause

SQL> CONNECT TBOW/DEMO
Connected.

SQL> SHOW USER
USER is "TBOW"

SQL> GRANT SELECT ON TBOW.DUMMY_TABLE TO VWOW WITH GRANT OPTION;

Grant succeeded.

-- GRANTABLE column indicates whether the privilege was granted with the GRANT OPTION (YES) or not (NO)

SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL TABLE_NAME FOR A15
COL GRANTEE FOR A20
COL GRANTOR FOR A10
COL PRIVILEGE FOR A10
COL GRANTABLE FOR A10

SELECT OWNER,
       TABLE_NAME,
       GRANTOR,
       GRANTEE,
       PRIVILEGE,
       GRANTABLE
FROM 
       DBA_TAB_PRIVS 
WHERE 
       OWNER='TBOW' AND TABLE_NAME ='DUMMY_TABLE';

OWNER      TABLE_NAME      GRANTOR    GRANTEE              PRIVILEGE  GRANTABLE
---------- --------------- ---------- -------------------- ---------- ----------
TBOW       DUMMY_TABLE     TBOW       VWOW                 SELECT     YES

As View Owner(VWOW) create a view and grant the SELECT privilege on the view to Third Party User(GR1). It will succeed as View Owner has GRANT OPTION on underlying table.

SQL> CONNECT VWOW/DEMO
Connected.

SQL> SHO USER
USER is "VWOW"

SQL> CREATE VIEW VWOW.DUMMY_VIEW AS SELECT * FROM TBOW.DUMMY_TABLE;

View created.

SQL> SELECT * FROM VWOW.DUMMY_VIEW;

      COL1
----------
         1

SQL> GRANT SELECT ON VWOW.DUMMY_VIEW TO GR1;

Grant succeeded.

Connect as Third Party User(GR1) and verify that the one is allowed to select from the view

SQL> CONNECT GR1/DEMO
Connected.

SQL> SHOW USER
USER is "GR1"

SQL> SELECT * FROM VWOW.DUMMY_VIEW;

      COL1
----------
         1

That's it. It works. Drop DEMO users.

-- To drop DEMO users if needed

/*
CONNECT / AS SYSDBA;
DROP USER TBOW CASCADE;
DROP USER VWOW CASCADE;
DROP USER GR1 CASCADE;
*/

Written At

25 SEPTEMBER 202218:30

Red Hat Release

7.x x64

Database Release

19.13.0.0.0