Oracle Security Hardening — Profiles, Auditing, and Privilege Reviews

DEFAULT Profile — What Auditors Check First

The DEFAULT profile applies to every user that does not have a custom profile assigned. In most Oracle installations the DEFAULT profile has unlimited or very permissive settings. Auditors always check this first.

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

Production minimum settings:

| Parameter | Recommended Value | Why |
|-----------|------------------|-----|
| PASSWORD_LIFE_TIME | 90 | Force quarterly rotation |
| FAILED_LOGIN_ATTEMPTS | 5 | Lock after 5 failures |
| PASSWORD_LOCK_TIME | 1/24 | Lock for 1 hour |
| PASSWORD_REUSE_TIME | 365 | Cannot reuse within 1 year |
| PASSWORD_REUSE_MAX | 10 | Cannot reuse last 10 passwords |
| PASSWORD_GRACE_TIME | 7 | 7-day warning before expiry |

Apply them:

ALTER PROFILE DEFAULT LIMIT
  PASSWORD_LIFE_TIME       90
  FAILED_LOGIN_ATTEMPTS    5
  PASSWORD_LOCK_TIME       1/24
  PASSWORD_REUSE_TIME      365
  PASSWORD_REUSE_MAX       10
  PASSWORD_GRACE_TIME      7;

Leave SESSIONS_PER_USER as UNLIMITED for application schema owners (APPS, HR, etc.) — connection pools open many sessions under the same database user and a hard cap will break the application.

Locking Unused Default Accounts

Oracle ships with dozens of default accounts. Any account that is unlocked and not actively managed is a potential entry point — default passwords are documented in Oracle's own manuals.

SELECT username, account_status, last_login, created
FROM dba_users
WHERE account_status NOT LIKE '%LOCKED%'
  AND username NOT IN (
    'SYS', 'SYSTEM', 'DBSNMP', 'DBSFWUSER',
    'APPQOSSYS', 'GSMADMIN_INTERNAL',
    'APPS', 'APPLSYS', 'APPLTMP'
  )
ORDER BY last_login DESC NULLS LAST;

Lock and expire any account not needed for your application:

ALTER USER OUTLN        ACCOUNT LOCK PASSWORD EXPIRE;
ALTER USER ANONYMOUS    ACCOUNT LOCK PASSWORD EXPIRE;
ALTER USER SCOTT        ACCOUNT LOCK PASSWORD EXPIRE;
ALTER USER MDDATA       ACCOUNT LOCK PASSWORD EXPIRE;
ALTER USER SPATIAL_WFS_ADMIN_USR ACCOUNT LOCK PASSWORD EXPIRE;

Do not lock SYS or SYSTEM directly — Oracle does not support that. Ensure SYS only connects AS SYSDBA and that REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED, not NONE.

Privilege Review — Queries Auditors Always Run

Excessive System Privileges

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

Any non-DBA application user holding SELECT ANY TABLE can read SYS.USER$ and every business table in the database. This should exist nowhere on a production system.

PUBLIC Grants (Frequently Overlooked)

SELECT owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'PUBLIC'
  AND owner NOT IN ('SYS', 'PUBLIC', 'XDB')
ORDER BY owner, table_name;

Every current and future database user inherits grants to PUBLIC. An EXECUTE grant on a business application procedure granted to PUBLIC is almost certainly a mistake — every DBA, developer, and read-only monitoring user gains that privilege automatically.

Users with DBA Role

SELECT grantee, admin_option, default_role
FROM dba_role_privs
WHERE granted_role = 'DBA'
  AND grantee NOT IN ('SYS', 'SYSTEM')
ORDER BY grantee;

The DBA role grants nearly unlimited database access. In most production databases, only SYS and SYSTEM should have it. Application schema owners should hold specific object privileges, not the DBA role.

Unified Auditing (12c+)

Check If Unified Auditing Is Active

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
-- TRUE  = pure unified or mixed mode (12c+ with recompile)
-- FALSE = traditional audit tables only (pre-12c behavior)

In 12c mixed mode, both traditional (aud$) and unified audit records are written. Pure unified auditing requires recompiling the Oracle binary — most sites stay in mixed mode.

Create and Enable an Audit Policy

-- Audit DDL on privileged operations and DML on sensitive tables
CREATE AUDIT POLICY prod_security_ops
  ACTIONS
    DELETE ON hr.employees,
    UPDATE ON hr.employees,
    DELETE ON ar.ra_customer_trx_all
  PRIVILEGES
    CREATE USER, DROP USER, ALTER USER,
    CREATE ROLE, DROP ROLE,
    GRANT ANY PRIVILEGE, REVOKE ANY PRIVILEGE;
AUDIT POLICY prod_security_ops;

To limit noise, audit only specific users rather than all sessions:

AUDIT POLICY prod_security_ops BY dba_admin, hr_super_user;

Query the Unified Audit Trail

SELECT dbusername, action_name, object_schema, object_name,
       sql_text,
       TO_CHAR(event_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') event_utc,
       unified_audit_policies
FROM unified_audit_trail
WHERE event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
  AND dbusername NOT IN ('SYS', 'DBSNMP')
ORDER BY event_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Purge the audit trail using DBMS_AUDIT_MGMT — do not delete from UNIFIED_AUDIT_TRAIL directly, it is a view over a secured LOB in the AUDSYS schema.

Network Encryption

Without network encryption, credentials and query results travel in plaintext over the network. Configure both server and client sqlnet.ora:

# $ORACLE_HOME/network/admin/sqlnet.ora (server-side)
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

Setting REQUIRED on the server means any client that does not support encryption will be refused. Use REQUESTED if you have legacy clients you cannot update yet.

Verify a session is encrypted after connecting:

SELECT network_service_banner
FROM v$session_connect_info
WHERE sid = SYS_CONTEXT('USERENV', 'SID')
  AND network_service_banner LIKE '%Encryption%';

If this query returns no rows, the connection is not encrypted. The client sqlnet.ora is either missing or has SQLNET.ENCRYPTION_CLIENT = REJECTED.

Password Verification Function

Without a verification function, Oracle does not enforce password complexity. A user can set their password to password1 and the database accepts it.

Assign Oracle's built-in strong verification function:

ALTER PROFILE DEFAULT LIMIT
  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

ora12c_strong_verify_function (defined in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql) enforces: minimum 8 characters, at least one letter and one digit, differs from username, differs from old password by at least 3 characters.

For stricter requirements, create a custom function:

CREATE OR REPLACE FUNCTION custom_pwd_verify(
  username     VARCHAR2,
  password     VARCHAR2,
  old_password VARCHAR2
) RETURN BOOLEAN AS
BEGIN
  IF LENGTH(password) < 12 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Password must be at least 12 characters');
  END IF;
  IF REGEXP_INSTR(password, '[A-Z]') = 0 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Password must contain at least one uppercase letter');
  END IF;
  IF REGEXP_INSTR(password, '[0-9]') = 0 THEN
    RAISE_APPLICATION_ERROR(-20003, 'Password must contain at least one digit');
  END IF;
  IF REGEXP_INSTR(password, '[^A-Za-z0-9]') = 0 THEN
    RAISE_APPLICATION_ERROR(-20004, 'Password must contain at least one special character');
  END IF;
  RETURN TRUE;
END;
/
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION custom_pwd_verify;

Test after applying:

-- This should raise ORA-28003 with your custom message
ALTER USER testuser IDENTIFIED BY short;
-- This should succeed
ALTER USER testuser IDENTIFIED BY "Str0ng!Pass#2026";