How to Create a User in Oracle - Grants & Privileges

How to Create a User in Oracle

Creating a user in Oracle involves more than just CREATE USER — you need to assign a default tablespace, set a quota, grant the right privileges, and attach a profile for password and resource management. This guide covers every step for a production-ready user.

Prerequisites

You need the CREATE USER system privilege (typically held by DBAs). For multi-tenant databases (CDB/PDB), decide whether you’re creating a common user (C## prefix, exists in all containers) or a local user (PDB-only).

-- Check your current privileges
SELECT privilege FROM session_privs WHERE privilege = 'CREATE USER';

-- If working in a CDB, check which container you're in
SHOW CON_NAME;

-- For PDB work, connect to the right PDB
ALTER SESSION SET CONTAINER = pdb_prod;

Step 1: Create a Basic Application User

-- Create a standard application user
CREATE USER app_user
  IDENTIFIED BY "SecureP@ssw0rd2024"
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  QUOTA 10G ON app_data
  ACCOUNT UNLOCK;

-- Grant CREATE SESSION so the user can log in
GRANT CREATE SESSION TO app_user;

Without GRANT CREATE SESSION, the user will receive ORA-01045 when trying to connect.

Step 2: Grant Standard Roles

Oracle provides predefined roles that bundle common privileges.

-- For a developer/application schema owner
GRANT CONNECT, RESOURCE TO app_user;

-- RESOURCE includes: CREATE TABLE, CREATE INDEX, CREATE PROCEDURE,
-- CREATE SEQUENCE, CREATE TRIGGER, CREATE TYPE, CREATE CLUSTER

-- For a read-only reporting user
GRANT CREATE SESSION TO report_user;
GRANT SELECT ANY TABLE TO report_user;  -- Broad — prefer object-level grants

-- For a DBA user (admin work only, not application connections)
GRANT DBA TO dba_app_user;

Step 3: Grant Object-Level Privileges

Prefer explicit object grants over broad system privileges — this follows the principle of least privilege.

-- Grant SELECT on specific tables to a reporting user
GRANT SELECT ON app_schema.orders       TO report_user;
GRANT SELECT ON app_schema.customers    TO report_user;
GRANT SELECT ON app_schema.products     TO report_user;

-- Grant DML permissions for an application user
GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.orders    TO app_user;
GRANT SELECT, INSERT               ON app_schema.audit_log  TO app_user;

-- Grant EXECUTE on packages and procedures
GRANT EXECUTE ON app_schema.order_pkg  TO app_user;
GRANT EXECUTE ON app_schema.util_pkg   TO app_user;

-- Allow the user to grant their own privileges to others (with GRANT OPTION)
GRANT SELECT ON app_schema.reference_data TO report_user WITH GRANT OPTION;

Step 4: Set Tablespace Quotas

Without a quota, a user with RESOURCE role or UNLIMITED TABLESPACE will consume space without limits.

-- Set a specific quota
ALTER USER app_user QUOTA 20G ON app_data;
ALTER USER app_user QUOTA 2G  ON app_idx;

-- Remove quota restriction (unlimited on a specific tablespace)
ALTER USER app_user QUOTA UNLIMITED ON app_data;

-- Check current quotas
SELECT username, tablespace_name,
       ROUND(bytes/1024/1024, 0) AS used_mb,
       DECODE(max_bytes, -1, 'UNLIMITED',
              ROUND(max_bytes/1024/1024, 0)) AS quota_mb
FROM dba_ts_quotas
WHERE username = 'APP_USER';

Note: GRANT RESOURCE in Oracle 12c+ no longer implicitly grants UNLIMITED TABLESPACE. You must set quotas explicitly.

Step 5: Assign a Profile

Profiles enforce password policies and resource limits.

-- Create a profile for application users
CREATE PROFILE app_profile LIMIT
  FAILED_LOGIN_ATTEMPTS      5
  PASSWORD_LOCK_TIME         1/24    -- 1 hour
  PASSWORD_LIFE_TIME         180     -- 180 days
  PASSWORD_REUSE_TIME        365
  PASSWORD_REUSE_MAX         10
  PASSWORD_GRACE_TIME        7
  PASSWORD_VERIFY_FUNCTION   ora12c_strong_verify_function
  SESSIONS_PER_USER          UNLIMITED
  CPU_PER_SESSION            UNLIMITED
  CONNECT_TIME               UNLIMITED
  IDLE_TIME                  60;     -- Disconnect after 60 min idle

-- Assign profile to user
ALTER USER app_user PROFILE app_profile;

-- Verify profile assignment
SELECT username, profile, account_status, expiry_date
FROM dba_users
WHERE username = 'APP_USER';

Step 6: Create a Schema-Only Account (Oracle 18c+)

Schema-only accounts have no password and cannot log in directly — they just own objects. Applications connect as a different user and reference the schema.

-- Create schema-only account (no password, no login)
CREATE USER app_schema NO AUTHENTICATION
  DEFAULT TABLESPACE app_data
  QUOTA UNLIMITED ON app_data;

-- Create the connecting application user
CREATE USER app_connect
  IDENTIFIED BY "ConnectP@ss2024"
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION TO app_connect;

-- Grant app_connect access to app_schema objects
GRANT SELECT, INSERT, UPDATE, DELETE
  ON app_schema.orders TO app_connect;

Advanced Examples

Create a Common User in a CDB

-- Must be connected as a common user with CREATE USER privilege
-- Common users must start with C##
CREATE USER c##dba_monitor
  IDENTIFIED BY "MonitorP@ss2024"
  CONTAINER = ALL;

GRANT SET CONTAINER TO c##dba_monitor CONTAINER=ALL;
GRANT CREATE SESSION TO c##dba_monitor CONTAINER=ALL;
GRANT SELECT ANY DICTIONARY TO c##dba_monitor CONTAINER=ALL;

Create a Proxy User

Proxy authentication lets one user connect on behalf of another — useful for connection pools.

-- Allow app_pool to proxy for app_user
ALTER USER app_user GRANT CONNECT THROUGH app_pool;

-- Application connects as: app_pool[app_user]
-- Verify proxy configuration
SELECT proxy, client, authentication, flags
FROM proxy_users
WHERE client = 'APP_USER';

Common Mistakes and Pitfalls

No DEFAULT TABLESPACE specified — The user lands in the SYSTEM tablespace by default, and any objects they create pollute the SYSTEM tablespace.

Granting DBA to application users — GRANT DBA is far too broad for application accounts. It grants access to every object and every system privilege.

Forgetting to set a quota — A user without a quota or UNLIMITED TABLESPACE privilege gets ORA-01950 when trying to create objects.

Weak passwords — Oracle 12c+ password complexity functions are available. Use them via profiles to enforce strength requirements.

Not assigning a profile — Without a profile, DEFAULT is used, which has unlimited failed login attempts and no password expiry in some configurations.

Creating users as SYS or SYSTEM — Never create application users that connect as SYS or SYSTEM. Create a dedicated DBA account instead.

Verification Queries

-- Confirm user was created correctly
SELECT
  username,
  account_status,
  default_tablespace,
  temporary_tablespace,
  profile,
  created,
  expiry_date
FROM dba_users
WHERE username = 'APP_USER';

-- Check all granted system privileges
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'APP_USER'
ORDER BY privilege;

-- Check all granted roles
SELECT grantee, granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee = 'APP_USER'
ORDER BY granted_role;

-- Check tablespace quotas
SELECT tablespace_name,
       ROUND(bytes/1024/1024, 0)     AS used_mb,
       DECODE(max_bytes, -1, 'UNLIMITED',
              ROUND(max_bytes/1024/1024, 0)) AS limit_mb
FROM dba_ts_quotas
WHERE username = 'APP_USER';

-- Check object-level grants the user has received
SELECT owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'APP_USER'
ORDER BY owner, table_name;

Related Topics

• How to Gather Statistics - After populating the schema
• Security Analysis Scripts - Audit user privileges
• Oracle Errors: ORA-01045 - Missing CREATE SESSION
• Oracle Errors: ORA-01950 - No tablespace quota