Oracle Security: Users, Privileges, Roles & Access Control

TAKEAWAYS:

• Oracle secures data via users, roles, and privileges, with granular control over SQL commands and objects.
• Authentication uses passwords, certificates, or external systems (e.g., LDAP), while authorization enforces "least privilege."
• Locking mechanisms (row, table, DDL) prevent concurrent conflicts; locking modes (shared/exclusive) balance performance and safety.
• Roles group privileges (e.g., DBA, CONNECT) for easier management, but must be explicitly granted.
• Auditing logs actions (e.g., ALTER TABLE) for compliance (e.g., NEPSE trade audits).
• Real-world: eSewa uses Oracle roles to restrict admin users from modifying transaction logs; NTC secures network access via Oracle’s NETWORK_ADMIN role.

1. Oracle Database Security Overview

Oracle’s security model protects data at three layers:

1. Authentication: Verifying who you are (user credentials).
2. Authorization: What you can do (privileges/roles).
3. Audit & Compliance: Tracking actions for accountability.

Why it matters:

• eSewa uses Oracle roles to ensure only approved staff can modify payment gateways.
• Ncell secures customer data via Oracle’s CREATE SESSION privilege, restricting unauthorized logins.

2. Users and Authentication

User Creation

Users are created with a schema (default tablespace) and default tablespace for storage.

CREATE USER john DOMAIN dbadmin
   IDENTIFIED BY "SecurePass123"
   DEFAULT TABLESPACE users
   TEMPORARY TABLESPACE temp
   QUOTA 100M ON users;

• DOMAIN: Adds a prefix to the username (e.g., john@dbadmin).
• QUOTA: Limits storage usage (e.g., 100MB for john).

Authentication Methods

Worked Example: Scenario: Ncell wants to restrict admin access to Oracle DB. Solution:

CREATE USER ncell_admin
   IDENTIFIED EXTERNALLY 'LDAP://ldap.ncell.com';

• Uses Ncell’s LDAP server for authentication, enforcing company policies.

3. Privileges: The "What You Can Do" Layer

Privileges grant permissions on:

• SQL commands (e.g., CREATE TABLE, DROP USER).
• Database objects (e.g., SELECT on customers table).

Types of Privileges

mindmap
  root((Privileges))
    System Privileges
      CREATE SESSION
      CREATE TABLE
      DROP USER
    Object Privileges
      SELECT ON customers
      INSERT ON orders
      ALTER ON salary

Granting Privileges

-- Grant system privilege
GRANT CREATE TABLE TO john;

-- Grant object privilege
GRANT SELECT, INSERT ON employees TO hr_staff;

-- Revoke privilege
REVOKE UPDATE ON orders FROM sales_team;

Real-World Tie: Daraz Inventory System:

• Privilege: SELECT ON products → Only inventory_analyst can query stock levels.
• Role: DARAZ_INVENTORY → Groups SELECT, UPDATE on products for efficiency.

4. Roles: Grouping Privileges

Roles bundle privileges for easier management. Common Oracle Roles:

Example:

-- Create a custom role
CREATE ROLE daraz_reports;

-- Grant privileges to the role
GRANT SELECT ON sales_data TO daraz_reports;
GRANT SELECT ON customer_data TO daraz_reports;

-- Grant the role to a user
GRANT daraz_reports TO analytics_team;

Advantages:

• Simplifies administration: Assign roles instead of individual privileges.
• Enforces least privilege: Users get only what they need (e.g., analytics_team cannot DROP TABLE).

Disadvantages:

• Role explosion: Too many roles can complicate auditing.
• Granularity limits: Some tasks require fine-grained privileges (e.g., ALTER TABLE on specific columns).

5. Locking Mechanisms: Preventing Concurrent Conflicts

Oracle uses locks to manage concurrent access to data. Lock Types:

Locking Modes

How Locks Are Acquired:

1. Automatic Locking:
   • Oracle grants locks when a user executes DML (e.g., UPDATE).
   • Example: Locking a row during UPDATE salary SET bonus = 1000 WHERE emp_id = 100.
2. Manual Locking:
   • Using LOCK TABLE or SELECT ... FOR UPDATE:

     -- Lock a table for exclusive access
     LOCK TABLE orders IN EXCLUSIVE MODE;
     
     -- Lock a row for update
     SELECT * FROM accounts WHERE account_id = 123 FOR UPDATE;
     

Worked Example: Scenario: Pathao drivers need to update their availability status without conflicts. Solution:

-- Driver locks their row to prevent another driver from updating it
SELECT * FROM drivers WHERE driver_id = 42 FOR UPDATE;
-- Now, only driver 42 can modify their status.

6. Auditing: Tracking Actions for Compliance

Oracle audits track who did what, when, and why. Auditing Methods:

1. Default Auditing (enabled by AUDIT command):

   AUDIT ALTER TABLE, DROP TABLE BY john;
   

   • Logs all ALTER/DROP actions by john to the AUD$ table.
2. Fine-Grained Auditing (FGA):

   CREATE POLICY salary_audit
   USING (salary > 100000)
   FOR UPDATE, DELETE
   STORE EXCEPT WHENEVER SUCCESSFUL;
   

   • Audits UPDATE/DELETE on salary > 100,000.

Real-World Example: NEPSE Trading System:

• Audit Rule: AUDIT TRADE_EXECUTE BY TRADER_ROLE;
• Why: Ensures no trader alters trade records after execution (prevents fraud).

AUD$ Table Structure:

-- Key columns in AUD$
SELECT * FROM AUD$
WHERE username = 'john'
ORDER BY timestamp DESC;

7. Security Best Practices

1. Least Privilege Principle:
   • Grant only the privileges needed (e.g., SELECT on orders, not DROP TABLE).
2. Regular Audits:
   • Review AUD$ for suspicious activity (e.g., unexpected DROP USER commands).
3. Role Hierarchy:
   • Avoid granting roles to roles (e.g., GRANT DBA TO CONNECT).
4. Password Policies:
   • Enforce strong passwords:

     ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME 90;
     ALTER PROFILE default LIMIT PASSWORD_REUSE_TIME UNLIMITED;
     ALTER PROFILE default LIMIT PASSWORD_REUSE_MAX 10;
     

5. Network Security:
   • Restrict NETWORK_ADMIN role to trusted admins (used in Oracle Listener config).

In the Real World

1. eSewa Payment Gateway:

   • Idea: Roles + Least Privilege
   • How: eSewa_DB_ADMIN role has ALTER TABLE on transactions, but eSewa_AUDITOR only has SELECT.
   • Why: Prevents fraud by separating write and read access.

2. NTC’s Network Management:

   • Idea: External Authentication
   • How: NTC’s Oracle DB uses IDENTIFIED EXTERNALLY with NTC’s LDAP server.
   • Why: Ensures only NTC employees (via Active Directory) can log in.

3. Daraz Order Processing:

   • Idea: Locking + Auditing
   • How:
     • Orders locked during UPDATE status (prevents race conditions).
     • AUDIT UPDATE ON orders BY order_processor;
   • Why: Tracks who changed an order’s status (e.g., from "Processing" to "Shipped").

Exam Tip

• Focus on:
  1. Syntax: CREATE USER, GRANT ROLE, AUDIT commands.
  2. Concepts: Least privilege, role hierarchy, lock types.
  3. Real-World Mapping: Always tie answers to apps like eSewa or NEPSE.
• Common Pitfalls:
  • Mixing up privileges (what you can do) and roles (groups of privileges).
  • Forgetting to include authentication methods (passwords, certificates, LDAP).
  • Overlooking auditing—exams often ask how to track actions.
• How to Score Full Marks:
  • Diagrams: Draw a role-privilege hierarchy (e.g., CONNECT → CREATE TABLE).
  • Examples: Use Daraz’s inventory system or NEPSE’s trade audits in answers.
  • Commands: Always show CREATE USER, GRANT ROLE, and AUDIT syntax.

In the real world

• eSewa: Uses Oracle’s EXCLUSIVE locks on transaction tables to prevent double-spending during payment processing. The system grants UPDATE privileges only to the PAYMENT_HANDLER role, which holds an exclusive lock during critical operations.
• NTC: Implements ROW SHARE locks for high-traffic network logs (e.g., SELECT on call_records) while allowing concurrent reads. The NETWORK_ADMIN role is restricted to ALTER operations with exclusive locks.
• Nepali Banks (e.g., NMB): Audits DROP TABLE operations via Oracle’s AUDIT feature to comply with RBI regulations, logging actions to the AUD$ table with timestamps and user IDs.