Security and Auditing

Database auditing configuration and monitoring (Access: DBA or AUDIT SYSTEM privilege)

-- Enable auditing
-- Access: DBA or users with AUDIT SYSTEM privilege
AUDIT ALL ON hr.employees;
AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees BY ACCESS;
AUDIT SESSION BY hr;

-- Audit specific users
-- Access: DBA or users with AUDIT SYSTEM privilege
AUDIT ALL BY hr BY ACCESS;
AUDIT ALL BY hr WHENEVER SUCCESSFUL;
AUDIT ALL BY hr WHENEVER NOT SUCCESSFUL;

-- Audit system privileges
-- Access: DBA or users with AUDIT SYSTEM privilege
AUDIT CREATE USER;
AUDIT DROP USER;
AUDIT GRANT ANY PRIVILEGE;
AUDIT CREATE ANY TABLE;

-- View audit trail
-- Access: DBA or users with DBA_AUDIT_TRAIL privilege
SELECT 
    username,
    action_name,
    obj_name,
    timestamp,
    returncode
FROM dba_audit_trail
WHERE timestamp > SYSDATE - 7
ORDER BY timestamp DESC;

-- Audit trail by object
-- Access: DBA or users with DBA_AUDIT_OBJECT privilege
SELECT 
    username,
    action_name,
    obj_name,
    timestamp,
    returncode
FROM dba_audit_object
WHERE obj_name = 'EMPLOYEES'
ORDER BY timestamp DESC;

-- Disable auditing
-- Access: DBA or users with AUDIT SYSTEM privilege
NOAUDIT ALL ON hr.employees;
NOAUDIT ALL BY hr;

Virtual Private Database (VPD) and row-level security (Access: DBA or policy management privileges)

-- Create Virtual Private Database (VPD) policy
-- Access: DBA or users with CREATE POLICY privilege
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema => 'HR',
        object_name => 'EMPLOYEES',
        policy_name => 'EMP_DEPT_POLICY',
        function_schema => 'HR',
        policy_function => 'EMP_DEPT_POLICY_FUNC',
        statement_types => 'SELECT, INSERT, UPDATE, DELETE',
        update_check => TRUE
    );
END;
/

-- Create policy function
-- Access: Users with CREATE FUNCTION privilege
CREATE OR REPLACE FUNCTION emp_dept_policy_func(
    p_schema IN VARCHAR2,
    p_object IN VARCHAR2
) RETURN VARCHAR2
IS
    v_predicate VARCHAR2(4000);
BEGIN
    -- Only allow access to employees in same department
    v_predicate := 'department_id = (SELECT department_id FROM employees WHERE emp_id = USERENV(''CLIENT_IDENTIFIER''))';
    RETURN v_predicate;
END;
/

-- Enable/disable policy
-- Access: DBA or users with ALTER POLICY privilege
BEGIN
    DBMS_RLS.ENABLE_POLICY('HR', 'EMPLOYEES', 'EMP_DEPT_POLICY');
END;
/

BEGIN
    DBMS_RLS.DISABLE_POLICY('HR', 'EMPLOYEES', 'EMP_DEPT_POLICY');
END;
/

-- Drop policy
-- Access: DBA or users with DROP POLICY privilege
BEGIN
    DBMS_RLS.DROP_POLICY('HR', 'EMPLOYEES', 'EMP_DEPT_POLICY');
END;
/

Password policies and user profile management (Access: DBA or profile management privileges)

-- Create password profile
-- Access: DBA or users with CREATE PROFILE privilege
CREATE PROFILE hr_profile LIMIT
    SESSIONS_PER_USER 5
    CPU_PER_SESSION 10000
    CPU_PER_CALL 1000
    CONNECT_TIME 60
    IDLE_TIME 30
    LOGICAL_READS_PER_SESSION 10000
    LOGICAL_READS_PER_CALL 1000
    PRIVATE_SGA 1M
    COMPOSITE_LIMIT 500000
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_TIME 30
    PASSWORD_REUSE_MAX 5
    PASSWORD_LOCK_TIME 1
    PASSWORD_GRACE_TIME 7
    PASSWORD_VERIFY_FUNCTION verify_function;

-- Assign profile to user
-- Access: DBA or users with ALTER USER privilege
ALTER USER hr_user PROFILE hr_profile;

-- Password complexity function
-- Access: Users with CREATE FUNCTION privilege
CREATE OR REPLACE FUNCTION verify_function(
    username VARCHAR2,
    password VARCHAR2,
    old_password VARCHAR2
) RETURN BOOLEAN
IS
    n BOOLEAN;
    m INTEGER;
    differ INTEGER;
    isdigit BOOLEAN := FALSE;
    ischar BOOLEAN := FALSE;
    ispunct BOOLEAN := FALSE;
BEGIN
    -- Check password length
    IF LENGTH(password) < 8 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Password must be at least 8 characters long');
    END IF;
    
    -- Check for at least one digit
    FOR i IN 1..LENGTH(password) LOOP
        IF SUBSTR(password, i, 1) BETWEEN '0' AND '9' THEN
            isdigit := TRUE;
            EXIT;
        END IF;
    END LOOP;
    
    -- Check for at least one character
    FOR i IN 1..LENGTH(password) LOOP
        IF SUBSTR(password, i, 1) BETWEEN 'A' AND 'Z' OR 
           SUBSTR(password, i, 1) BETWEEN 'a' AND 'z' THEN
            ischar := TRUE;
            EXIT;
        END IF;
    END LOOP;
    
    IF NOT (isdigit AND ischar) THEN
        RAISE_APPLICATION_ERROR(-20002, 'Password must contain at least one digit and one character');
    END IF;
    
    RETURN TRUE;
END;
/