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

SYMPTOMS
When the owner of a view granting the SELECT privilege on its view to another user the following error is encountered

ORA-01720: grant option does not exist for 'schema_name.table_name'

CAUSE
When a view is based on the objects of other schemas it is not allowed for the view’s owner to grant the SELECT privilege on its view to any other user until one has been granted to access the underlying objects with WITH GRANT OPTION privilege. And it does not matter whether a THIRD PARTY user already has the SELECT privileges on all underlying objects of such a view.

What the 12c Oracle DOCUMENTATION says:

To grant an object privilege on all other types of objects, you must own the object, or the owner of the object must have granted you the object privileges with the WITH GRANT OPTION, or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you have the GRANT ANY OBJECT PRIVILEGE, then you can grant the object privilege only if the object owner could have granted the same object privilege. In this case, the GRANTOR column of the *_TAB_PRIVS views displays the object owner rather than the user who issued the GRANT statement.

NOTE:

To grant SELECT on a view to another user, either you must own all of the objects underlying the view or you must have been granted the SELECT object privilege WITH GRANT OPTION on all of those underlying objects. This is true even if the grantee already has SELECT privileges on those underlying objects.
To grant READ on a view to another user, either you must own all of the objects underlying the view or you must have been granted the READ or SELECT object privilege WITH GRANT OPTION on all of those underlying objects. This is true even if the grantee already has the READ or SELECT privilege on those underlying objects.

WITH GRANT OPTION

Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles.

If you grant an object privilege to a user without specifying WITH GRANT OPTION, and then subsequently grant the privilege to the user WITH GRANT OPTION, then the user has the GRANT OPTION on the privilege.

To revoke the GRANT OPTION on an object privilege from a user, you must revoke the privilege from the user altogether and then grant the privilege to the user without the GRANT OPTION.

Restriction on Granting WITH GRANT OPTION
You can specify WITH GRANT OPTION only when granting to a user or to PUBLIC, not when granting to a role.

GRANT ANY OBJECT PRIVILEGE

Grant any object privilege that the object owner is permitted to grant.

Revoke any object privilege that was granted by the object owner or by some other user with the GRANT ANY OBJECT PRIVILEGE privilege.

SOLUTION
There are 3 ways to accomplish the task:

1. A user who has a view based on objects of other schemas must be granted with WITH GRANT OPTION privilege on those objects. The owners of the underlying view’s objects must provide those privileges.

See TESTCASE 1 section

2. A user who has a view based on objects of other schemas must be granted with WITH GRANT OPTION privilege on those objects.
A user with the GRANT ANY OBJECT PRIVILEGE(GAOP) system privilege must grant the WITH GRANT OPTION privilege to the view owner on all underlying view’s objects.
A user with GAOP system privilege can be considered as the Proxy Owner of all underlying view’s objects.

See TESTCASE 2 section

3. it’s also possible by the SYS user to grant the SELECT ANY TABLE privilege to the THIRD PARTY user but you’d better never do that.

See TESTCASE 3 section

CREATE OBJECTS FOR TESTCASES

-- TO DROP TESTCASE USERS IF NEEDED
-- DROP USER T1 CASCADE;
-- DROP USER T2 CASCADE;
-- DROP USER T3 CASCADE;
-- DROP USER T4 CASCADE;
-- DROP USER PO CASCADE;

-- THE TABLE OWNER
CREATE USER T1 IDENTIFIED BY T1; 
GRANT UNLIMITED TABLESPACE TO T1;
GRANT CONNECT, CREATE TABLE TO T1;
GRANT SELECT ON DBA_TAB_PRIVS TO T1;


-- THE VIEW OWNER
CREATE USER T2 IDENTIFIED BY T2; 
GRANT CONNECT, CREATE VIEW TO T2;
GRANT SELECT ON DBA_TAB_PRIVS TO T2;
GRANT SELECT ON DBA_SYS_PRIVS TO T2;

-- THE THIRD PARTY USER WHO WANTS TO ACCESS THE VIEW 
CREATE USER T3 IDENTIFIED BY T3; 
GRANT CONNECT TO T3;
GRANT SELECT ON DBA_TAB_PRIVS TO T3;

-- ANOTHER THIRD PARTY USER WHO WANTS TO ACCESS THE VIEW 
CREATE USER T4 IDENTIFIED BY T4; 
GRANT CONNECT TO T4;

-- THE PROXY OWNER WHO CAN GRANT REQUIRED PRIVILEGES ON OBJECTS TO ANYONE
CREATE USER PO IDENTIFIED BY PO; 
GRANT CONNECT TO PO; 
GRANT GRANT ANY OBJECT PRIVILEGE TO PO; 
GRANT SELECT ON DBA_TAB_PRIVS TO PO;
GRANT SELECT ON DBA_SYS_PRIVS TO PO;

-- THE TABLE FOR THE TESTCASES
CONNECT T1/T1
CREATE TABLE T1.T1(COL1 NUMBER);
INSERT INTO T1.T1 VALUES(1);
COMMIT;

-- THE TABLE CONTENT
SQL> SELECT * FROM T1.T1;

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

So now we are ready to perform the testcases.

TO REPRODUCE THE ISSUE
The TABLE OWNER(T1) has granted the SELECT privilege to the VIEW OWNER(T2) without WITH GRANT OPTION privilege. When the VIEW OWNER(T2) tries to grant the SELECT privilege on its VIEW(T2.V2) to the THIRD PARTY(T3) user it fails.

-- THE TABLE OWNER(T1) GRANTS THE SELECT PRIVILEGE TO THE VIEW OWNER(T2) WITHOUT "WITH GRANT OPTION"

SQL> CONNECT T1/T1
Connected.
SQL> SHO USER
USER is "T1"
SQL> GRANT SELECT ON T1.T1 TO T2;

Grant succeeded.

-- THE VIEW OWNER(T2) CREATES ITS VIEW

SQL> CONNECT T2/T2
SQL> SHO USER
USER is "T2"
SQL> CREATE VIEW T2.V2 AS SELECT * FROM T1.T1;

View created.

SQL> SELECT * FROM T2.V2;

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

-- THE VIEW OWNER(T2) GRANTS THE SELECT PRIVILEGE ON THE VIEW TO THE THIRD PARTY(T3) USER

SQL> SHO USER
USER is "T2"
SQL> GRANT SELECT ON T2.V2 TO T3;
GRANT SELECT ON T2.V2 TO T3
                   *
ERROR at line 1:
ORA-01720: grant option does not exist for 'T1.T1'

As expected it’s failed. It is because the TABLE OWNER(T1) has not provided the required privilege to the VIEW OWNER(T2) on its TABLE(T1.T1).

SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL TABLE_NAME FOR A10
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='T1' AND TABLE_NAME ='T1';


OWNER      TABLE_NAME GRANTOR    GRANTEE              PRIVILEGE  GRANTABLE
---------- ---------- ---------- -------------------- ---------- ----------
T1         T1         T1         T2                   SELECT     NO

Here the VIEW OWNER(T2) (GRANTEE) has the SELECT privilege but the one has been granted without WITH GRANT OPTION (GRANTABLE=NO).
As the SECOND USER(T2) does not have the required privilege so the one is not allowed to grant the VIEW(T2.V2) based on that TABLE(T1.T1) to anyone else.

Even when the THIRD PARTY(T3) user already has the SELECT privilege on the same table(s) the VIEW OWNER(T2) is still not allowed to grant the SELECT privilege on its VIEW(T2.V2) to the THIRD PARTY(T3) user, for instance

-- AS THE TABLE OWNER(T1), GRANT THE SELECT PRIVILEGE ON ITS TABLE TO THE THRID PARTY(T3) USER

SQL> CONNECT T1/T1
Connected.
SQL> GRANT SELECT ON T1.T1 TO T3;

Grant succeeded.

-- AS THE VIEW OWNER(T2), GRANT THE SELECT PRIVILEGE ON ITS VIEW TO THE THIRD PARTY(T3) USER

SQL> CONNECT T2/T2
Connected.
SQL> GRANT SELECT ON T2.V2 TO T3;
GRANT SELECT ON T2.V2 TO T3
                   *
ERROR at line 1:
ORA-01720: grant option does not exist for 'T1.T1'

-- THE VIEW OWNER(T2) IS UNABLE TO GRANT THE SELECT PRIVILEGE ON ITS VIEW(T2.V2) TO THE THIRD PARTY(T3) USER 
-- DESPITE THE ONE(T3) ALREADY HAS THE SELECT PRIVILEGE ON THE SAME TABLE(T1.T1)

SQL> CONNECT T3/T3
Connected.

SQL> SELECT * FROM T1.T1;

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

SQL> SELECT OWNER,TABLE_NAME,GRANTOR,GRANTEE,PRIVILEGE,GRANTABLE FROM DBA_TAB_PRIVS WHERE OWNER='T1';

OWNER      TABLE_NAME GRANTOR    GRANTEE              PRIVILEGE  GRANTABLE
---------- ---------- ---------- -------------------- ---------- ----------
T1         T1         T1         T2                   SELECT     NO
T1         T1         T1         T3                   SELECT     NO

Even though with the SYS user it’s not possible to grant the SELECT privilege on the VIEW(T2.V2) to the THIRD PARTY(T3) user.

-- CONNECT AS THE SYS USER

SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHO USER
USER is "SYS"

-- GRANT THE SELECT PRIVILEGE ON THE VIEW(T2.V2) TO THIRD PARTY(T3) USER

SQL> GRANT SELECT ON T2.V2 TO T3;

Grant succeeded.

Despite the command was succeeded the THIRD PARTY(T3) user still cannot issue the SELECT statement against the VIEW(T2.V2).

SQL> CONNECT T3/T3
Connected.
SQL> SELECT * FROM T2.V2;
SELECT * FROM T2.V2
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

So, in this section, it has been shown what error can be encountered if the SELECT privilege has been granted without WITH GRANT OPTION keyword.

TESTCASE 1
Recreate the users by following the CREATE OBJECTS FOR TESTCASES section again.

-- DROP USER T1 CASCADE;
-- DROP USER T2 CASCADE;
-- DROP USER T3 CASCADE;
-- DROP USER T4 CASCADE;
-- DROP USER PO CASCADE;

Right after the users have been recreated connect as the TABLE OWNER(T1) and grant the SELECT privilege to the VIEW OWNER(T2) with WITH GRANT OPTION keyword.

SQL> CONNECT T1/T1
Connected.
SQL> SHO USER
USER is "T1"
SQL> GRANT SELECT ON T1.T1 TO T2 WITH GRANT OPTION;

Grant succeeded.

Next, as the VIEW OWNER(T2) create the VIEW(T2.V2) and grant the SELECT privilege to the THIRD PARTY(T3) user. It will be succeded.

SQL> CONNECT T2/T2
SQL> SHO USER
USER is "T2"
SQL> CREATE VIEW T2.V2 AS SELECT * FROM T1.T1;

SQL> SELECT * FROM T2.V2;

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

SQL> GRANT SELECT ON T2.V2 TO T3;

Grant succeeded.

Connect as the THIRD PARTY(T3) user and verify that the one is allowed to query against the VIEW(T2.V2).

SQL> CONNECT T3/T3
Connected.
SQL> SELECT * FROM T2.V2;

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

It works as all required privileges are in place.

SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL TABLE_NAME FOR A10
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 IN ('T1','T2');

OWNER      TABLE_NAME GRANTOR    GRANTEE              PRIVILEGE  GRANTABLE
---------- ---------- ---------- -------------------- ---------- ----------
T1         T1         T1         T2                   SELECT     YES
T2         V2         T2         T3                   SELECT     NO

Here the VIEW OWNER(T2) (GRANTEE) has the SELECT privilege on the TABLE(T1.T1) with WITH GRANT OPTION (GRANTABLE=YES). It lets the VIEW OWNER(T2) grant the SELECT privilege on its VIEW(T2.V2) to any database user.
In contrast, the THIRD PARTY(T3) user is not allowed (GRANTABLE=NO) to grant the SELECT privilege on the VIEW(T2.V2) to any other users.

SQL> CONNECT T3/T3
Connected.
SQL> SHO USER
USER is "T3"

SQL> GRANT SELECT ON T2.V2 TO T4;
GRANT SELECT ON T2.V2 TO T4
                   *
ERROR at line 1:
ORA-01031: insufficient privileges

TESTCASE 2
In this TESTCASE 2 the Proxy Owner(PO) user is being used as the one who provides required grants.

Recreate the users by following the CREATE OBJECTS FOR TESTCASES section again.

-- DROP USER T1 CASCADE;
-- DROP USER T2 CASCADE;
-- DROP USER T3 CASCADE;
-- DROP USER T4 CASCADE;
-- DROP USER PO CASCADE;

Right after the users have been recreated connect as the TABLE OWNER(T1) and grant the SELECT privilege to the VIEW OWNER(T2) without WITH GRANT OPTION keyword.

SQL> CONNECT T1/T1
Connected.
SQL> SHO USER
USER is "T1"
SQL> GRANT SELECT ON T1.T1 TO T2;

Grant succeeded.

Next, as the VIEW OWNER(T2) create the VIEW(T2.V2) and grant the SELECT privilege to the THIRD PARTY(T3) user. It will fail.

-- THE VIEW OWNER(T2) CREATES ITS VIEW

SQL> CONNECT T2/T2
Connected.
SQL> SHO USER
USER is "T2"
SQL> CREATE VIEW T2.V2 AS SELECT * FROM T1.T1;

View created.

SQL> SELECT * FROM T2.V2;

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

-- THE VIEW OWNER(T2) GRANTS THE SELECT PRIVILEGE ON THE VIEW TO THE THIRD PARTY(T3) USER

SQL> SHO USER
USER is "T2"
SQL> GRANT SELECT ON T2.V2 TO T3;
GRANT SELECT ON T2.V2 TO T3
                   *
ERROR at line 1:
ORA-01720: grant option does not exist for 'T1.T1'

By reviewing the available grants from the DBA_TAB_PRIVS for the underlying view’s objects you realized that there are missing grants

SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL TABLE_NAME FOR A10
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='T1' AND TABLE_NAME ='T1';


OWNER      TABLE_NAME GRANTOR    GRANTEE              PRIVILEGE  GRANTABLE
---------- ---------- ---------- -------------------- ---------- ----------
T1         T1         T1         T2                   SELECT     NO

At this point, you need to provide the required privilege. In the TESTCASE 2 you don’t use the actual TABLE OWNER(T1) for that purpose but the Proxy Owner(PO) user.
The Proxy Owner is any user with the GRANT ANY OBJECT PRIVILEGE system privilege.
To find out such a user the following query can be used

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

GRANTEE                        PRIVILEGE                                ADMIN_OPTI
------------------------------ ---------------------------------------- ----------
DATAPUMP_IMP_FULL_DATABASE     GRANT ANY OBJECT PRIVILEGE               NO
USER_B                         GRANT ANY OBJECT PRIVILEGE               NO
DBA                            GRANT ANY OBJECT PRIVILEGE               YES
SYS                            GRANT ANY OBJECT PRIVILEGE               NO
PO                             GRANT ANY OBJECT PRIVILEGE               NO
IMP_FULL_DATABASE              GRANT ANY OBJECT PRIVILEGE               NO

6 rows selected.

The query lists a number of users with the required system privilege. Among of those we can see the previously created Proxy Owner(PO) user.
So let’s use that user for granting missing object privilege

SQL> CONNECT PO/PO
Connected.
SQL> SHO USER
USER is "PO"

SQL> GRANT SELECT ON T1.T1 TO T2 WITH GRANT OPTION;

Grant succeeded.

Verify that the VIEW OWNER(T2) has the former privelege(GRANTABLE=YES) on the TABLE(T1.T1)

SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL TABLE_NAME FOR A10
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='T1' AND TABLE_NAME ='T1';


OWNER      TABLE_NAME GRANTOR    GRANTEE              PRIVILEGE  GRANTABLE
---------- ---------- ---------- -------------------- ---------- ----------
T1         T1         T1         T2                   SELECT     YES

Next, as the VIEW OWNER(T2), grant the SELECT privilege on its VIEW(T2.V2) to the THIRD PARTY(T3) user. It will be succeded.

SQL> CONNECT T2/T2
Connected.
SQL> SHO USER
USER is "T2"
SQL> GRANT SELECT ON T2.V2 TO T3;

Grant succeeded.

Connect as the THIRD PARTY(T3) user and verify that the one is allowed to query against the VIEW(T2.V2).

SQL> CONNECT T3/T3
Connected.
SQL> SELECT * FROM T2.V2;

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

It works as all required privileges are in place.

SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL TABLE_NAME FOR A10
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 IN ('T1','T2');

OWNER      TABLE_NAME GRANTOR    GRANTEE              PRIVILEGE  GRANTABLE
---------- ---------- ---------- -------------------- ---------- ----------
T1         T1         T1         T2                   SELECT     YES
T2         V2         T2         T3                   SELECT     NO

SQL> CONNECT T3/T3
Connected.
SQL> SHO USER
USER is "T3"

SQL> GRANT SELECT ON T2.V2 TO T4;
GRANT SELECT ON T2.V2 TO T4
                   *
ERROR at line 1:
ORA-01031: insufficient privileges

TESTCASE 3
As workaround it’s also possible by the SYS user to grant the SELECT ANY TABLE privilege to the THIRD PARTY(T3) user.
But you must keep in mind that YOU’D BETTER NOT TO DO IT AS IT WOULD BE A HUGE SECURITY HOLE

Recreate the users by following the CREATE OBJECTS FOR TESTCASES section again.

-- DROP USER T1 CASCADE;
-- DROP USER T2 CASCADE;
-- DROP USER T3 CASCADE;
-- DROP USER T4 CASCADE;
-- DROP USER PO CASCADE;

Right after the users have been recreated connect as the TABLE OWNER(T1) and grant the SELECT privilege to the VIEW OWNER(T2) without WITH GRANT OPTION keyword.

SQL> CONNECT T1/T1
Connected.
SQL> SHO USER
USER is "T1"
SQL> GRANT SELECT ON T1.T1 TO T2;

Grant succeeded.

Next, as the VIEW OWNER(T2) create the VIEW(T2.V2) and grant the SELECT privilege to the THIRD PARTY(T3) user. It will fail.

-- THE VIEW OWNER(T2) CREATES ITS VIEW

SQL> CONNECT T2/T2
SQL> SHO USER
USER is "T2"
SQL> CREATE VIEW T2.V2 AS SELECT * FROM T1.T1;

View created.

SQL> SELECT * FROM T2.V2;

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

-- THE VIEW OWNER(T2) GRANTS THE SELECT PRIVILEGE ON THE VIEW TO THE THIRD PARTY(T3) USER

SQL> SHO USER
USER is "T2"
SQL> GRANT SELECT ON T2.V2 TO T3;
GRANT SELECT ON T2.V2 TO T3
                   *
ERROR at line 1:
ORA-01720: grant option does not exist for 'T1.T1'

At this point, you wish to apply a quick fix to eliminate spending time for the investigation about missing privileges. For that purpose, by the user SYS you can grant the GRANT SELECT ANY TABLE privilege to the THIRD PARTY(T3) user.

SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHO USER
USER is "SYS"

SQL> GRANT SELECT ANY TABLE TO T3;

Grant succeeded.

Connect as the THIRD PARTY(T3) user and verify that the one is allowed to query the VIEW(T2.V2).

SQL> CONNECT T3/T3
Connected.
SQL> SHO USER
USER is "T3"
SQL> SELECT * FROM T2.V2;

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

It works as the THIRD PARTY(T3) user has been granted the GRANT SELECT ANY TABLE privilege that allows one to query against any table/view/matview of others schemas.

NOTE:

Think twice before granting the GRANT SELECT ANY TABLE privilege to anyone as it would be a huge security hole.

REFERENCES

Grant Object Privileges on Another Schema Object to Other Users as SYSTEM or SYS (Doc ID 107843.1)
https://blogs.oracle.com/sql/how-to-create-users-grant-them-privileges-and-remove-them-in-oracle-database

Version : 1:35 17.01.2021
Database : 11.2.0.4.0, 12.1.0.1.0

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

Tags In

Categories