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

Normaly you should NOT grant SELECT ANY TABLE to an ordinary database user

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) 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 privileged user grant the SELECT ANY TABLE system privilege to Third Party User(GR1).

SQL> CONNECT / AS SYSDBA
Connected.

SQL> SHOW USER
USER is "SYS"

SQL> GRANT SELECT ANY TABLE TO GR1;

Grant succeeded.

As Third Party User(GR1) has now SELECT ANY TABLE system privilege the one can query any table or view. So there is no need to grant the SELECT privilege on the view to Third Party User in this case.

 
Connect as Third Party User(GR1) and 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