Granting CREATE SESSION Privileges in Oracle AI Database 26ai

Creating a user account in Oracle does not automatically give that user the ability to connect to the database. A newly created account has an empty privilege domain: no system privileges, no object privileges, and no roles. The very first privilege a DBA must grant is CREATE SESSION, which authorizes the user to establish a database connection. Without it, any attempt to connect will be rejected immediately, regardless of whether the correct password is supplied.

Granting CREATE SESSION

The GRANT command assigns privileges to users. To allow COIN_ADMIN to connect to the database:

GRANT CREATE SESSION TO coin_admin;

After this grant, the user can establish a session using SQL*Plus, SQL Developer, or any Oracle-compatible client:

sqlplus coin_admin/CoinAdmin#2024@dbname

CREATE SESSION is the minimum privilege required for any database interaction. It does not grant the user the ability to query tables, create objects, or perform any other database operation; it only permits the connection itself. Every subsequent action requires its own privilege or role assignment.

System Privileges vs Object Privileges

Oracle organizes privileges into two categories that serve distinct purposes.

System privileges authorize users to perform database-level operations, actions that affect the database as a whole or that span multiple schemas. CREATE SESSION is a system privilege. Other common system privileges include CREATE TABLE, CREATE INDEX, CREATE VIEW, CREATE SEQUENCE, and administrative privileges such as SYSDBA and SYSOPER. System privileges are granted with the GRANT command and revoked with REVOKE.

Object privileges authorize users to perform specific operations on specific database objects, a particular table, view, sequence, or procedure owned by another user. SELECT, INSERT, UPDATE, and DELETE are object privileges when applied to a named table.

Building a Minimum Privilege Set

After granting CREATE SESSION, the DBA grants additional privileges based on what the user needs to accomplish. Oracle follows the principle of least privilege: grant only what is required for the user's designated tasks, nothing more.

For a developer account that needs to create and manage their own objects:

GRANT CREATE SESSION, CREATE TABLE, CREATE INDEX,
     CREATE VIEW, CREATE SEQUENCE TO coin_admin;

For a read-only reporting user that needs to query a specific table in another schema:

GRANT CREATE SESSION TO reporting_user;
GRANT SELECT ON coin_admin.transactions TO reporting_user;

For a data entry user who needs to insert and update but not delete:

GRANT CREATE SESSION TO data_entry_user;
GRANT INSERT, UPDATE ON coin_admin.orders TO data_entry_user;

Granting excessive privileges is a common security mistake. A user who only needs to run reports does not need CREATE TABLE or DELETE on production tables. Review privilege assignments regularly and revoke anything that is no longer required.

Using Roles to Simplify Privilege Management

Granting individual privileges to each user becomes unmanageable as the number of users and objects grows. Oracle roles solve this problem by bundling related privileges into a named collection that can be assigned to multiple users with a single grant.

Create a role for application users who need read access to the COIN schema:

CREATE ROLE coin_read_only;
GRANT SELECT ON coin_admin.accounts TO coin_read_only;
GRANT SELECT ON coin_admin.transactions TO coin_read_only;
GRANT SELECT ON coin_admin.customers TO coin_read_only;

Assign the role to users who need it:

GRANT CREATE SESSION TO user1;
GRANT CREATE SESSION TO user2;
GRANT coin_read_only TO user1, user2;

When a new table is added to the COIN schema, granting SELECT on it to the role automatically extends access to all role members; no need to update each user individually. Dropping the role entirely removes it, and every privilege bundled inside it, from every user who held it, which is a genuinely useful all-at-once cleanup when a role is retired.

Oracle's built-in CONNECT role historically bundled CREATE SESSION with several other privileges. In current Oracle releases, CONNECT retains only two privileges: CREATE SESSION and SET CONTAINER. That second privilege is worth knowing about specifically in a mandatory-CDB environment, since it is what lets a common user switch between containers at all. Do not rely on the historical behavior of CONNECT bundling table or view creation rights; grant the specific privileges your users need explicitly.

Granting Privileges with Admin Option

The WITH ADMIN OPTION clause allows the grantee to pass a system privilege or role on to other users. This should be used sparingly and only for trusted DBAs or administrative accounts:

GRANT CREATE SESSION TO dba_assistant WITH ADMIN OPTION;

The WITH GRANT OPTION clause performs the same function for object privileges: it allows the grantee to grant the object privilege to others:

GRANT SELECT ON coin_admin.accounts TO team_lead WITH GRANT OPTION;

Be deliberate about granting these options. A user with WITH ADMIN OPTION on CREATE SESSION can authorize any other user to connect to the database without DBA involvement. Unified auditing, the only auditing mechanism current Oracle releases support, can track these grant operations for compliance purposes.

One practical mechanic worth knowing precisely: there is no way to revoke just the admin option itself while leaving the underlying privilege or role in place. REVOKE always removes the whole grant. To downgrade dba_assistant from holding CREATE SESSION with admin option to holding it without, you must revoke it entirely and then grant it again without the option:

REVOKE CREATE SESSION FROM dba_assistant;
GRANT CREATE SESSION TO dba_assistant;

Revoking Privileges

Privileges granted with GRANT are removed with REVOKE. To prevent a user from connecting to the database:

REVOKE CREATE SESSION FROM coin_admin;

To remove an object privilege:

REVOKE SELECT ON coin_admin.accounts FROM reporting_user;

Revoking a system privilege or role granted WITH ADMIN OPTION does not cascade downstream: if dba_assistant used their admin option to grant CREATE SESSION to ten other users, revoking it from dba_assistant does not automatically revoke it from those ten users. Each downstream grant must be revoked separately. Do not confuse this with dropping a role entirely, covered above, which does remove that role's privileges from every user who held it; those are two different operations with two different scopes, not a contrast between roles and privileges revoking differently from a single user.

Auditing Privilege Grants

Unified auditing records privilege grant and revoke operations. It is worth being precise about its current status rather than describing it as simply the recommended default: starting with Oracle AI Database 26ai, traditional auditing is fully desupported, and unified auditing is the only mechanism available for any new auditing configuration. To create an explicit audit policy covering user privilege management:

CREATE AUDIT POLICY privilege_mgmt_policy
 ACTIONS GRANT, REVOKE;

AUDIT POLICY privilege_mgmt_policy;

Query the audit trail to review recent privilege changes. One correction worth making directly: the username column on this view is DBUSERNAME, with no underscore, not DB_USERNAME as sometimes shown; the underscored form fails with an invalid identifier error.

SELECT event_timestamp, dbusername, action_name,
      object_schema, object_name, return_code
FROM unified_audit_trail
WHERE action_name IN ('GRANT', 'REVOKE')
ORDER BY event_timestamp DESC;

Schema-level privilege grants simplify access management for application schemas. Rather than granting SELECT on each table individually, a DBA can grant access to every table in a schema with a single statement:

GRANT SELECT ANY TABLE ON SCHEMA coin_admin TO reporting_user;

This eliminates the need to update grants each time a new table is added to the schema, reducing administrative overhead for read-heavy reporting environments.

Verifying Privilege Assignments

Query the data dictionary to confirm what privileges a user holds before and after any grant or revoke operation.

System privileges granted directly to a user:

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

Object privileges granted to a user:

SELECT owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'COIN_ADMIN';

Roles assigned to a user:

SELECT granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee = 'COIN_ADMIN';

Summary

CREATE SESSION is the entry point for every Oracle database user; without it, no connection is possible regardless of any other configuration. Grant it immediately after creating a user account, then build the user's privilege set based on their actual needs. Use roles to manage groups of users with shared access requirements, remembering that dropping a role removes its privileges from every holder at once, while revoking a role or an admin-option privilege from one user never cascades to whoever that user granted it to downstream. Apply WITH ADMIN OPTION and WITH GRANT OPTION sparingly and only to trusted accounts, and remember that revoking just the option itself is not possible; you revoke the whole grant and reissue it without the option. Audit all grant and revoke operations through unified auditing, now the only auditing mechanism available, and verify privilege assignments regularly through DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS. Schema-level privilege grants reduce the ongoing administrative burden of maintaining object-level access for application users.