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;
DROP USER PROW 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;

-- A user with  GRANT ANY OBJECT PRIVILEGE system privilege

CREATE USER PROW IDENTIFIED BY DEMO; 
GRANT CONNECT TO PROW; 
GRANT GRANT ANY OBJECT PRIVILEGE TO PROW; 
GRANT SELECT ON DBA_TAB_PRIVS TO PROW;
GRANT SELECT ON DBA_SYS_PRIVS TO PROW;

-- 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) without specifying WITH GRANT OPTION clause

SQL> CONNECT TBOW/DEMO
Connected.

SQL> SHO USER
USER is "TBOW"

SQL> GRANT SELECT ON TBOW.DUMMY_TABLE TO VWOW;

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 
       TABLE_NAME IN ('DUMMY_TABLE','DUMMY_VIEW');


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

As View Owner(VWOW) create a view

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.

As a user with the GRANT ANY OBJECT PRIVILEGE system privilege, grant the SELECT privilege on all underlying objects of a view to a View Owner with specifying WITH GRANT OPTION clause

-- To find all users with GRANT ANY OBJECT PRIVILEGE system privilege

SET LINES 300
SET PAGES 999
COL GRANTEE FOR A30 
COL PRIVILEGE FOR A30
COL ADMIN_OPTION FOR A15 
SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE='GRANT ANY OBJECT PRIVILEGE';

GRANTEE                        PRIVILEGE                      ADMIN_OPTION    COM INH
------------------------------ ------------------------------ --------------- --- ---
PROW                           GRANT ANY OBJECT PRIVILEGE     NO              NO  NO
SYS                            GRANT ANY OBJECT PRIVILEGE     NO              YES YES
DATAPUMP_IMP_FULL_DATABASE     GRANT ANY OBJECT PRIVILEGE     NO              YES YES
DBA                            GRANT ANY OBJECT PRIVILEGE     NO              YES YES
IMP_FULL_DATABASE              GRANT ANY OBJECT PRIVILEGE     NO              YES YES
EM_EXPRESS_ALL                 GRANT ANY OBJECT PRIVILEGE     NO              YES YES

6 rows selected.

Among of users we can see the previously created Proxy Owner(PROW) user. So let’s use that user for granting missing object privilege

SQL> CONNECT PROW/DEMO
Connected.

SQL> SHOW USER
USER is "PROW"

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 
       TABLE_NAME IN ('DUMMY_TABLE','DUMMY_VIEW');


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

Now as GRANT OPTION is presented for View Owner(VWOW) the one is allowed to grant the SELECT privilege on its view to Third Party User(GR1).

SQL> CONNECT VWOW/DEMO
Connected.

SQL> SHO USER
USER is "VWOW"

SQL>  GRANT SELECT ON VWOW.DUMMY_VIEW TO GR1;

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 
       TABLE_NAME IN ('DUMMY_TABLE','DUMMY_VIEW');


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

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;
DROP USER PROW CASCADE;
*/

Written At

25 SEPTEMBER 202218:30

Red Hat Release

7.x x64

Database Release

19.13.0.0.0