Database Security and User Management – concepts, privileges, policies, auditing

1. Introduction

Database security in Oracle is a layered discipline that protects confidentiality, integrity, and availability of data. The two fundamental pillars are:

1. Authentication – verifying the identity of a client (who you are).
2. Authorization – determining what the authenticated user is allowed to do (what you can do).

User management ties these pillars together through profiles, roles, and privileges. The DBA’s responsibilities include defining policies, monitoring activity, and ensuring that security metadata is safely backed up.

2. Authentication Mechanisms

Oracle supports several authentication methods:

During a login, the client sends credentials to the listener, which forwards them to the oracle process. The process validates the credentials against the chosen authentication source and, on success, creates a session in the Program Global Area (PGA).

sequenceDiagram
    participant C as Client (SQL*Plus)
    participant L as Listener
    participant S as Oracle Server
    C->>L: CONNECT username/password@service
    L->>S: Forward credentials
    S->>S: Authenticate (password file / OS / LDAP)
    alt Success
        S-->>C: Session established (PGA created)
    else Failure
        S-->>C: ORA‑01017 (invalid credentials)
    end

3. Users, Profiles, and Password Policies

3.1 Creating a User

CREATE USER sales_user IDENTIFIED BY "S@les2026"
   PROFILE default
   DEFAULT TABLESPACE users
   QUOTA 500M ON users;

• IDENTIFIED BY stores the password (hashed) in the data dictionary.
• PROFILE links the user to a set of password and resource limits.

3.2 Profiles

A profile groups password policy parameters and resource limits (sessions, CPU time, etc.). Example:

CREATE PROFILE secure_profile LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 90
   PASSWORD_GRACE_TIME 10
   PASSWORD_REUSE_MAX 5
   PASSWORD_REUSE_TIME 365
   PASSWORD_VERIFY_FUNCTION verify_complexity;

• FAILED_LOGIN_ATTEMPTS triggers account lockout.
• PASSWORD_VERIFY_FUNCTION is a PL/SQL routine that enforces custom complexity (e.g., at least one uppercase, one digit, one special character).

3.3 Worked Example – Password Expiration & Lockout

1. Create profile secure_profile as above.
2. Create user john_doe with that profile.
3. John logs in correctly 5 times, then mistypes password 5 times → account locked.

ALTER USER john_doe ACCOUNT UNLOCK;

The DBA can also set a default profile for all new users:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 180;

4. Roles and Privileges

4.1 System vs. Object Privileges

4.2 Role Hierarchy

Roles can contain other roles, forming a hierarchy that simplifies management.

classDiagram
    class "DBA_ROLE" {
        <<role>>
        +CREATE USER
        +DROP USER
        +GRANT ANY PRIVILEGE
    }
    class "DEV_ROLE" {
        <<role>>
        +CREATE TABLE
        +CREATE PROCEDURE
    }
    class "READ_ONLY_ROLE" {
        <<role>>
        +SELECT ANY TABLE
    }
    "DEV_ROLE" --> "READ_ONLY_ROLE" : inherits
    "DBA_ROLE" --> "DEV_ROLE" : inherits

4.3 Granting and Revoking

GRANT CREATE SESSION TO sales_user;
GRANT SELECT, INSERT ON orders TO sales_user;
GRANT READ_ONLY_ROLE TO sales_user;

REVOKE INSERT ON orders FROM sales_user;
REVOKE READ_ONLY_ROLE FROM sales_user;

Important: Use WITH ADMIN OPTION sparingly; it allows the grantee to grant the privilege further.

5. Account Locking, Unlocking, and Password Expiration

Oracle tracks login attempts in the USER$ table. When the threshold (FAILED_LOGIN_ATTEMPTS) is reached, the account status becomes LOCKED(TIMED) or LOCKED.

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

Unlocking options

6. Auditing and the Automatic Diagnostic Repository (ADR)

6.1 Auditing Types

Example: Audit all failed login attempts.

AUDIT SESSION WHENEVER NOT SUCCESSFUL BY ACCESS;

6.2 ADR Overview

ADR stores trace files, alert logs, and incident reports. It is the first place to look when a security‑related error occurs.

adrci> show alert -tail
adrci> show trace -p '*sqlnet*' -tail

ADR helps the DBA pinpoint:

• Unauthorized login attempts.
• Privilege escalation attempts.
• Corrupted audit files.

7. Fine‑Grained Access Control (VPD / DBMS_RLS)

Row‑level security is essential for multi‑tenant applications (e.g., a SaaS platform serving many customers). Oracle’s Virtual Private Database (VPD) adds a policy predicate to every query on a protected table.

BEGIN
   DBMS_RLS.ADD_POLICY(
      object_schema   => 'sales',
      object_name     => 'orders',
      policy_name     => 'tenant_policy',
      function_schema => 'security',
      policy_function => 'fn_tenant_predicate',
      statement_types => 'SELECT, INSERT, UPDATE, DELETE');
END;
/

fn_tenant_predicate returns a WHERE clause like customer_id = SYS_CONTEXT('USERENV','SESSION_USER'), ensuring each tenant sees only its own rows.

8. Backing Up Security Metadata

Security objects (users, roles, profiles, privileges) are stored in the data dictionary. They must be exported regularly.

expdp system/password DIRECTORY dp_dir DUMPFILE sec_metadata.dmp \
   LOGFILE sec_metadata.log CONTENT=METADATA_ONLY \
   INCLUDE=USER,ROLE,PROFILE,GRANT

• CONTENT=METADATA_ONLY ensures only security definitions are exported, not data.
• Store the dump in a secure off‑site location and test restoration:

impdp system/password DIRECTORY dp_dir DUMPFILE sec_metadata.dmp \
   LOGFILE sec_import.log CONTENT=METADATA_ONLY

9. Comparison Table – Security Features vs. Typical DBA Tasks

| Feature                     | Primary DBA Task                     | Benefit                              | Drawback |
|-----------------------------|--------------------------------------|--------------------------------------|----------|
| Password Profiles           | Define `CREATE PROFILE`              | Enforces uniform password policy    | Requires periodic tuning |
| Roles & Privileges          | `GRANT`/`REVOKE`                     | Implements least‑privilege principle | Over‑granting possible if not audited |
| Account Lockout             | Set `FAILED_LOGIN_ATTEMPTS`          | Stops brute‑force attacks            | May lock out legitimate users |
| Auditing (Unified)          | `AUDIT` statements / `CREATE AUDIT POLICY` | Detects suspicious activity          | Generates large log volume |
| ADR                         | Monitor trace/alert logs             | Centralized diagnostics              | Requires storage management |
| VPD (Row‑level security)    | `DBMS_RLS.ADD_POLICY`                | Multi‑tenant data isolation          | Slight performance overhead |

10. Worked Example – End‑to‑End User Setup

Scenario: A new sales analyst, Anita, needs read‑only access to the orders table, must change her password every 60 days, and her account should lock after 3 failed attempts.

-- 1. Create a profile with required password rules
CREATE PROFILE analyst_profile LIMIT
   FAILED_LOGIN_ATTEMPTS 3
   PASSWORD_LIFE_TIME 60
   PASSWORD_GRACE_TIME 5
   PASSWORD_VERIFY_FUNCTION verify_complexity;

-- 2. Create a role for read‑only sales access
CREATE ROLE sales_readonly;
GRANT SELECT ON sales.orders TO sales_readonly;

-- 3. Create the user and assign profile & role
CREATE USER anita IDENTIFIED BY "An!ta2026"
   PROFILE analyst_profile
   DEFAULT TABLESPACE users
   QUOTA 200M ON users;

GRANT sales_readonly TO anita;
GRANT CREATE SESSION TO anita;

-- 4. Verify account status
SELECT username, account_status FROM dba_users WHERE username='ANITA';

Test trace:

1. Anita logs in successfully → session created in PGA.
2. She runs SELECT * FROM sales.orders; → Oracle checks that sales_readonly role grants SELECT on the table, query succeeds.
3. She mistypes password three times → account status changes to LOCKED(TIMED).
4. After PASSWORD_LOCK_TIME (default 1 day) or manual ALTER USER anita ACCOUNT UNLOCK;, she can log in again.

11. In the real world

• eSewa uses Oracle Database to store transaction records. Each merchant has a role that only permits INSERT on its own transactions table, enforced by VPD so that merchants cannot view others’ payments.
• Khalti implements a password profile that requires a minimum of 12 characters, at least one special character, and forces password change every 90 days. Failed login attempts beyond 5 lock the account, and the security team receives an audit alert via the Unified Auditing framework.
• NTC (telecom) runs a multi‑tenant billing system on a single Oracle CDB. Using Fine‑Grained Access Control, each subscriber’s billing data is filtered by customer_id = SYS_CONTEXT('USERENV','SESSION_USER'), guaranteeing that a call‑center agent can only see records belonging to the subscriber they are assisting.

These examples illustrate how the concepts of profiles, roles, VPD, and auditing are applied in everyday Nepali digital services.

12. Visual Summary

stateDiagram-v2
    [*] --> Unauthenticated
    Unauthenticated --> Authenticated : Successful login
    Authenticated --> Locked : Exceeds FAILED_LOGIN_ATTEMPTS
    Locked --> Unauthenticated : Account unlock / password reset
    Authenticated --> SessionActive : CREATE SESSION granted
    SessionActive --> [*] : Logout / timeout

flowchart LR
    A["Client Application"] --> B["Listener"]
    B --> C["Oracle Process (PGA)"]
    C --> D["Authentication Service"]
    D -->|"Success"| E["Session (PGA)"]
    E --> F["Authorization Engine"]
    F --> G["Roles & Privileges"]
    F --> H["Fine‑Grained Access Control"]
    G --> I["Data Dictionary (Users, Roles)"]
    H --> I
    I --> J["ADR (Trace & Audit)"]

13. Exam tip

• Remember the hierarchy: Authentication → Profile → User → Role → Privilege. Many exam questions ask you to place a concept in the correct layer.
• Command patterns: For each task (create user, set profile, lock account, audit login) memorize the exact syntax; the marks are often awarded for correct clauses (IDENTIFIED BY, PROFILE, ACCOUNT LOCK, AUDIT SESSION).
• Worked example is gold: In answer scripts, write a short, complete script that creates a user, assigns a profile, grants a role, and then shows how to lock/unlock. Include the SELECT username, account_status FROM dba_users query to prove the result – this demonstrates end‑to‑end understanding and fetches extra points.
• Audit vs. ADR: Distinguish that auditing records who did what while ADR stores how the database behaved (trace files, alert logs). A common mistake is to mix them up.
• Backup of security metadata: The exam often asks for the only command to export users/roles – expdp ... CONTENT=METADATA_ONLY. Write the command exactly; omit the INCLUDE=USER part and you lose marks.

Focus on definitions, SQL syntax, and the flow of a login session; the visual diagrams above can be reproduced quickly in the answer to earn presentation marks. Good luck!