DEMO : HOW TO GRANT THE SELECT PRIVILEGE ON A VIEW TO THIRD PARTY USERS, SYMPTOMS
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
Reproducing the "ORA-01720: grant option does not exist" error
-- Table Owner(TBOW) grants the SELECT privilege to View Owner(VWOW) without issuing "WITH GRANT OPTION" keyword
SQL> CONNECT TBOW/DEMO
Connected.
SQL> SHO USER
USER is "TBOW"
SQL> GRANT SELECT ON TBOW.DUMMY_TABLE TO VWOW;
Grant succeeded.
-- View Owner (VWOW) creates 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.
SQL> SELECT * FROM VWOW.DUMMY_VIEW;
COL1
----------
1
-- View Owner(VWOW) grants the SELECT privilege on a view to Third Party User(GR1)
SQL> SHOW USER
USER is "VWOW"
SQL> GRANT SELECT ON VWOW.DUMMY_VIEW TO GR1;
GRANT SELECT ON VWOW.DUMMY_VIEW TO GR1
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'TBOW.DUMMY_TABLE'The GRANT statement failed because Table Owner(TBOW) provided the SELECT privilege on its TBOW.DUMMY_TABLE table to View Owner(VWOW) without specifying WITH GRANT OPTION clause.
-- 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 NOThat's why View Owner gets ORA-01720 error when granting the SELECT privilege on its VWOW.DUMMY_VIEW view to anyone else.
Even if Third Party User(GR1) already has the SELECT privilege on the same table the view is based on, View Owner(VWOW) is still not allowed to grant the SELECT privilege on its view to anyone else, for instance
-- Table Owner(TBOW) grants the SELECT privilege on its table to Third Party User(GR1)
SQL> CONNECT TBOW/DEMO
Connected.
SQL> GRANT SELECT ON TBOW.DUMMY_TABLE TO GR1;
Grant succeeded.
-- Current privilegies on the TBOW.DUMMY_TABLE table
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 NO
TBOW DUMMY_TABLE TBOW GR1 SELECT NO
-- Verify whether Third Party User(GR1) is able to query the table
SQL> CONNECT GR1/DEMO
Connected.
SQL> SELECT * FROM TBOW.DUMMY_TABLE;
COL1
----------
1
-- View Onwer(VWOW) grants the SELECT privilege on tis view to Third Party User(GR1). But it fails.
SQL> CONNECT VWOW/DEMO
Connected.
SQL> SHOW USER
USER is "VWOW"
SQL> GRANT SELECT ON VWOW.DUMMY_VIEW TO GR1;
GRANT SELECT ON VWOW.DUMMY_VIEW TO GR1
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'TBOW.DUMMY_TABLE'
Even though with SYS user it’s not possible to grant the SELECT privilege on a view to Third Party User.
-- Connect as SYS user
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHO USER
USER is "SYS"
-- SYS user grants the SELECT privilege on the view to Third Party User(GR1)
SQL> GRANT SELECT ON VWOW.DUMMY_VIEW TO GR1;
Grant succeeded.
-- Current privilegies on the TBOW.DUMMY_TABLE table
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
TBOW DUMMY_TABLE TBOW GR1 SELECT NO
VWOW DUMMY_VIEW VWOW GR1 SELECT NODespite the command was succeeded and now Third Party User(GR1) has the SELECT privilege on the view, Third Party User(GR1) still cannot issue the SELECT statement on the view. Third Party User will get ORA-01031 in this case. The reason is the same - Missing WITH GRANT OPTION privilege for View Owner(VWOW).
SQL> CONNECT GR1/DEMO
Connected.
SQL> SELECT * FROM VWOW.DUMMY_VIEW;
SELECT * FROM VWOW.DUMMY_VIEW
*
ERROR at line 1:
ORA-01031: insufficient privilegesThat'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;
*/
In summary, when WITH GRANT OPTION is not present for all underlying objects of a view, performing GRANT on a view to anyone else will cause either ORA-01720 or ORA-01031 errors.
