The WITH GRANT OPTION clause
The owner of an object can grant it to another user by specifying the WITH GRANT OPTION clause in the GRANT statement. In this case, the new grantee can then grant the same level of access to other users or roles. Here are three points to keep in mind about the WITH GRANT OPTION clause:
You cannot grant WITH GRANT OPTION to a role.
If you revoke access to a user who had been granted access to an object WITH GRANT OPTION, and that user had granted access to another user, both sets of grants will be revoked.
The WITH GRANT OPTION does not come automatically with the system privileges listed in Table 15.4 that allow you to manipulate objects in any schema. Thus, although a DBA can create a table in someone else’s schema through the CREATE ANY TABLE system privilege, and you can SELECT, INSERT, UPDATE, or DELETE from it through the SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, and DELETE ANY TABLE system privileges, you cannot grant access to the table to any other user or role.
Get Oracle Database Administration: The Essential Refe now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
