Creating Users and Granting Privileges in Oracle Database

Oracle Database provides robust user management and privilege control mechanisms that are essential for database security and proper access control. Whether you're a DBA or a developer working with Oracle, understanding how to create users and manage their privileges is fundamental.

---

Creating Users in Oracle

The basic syntax for creating a user is:

CREATE USER username 
IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE temp_tablespace_name]
[QUOTA size ON tablespace_name]
[PROFILE profile_name]
[ACCOUNT {LOCK | UNLOCK}]
[PASSWORD EXPIRE];

Example: Creating a Basic User

CREATE USER app_user 
IDENTIFIED BY "Str0ngP@ssw0rd"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;

Key Options Explained

1. IDENTIFIED BY – Sets the user's password. You can also use IDENTIFIED EXTERNALLY for OS authentication.

2. DEFAULT/TEMPORARY TABLESPACE – Specifies the user's primary and temporary storage areas.

3. QUOTA – Limits the amount of space a user can consume in a specific tablespace.

4. PROFILE – Assigns the user a resource profile that can enforce limits and password policies.

5. ACCOUNT LOCK/UNLOCK – Controls whether the user can log in immediately.

6. PASSWORD EXPIRE – Forces a password change upon first login.

---

Granting Privileges

Oracle uses two main types of privileges:

1. System Privileges

System privileges allow users to perform high-level database operations.

GRANT privilege_name TO username;

Common examples:

• CREATE SESSION (required for login)

• CREATE TABLE, CREATE VIEW, CREATE PROCEDURE

• UNLIMITED TABLESPACE

• SYSDBA, SYSOPER (administrative roles)

Example:

GRANT CREATE SESSION, CREATE TABLE TO app_user;

2. Object Privileges

Object privileges control access to specific schema objects.

GRANT privilege_name ON object_name TO username;

Common object privileges:

• SELECT, INSERT, UPDATE, DELETE on tables

• EXECUTE on procedures and functions

• REFERENCES for creating foreign keys

Example:

GRANT SELECT, INSERT ON hr.employees TO app_user;

---

Using Roles for Simplified Privilege Management

Roles allow you to group privileges and assign them to users in bulk.

-- Create a role
CREATE ROLE report_viewer;

-- Grant object privileges to the role
GRANT SELECT ON hr.employees TO report_viewer;
GRANT SELECT ON hr.departments TO report_viewer;

-- Assign the role to a user
GRANT report_viewer TO app_user;

---

Common Administrative Tasks

Viewing Privileges

-- System privileges
SELECT * FROM dba_sys_privs WHERE grantee = 'APP_USER';

-- Object privileges
SELECT * FROM dba_tab_privs WHERE grantee = 'APP_USER';

-- Role privileges
SELECT * FROM dba_role_privs WHERE grantee = 'APP_USER';

Modifying User Accounts

-- Change password
ALTER USER app_user IDENTIFIED BY "NewP@ss123";

-- Increase tablespace quota
ALTER USER app_user QUOTA 200M ON users;

-- Lock or unlock account
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;

Revoking Privileges

-- Revoke a system privilege
REVOKE CREATE TABLE FROM app_user;

-- Revoke an object privilege
REVOKE INSERT ON hr.employees FROM app_user;

-- Revoke a role
REVOKE report_viewer FROM app_user;

---

Best Practices

1. Follow the Principle of Least Privilege – Always grant the minimum access necessary for a user's role.

2. Use Roles – Manage privileges more efficiently by grouping them.

3. Enforce Strong Password Policies – Use profiles to set complexity and expiration rules.

4. Conduct Regular Privilege Audits – Use data dictionary views to review and document user access.

5. Avoid PUBLIC Grants – Avoid assigning any powerful privileges to the PUBLIC role.

6. Design Schemas Thoughtfully – Consider schema-based application models over creating many individual users.

---

Example Workflow: Setting Up an Application User

-- Step 1: Create a dedicated tablespace
CREATE TABLESPACE app_data 
DATAFILE '/path/to/app_data01.dbf' SIZE 500M;

-- Step 2: Create the application owner schema
CREATE USER app_owner 
IDENTIFIED BY "0wnerP@ss!23"
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON app_data;

-- Grant essential privileges
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, 
      CREATE PROCEDURE, CREATE SEQUENCE TO app_owner;

-- Step 3: Create a role to encapsulate app access
CREATE ROLE app_user_role;

-- Grant object-level privileges via the role
GRANT SELECT, INSERT, UPDATE ON app_owner.customers TO app_user_role;
GRANT SELECT, INSERT ON app_owner.orders TO app_user_role;
GRANT EXECUTE ON app_owner.process_order TO app_user_role;

-- Step 4: Create a restricted application user
CREATE USER app_user 
IDENTIFIED BY "Us3rP@ss456"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

-- Assign minimal system privileges
GRANT CREATE SESSION TO app_user;

-- Assign role-based privileges
GRANT app_user_role TO app_user;

---

Conclusion

Effective user and privilege management is a cornerstone of Oracle Database security. By following structured processes and adhering to best practices, you can maintain secure, controlled, and auditable access across your database environment. Make it a habit to regularly review and refine your privilege assignments to keep your systems protected and compliant.