Oracle Security Best Practices: Authentication, Authorization, and Encryption

Oracle Database Security: Complete Guide to Protecting Your Data

Oracle provides a comprehensive security framework to protect your database from unauthorized access and ensure data confidentiality, integrity, and availability. This guide covers essential security practices every Oracle DBA and developer should know.

1. User Management and Authentication

Create Secure Users

-- Create user with strong password and specific profile
CREATE USER app_user IDENTIFIED BY "Str0ngP@ssw0rd#2024"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
PROFILE secure_profile
PASSWORD EXPIRE;

-- Set account properties
ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user PASSWORD EXPIRE;

-- Create user with external authentication (OS authentication)
CREATE USER opsys_user IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users;

Password Policies with Profiles

-- Create a secure profile
CREATE PROFILE secure_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 5      -- Lock after 5 failed attempts
  PASSWORD_LIFE_TIME 90        -- Expire password every 90 days
  PASSWORD_REUSE_TIME 365      -- Can't reuse password for 365 days
  PASSWORD_REUSE_MAX 10        -- Remember last 10 passwords
  PASSWORD_LOCK_TIME 1/24      -- Lock for 1 hour on failure
  PASSWORD_GRACE_TIME 7        -- 7-day grace period after expiration
  SESSIONS_PER_USER 10         -- Maximum 10 concurrent sessions
  IDLE_TIME 30;                -- Disconnect after 30 minutes idle

-- Assign profile to user
ALTER USER hr_user PROFILE secure_profile;

-- View password settings
SELECT * FROM dba_profiles WHERE profile = 'SECURE_PROFILE';

2. Privilege Management

System Privileges

-- Grant specific system privileges
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_user;
GRANT CREATE PROCEDURE, CREATE TRIGGER TO developer_role;

-- Revoke unnecessary privileges
REVOKE CREATE ANY TABLE FROM app_user;

-- View system privileges
SELECT * FROM dba_sys_privs WHERE grantee = 'APP_USER';

Object Privileges

-- Grant specific table privileges
GRANT SELECT, INSERT, UPDATE ON hr.employees TO app_user;
GRANT SELECT ON hr.departments TO reporting_role;

-- Column-level privileges
GRANT UPDATE (salary, commission_pct) ON hr.employees TO manager_role;

-- Grant with grant option (delegate)
GRANT SELECT ON sales.transactions TO sales_mgr WITH GRANT OPTION;

-- View object privileges
SELECT * FROM dba_tab_privs WHERE grantee = 'APP_USER';

3. Role-Based Access Control (RBAC)

Create and Manage Roles

-- Create roles
CREATE ROLE read_only_role;
CREATE ROLE data_entry_role;
CREATE ROLE manager_role;

-- Grant privileges to roles
GRANT SELECT ANY TABLE TO read_only_role;
GRANT INSERT, UPDATE ON sales.orders TO data_entry_role;
GRANT read_only_role, data_entry_role TO manager_role;

-- Grant roles to users
GRANT read_only_role TO reporting_user;
GRANT data_entry_role TO clerk_user;

-- Set default and enabled roles
ALTER USER app_user DEFAULT ROLE read_only_role;
ALTER USER app_user DEFAULT ROLE ALL EXCEPT manager_role;

-- View role information
SELECT * FROM dba_roles;
SELECT * FROM dba_role_privs WHERE grantee = 'APP_USER';

4. Database Encryption

Transparent Data Encryption (TDE)

-- Check if TDE is configured
SELECT * FROM v$encryption_wallet;

-- Create tablespace with encryption
CREATE TABLESPACE encrypted_ts
DATAFILE '/u01/oradata/encrypted01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT);

-- Create table in encrypted tablespace
CREATE TABLE sensitive_data (
    id NUMBER,
    secret_info VARCHAR2(4000)
) TABLESPACE encrypted_ts;

-- Encrypt existing table columns
ALTER TABLE hr.employees MODIFY (
    ssn ENCRYPT USING 'AES256' NO SALT,
    salary ENCRYPT USING 'AES192'
);

Column-Level Encryption with DBMS_CRYPTO

-- Create encryption key table
CREATE TABLE encryption_keys (
    key_id NUMBER PRIMARY KEY,
    key_value RAW(32),
    created_date DATE DEFAULT SYSDATE
);

-- Encrypt data
DECLARE
    l_key RAW(32) := utl_raw.cast_to_raw('MySecretKey1234567890123456');
    l_encrypted RAW(2000);
BEGIN
    l_encrypted := DBMS_CRYPTO.ENCRYPT(
        src => utl_raw.cast_to_raw('Sensitive Data'),
        typ => DBMS_CRYPTO.DES_CBC_PKCS5,
        key => l_key
    );
    
    INSERT INTO sensitive_data (id, encrypted_column)
    VALUES (1, l_encrypted);
END;
/

-- Decrypt data
DECLARE
    l_key RAW(32) := utl_raw.cast_to_raw('MySecretKey1234567890123456');
    l_decrypted VARCHAR2(4000);
BEGIN
    SELECT utl_raw.cast_to_varchar2(
        DBMS_CRYPTO.DECRYPT(
            src => encrypted_column,
            typ => DBMS_CRYPTO.DES_CBC_PKCS5,
            key => l_key
        )
    ) INTO l_decrypted
    FROM sensitive_data
    WHERE id = 1;
    
    DBMS_OUTPUT.PUT_LINE('Decrypted: ' || l_decrypted);
END;
/

5. Database Auditing

Standard Auditing

-- Enable database auditing
AUDIT CREATE TABLE, DROP TABLE BY ACCESS;
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE BY ACCESS;
AUDIT EXECUTE ANY PROCEDURE BY ACCESS;

-- Audit specific users
AUDIT ALL BY hr_user BY ACCESS;
AUDIT NOT EXISTS;

-- View audit trail
SELECT username, action_name, obj_name, timestamp
FROM dba_audit_trail
WHERE timestamp > SYSDATE - 7
ORDER BY timestamp DESC;

-- Clean up old audit records (retain 90 days)
DELETE FROM sys.aud$ WHERE ntimestamp# < SYSDATE - 90;
COMMIT;

Fine-Grained Auditing (FGA)

-- Create FGA policy for salary access
BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'SALARY_ACCESS_POLICY',
        audit_condition => 'salary > 100000',
        audit_column    => 'SALARY',
        enable          => TRUE,
        statement_types => 'SELECT,UPDATE'
    );
END;
/

-- View FGA audit trail
SELECT db_user, object_schema, object_name, policy_name, sql_text, timestamp
FROM dba_fga_audit_trail
WHERE timestamp > SYSDATE - 30;

-- Drop FGA policy
BEGIN
    DBMS_FGA.DROP_POLICY(
        object_schema => 'HR',
        object_name   => 'EMPLOYEES',
        policy_name   => 'SALARY_ACCESS_POLICY'
    );
END;
/

6. Virtual Private Database (VPD)

Implement Row-Level Security

-- Create security policy function
CREATE OR REPLACE FUNCTION hr_security_policy(
    schema_name IN VARCHAR2,
    table_name IN VARCHAR2
) RETURN VARCHAR2 AS
    l_predicate VARCHAR2(1000);
BEGIN
    -- Employees can only see their own department
    IF SYS_CONTEXT('USERENV', 'SESSION_USER') != 'HR' THEN
        l_predicate := 'department_id = ' || 
            SYS_CONTEXT('HR_CTX', 'USER_DEPT_ID');
    ELSE
        l_predicate := NULL; -- HR sees everything
    END IF;
    
    RETURN l_predicate;
END;
/

-- Apply VPD policy
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'EMP_SECURITY_POLICY',
        function_schema => 'HR',
        policy_function => 'hr_security_policy',
        statement_types => 'SELECT,UPDATE,DELETE',
        update_check    => TRUE,
        enable          => TRUE
    );
END;
/

7. Security Views and Monitoring

Monitor User Activity

-- Currently logged in users
SELECT username, osuser, machine, program, logon_time
FROM v$session
WHERE username IS NOT NULL
ORDER BY logon_time;

-- User privileges review
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee IN (SELECT username FROM dba_users)
ORDER BY grantee, privilege;

-- Object privileges review
SELECT owner, table_name, grantee, privilege, grantable
FROM dba_tab_privs
WHERE owner = 'HR'
ORDER BY table_name, grantee;

-- Failed login attempts
SELECT username, os_username, userhost, timestamp, returncode
FROM dba_audit_session
WHERE returncode != 0
AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC;

8. Security Best Practices Checklist

1. Authentication

Use strong password policies with profiles
Implement password complexity verification
Consider multi-factor authentication (Oracle Advanced Security)
Use proxy authentication for application connections

2. Authorization

Follow principle of least privilege
Use roles instead of direct privilege grants
Regularly review user privileges
Implement separation of duties

3. Data Protection

Encrypt sensitive data at rest (TDE)
Consider encrypting data in transit (SSL/TLS)
Implement data masking for non-production
Use redaction for sensitive data display

4. Auditing

Enable auditing for critical operations
Regularly review audit logs
Implement Fine-Grained Auditing for sensitive data
Secure audit trail from tampering

5. Network Security

Use database firewall (Oracle Audit Vault)
Implement network encryption
Restrict database listener access
Use valid node checking

6. Patch Management

Apply Critical Patch Updates (CPU) regularly
Stay current with security alerts
Test patches in non-production first
Maintain security configuration standards

Sample Security Implementation Script

-- Complete security setup for new application
BEGIN
    -- Create secure profile
    CREATE PROFILE app_profile LIMIT
        FAILED_LOGIN_ATTEMPTS 3
        PASSWORD_LIFE_TIME 60
        PASSWORD_REUSE_TIME 180
        PASSWORD_REUSE_MAX 5
        PASSWORD_LOCK_TIME 1/24
        PASSWORD_GRACE_TIME 5
        SESSIONS_PER_USER 5
        IDLE_TIME 20;
    
    -- Create application roles
    CREATE ROLE app_read_role;
    CREATE ROLE app_write_role;
    CREATE ROLE app_admin_role;
    
    -- Grant privileges to roles
    GRANT CREATE SESSION TO app_read_role;
    GRANT SELECT ON app_schema.* TO app_read_role;
    GRANT INSERT, UPDATE, DELETE ON app_schema.transactions TO app_write_role;
    GRANT app_read_role, app_write_role TO app_admin_role;
    
    -- Create application user
    CREATE USER app_user IDENTIFIED BY "InitialP@ss123"
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA 500M ON users
    PROFILE app_profile
    PASSWORD EXPIRE;
    
    -- Grant roles
    GRANT app_read_role TO app_user;
    
    -- Enable auditing
    AUDIT ALL BY app_user BY ACCESS;
    
    DBMS_OUTPUT.PUT_LINE('Security setup completed successfully');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
END;
/

Implementing comprehensive security measures is essential for protecting sensitive data and ensuring regulatory compliance. Regular security reviews, monitoring, and updates should be part of every Oracle database administrator's routine.