HOW TO GRANT THE SELECT PRIVILEGE ON A VIEW TO THIRD PARTY USERS
When an Owner of a View grants 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'
Look DEMO for details
When a View is based on objects that are located in other schemas it is not allowed for a View Owner to grant the SELECT privilege on its view to any other user (THIRD PARTY user) if previously the SELECT permission to all underlying objects of a view were granted without WITH GRANT OPTION clause. And it's TRUE even if THIRD PARTY user already has the SELECT privileges on all underlying objects of such a view.
What the 19c 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.
There are 3 ways to provide the SELECT privilege on a view to a Third Party User.
1. Grant the SELECT privilege on all underlying objects of a view to a View Owner with WITH GRANT OPTION clause. See Test Case #1
2. 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 WITH GRANT OPTION clause. See Test Case #2
3. Grant the SELECT ANY TABLE system privilege to a Third Party User. See Test Case #3
- Bash Scripts
- Default Category
- Directory Objects
- Environment Variables
- Initialization Parameters
- Java Program
- Memory Usage
- Metadata API
- NLS Settings
- Optimizer Statistics
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Red Hat Enterprise Linux
- Redo Logs
- Session Tracing
- SQL Trace
- Wait Events