Tag Archives: User scripts for password

How to Create a User in Oracle with Password & Permissions

Leave a reply

Managing users and permissions is one of the most important tasks for a DBA.
In this guide, we will learn how to:

✔ Create a new user in Oracle
✔ Assign a secure password
✔ Grant required permissions
✔ Assign tablespace quotas
✔ Provide object-level privileges
✔ Verify user permissions

This tutorial uses simple SQL that works on Oracle 11g, 12c, 19c, 21c & 23c.

🔐 1. Why Create a Custom User?

Using SYSTEM or SYS for application connections is a bad practice.
Instead, create separate users for:

  • Application access
  • Schema ownership
  • Developer access
  • Testing & QA

This increases security, performance, and audit control.

🏗 2. Connect to Oracle as Admin

Login using SQL*Plus or SQL Developer:

sqlplus system/password@ORCL

🧑‍💻 3. Create a Tablespace (Optional)

If you want a dedicated area for the user:

CREATE TABLESPACE app_data
DATAFILE '/u01/app/oracle/oradata/app_data01.dbf'
SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

👤 4. Create a New User

CREATE USER app_user IDENTIFIED BY StrongPassword123
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE temp;

👉 Change the username & password as per your need.

🎯 5. Grant Essential Permissions

Option 1: Minimum Access

For normal application user:

GRANT CONNECT, RESOURCE TO app_user;
GRANT CREATE SESSION TO app_user;

Option 2: Recommended New Permissions (19c+)

GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE TO app_user;
GRANT CREATE VIEW TO app_user;
GRANT CREATE PROCEDURE TO app_user;
GRANT CREATE SEQUENCE TO app_user;

💾 6. Assign Tablespace Quota

Without quota, user cannot create objects:

ALTER USER app_user QUOTA UNLIMITED ON app_data;

OR

ALTER USER app_user QUOTA 500M ON app_data;

🔒 7. System Privileges (Optional)

If user needs management privileges:

GRANT CREATE ANY TABLE TO app_user;
GRANT CREATE ANY VIEW TO app_user;
GRANT CREATE ANY PROCEDURE TO app_user;
GRANT SELECT ANY TABLE TO app_user;

🎯 8. Grant Object Level Privileges

If you want to allow access to specific tables:

GRANT SELECT, INSERT, UPDATE ON sales TO app_user;

OR 

-- Script for all tables:
BEGIN
  FOR t IN (SELECT table_name FROM user_tables) LOOP
   EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO app_user';
  END LOOP;
END;
/

🔍 9. Verify User & Privileges

Check user:

SELECT username, account_status FROM dba_users WHERE username='APP_USER';

Check granted roles:

SELECT * FROM dba_role_privs WHERE grantee='APP_USER';

Check privileges:

SELECT * FROM dba_sys_privs WHERE grantee='APP_USER';