ORA-01727 view authorization with grant option

ORA-01727 view authorization with grant option

ORA-01720: grant option does not exist for 'Scott. lock1'
This problem occurs when a view is granted to another user, and the view references the table under the third user.

For example:
I have three users: scott, roy, and test.
Create a view referenced to scott under roy.
Then grant the view access permission under roy to test
If you follow the normal authorization mode, a ORA-01720 error is reported.

Solution:
Add the with grant option when granting table access permissions under Table scott to the roy user.
In this way, when the view is granted the test permission, the scott table permission is automatically assigned to the test, so that no error occurs.

Lab test:
Environment local linux RedHat 5, Oracle 11gR2

1. Log On As A dba user. First try to create view roy. new_view, and an error is returned.
SQL> connect dbmgr/dbmgr;
Connected.

SQL> create view roy. new_view (id, comm) as select id, comm from scott. lock1;
Create view roy. new_view (id, comm) as select id, comm from scott. lock1
*
ERROR at line 1:
ORA-00942: table or view does not exist

2. After roy is granted normal permissions, the view is created successfully.
SQL> grant select on scott. lock1 to roy;

Grant succeeded.

SQL> create view roy. new_view (id, comm) as select id, comm from scott. lock1;

View created.

3. Assign view to test user, error ORA-01720
SQL> grant select on roy. new_view to test;
Grant select on roy. new_view to test
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'Scott. lock1'

4. Add the with grant option to roy and then assign the view to the test user.
SQL> grant select on scott. lock1 to roy with grant option;

Grant succeeded.

SQL> grant select on roy. new_view to test;

Grant succeeded.