# Oracle Security Hardening — Profiles, Auditing, and Privilege Reviews

Most Oracle databases are breached not through zero-days but through misconfiguration — default passwords, excessive privileges, disabled auditing, and accounts that should have been locked years ago. This article walks through every major security hardening area with exact commands you can run today.

## 1. User Account Hardening

### Lock and expire default accounts

Every Oracle installation ships with dozens of default schema accounts. Most should never log in interactively. Start by identifying which are open:

    SELECT username, account_status, last_login, created
    FROM dba_users
    WHERE account_status NOT IN ('LOCKED', 'EXPIRED & LOCKED')
    ORDER BY created;

Lock everything that isn't actively used by your application or DBAs:

    -- Lock and expire in one step
    ALTER USER DBSNMP ACCOUNT LOCK PASSWORD EXPIRE;
    ALTER USER OUTLN ACCOUNT LOCK PASSWORD EXPIRE;
    ALTER USER SCOTT ACCOUNT LOCK PASSWORD EXPIRE;
    ALTER USER HR ACCOUNT LOCK PASSWORD EXPIRE;

    -- For EBS environments, never lock APPS/APPLSYS directly --
    -- use FNDCPASS instead

Oracle ships 40+ default accounts. A quick way to identify which ones Oracle itself considers "default":

    SELECT username, account_status
    FROM dba_users
    WHERE username IN (
      SELECT username FROM dba_users_with_defpwd
    )
    ORDER BY username;

### Default password detection

The `DBA_USERS_WITH_DEFPWD` view (introduced in 11g) flags accounts still using Oracle's factory-set passwords — a critical finding in any security audit:

    SELECT u.username, u.account_status, u.last_login
    FROM dba_users u
    JOIN dba_users_with_defpwd d ON u.username = d.username
    WHERE u.account_status NOT IN ('LOCKED', 'EXPIRED & LOCKED')
    ORDER BY u.username;

Any result here is a P1 finding. Change these immediately — the password list is publicly known.

## 2. Password Profiles

Oracle's DEFAULT profile is dangerously permissive out of the box. Create a hardened profile and assign it to all non-system users:

    CREATE PROFILE app_user_profile LIMIT
      FAILED_LOGIN_ATTEMPTS    5
      PASSWORD_LOCK_TIME       1/24        -- 1 hour
      PASSWORD_LIFE_TIME       90
      PASSWORD_REUSE_TIME      365
      PASSWORD_REUSE_MAX       10
      PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function
      SESSIONS_PER_USER        10
      IDLE_TIME                30          -- minutes
      CONNECT_TIME             480;        -- 8 hours max session

    -- Apply to all application users
    ALTER USER app_user PROFILE app_user_profile;

Check which users are still on DEFAULT:

    SELECT username, profile, account_status
    FROM dba_users
    WHERE profile = 'DEFAULT'
      AND account_status = 'OPEN'
      AND username NOT IN ('SYS','SYSTEM')
    ORDER BY username;

Verify the DEFAULT profile itself isn't too permissive:

    SELECT resource_name, limit
    FROM dba_profiles
    WHERE profile = 'DEFAULT'
      AND resource_type = 'PASSWORD'
    ORDER BY resource_name;

Key limits to check: `PASSWORD_LIFE_TIME` should not be UNLIMITED, `FAILED_LOGIN_ATTEMPTS` should not be UNLIMITED, and `PASSWORD_VERIFY_FUNCTION` should not be NULL.

## 3. Privilege Auditing and Review

### Who has DBA?

    SELECT grantee, granted_role, admin_option, default_role
    FROM dba_role_privs
    WHERE granted_role = 'DBA'
    ORDER BY grantee;

In most databases, only SYS and SYSTEM should have DBA. Any application schema with DBA is a red flag.

### Dangerous system privileges

These privileges allow privilege escalation or data exfiltration and should be tightly controlled:

    SELECT grantee, privilege, admin_option
    FROM dba_sys_privs
    WHERE privilege IN (
      'CREATE ANY TABLE',
      'DROP ANY TABLE',
      'SELECT ANY TABLE',
      'EXECUTE ANY PROCEDURE',
      'CREATE ANY PROCEDURE',
      'ALTER ANY TABLE',
      'ALTER DATABASE',
      'CREATE USER',
      'DROP USER',
      'GRANT ANY PRIVILEGE',
      'GRANT ANY ROLE',
      'BECOME USER',
      'ALTER SYSTEM'
    )
    AND grantee NOT IN ('SYS','SYSTEM','DBA','IMP_FULL_DATABASE','EXP_FULL_DATABASE')
    ORDER BY grantee, privilege;

### Roles granted to PUBLIC

Anything granted to PUBLIC is effectively granted to every user in the database:

    SELECT privilege, admin_option
    FROM dba_sys_privs
    WHERE grantee = 'PUBLIC'
    ORDER BY privilege;

    SELECT granted_role
    FROM dba_role_privs
    WHERE grantee = 'PUBLIC';

In most hardened databases, PUBLIC should have almost nothing. Execute on `UTL_FILE`, `UTL_TCP`, `UTL_HTTP`, and `DBMS_ADVISOR` granted to PUBLIC are common findings.

### Dangerous package grants

    SELECT grantee, table_name, privilege
    FROM dba_tab_privs
    WHERE table_name IN (
      'UTL_FILE', 'UTL_TCP', 'UTL_HTTP', 'UTL_SMTP',
      'DBMS_SCHEDULER', 'DBMS_JAVA', 'DBMS_JAVA_TEST',
      'DBMS_LDAP', 'HTTPURITYPE'
    )
    AND grantee NOT IN ('SYS','SYSTEM','DBA')
    ORDER BY table_name, grantee;

## 4. Auditing

### Unified Auditing (12c+)

Oracle 12c introduced Unified Auditing — a single audit trail in `UNIFIED_AUDIT_TRAIL` replacing the fragmented pre-12c approach. Check if it's enabled:

    SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

If the result is FALSE, you're on mixed-mode auditing. Enable pure unified auditing by relinking the Oracle binary (requires downtime) or accept mixed mode and configure both trails.

Create a policy covering the critical events every security audit expects:

    -- Audit DDL on sensitive tables
    CREATE AUDIT POLICY sensitive_ddl_policy
      ACTIONS
        CREATE TABLE, DROP TABLE, ALTER TABLE,
        CREATE USER, ALTER USER, DROP USER,
        GRANT, REVOKE
      WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''
      EVALUATE PER SESSION;

    -- Audit failed logins (always critical)
    CREATE AUDIT POLICY failed_login_policy
      ACTIONS LOGON
      WHEN '1=1'
      EVALUATE PER SESSION;

    AUDIT POLICY sensitive_ddl_policy;
    AUDIT POLICY failed_login_policy WHENEVER NOT SUCCESSFUL;

### What's currently being audited?

    -- Unified audit policies
    SELECT policy_name, enabled_option, success, failure
    FROM audit_unified_enabled_policies
    ORDER BY policy_name;

    -- Check recent audit events
    SELECT event_timestamp, db_username, action_name,
           object_schema, object_name, return_code
    FROM unified_audit_trail
    WHERE event_timestamp > SYSDATE - 1
    ORDER BY event_timestamp DESC
    FETCH FIRST 50 ROWS ONLY;

### Audit trail size management

Audit trails grow fast. Set up purging:

    -- Configure purge interval (keep 90 days)
    BEGIN
      DBMS_AUDIT_MGMT.INIT_CLEANUP(
        audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        default_cleanup_interval => 24
      );
    END;
    /

    BEGIN
      DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
        audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        audit_trail_purge_interval => 24,
        audit_trail_purge_name     => 'UNIFIED_AUDIT_PURGE',
        use_last_arch_timestamp    => TRUE
      );
    END;
    /

## 5. Network Encryption

### Native Network Encryption (sqlnet.ora)

Without encryption, Oracle credentials and data travel in plaintext across the network. Configure native encryption in `sqlnet.ora` on both client and server:

    # Server sqlnet.ora
    SQLNET.ENCRYPTION_SERVER = REQUIRED
    SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
    SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
    SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

Setting REQUIRED (rather than REQUESTED) ensures connections without encryption are rejected — not just preferred.

### TDE — Transparent Data Encryption

TDE encrypts data at rest — tablespaces, columns, or the entire database. Check current TDE status:

    -- Check if TDE wallet is open
    SELECT status, wallet_type FROM v$encryption_wallet;

    -- What's currently encrypted?
    SELECT tablespace_name, encrypted
    FROM dba_tablespaces
    WHERE encrypted = 'YES';

    -- Column-level encryption
    SELECT owner, table_name, column_name, encryption_alg
    FROM dba_encrypted_columns
    ORDER BY owner, table_name;

Enable TDE tablespace encryption (19c+):

    -- Set keystore location in sqlnet.ora first:
    -- ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/etc/oracle/wallet)))

    -- Create and open the keystore
    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/oracle/wallet' IDENTIFIED BY "WalletPassword123#";
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "WalletPassword123#";
    ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "WalletPassword123#" WITH BACKUP;

    -- Encrypt a tablespace (online, no downtime in 12.2+)
    ALTER TABLESPACE users ENCRYPTION ONLINE ENCRYPT;

## 6. Database Vault and Label Security

For environments requiring strict separation of duties — where even DBAs should not see application data — Oracle Database Vault prevents privileged users from accessing application schemas:

    -- Check if Database Vault is enabled
    SELECT parameter, value
    FROM v$option
    WHERE parameter IN ('Oracle Database Vault', 'Oracle Label Security');

    -- View existing realms
    SELECT name, description, enabled
    FROM dvsys.dba_dv_realm
    ORDER BY name;

Database Vault is a significant implementation — it requires careful planning as it will break DBA access patterns your team relies on. Enable it in a non-production environment first and test all DBA tooling including TuneVault's health checks, RMAN backup, and Data Pump operations.

## 7. Security Posture Checklist

Run these queries as your monthly security review baseline:

    -- 1. Open accounts with default passwords
    SELECT username FROM dba_users_with_defpwd
    JOIN dba_users USING (username)
    WHERE account_status = 'OPEN';

    -- 2. Accounts not logged in for 90+ days (candidates for locking)
    SELECT username, last_login, account_status
    FROM dba_users
    WHERE last_login < SYSDATE - 90
      AND account_status = 'OPEN'
      AND username NOT IN ('SYS','SYSTEM','DBSNMP')
    ORDER BY last_login;

    -- 3. Users with SYSDBA privilege
    SELECT * FROM v$pwfile_users WHERE sysdba = 'TRUE';

    -- 4. World-readable directory objects
    SELECT directory_name, directory_path, grantee, privilege
    FROM dba_directories d
    JOIN dba_tab_privs p ON d.directory_name = p.table_name
    WHERE p.grantee = 'PUBLIC';

    -- 5. Non-system objects in SYSTEM/SYSAUX tablespace
    SELECT owner, segment_name, segment_type
    FROM dba_segments
    WHERE tablespace_name IN ('SYSTEM','SYSAUX')
      AND owner NOT IN ('SYS','SYSTEM','AUDSYS','DBSNMP','XDB')
    ORDER BY owner;

## TuneVault Security Posture

TuneVault's Security Posture scanner automates this entire checklist — running all checks above continuously and scoring your database 0-100. It flags default passwords, excessive privileges, missing auditing, and unencrypted connections without requiring you to run any SQL manually. Connect your Oracle database at tunevault.app to get your baseline score.