The WITH ADMIN OPTION Clause in Oracle AI Database 26ai

WITH ADMIN OPTION is one of the oldest privilege-delegation mechanisms in Oracle, well established by the time of Oracle 8i and likely older still; a delegation feature this fundamental tends to predate most of what a course like this one needs to cover as version-specific. Its actual behavior remains exactly what it has always been: a user who receives a privilege WITH ADMIN OPTION can, in turn, grant that same privilege to others.

System privileges are usually granted by a DBA, but the DBA isn't the only person who can do it. Anyone holding a specific privilege WITH ADMIN OPTION can grant that one privilege onward, and anyone holding GRANT ANY PRIVILEGE can grant nearly any system privilege at all. Trusting a specific user with one of these paths, rather than routing every grant through the DBA personally, is exactly what this lesson covers.

To grant a privilege and let the recipient further grant that same privilege to others, add WITH ADMIN OPTION to the end of the GRANT command:

GRANT CREATE USER, CREATE SESSION TO jeff WITH ADMIN OPTION;

Jeff can now create users himself, let them connect to the database, and pass the admin option along to those users in turn if he chooses. The diagram below shows exactly this branching in practice.

One behavior worth understanding precisely, since it comes up constantly once privileges start flowing through more than one person: a privilege granted through the admin option stays with the recipient even if it is later revoked from whoever did the granting. Looking at the diagram above, revoking CREATE SESSION from Jeff would not affect Jenny or Ashley at all; each of their grants exists independently once made; the DBA would need to revoke from Jenny and Ashley separately to remove it from them too.

The general form of GRANT for system privileges and roles:

GRANT {system_privilege | role}
[, {system_privilege | role}, ...]
TO {user | role} [, {user | role}] ...
[WITH ADMIN OPTION];

The first part lists one or more system privileges or roles, comma-separated. TO identifies one or more users or roles receiving them. WITH ADMIN OPTION, if included, lets every recipient further grant whatever was just granted to still other users or roles. Using this command, you can grant a system privilege or role to a specific user, to a role, or to PUBLIC, and a grantor with the appropriate authority can revoke a role from a user the same way a privilege is revoked.

WITH ADMIN OPTION on a single privilege only lets the recipient manage that one privilege. For someone who genuinely needs broader delegation authority, GRANT ANY PRIVILEGE grants the ability to grant or revoke nearly any system privilege at all, including granting to roles:

GRANT GRANT ANY PRIVILEGE TO security_admin;

With this in place, security_admin can run statements like:

GRANT CREATE SESSION, CREATE VIEW TO app_user;
REVOKE ALTER USER FROM app_user;

If security_admin should also be able to pass this same broad authority on to someone else, add the admin option to the grant of GRANT ANY PRIVILEGE itself:

GRANT GRANT ANY PRIVILEGE TO security_admin WITH ADMIN OPTION;

GRANT ANY PRIVILEGE is genuinely powerful and should be reserved for trusted security administrators, never for ordinary application accounts.

Rather than granting several delegation-related privileges to each security administrator individually, bundling them into a dedicated role is usually cleaner:

CREATE ROLE privilege_admin;

GRANT GRANT ANY PRIVILEGE TO privilege_admin;
GRANT GRANT ANY ROLE TO privilege_admin;
GRANT CREATE USER, ALTER USER, DROP USER TO privilege_admin WITH ADMIN OPTION;

GRANT privilege_admin TO security_admin;

One nuance worth knowing before relying on this pattern: the grantee must actually enable the role, or have it set as a default role, before privileges granted through it become usable. If a system privilege reaches a user only through a role via GRANT ANY PRIVILEGE, that user has it only while the role is enabled in their session. Grant the privilege directly WITH ADMIN OPTION instead, and it stays available regardless of which roles happen to be enabled at the moment.

Schema-level privileges, covered earlier in this module, have their own delegation path, distinct from ordinary system privileges. To let someone grant schema-level privileges on a schema they do not themselves own, they need GRANT ANY SCHEMA PRIVILEGE, the full, three-word privilege name, or the broader GRANT ANY PRIVILEGE:

GRANT GRANT ANY SCHEMA PRIVILEGE TO security_admin;

A schema's own owner can already grant schema-level privileges on their own schema without needing either of these; this privilege only matters for granting on someone else's schema. This governs statements like GRANT SELECT ANY TABLE ON SCHEMA hr TO bob;, a schema privilege, not a classic system privilege, even though the syntax looks related.

Query the data dictionary directly to see who currently holds delegation authority, rather than relying on memory of who was granted what and when:

SELECT grantee, privilege, admin_option
FROM   dba_sys_privs
WHERE  privilege IN ('GRANT ANY PRIVILEGE', 'GRANT ANY SCHEMA PRIVILEGE', 'GRANT ANY ROLE')
ORDER BY grantee, privilege;

Any user showing up in this result with ADMIN_OPTION set to YES on one of these privileges can extend that same authority to still others, which makes this exactly the query worth running periodically as part of a security review.