Granting System Privileges in Oracle AI Database 26ai

The GRANT command is what actually assigns a system privilege to a user. The basic syntax is unchanged from earlier Oracle releases:

GRANT privilege[, privilege...] TO username
[WITH ADMIN OPTION];

username is the user receiving the privilege. WITH ADMIN OPTION additionally lets that user grant or revoke the same privilege to others, a delegation mechanism covered in more depth later in this module.

A representative example, granting several privileges to coin_admin in one statement:

GRANT CREATE SESSION, CREATE TABLE,
CREATE INDEX, ALTER SESSION TO coin_admin;

Working through each piece: GRANT is the command keyword. CREATE SESSION, CREATE TABLE, CREATE INDEX, and ALTER SESSION are the privileges being granted, listed as a comma-separated list following GRANT. TO coin_admin identifies the user receiving them.

Connect as a privileged user before attempting any of this: SYS, a user holding GRANT ANY PRIVILEGE, or a user who already holds the specific privilege being granted WITH ADMIN OPTION. Any one of these three is sufficient; you do not need all three.

Putting the pieces together, from creating a new user through granting both system and object privileges:

CREATE USER my_user IDENTIFIED BY SecurePass#2026;

GRANT CREATE SESSION TO my_user;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO my_user;
GRANT SELECT ON employees TO my_user;

The first statement creates the account. The second grants the ability to connect. The third grants a representative set of object-creation privileges. The fourth grants an object privilege, SELECT on a specific table, illustrating that system and object privileges are granted with closely related but distinct syntax: a system privilege names no object at all, while an object privilege always includes an ON clause naming exactly what it applies to.

ALL PRIVILEGES grants every system privilege in one statement, appropriate only for genuinely trusted administrative accounts:

GRANT ALL PRIVILEGES TO trusted_admin;

This does not grant literally everything, though, and the exclusions are worth knowing precisely: ALL PRIVILEGES grants every system privilege except SELECT ANY DICTIONARY, ALTER DATABASE LINK, and ALTER PUBLIC DATABASE LINK. Separately, ADMINISTER KEY MANAGEMENT is never touched by ALL PRIVILEGES at all, whether granting or revoking; that one specific privilege always requires its own explicit GRANT or REVOKE statement regardless of what else you're doing with ALL PRIVILEGES.

Current Oracle releases also support a schema-scoped ANY-style grant, distinct from granting the same privilege database-wide:

GRANT SELECT ANY TABLE ON SCHEMA hr TO app_user;

This grants SELECT across every current and future table in the HR schema specifically, not across the whole database the way a plain GRANT SELECT ANY TABLE TO app_user would. This is a schema privilege, a distinct third category alongside system and object privileges, covered in more depth in an earlier lesson of this module.

Since every Oracle AI Database 26ai database is a multitenant container database, a CONTAINER clause is available when a grant needs to apply across every PDB at once: add CONTAINER = ALL only when you are connected to the CDB root and specifically intend the grant to be common across the whole container, rather than local to one PDB.

Confirm a grant took effect by querying the data dictionary directly rather than assuming:

SELECT grantee, privilege, admin_option
FROM   dba_sys_privs
WHERE  grantee = 'MY_USER';

A user checking their own privileges without DBA-level access can use the equivalent USER_SYS_PRIVS view instead, which is automatically scoped to whoever is running the query. Current Oracle releases also provide DBA_SYS_PRIVS_ALL, ROLE_SYS_PRIVS_ALL, and SESSION_PRIVS_ALL, which report system privileges and schema privileges together in a single view rather than requiring two separate queries to see the complete picture for a given grantee.

Removing a previously granted privilege uses REVOKE with the same basic shape as GRANT:

REVOKE CREATE TABLE FROM my_user;

Occasionally a privilege genuinely belongs to every user rather than a specific individual. This is rare, and worth real caution before doing it, since it applies immediately and permanently to every current and future account in the database. The keyword PUBLIC handles this:

GRANT CREATE TABLE TO PUBLIC;

This specific statement gives every user in the database the ability to create tables in their own schema, not merely the ability to log in; CREATE SESSION granted to PUBLIC would be the corresponding way to let everyone connect. Whichever privilege you grant to PUBLIC, think carefully first: there is no per-user exception once it's granted this way, short of revoking it from PUBLIC entirely.

Before you move on to the next lesson, click the Exercise link to practice writing GRANT statements.
Granting System Privileges - Exercise