Oracle Database 19c User Creation and Management

In this article, we will walk through how to create and manage a user in an oracle database, specifically assuming a non-CDB configuration in 19c. We will also cover common areas of confusion in the field, such as system privileges, roles, profiles, and tablespace quotas.

Relationship Between Users and Schemas in oracle
Basics of User Creation (CREATE USER)
Verifying User Functionality (Creating a Table and Running SELECT)
1. Process Flow (Diagram)
2. Example
Day-to-Day User Management (ALTER / LOCK / DROP)
Checking Privileges, Roles, and Quotas
Using Profiles for Password Policy Enforcement
Common Errors and Fixes
Summary

Relationship Between Users and Schemas in oracle

A user is an entity that can log in to the database. Each user owns a schema with the same name, which is the logical container for database objects.
A user is associated with a tablespace (the storage location for data) and a profile (password and resource limitations).

[User(=Schema)]
   ├─ Privileges (System / Object)
   ├─ Roles (Set of privileges)
   ├─ Profile (Password/Resource limits)
   └─ Quota (Per tablespace storage limit)

Terminology

System Privilege: Determines what operations can be performed, e.g., CREATE TABLE.

Object Privilege: Controls access to another user’s objects, e.g., SELECT/INSERT.

Role: A collection of privileges, easier to manage and assign.

Profile: A bundle of password policies and resource restrictions.

Basics of User Creation (CREATE USER)

1) Creating a Tablespace (Optional)

You may use the default USERS tablespace, but for application separation and easier management, it is recommended to create a dedicated one.

-- Adjust file paths and sizes to fit your environment
SQL> CREATE TABLESPACE APP_TBS
       DATAFILE '/u01/app/oracle/oradata/ORCL/app_tbs01.dbf'
       SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

SQL> CREATE TEMPORARY TABLESPACE APP_TEMP
       TEMPFILE '/u01/app/oracle/oradata/ORCL/app_temp01.dbf'
       SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE 5G;

[reference]

2) Creating the User (Password, Tablespace, Quota)

-- Example: create application user APPUSER
SQL> CREATE USER APPUSER IDENTIFIED BY "SecretPW_2025"
       DEFAULT TABLESPACE APP_TBS
       TEMPORARY TABLESPACE APP_TEMP
       QUOTA 1G ON APP_TBS
       ACCOUNT UNLOCK;

Tips

Without a QUOTA, table creation will fail with ORA-01950.

Avoid granting UNLIMITED TABLESPACE unless absolutely necessary.

Use strong, complex passwords. Enclosing the password in quotes makes it case-sensitive.

3) Granting Minimum Required Privileges

-- Required for login
SQL> GRANT CREATE SESSION TO APPUSER;

-- Required to create schema objects
SQL> GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO APPUSER;

The old CONNECT and RESOURCE roles are deprecated and should not be used. Assign system privileges individually.

Verifying User Functionality (Creating a Table and Running SELECT)

Process Flow (Diagram)

[Login as APPUSER]
     │
     ▼
CREATE TABLE / INSERT
     │
     ▼
SELECT for verification

Example

-- Connect as APPUSER
SQL> CONN APPUSER/SecretPW_2025

-- Create a table
SQL> CREATE TABLE T_CUSTOMER (
       CUSTOMER_ID NUMBER(10)      PRIMARY KEY,
       NAME        VARCHAR2(50)    NOT NULL,
       CREATED_AT  DATE            DEFAULT SYSDATE
     );

-- Insert a row
SQL> INSERT INTO T_CUSTOMER (CUSTOMER_ID, NAME)
     VALUES (1, 'Yamada');

SQL> COMMIT;

-- Query the data
SQL> SELECT CUSTOMER_ID, NAME, CREATED_AT
       FROM T_CUSTOMER
      WHERE CUSTOMER_ID = 1;

Notes for beginners

Always define constraints (PK, NOT NULL, defaults) when creating tables.

Explicitly list columns in INSERT for clarity and safety.

Without COMMIT, other sessions will not see the changes.

Use WHERE to filter rows and ORDER BY to make results deterministic.

Day-to-Day User Management (ALTER / LOCK / DROP)

-- Change password
SQL> ALTER USER APPUSER IDENTIFIED BY "NewPW_2025";

-- Change default tablespace and quota
SQL> ALTER USER APPUSER DEFAULT TABLESPACE APP_TBS;
SQL> ALTER USER APPUSER QUOTA 2G ON APP_TBS;

-- Lock / unlock account
SQL> ALTER USER APPUSER ACCOUNT LOCK;
SQL> ALTER USER APPUSER ACCOUNT UNLOCK;

-- Drop user (including schema objects)
SQL> DROP USER APPUSER CASCADE;

Checking Privileges, Roles, and Quotas

-- List of users
SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, PROFILE
  FROM DBA_USERS
 ORDER BY USERNAME;

-- Roles granted
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'APPUSER';

-- System privileges granted
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'APPUSER';

-- Quota details
SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME = 'APPUSER';

Using Profiles for Password Policy Enforcement

-- Example: password expires after 180 days, 10 failed logins lock the account
SQL> CREATE PROFILE APP_PROFILE LIMIT
       PASSWORD_LIFE_TIME 180
       FAILED_LOGIN_ATTEMPTS 10
       PASSWORD_LOCK_TIME 1;

-- Assign the profile
SQL> ALTER USER APPUSER PROFILE APP_PROFILE;

Common Errors and Fixes

ORA-01045: Missing CREATE SESSION privilege
→ Grant CREATE SESSION.
ORA-01950: No privileges on tablespace
→ Set a quota with ALTER USER.
ORA-28000 / ORA-28001
→ Account locked or password expired. Unlock or reset as appropriate.
ORA-01920: Username already exists
→ Use a different name or drop the old user with DROP USER.

Summary

In a non-CDB environment, users are created directly under the instance.
Always specify tablespace and quotas to control storage usage.
Grant only the minimum privileges needed.
Use profiles and roles to balance security and manageability.

This article explains procedures based on Oracle Database 19c in a non-CDB environment (other versions may differ in defaults or UI behavior).

[reference]
CREATE USER