Granting CREATE SESSION Privileges

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. When a user's role changes, revoking the role from them removes all associated privileges in a single operation.

Oracle's built-in CONNECT role historically bundled CREATE SESSION with several other privileges. In Oracle 19c and later, CONNECT grants only CREATE SESSION. Do not rely on the historical behavior — 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 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. Oracle Unified Auditing can track these grant operations for compliance purposes.

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 granted WITH ADMIN OPTION does not cascade — 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. This is an important distinction from role revocation, which does cascade to all role members.

Auditing Privilege Grants in Oracle 23ai

Oracle Unified Auditing, enabled by default from Oracle 19c onward, records privilege grant and revoke operations automatically. 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:

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

In Oracle 23ai, schema-level privilege grants simplify access management for application schemas. Rather than granting SELECT on each table individually, a DBA can grant access to all current and future objects 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. Apply WITH ADMIN OPTION and WITH GRANT OPTION sparingly and only to trusted accounts. Audit all grant and revoke operations through Oracle Unified Auditing, and verify privilege assignments regularly through DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS. In Oracle 23ai, schema-level privilege grants reduce the ongoing administrative burden of maintaining object-level access for application users.