Security and User Management

Security Principles and Guidelines

Database security is a multi-layered discipline that encompasses confidentiality, integrity, and availability of data. Oracle implements a defense-in-depth strategy through the following security layers: network security (firewalls, encryption), operating system security (file permissions, OS authentication), database access control (authentication, authorization, privileges), data encryption (Transparent Data Encryption — TDE), and auditing (tracking access and changes). The DBA must enforce the principle of least privilege — granting users only the minimum privileges necessary to perform their jobs — and regularly review access rights.

The security guidelines prescribed by Oracle include: restricting administrative privileges to the minimum necessary set of accounts, using strong password policies, implementing Transparent Data Encryption (TDE) for sensitive data at rest and in transit, encrypting network traffic using Oracle Net encryption, applying security patches promptly, and auditing all privileged operations.

Standard Auditing

Standard auditing tracks SQL statements, privileges, and schema objects. It is configured using the AUDIT command and recorded in the AUD$ base table (or, in newer versions, written to operating system files). The audit trail can be stored in the database (AUDIT_TRAIL=DB), in OS files (AUDIT_TRAIL=OS), or in XML files (AUDIT_TRAIL=XML).

-- Enable auditing of all DDL statements
AUDIT CREATE TABLE, CREATE VIEW, ALTER TABLE, DROP TABLE BY ACCESS;

-- Audit all inserts, updates, and deletes on a sensitive table
AUDIT INSERT, UPDATE, DELETE ON hr.employees BY ACCESS;

-- Audit all failed login attempts
AUDIT SESSION WHENEVER NOT SUCCESSFUL;

The audit trail can be queried through DBA_AUDIT_TRAIL (for database audit trail) or DBA_AUDIT_OBJECT and DBA_AUDIT_SESSION views. The DBA should periodically purge old audit records to prevent the SYSTEM tablespace from filling up.

Fine-Grained Auditing (FGA)

Fine-Grained Auditing extends standard auditing by allowing the DBA to define conditional audit policies at the row and column level. FGA policies are created using the DBMS_FGA package:

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema   => 'HR',
    object_name     => 'EMPLOYEES',
    policy_name     => 'AUDIT_HIGH_SALARY',
    audit_condition => 'SALARY > 10000',
    audit_column    => 'SALARY',
    handler_schema  => NULL,
    handler_module  => NULL,
    enable          => TRUE,
    statement_types => 'SELECT, INSERT, UPDATE, DELETE'
  );
END;

This policy audits any access to the SALARY column of the EMPLOYEES table when the salary exceeds 10,000. FGA policies can also invoke a handler procedure (e.g., to send an email alert) when the policy is violated. The audit trail is accessible through DBA_FGA_AUDIT_TRAIL.

Unified Auditing

Starting with Oracle Database 12c, Unified Auditing consolidates all audit records into a single, read-only UNIFIED_AUDIT_TRAIL view. It replaces the fragmented audit trails of standard, FGA, and mandatory auditing. Unified auditing is enabled by default in Oracle 21c and later. Policies are created using the CREATE AUDIT POLICY statement:

CREATE AUDIT POLICY hr_sensitive_actions
  ACTIONS SELECT ON hr.employees,
          INSERT ON hr.employees,
          UPDATE ON hr.employees,
          DELETE ON hr.employees
  WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') NOT IN (''HR_ADMIN'')'
  EVALUATE PER SESSION;

AUDIT POLICY hr_sensitive_actions;

The unified audit trail provides a single source of truth for all audit data, simplifying compliance reporting and forensic analysis. The UNIFIED_AUDIT_TRAIL view can be queried with standard SQL, and its records are stored in the SYSAUX tablespace by default.

Authentication Methods

Authentication is the process of verifying the identity of a user or process attempting to connect to the database. Oracle supports multiple authentication methods:

• Password Authentication: The most common method, where the user provides a username and password. Passwords are hashed using SHA-512 (in Oracle 12c and later) and stored in the data dictionary. The IDENTIFIED BY clause creates a database-authenticated user.
• Operating System (OS) Authentication: The operating system verifies the user's identity, and Oracle trusts the OS. This is enabled by setting the initialization parameter OS_AUTHENT_PREFIX=OPS$ and creating users with IDENTIFIED EXTERNALLY. OS-authenticated users do not need to supply a database password.
• Strong Authentication: Integration with enterprise directory services such as Oracle Internet Directory (OID), Microsoft Active Directory, or Kerberos. Users are authenticated centrally, and the database trusts the directory service.
• Administrative Authentication: Special authentication methods for privileged users (SYSDBA, SYSOPER, SYSBACKUP, etc.) that bypass normal database authentication. These include password file authentication (orapwd utility) and OS authentication via membership in the dba OS group.

Authorization Methods

Authorization determines what actions an authenticated user can perform. Oracle implements authorization through:

• System Privileges: Grant the right to perform specific database-wide operations (e.g., CREATE TABLE, ALTER SYSTEM, SELECT ANY TABLE).
• Object Privileges: Grant the right to perform operations on specific schema objects (e.g., SELECT ON hr.employees, EXECUTE ON dbms_stats).
• Roles: Named collections of privileges that can be granted to users or other roles. Roles simplify privilege management and support the principle of least privilege.
• Profiles: Enforce resource limits and password policies on users.

The authorization model is discretionary: the owner of a schema object can grant privileges on that object to other users. Additionally, Oracle supports Virtual Private Database (VPD) for row-level security and Oracle Label Security for multi-level security classifications.

Common and Local Users

In the multitenant architecture, the distinction between common and local users is fundamental to isolation guarantees:

• Common Users: Created in the root container (CDB$ROOT) with a C## or c## prefix (by default, configurable via COMMON_USER_PREFIX). Common users can connect to any PDB and perform administrative tasks, provided they hold the appropriate privileges. Common users are stored in the root's data dictionary and are visible across all containers.
• Local Users: Exist only within a single PDB. They have no visibility beyond their container and cannot connect to other PDBs or the root. Local users are created without the C## prefix and are scoped strictly to the current container.

-- Create a common user in the root
CREATE USER C##db_admin IDENTIFIED BY password CONTAINER=ALL;

-- Grant common privileges
GRANT CREATE SESSION, SET CONTAINER TO C##db_admin CONTAINER=ALL;

-- Create a local user in a PDB
ALTER SESSION SET CONTAINER = hrpdb;
CREATE USER app_user IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO app_user;

Monitoring User Information

The DBA can monitor user information through several data dictionary views:

• DBA_USERS (or CDB_USERS for all containers): Username, account status, default tablespace, temporary tablespace, profile, and authentication type.
• DBA_PROFILES (or CDB_PROFILES): Profile resource limits and password parameters.
• DBA_ROLE_PRIVS: Roles granted to users.
• DBA_SYS_PRIVS: System privileges granted to users.
• DBA_TAB_PRIVS: Object privileges granted to users.