database_security_hardening.sql
-- ------------------------------------------------------------------------------------- File Name : database_security_hardening.sql-- Author : Pierre Montbleau-- Description : Oracle database security hardening and compliance assessment utility-- Purpose : Implement security best practices, assess vulnerabilities, and enforce security policies-- Call Syntax : @F:\DBA\Scripts\database_security_hardening.sql (action) (hardening_level) (apply_changes)-- Parameters : action - Security action (ASSESS, HARDEN, COMPLIANCE, AUDIT, REPORT)-- hardening_level - Level of hardening (BASIC, STANDARD, STRICT, CIS)-- apply_changes - Apply changes (YES/NO) or DRYRUN for simulation-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_security_hardening_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Database Security Hardening...PROMPT =======================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Hardening Level: &2PROMPT Apply Changes: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_hardening_level VARCHAR2(20) := UPPER('&2'); v_apply_changes VARCHAR2(20) := UPPER('&3');BEGIN IF v_action NOT IN ('ASSESS', 'HARDEN', 'COMPLIANCE', 'AUDIT', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be ASSESS, HARDEN, COMPLIANCE, AUDIT, or REPORT'); END IF; IF v_hardening_level NOT IN ('BASIC', 'STANDARD', 'STRICT', 'CIS') THEN RAISE_APPLICATION_ERROR(-20002, 'Hardening level must be BASIC, STANDARD, STRICT, or CIS'); END IF; IF v_apply_changes NOT IN ('YES', 'NO', 'DRYRUN') THEN RAISE_APPLICATION_ERROR(-20003, 'Apply changes must be YES, NO, or DRYRUN'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful'); DBMS_OUTPUT.PUT_LINE('Mode: ' || CASE WHEN v_apply_changes = 'YES' THEN 'LIVE' ELSE 'SIMULATION' END);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- SECURITY ASSESSMENT SUMMARY: Current security posture overview-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. SECURITY POSTURE ASSESSMENTPROMPT ===============================
COLUMN security_area FORMAT A30COLUMN current_status FORMAT A20COLUMN risk_level FORMAT A10COLUMN recommendation FORMAT A60
SELECT 'Default Accounts Status' as security_area, CASE WHEN COUNT(*) = 0 THEN 'SECURE' ELSE 'VULNERABLE' END as current_status, CASE WHEN COUNT(*) > 0 THEN 'HIGH' ELSE 'LOW' END as risk_level, CASE WHEN COUNT(*) > 0 THEN 'Lock or remove default user accounts' ELSE 'No action required' END as recommendationFROM dba_usersWHERE username IN ('SCOTT', 'HR', 'OE', 'SH', 'PM') AND account_status = 'OPEN'UNION ALLSELECT 'Password Policies', CASE WHEN COUNT(*) > 0 THEN 'ENABLED' ELSE 'DISABLED' END, CASE WHEN COUNT(*) = 0 THEN 'HIGH' ELSE 'LOW' END, CASE WHEN COUNT(*) = 0 THEN 'Implement password complexity policies' ELSE 'Password policies active' ENDFROM dba_profilesWHERE resource_type = 'PASSWORD' AND resource_name = 'FAILED_LOGIN_ATTEMPTS' AND limit != 'UNLIMITED'UNION ALLSELECT 'Database Auditing', CASE WHEN COUNT(*) > 0 THEN 'ENABLED' ELSE 'DISABLED' END, CASE WHEN COUNT(*) = 0 THEN 'MEDIUM' ELSE 'LOW' END, CASE WHEN COUNT(*) = 0 THEN 'Enable basic database auditing' ELSE 'Auditing configured' ENDFROM dba_audit_trailWHERE ROWNUM = 1UNION ALLSELECT 'Encryption Status', CASE WHEN (SELECT COUNT(*) FROM v$encryption_wallet WHERE status = 'OPEN') > 0 THEN 'ENABLED' ELSE 'DISABLED' END, CASE WHEN (SELECT COUNT(*) FROM v$encryption_wallet WHERE status = 'OPEN') = 0 THEN 'MEDIUM' ELSE 'LOW' END, CASE WHEN (SELECT COUNT(*) FROM v$encryption_wallet WHERE status = 'OPEN') = 0 THEN 'Consider TDE implementation' ELSE 'Encryption active' ENDFROM dualORDER BY 3 DESC, 1;
-- ------------------------------------------------------------------------------------- USER ACCOUNT SECURITY: User account analysis and hardening-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. USER ACCOUNT SECURITY ANALYSISPROMPT ===================================
COLUMN username FORMAT A25COLUMN account_status FORMAT A20COLUMN profile FORMAT A30COLUMN last_login FORMAT A20COLUMN security_risk FORMAT A15
SELECT * FROM ( SELECT username, account_status, profile, TO_CHAR(last_login, 'YYYY-MM-DD HH24:MI:SS') as last_login, CASE WHEN account_status != 'OPEN' THEN 'LOW' WHEN profile = 'DEFAULT' THEN 'MEDIUM' WHEN last_login IS NULL OR last_login < SYSDATE - 90 THEN 'MEDIUM' WHEN username IN (SELECT grantee FROM dba_sys_privs WHERE privilege LIKE '%ANY%') THEN 'HIGH' ELSE 'LOW' END as security_risk FROM dba_users WHERE username NOT IN ('SYS', 'SYSTEM') AND oracle_maintained = 'N') user_securityWHERE UPPER('CIS') IN ('STRICT', 'CIS') OR security_risk != 'LOW'ORDER BY security_risk DESC, username;
-- ------------------------------------------------------------------------------------- PRIVILEGE ANALYSIS: Excessive privilege identification-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. EXCESSIVE PRIVILEGE ANALYSISPROMPT ================================
COLUMN grantee FORMAT A35COLUMN privilege_type FORMAT A20COLUMN privilege_count FORMAT 999COLUMN risk_level FORMAT A10COLUMN recommendation FORMAT A60
SELECT grantee, 'SYSTEM_PRIVILEGE' as privilege_type, COUNT(*) as privilege_count, CASE WHEN COUNT(*) > 10 THEN 'HIGH' WHEN COUNT(*) > 5 THEN 'MEDIUM' ELSE 'LOW' END as risk_level, CASE WHEN COUNT(*) > 10 THEN 'Review and reduce system privileges' WHEN COUNT(*) > 5 THEN 'Consider privilege consolidation' ELSE 'Acceptable privilege count' END as recommendationFROM dba_sys_privsWHERE grantee NOT IN ('SYS', 'SYSTEM') AND grantee NOT LIKE 'SYS%'GROUP BY granteeHAVING COUNT(*) > 3UNION ALLSELECT grantee, 'ROLE_PRIVILEGE', COUNT(*), CASE WHEN COUNT(*) > 5 THEN 'HIGH' WHEN COUNT(*) > 3 THEN 'MEDIUM' ELSE 'LOW' END, CASE WHEN COUNT(*) > 5 THEN 'Review role assignments' WHEN COUNT(*) > 3 THEN 'Monitor role usage' ELSE 'Acceptable role count' ENDFROM dba_role_privsWHERE grantee NOT IN ('SYS', 'SYSTEM') AND granted_role NOT IN ('CONNECT', 'RESOURCE')GROUP BY granteeHAVING COUNT(*) > 2ORDER BY 4 DESC, 3 DESC;
-- ------------------------------------------------------------------------------------- PASSWORD POLICY ASSESSMENT: Password security configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. PASSWORD POLICY ASSESSMENTPROMPT ==============================
COLUMN profile FORMAT A30COLUMN parameter FORMAT A30COLUMN current_value FORMAT A30COLUMN recommended_value FORMAT A20COLUMN compliance FORMAT A20
SELECT profile, resource_name as parameter, limit as current_value, CASE resource_name WHEN 'FAILED_LOGIN_ATTEMPTS' THEN '10' WHEN 'PASSWORD_LIFE_TIME' THEN '90' WHEN 'PASSWORD_REUSE_TIME' THEN '365' WHEN 'PASSWORD_REUSE_MAX' THEN '10' WHEN 'PASSWORD_LOCK_TIME' THEN '1' WHEN 'PASSWORD_GRACE_TIME' THEN '7' ELSE 'UNLIMITED' END as recommended_value, CASE WHEN limit = CASE resource_name WHEN 'FAILED_LOGIN_ATTEMPTS' THEN '10' WHEN 'PASSWORD_LIFE_TIME' THEN '90' WHEN 'PASSWORD_REUSE_TIME' THEN '365' WHEN 'PASSWORD_REUSE_MAX' THEN '10' WHEN 'PASSWORD_LOCK_TIME' THEN '1' WHEN 'PASSWORD_GRACE_TIME' THEN '7' ELSE limit END THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as complianceFROM dba_profilesWHERE resource_type = 'PASSWORD' AND (profile = 'DEFAULT' OR profile IN (SELECT DISTINCT profile FROM dba_users WHERE oracle_maintained = 'N'))ORDER BY profile, resource_name;
-- ------------------------------------------------------------------------------------- SECURITY HARDENING ACTIONS: Implement security controls-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. SECURITY HARDENING ACTIONSPROMPT ==============================
BEGIN IF UPPER('&1') IN ('HARDEN', 'COMPLIANCE') AND UPPER('&3') IN ('YES', 'DRYRUN') THEN DECLARE v_hardening_level VARCHAR2(20) := UPPER('&2'); v_apply_changes VARCHAR2(20) := UPPER('&3'); v_action_count NUMBER := 0; BEGIN DBMS_OUTPUT.PUT_LINE('Executing security hardening actions...'); DBMS_OUTPUT.PUT_LINE('Mode: ' || CASE WHEN v_apply_changes = 'YES' THEN 'LIVE' ELSE 'DRY RUN' END); -- Action 1: Lock default accounts IF v_hardening_level IN ('STANDARD', 'STRICT', 'CIS') THEN FOR user_rec IN ( SELECT username FROM dba_users WHERE username IN ('SCOTT', 'HR', 'OE', 'SH', 'PM') AND account_status = 'OPEN' ) LOOP IF v_apply_changes = 'YES' THEN EXECUTE IMMEDIATE 'ALTER USER ' || user_rec.username || ' ACCOUNT LOCK'; END IF; DBMS_OUTPUT.PUT_LINE('Action: Locked default account - ' || user_rec.username); v_action_count := v_action_count + 1; END LOOP; END IF; -- Action 2: Implement password policies IF v_hardening_level IN ('STRICT', 'CIS') THEN IF v_apply_changes = 'YES' THEN EXECUTE IMMEDIATE 'ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 10 PASSWORD_LOCK_TIME 1 PASSWORD_GRACE_TIME 7'; END IF; DBMS_OUTPUT.PUT_LINE('Action: Enhanced password policy for DEFAULT profile'); v_action_count := v_action_count + 1; END IF; -- Action 3: Enable basic auditing IF v_hardening_level = 'CIS' THEN IF v_apply_changes = 'YES' THEN EXECUTE IMMEDIATE 'AUDIT CREATE SESSION'; EXECUTE IMMEDIATE 'AUDIT CREATE ANY TABLE'; EXECUTE IMMEDIATE 'AUDIT ALTER ANY TABLE'; EXECUTE IMMEDIATE 'AUDIT DROP ANY TABLE'; END IF; DBMS_OUTPUT.PUT_LINE('Action: Enabled basic database auditing'); v_action_count := v_action_count + 1; END IF; -- Action 4: Secure remote authentication IF v_hardening_level IN ('STRICT', 'CIS') THEN BEGIN IF v_apply_changes = 'YES' THEN EXECUTE IMMEDIATE 'ALTER SYSTEM SET REMOTE_OS_AUTHENT = FALSE SCOPE = BOTH'; END IF; DBMS_OUTPUT.PUT_LINE('Action: Disabled remote OS authentication'); v_action_count := v_action_count + 1; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Note: REMOTE_OS_AUTHENT already disabled or not applicable'); END; END IF; DBMS_OUTPUT.PUT_LINE('Security hardening completed: ' || v_action_count || ' actions processed'); DBMS_OUTPUT.PUT_LINE('Changes applied: ' || v_apply_changes); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error during security hardening: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Security hardening skipped (action: ' || UPPER('&1') || ', apply: ' || UPPER('&3') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- DATABASE PARAMETER SECURITY: Security-related init parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. SECURITY PARAMETER ASSESSMENTPROMPT ==================================
COLUMN parameter_name FORMAT A30COLUMN current_value FORMAT A20COLUMN recommended_value FORMAT A20COLUMN security_impact FORMAT A40
SELECT name as parameter_name, value as current_value, CASE name WHEN 'remote_os_authent' THEN 'FALSE' WHEN 'os_authent_prefix' THEN 'OPS$' WHEN '07_DICTIONARY_ACCESSIBILITY' THEN 'FALSE' WHEN 'audit_trail' THEN 'DB,EXTENDED' WHEN 'sec_case_sensitive_logon' THEN 'TRUE' ELSE value END as recommended_value, CASE name WHEN 'remote_os_authent' THEN 'HIGH - Disables remote OS auth' WHEN 'os_authent_prefix' THEN 'MEDIUM - OS authentication prefix' WHEN '07_DICTIONARY_ACCESSIBILITY' THEN 'HIGH - Restricts dictionary access' WHEN 'audit_trail' THEN 'MEDIUM - Enables auditing' WHEN 'sec_case_sensitive_logon' THEN 'MEDIUM - Enforces case sensitivity' ELSE 'LOW - No security impact' END as security_impactFROM v$parameterWHERE name IN ( 'remote_os_authent', 'os_authent_prefix', '07_DICTIONARY_ACCESSIBILITY', 'audit_trail', 'sec_case_sensitive_logon') AND UPPER('&2') IN ('STRICT', 'CIS')ORDER BY 4;
-- ------------------------------------------------------------------------------------- AUDIT CONFIGURATION: Current auditing settings-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. AUDIT CONFIGURATION STATUSPROMPT ==============================
COLUMN audit_type FORMAT A30COLUMN status FORMAT A20COLUMN last_audit FORMAT A20COLUMN recommendation FORMAT A60
SELECT 'Standard Database Auditing' as audit_type, CASE WHEN COUNT(*) > 0 THEN 'ENABLED' ELSE 'DISABLED' END as status, TO_CHAR(MAX(timestamp), 'YYYY-MM-DD HH24:MI:SS') as last_audit, CASE WHEN COUNT(*) = 0 THEN 'Enable basic database auditing' ELSE 'Auditing active - review policies' END as recommendationFROM dba_audit_trailWHERE ROWNUM = 1UNION ALLSELECT 'Fine-Grained Auditing', CASE WHEN COUNT(*) > 0 THEN 'ENABLED' ELSE 'DISABLED' END, 'N/A', CASE WHEN COUNT(*) = 0 THEN 'Consider FGA for sensitive data' ELSE 'FGA policies active' ENDFROM dba_audit_policiesUNION ALLSELECT 'Mandatory Auditing', 'ENABLED', 'N/A', 'System-level auditing active'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_stmt_audit_opts WHERE user_name IS NULL)ORDER BY 2;
-- ------------------------------------------------------------------------------------- SECURITY COMPLIANCE CHECKLIST: Compliance verification-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. SECURITY COMPLIANCE CHECKLISTPROMPT =================================
COLUMN compliance_item FORMAT A50COLUMN status FORMAT A15COLUMN requirement FORMAT A40COLUMN evidence FORMAT A30
SELECT 'Default accounts are locked' as compliance_item, CASE WHEN COUNT(*) = 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as status, 'Lock all default demonstration accounts' as requirement, TO_CHAR(COUNT(*)) || ' accounts open' as evidenceFROM dba_usersWHERE username IN ('SCOTT', 'HR', 'OE', 'SH', 'PM') AND account_status = 'OPEN'UNION ALLSELECT 'Password policies enforced', CASE WHEN COUNT(*) > 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, 'Implement password complexity rules', TO_CHAR(COUNT(*)) || ' policies active'FROM dba_profilesWHERE resource_type = 'PASSWORD' AND resource_name = 'FAILED_LOGIN_ATTEMPTS' AND limit != 'UNLIMITED'UNION ALLSELECT 'Excessive privileges reviewed', CASE WHEN COUNT(*) = 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, 'Review users with excessive privileges', TO_CHAR(COUNT(*)) || ' users to review'FROM ( SELECT grantee FROM dba_sys_privs WHERE grantee NOT IN ('SYS', 'SYSTEM') GROUP BY grantee HAVING COUNT(*) > 10)UNION ALLSELECT 'Database auditing enabled', CASE WHEN COUNT(*) > 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, 'Enable basic database auditing', CASE WHEN COUNT(*) > 0 THEN 'Auditing active' ELSE 'No audit records' ENDFROM dba_audit_trailWHERE ROWNUM = 1ORDER BY 2, 1;
-- ------------------------------------------------------------------------------------- SECURITY RECOMMENDATIONS: Actionable security improvements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. SECURITY IMPROVEMENT RECOMMENDATIONSPROMPT ========================================
COLUMN recommendation FORMAT A60COLUMN priority FORMAT A10COLUMN effort FORMAT A15COLUMN impact FORMAT A15
SELECT 'Implement Transparent Data Encryption (TDE)' as recommendation, 'HIGH' as priority, 'MEDIUM' as effort, 'HIGH' as impactFROM dualWHERE (SELECT COUNT(*) FROM v$encryption_wallet WHERE status = 'OPEN') = 0UNION ALLSELECT 'Enable fine-grained auditing for sensitive tables', 'HIGH', 'LOW', 'HIGH'FROM dualWHERE (SELECT COUNT(*) FROM dba_audit_policies) = 0UNION ALLSELECT 'Implement database vault for administrative control', 'MEDIUM', 'HIGH', 'HIGH'FROM dualWHERE UPPER('&2') = 'CIS'UNION ALLSELECT 'Regular user access reviews and certification', 'MEDIUM', 'MEDIUM', 'MEDIUM'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_users WHERE last_login IS NULL OR last_login < SYSDATE - 180)UNION ALLSELECT 'Implement network encryption (SQLNet)', 'MEDIUM', 'LOW', 'MEDIUM'FROM dualWHERE UPPER('&2') IN ('STRICT', 'CIS')ORDER BY 2,3;
-- ------------------------------------------------------------------------------------- COMPLETION: Security hardening summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =======================================PROMPT DATABASE SECURITY HARDENING COMPLETEDPROMPT =======================================PROMPT Action: &1PROMPT Hardening Level: &2PROMPT Apply Changes: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Security Summary:PROMPT - Review HIGH priority recommendations immediatelyPROMPT - Address compliance gaps identifiedPROMPT - Schedule regular security assessmentsPROMPT - Document security policies and proceduresPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_security_hardening_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Database Security Hardening...PROMPT =======================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Hardening Level: &2PROMPT Apply Changes: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_hardening_level VARCHAR2(20) := UPPER('&2'); v_apply_changes VARCHAR2(20) := UPPER('&3');BEGIN IF v_action NOT IN ('ASSESS', 'HARDEN', 'COMPLIANCE', 'AUDIT', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be ASSESS, HARDEN, COMPLIANCE, AUDIT, or REPORT'); END IF; IF v_hardening_level NOT IN ('BASIC', 'STANDARD', 'STRICT', 'CIS') THEN RAISE_APPLICATION_ERROR(-20002, 'Hardening level must be BASIC, STANDARD, STRICT, or CIS'); END IF; IF v_apply_changes NOT IN ('YES', 'NO', 'DRYRUN') THEN RAISE_APPLICATION_ERROR(-20003, 'Apply changes must be YES, NO, or DRYRUN'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful'); DBMS_OUTPUT.PUT_LINE('Mode: ' || CASE WHEN v_apply_changes = 'YES' THEN 'LIVE' ELSE 'SIMULATION' END);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- SECURITY ASSESSMENT SUMMARY: Current security posture overview-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. SECURITY POSTURE ASSESSMENTPROMPT ===============================
COLUMN security_area FORMAT A30COLUMN current_status FORMAT A20COLUMN risk_level FORMAT A10COLUMN recommendation FORMAT A60
SELECT 'Default Accounts Status' as security_area, CASE WHEN COUNT(*) = 0 THEN 'SECURE' ELSE 'VULNERABLE' END as current_status, CASE WHEN COUNT(*) > 0 THEN 'HIGH' ELSE 'LOW' END as risk_level, CASE WHEN COUNT(*) > 0 THEN 'Lock or remove default user accounts' ELSE 'No action required' END as recommendationFROM dba_usersWHERE username IN ('SCOTT', 'HR', 'OE', 'SH', 'PM') AND account_status = 'OPEN'UNION ALLSELECT 'Password Policies', CASE WHEN COUNT(*) > 0 THEN 'ENABLED' ELSE 'DISABLED' END, CASE WHEN COUNT(*) = 0 THEN 'HIGH' ELSE 'LOW' END, CASE WHEN COUNT(*) = 0 THEN 'Implement password complexity policies' ELSE 'Password policies active' ENDFROM dba_profilesWHERE resource_type = 'PASSWORD' AND resource_name = 'FAILED_LOGIN_ATTEMPTS' AND limit != 'UNLIMITED'UNION ALLSELECT 'Database Auditing', CASE WHEN COUNT(*) > 0 THEN 'ENABLED' ELSE 'DISABLED' END, CASE WHEN COUNT(*) = 0 THEN 'MEDIUM' ELSE 'LOW' END, CASE WHEN COUNT(*) = 0 THEN 'Enable basic database auditing' ELSE 'Auditing configured' ENDFROM dba_audit_trailWHERE ROWNUM = 1UNION ALLSELECT 'Encryption Status', CASE WHEN (SELECT COUNT(*) FROM v$encryption_wallet WHERE status = 'OPEN') > 0 THEN 'ENABLED' ELSE 'DISABLED' END, CASE WHEN (SELECT COUNT(*) FROM v$encryption_wallet WHERE status = 'OPEN') = 0 THEN 'MEDIUM' ELSE 'LOW' END, CASE WHEN (SELECT COUNT(*) FROM v$encryption_wallet WHERE status = 'OPEN') = 0 THEN 'Consider TDE implementation' ELSE 'Encryption active' ENDFROM dualORDER BY 3 DESC, 1;
-- ------------------------------------------------------------------------------------- USER ACCOUNT SECURITY: User account analysis and hardening-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. USER ACCOUNT SECURITY ANALYSISPROMPT ===================================
COLUMN username FORMAT A25COLUMN account_status FORMAT A20COLUMN profile FORMAT A30COLUMN last_login FORMAT A20COLUMN security_risk FORMAT A15
SELECT * FROM ( SELECT username, account_status, profile, TO_CHAR(last_login, 'YYYY-MM-DD HH24:MI:SS') as last_login, CASE WHEN account_status != 'OPEN' THEN 'LOW' WHEN profile = 'DEFAULT' THEN 'MEDIUM' WHEN last_login IS NULL OR last_login < SYSDATE - 90 THEN 'MEDIUM' WHEN username IN (SELECT grantee FROM dba_sys_privs WHERE privilege LIKE '%ANY%') THEN 'HIGH' ELSE 'LOW' END as security_risk FROM dba_users WHERE username NOT IN ('SYS', 'SYSTEM') AND oracle_maintained = 'N') user_securityWHERE UPPER('CIS') IN ('STRICT', 'CIS') OR security_risk != 'LOW'ORDER BY security_risk DESC, username;
-- ------------------------------------------------------------------------------------- PRIVILEGE ANALYSIS: Excessive privilege identification-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. EXCESSIVE PRIVILEGE ANALYSISPROMPT ================================
COLUMN grantee FORMAT A35COLUMN privilege_type FORMAT A20COLUMN privilege_count FORMAT 999COLUMN risk_level FORMAT A10COLUMN recommendation FORMAT A60
SELECT grantee, 'SYSTEM_PRIVILEGE' as privilege_type, COUNT(*) as privilege_count, CASE WHEN COUNT(*) > 10 THEN 'HIGH' WHEN COUNT(*) > 5 THEN 'MEDIUM' ELSE 'LOW' END as risk_level, CASE WHEN COUNT(*) > 10 THEN 'Review and reduce system privileges' WHEN COUNT(*) > 5 THEN 'Consider privilege consolidation' ELSE 'Acceptable privilege count' END as recommendationFROM dba_sys_privsWHERE grantee NOT IN ('SYS', 'SYSTEM') AND grantee NOT LIKE 'SYS%'GROUP BY granteeHAVING COUNT(*) > 3UNION ALLSELECT grantee, 'ROLE_PRIVILEGE', COUNT(*), CASE WHEN COUNT(*) > 5 THEN 'HIGH' WHEN COUNT(*) > 3 THEN 'MEDIUM' ELSE 'LOW' END, CASE WHEN COUNT(*) > 5 THEN 'Review role assignments' WHEN COUNT(*) > 3 THEN 'Monitor role usage' ELSE 'Acceptable role count' ENDFROM dba_role_privsWHERE grantee NOT IN ('SYS', 'SYSTEM') AND granted_role NOT IN ('CONNECT', 'RESOURCE')GROUP BY granteeHAVING COUNT(*) > 2ORDER BY 4 DESC, 3 DESC;
-- ------------------------------------------------------------------------------------- PASSWORD POLICY ASSESSMENT: Password security configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. PASSWORD POLICY ASSESSMENTPROMPT ==============================
COLUMN profile FORMAT A30COLUMN parameter FORMAT A30COLUMN current_value FORMAT A30COLUMN recommended_value FORMAT A20COLUMN compliance FORMAT A20
SELECT profile, resource_name as parameter, limit as current_value, CASE resource_name WHEN 'FAILED_LOGIN_ATTEMPTS' THEN '10' WHEN 'PASSWORD_LIFE_TIME' THEN '90' WHEN 'PASSWORD_REUSE_TIME' THEN '365' WHEN 'PASSWORD_REUSE_MAX' THEN '10' WHEN 'PASSWORD_LOCK_TIME' THEN '1' WHEN 'PASSWORD_GRACE_TIME' THEN '7' ELSE 'UNLIMITED' END as recommended_value, CASE WHEN limit = CASE resource_name WHEN 'FAILED_LOGIN_ATTEMPTS' THEN '10' WHEN 'PASSWORD_LIFE_TIME' THEN '90' WHEN 'PASSWORD_REUSE_TIME' THEN '365' WHEN 'PASSWORD_REUSE_MAX' THEN '10' WHEN 'PASSWORD_LOCK_TIME' THEN '1' WHEN 'PASSWORD_GRACE_TIME' THEN '7' ELSE limit END THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as complianceFROM dba_profilesWHERE resource_type = 'PASSWORD' AND (profile = 'DEFAULT' OR profile IN (SELECT DISTINCT profile FROM dba_users WHERE oracle_maintained = 'N'))ORDER BY profile, resource_name;
-- ------------------------------------------------------------------------------------- SECURITY HARDENING ACTIONS: Implement security controls-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. SECURITY HARDENING ACTIONSPROMPT ==============================
BEGIN IF UPPER('&1') IN ('HARDEN', 'COMPLIANCE') AND UPPER('&3') IN ('YES', 'DRYRUN') THEN DECLARE v_hardening_level VARCHAR2(20) := UPPER('&2'); v_apply_changes VARCHAR2(20) := UPPER('&3'); v_action_count NUMBER := 0; BEGIN DBMS_OUTPUT.PUT_LINE('Executing security hardening actions...'); DBMS_OUTPUT.PUT_LINE('Mode: ' || CASE WHEN v_apply_changes = 'YES' THEN 'LIVE' ELSE 'DRY RUN' END); -- Action 1: Lock default accounts IF v_hardening_level IN ('STANDARD', 'STRICT', 'CIS') THEN FOR user_rec IN ( SELECT username FROM dba_users WHERE username IN ('SCOTT', 'HR', 'OE', 'SH', 'PM') AND account_status = 'OPEN' ) LOOP IF v_apply_changes = 'YES' THEN EXECUTE IMMEDIATE 'ALTER USER ' || user_rec.username || ' ACCOUNT LOCK'; END IF; DBMS_OUTPUT.PUT_LINE('Action: Locked default account - ' || user_rec.username); v_action_count := v_action_count + 1; END LOOP; END IF; -- Action 2: Implement password policies IF v_hardening_level IN ('STRICT', 'CIS') THEN IF v_apply_changes = 'YES' THEN EXECUTE IMMEDIATE 'ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 10 PASSWORD_LOCK_TIME 1 PASSWORD_GRACE_TIME 7'; END IF; DBMS_OUTPUT.PUT_LINE('Action: Enhanced password policy for DEFAULT profile'); v_action_count := v_action_count + 1; END IF; -- Action 3: Enable basic auditing IF v_hardening_level = 'CIS' THEN IF v_apply_changes = 'YES' THEN EXECUTE IMMEDIATE 'AUDIT CREATE SESSION'; EXECUTE IMMEDIATE 'AUDIT CREATE ANY TABLE'; EXECUTE IMMEDIATE 'AUDIT ALTER ANY TABLE'; EXECUTE IMMEDIATE 'AUDIT DROP ANY TABLE'; END IF; DBMS_OUTPUT.PUT_LINE('Action: Enabled basic database auditing'); v_action_count := v_action_count + 1; END IF; -- Action 4: Secure remote authentication IF v_hardening_level IN ('STRICT', 'CIS') THEN BEGIN IF v_apply_changes = 'YES' THEN EXECUTE IMMEDIATE 'ALTER SYSTEM SET REMOTE_OS_AUTHENT = FALSE SCOPE = BOTH'; END IF; DBMS_OUTPUT.PUT_LINE('Action: Disabled remote OS authentication'); v_action_count := v_action_count + 1; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Note: REMOTE_OS_AUTHENT already disabled or not applicable'); END; END IF; DBMS_OUTPUT.PUT_LINE('Security hardening completed: ' || v_action_count || ' actions processed'); DBMS_OUTPUT.PUT_LINE('Changes applied: ' || v_apply_changes); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error during security hardening: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Security hardening skipped (action: ' || UPPER('&1') || ', apply: ' || UPPER('&3') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- DATABASE PARAMETER SECURITY: Security-related init parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. SECURITY PARAMETER ASSESSMENTPROMPT ==================================
COLUMN parameter_name FORMAT A30COLUMN current_value FORMAT A20COLUMN recommended_value FORMAT A20COLUMN security_impact FORMAT A40
SELECT name as parameter_name, value as current_value, CASE name WHEN 'remote_os_authent' THEN 'FALSE' WHEN 'os_authent_prefix' THEN 'OPS$' WHEN '07_DICTIONARY_ACCESSIBILITY' THEN 'FALSE' WHEN 'audit_trail' THEN 'DB,EXTENDED' WHEN 'sec_case_sensitive_logon' THEN 'TRUE' ELSE value END as recommended_value, CASE name WHEN 'remote_os_authent' THEN 'HIGH - Disables remote OS auth' WHEN 'os_authent_prefix' THEN 'MEDIUM - OS authentication prefix' WHEN '07_DICTIONARY_ACCESSIBILITY' THEN 'HIGH - Restricts dictionary access' WHEN 'audit_trail' THEN 'MEDIUM - Enables auditing' WHEN 'sec_case_sensitive_logon' THEN 'MEDIUM - Enforces case sensitivity' ELSE 'LOW - No security impact' END as security_impactFROM v$parameterWHERE name IN ( 'remote_os_authent', 'os_authent_prefix', '07_DICTIONARY_ACCESSIBILITY', 'audit_trail', 'sec_case_sensitive_logon') AND UPPER('&2') IN ('STRICT', 'CIS')ORDER BY 4;
-- ------------------------------------------------------------------------------------- AUDIT CONFIGURATION: Current auditing settings-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. AUDIT CONFIGURATION STATUSPROMPT ==============================
COLUMN audit_type FORMAT A30COLUMN status FORMAT A20COLUMN last_audit FORMAT A20COLUMN recommendation FORMAT A60
SELECT 'Standard Database Auditing' as audit_type, CASE WHEN COUNT(*) > 0 THEN 'ENABLED' ELSE 'DISABLED' END as status, TO_CHAR(MAX(timestamp), 'YYYY-MM-DD HH24:MI:SS') as last_audit, CASE WHEN COUNT(*) = 0 THEN 'Enable basic database auditing' ELSE 'Auditing active - review policies' END as recommendationFROM dba_audit_trailWHERE ROWNUM = 1UNION ALLSELECT 'Fine-Grained Auditing', CASE WHEN COUNT(*) > 0 THEN 'ENABLED' ELSE 'DISABLED' END, 'N/A', CASE WHEN COUNT(*) = 0 THEN 'Consider FGA for sensitive data' ELSE 'FGA policies active' ENDFROM dba_audit_policiesUNION ALLSELECT 'Mandatory Auditing', 'ENABLED', 'N/A', 'System-level auditing active'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_stmt_audit_opts WHERE user_name IS NULL)ORDER BY 2;
-- ------------------------------------------------------------------------------------- SECURITY COMPLIANCE CHECKLIST: Compliance verification-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. SECURITY COMPLIANCE CHECKLISTPROMPT =================================
COLUMN compliance_item FORMAT A50COLUMN status FORMAT A15COLUMN requirement FORMAT A40COLUMN evidence FORMAT A30
SELECT 'Default accounts are locked' as compliance_item, CASE WHEN COUNT(*) = 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as status, 'Lock all default demonstration accounts' as requirement, TO_CHAR(COUNT(*)) || ' accounts open' as evidenceFROM dba_usersWHERE username IN ('SCOTT', 'HR', 'OE', 'SH', 'PM') AND account_status = 'OPEN'UNION ALLSELECT 'Password policies enforced', CASE WHEN COUNT(*) > 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, 'Implement password complexity rules', TO_CHAR(COUNT(*)) || ' policies active'FROM dba_profilesWHERE resource_type = 'PASSWORD' AND resource_name = 'FAILED_LOGIN_ATTEMPTS' AND limit != 'UNLIMITED'UNION ALLSELECT 'Excessive privileges reviewed', CASE WHEN COUNT(*) = 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, 'Review users with excessive privileges', TO_CHAR(COUNT(*)) || ' users to review'FROM ( SELECT grantee FROM dba_sys_privs WHERE grantee NOT IN ('SYS', 'SYSTEM') GROUP BY grantee HAVING COUNT(*) > 10)UNION ALLSELECT 'Database auditing enabled', CASE WHEN COUNT(*) > 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, 'Enable basic database auditing', CASE WHEN COUNT(*) > 0 THEN 'Auditing active' ELSE 'No audit records' ENDFROM dba_audit_trailWHERE ROWNUM = 1ORDER BY 2, 1;
-- ------------------------------------------------------------------------------------- SECURITY RECOMMENDATIONS: Actionable security improvements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. SECURITY IMPROVEMENT RECOMMENDATIONSPROMPT ========================================
COLUMN recommendation FORMAT A60COLUMN priority FORMAT A10COLUMN effort FORMAT A15COLUMN impact FORMAT A15
SELECT 'Implement Transparent Data Encryption (TDE)' as recommendation, 'HIGH' as priority, 'MEDIUM' as effort, 'HIGH' as impactFROM dualWHERE (SELECT COUNT(*) FROM v$encryption_wallet WHERE status = 'OPEN') = 0UNION ALLSELECT 'Enable fine-grained auditing for sensitive tables', 'HIGH', 'LOW', 'HIGH'FROM dualWHERE (SELECT COUNT(*) FROM dba_audit_policies) = 0UNION ALLSELECT 'Implement database vault for administrative control', 'MEDIUM', 'HIGH', 'HIGH'FROM dualWHERE UPPER('&2') = 'CIS'UNION ALLSELECT 'Regular user access reviews and certification', 'MEDIUM', 'MEDIUM', 'MEDIUM'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_users WHERE last_login IS NULL OR last_login < SYSDATE - 180)UNION ALLSELECT 'Implement network encryption (SQLNet)', 'MEDIUM', 'LOW', 'MEDIUM'FROM dualWHERE UPPER('&2') IN ('STRICT', 'CIS')ORDER BY 2,3;
-- ------------------------------------------------------------------------------------- COMPLETION: Security hardening summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =======================================PROMPT DATABASE SECURITY HARDENING COMPLETEDPROMPT =======================================PROMPT Action: &1PROMPT Hardening Level: &2PROMPT Apply Changes: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Security Summary:PROMPT - Review HIGH priority recommendations immediatelyPROMPT - Address compliance gaps identifiedPROMPT - Schedule regular security assessmentsPROMPT - Document security policies and proceduresPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------