security_audit_report.sql
-- ------------------------------------------------------------------------------------- File Name : security_audit_report.sql-- Author : Pierre Montbleau-- Description : Comprehensive Oracle database security audit and compliance reporting-- Purpose : Security assessment, privilege analysis, and compliance verification-- Call Syntax : @F:\DBA\Scripts\security_audit_report.sql (audit_level) (schema_filter)-- Parameters : audit_level - Level of audit detail (BASIC, STANDARD, COMPREHENSIVE)-- schema_filter - Specific schema to audit or '%' for all schemas-- 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_audit_report_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.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 Security Audit Report Script...PROMPT ========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Audit Level: &1PROMPT Schema Filter: &2PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_audit_level VARCHAR2(20) := UPPER(NVL('&1', 'STANDARD')); v_schema_filter VARCHAR2(30) := UPPER(NVL('&2', '%'));BEGIN IF v_audit_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20001, 'Audit level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; IF v_schema_filter != '%' THEN DECLARE v_schema_exists NUMBER; BEGIN SELECT COUNT(*) INTO v_schema_exists FROM dba_users WHERE username = v_schema_filter; IF v_schema_exists = 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Schema ' || v_schema_filter || ' does not exist'); END IF; END; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- USER ACCOUNT SECURITY: User account status and password policies-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. USER ACCOUNT SECURITY ASSESSMENTPROMPT ====================================
COLUMN username FORMAT A25COLUMN account_status FORMAT A20COLUMN lock_date FORMAT A12COLUMN expiry_date FORMAT A12COLUMN profile FORMAT A30COLUMN created FORMAT A12
SELECT username, account_status, TO_CHAR(lock_date, 'YYYY-MM-DD') as lock_date, TO_CHAR(expiry_date, 'YYYY-MM-DD') as expiry_date, profile, TO_CHAR(created, 'YYYY-MM-DD') as createdFROM dba_usersWHERE username NOT IN ('SYS', 'SYSTEM') AND account_status != 'OPEN' AND UPPER('&1') != 'BASIC'ORDER BY username;
-- ------------------------------------------------------------------------------------- PRIVILEGE ANALYSIS: System and object privilege assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. PRIVILEGE ANALYSIS - POWERFUL SYSTEM PRIVILEGESPROMPT ==================================================
COLUMN grantee FORMAT A35COLUMN privilege FORMAT A30COLUMN admin_option FORMAT A5
SELECT grantee, privilege, admin_optionFROM dba_sys_privsWHERE privilege IN ( 'DBA', 'SYSDBA', 'SYSOPER', 'ALTER SYSTEM', 'ALTER DATABASE', 'CREATE ANY TABLE', 'DROP ANY TABLE', 'SELECT ANY TABLE', 'INSERT ANY TABLE', 'UPDATE ANY TABLE', 'DELETE ANY TABLE', 'GRANT ANY OBJECT PRIVILEGE', 'GRANT ANY PRIVILEGE', 'AUDIT ANY', 'EXEMPT ACCESS POLICY')AND grantee NOT IN ('SYS', 'SYSTEM', 'DBA')AND grantee NOT LIKE '%SYS%'ORDER BY grantee, privilege;
-- ------------------------------------------------------------------------------------- ROLE ASSIGNMENTS: Critical role assignments analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. CRITICAL ROLE ASSIGNMENTSPROMPT =============================
COLUMN grantee FORMAT A25COLUMN granted_role FORMAT A25COLUMN admin_option FORMAT A5COLUMN default_role FORMAT A5
SELECT grantee, granted_role, admin_option, default_roleFROM dba_role_privsWHERE granted_role IN ('DBA', 'RESOURCE', 'CONNECT', 'SELECT_CATALOG_ROLE', 'EXECUTE_CATALOG_ROLE') AND grantee NOT IN ('SYS', 'SYSTEM') AND grantee NOT LIKE '%SYS%'ORDER BY granted_role, grantee;
-- ------------------------------------------------------------------------------------- OBJECT PRIVILEGES: Sensitive object access analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. SENSITIVE OBJECT PRIVILEGESPROMPT ===============================
COLUMN owner FORMAT A15COLUMN table_name FORMAT A30COLUMN grantee FORMAT A20COLUMN privilege FORMAT A15COLUMN grantable FORMAT A5
SELECT owner, table_name, grantee, privilege, grantableFROM dba_tab_privsWHERE owner IN ('SYS', 'SYSTEM') AND table_name IN ('USER$', 'USER_HISTORY$', 'LINK$', 'SYSAUTH$', 'SYSPRIV$') AND UPPER('&1') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY owner, table_name, grantee;
-- ------------------------------------------------------------------------------------- AUDIT CONFIGURATION: Current audit settings and policies-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. AUDIT CONFIGURATION STATUSPROMPT ==============================
COLUMN audit_option FORMAT A40COLUMN success FORMAT A10COLUMN failure FORMAT A10
SELECT audit_option, success, failureFROM dba_stmt_audit_optsWHERE UPPER('&1') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY audit_option;
-- ------------------------------------------------------------------------------------- PASSWORD POLICIES: Password profile and policy analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. PASSWORD POLICY ANALYSISPROMPT ============================
COLUMN profile FORMAT A20COLUMN resource_name FORMAT A30COLUMN limit FORMAT A15COLUMN description FORMAT A50
SELECT profile, resource_name, limit, CASE resource_name WHEN 'PASSWORD_LIFE_TIME' THEN 'Days until password expires' WHEN 'PASSWORD_REUSE_TIME' THEN 'Days before password can be reused' WHEN 'PASSWORD_REUSE_MAX' THEN 'Number of password changes required' WHEN 'FAILED_LOGIN_ATTEMPTS' THEN 'Failed attempts before account lock' WHEN 'PASSWORD_LOCK_TIME' THEN 'Days account remains locked' ELSE 'Other password policy' END as descriptionFROM dba_profilesWHERE resource_type = 'PASSWORD' AND (profile = 'DEFAULT' OR profile IN ( SELECT DISTINCT profile FROM dba_users WHERE username LIKE '&2' ))ORDER BY profile, resource_name;
-- ------------------------------------------------------------------------------------- DATABASE LINKS: Database link security assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. DATABASE LINK SECURITY ASSESSMENTPROMPT =====================================
COLUMN owner FORMAT A15COLUMN db_link FORMAT A30COLUMN username FORMAT A20COLUMN host FORMAT A30COLUMN created FORMAT A12
SELECT owner, db_link, username, host, TO_CHAR(created, 'YYYY-MM-DD') as createdFROM dba_db_linksWHERE UPPER('&1') IN ('STANDARD', 'COMPREHENSIVE') AND (owner LIKE '&2' OR '&2' = '%')ORDER BY owner, db_link;
-- ------------------------------------------------------------------------------------- ENCRYPTION STATUS: TDE and encryption configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. ENCRYPTION STATUS ASSESSMENTPROMPT ================================
COLUMN wallet_status FORMAT A20COLUMN encryption_algorithm FORMAT A25COLUMN master_key_id FORMAT A30
BEGIN IF UPPER('&1') = 'COMPREHENSIVE' THEN DBMS_OUTPUT.PUT_LINE('Checking TDE and encryption status...'); END IF;END;/
SELECT status as wallet_status, wallet_type, CON_IDFROM v$encryption_walletWHERE UPPER('&1') = 'COMPREHENSIVE';
-- ------------------------------------------------------------------------------------- SECURITY FINDINGS SUMMARY: Consolidated security issues-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. SECURITY FINDINGS SUMMARYPROMPT =============================
COLUMN finding_level FORMAT A10COLUMN finding_description FORMAT A85COLUMN recommendation FORMAT A80
SELECT 'HIGH' as finding_level, 'User with DBA role: ' || grantee as finding_description, 'Review necessity of DBA role for user: ' || grantee as recommendationFROM dba_role_privsWHERE granted_role = 'DBA' AND grantee NOT IN ('SYS', 'SYSTEM')UNION ALLSELECT 'HIGH' as finding_level, 'Expired account: ' || username as finding_description, 'Review and reactivate or remove expired account: ' || username as recommendationFROM dba_usersWHERE account_status = 'EXPIRED' AND username NOT IN ('SYS', 'SYSTEM')UNION ALLSELECT 'MEDIUM' as finding_level, 'User with ANY privileges: ' || grantee || ' - ' || privilege as finding_description, 'Review ANY privileges for user: ' || grantee as recommendationFROM dba_sys_privsWHERE privilege LIKE '%ANY%' AND grantee NOT IN ('SYS', 'SYSTEM') AND grantee NOT LIKE '%SYS%'UNION ALLSELECT 'LOW' as finding_level, 'Default password profile in use' as finding_description, 'Consider implementing custom password policies' as recommendationFROM dualWHERE EXISTS ( SELECT 1 FROM dba_users WHERE profile = 'DEFAULT' AND username NOT IN ('SYS', 'SYSTEM'))ORDER BY 1 DESC, 2;
-- ------------------------------------------------------------------------------------- COMPLIANCE CHECKLIST: Security compliance verification-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. SECURITY COMPLIANCE CHECKLISTPROMPT ==================================
COLUMN check_item FORMAT A50COLUMN status FORMAT A15COLUMN details FORMAT A40
SELECT 'Default accounts are locked' as check_item, CASE WHEN COUNT(*) = 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as status, COUNT(*) || ' default accounts active' as detailsFROM dba_usersWHERE username IN ('SCOTT', 'HR', 'OE', 'SH', 'PM') AND account_status = 'OPEN'UNION ALLSELECT 'DBA role assignments reviewed' as check_item, CASE WHEN COUNT(*) = 0 THEN 'COMPLIANT' ELSE 'REVIEW NEEDED' END as status, COUNT(*) || ' non-SYS/SYSTEM users with DBA' as detailsFROM dba_role_privsWHERE granted_role = 'DBA' AND grantee NOT IN ('SYS', 'SYSTEM')UNION ALLSELECT 'Password policies enforced' as check_item, CASE WHEN COUNT(*) > 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as status, 'Password limits configured' as detailsFROM dba_profilesWHERE resource_type = 'PASSWORD' AND resource_name = 'FAILED_LOGIN_ATTEMPTS' AND limit != 'UNLIMITED'ORDER BY 1;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT SECURITY AUDIT REPORT COMPLETEDPROMPT ========================================PROMPT Audit Level: &1PROMPT Schema Filter: &2PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Address HIGH priority findings immediatelyPROMPT 2. Review MEDIUM priority security concernsPROMPT 3. Implement compliance recommendationsPROMPT 4. Schedule regular security auditsPROMPT
-- ------------------------------------------------------------------------------------- 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_audit_report_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.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 Security Audit Report Script...PROMPT ========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Audit Level: &1PROMPT Schema Filter: &2PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_audit_level VARCHAR2(20) := UPPER(NVL('&1', 'STANDARD')); v_schema_filter VARCHAR2(30) := UPPER(NVL('&2', '%'));BEGIN IF v_audit_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20001, 'Audit level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; IF v_schema_filter != '%' THEN DECLARE v_schema_exists NUMBER; BEGIN SELECT COUNT(*) INTO v_schema_exists FROM dba_users WHERE username = v_schema_filter; IF v_schema_exists = 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Schema ' || v_schema_filter || ' does not exist'); END IF; END; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- USER ACCOUNT SECURITY: User account status and password policies-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. USER ACCOUNT SECURITY ASSESSMENTPROMPT ====================================
COLUMN username FORMAT A25COLUMN account_status FORMAT A20COLUMN lock_date FORMAT A12COLUMN expiry_date FORMAT A12COLUMN profile FORMAT A30COLUMN created FORMAT A12
SELECT username, account_status, TO_CHAR(lock_date, 'YYYY-MM-DD') as lock_date, TO_CHAR(expiry_date, 'YYYY-MM-DD') as expiry_date, profile, TO_CHAR(created, 'YYYY-MM-DD') as createdFROM dba_usersWHERE username NOT IN ('SYS', 'SYSTEM') AND account_status != 'OPEN' AND UPPER('&1') != 'BASIC'ORDER BY username;
-- ------------------------------------------------------------------------------------- PRIVILEGE ANALYSIS: System and object privilege assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. PRIVILEGE ANALYSIS - POWERFUL SYSTEM PRIVILEGESPROMPT ==================================================
COLUMN grantee FORMAT A35COLUMN privilege FORMAT A30COLUMN admin_option FORMAT A5
SELECT grantee, privilege, admin_optionFROM dba_sys_privsWHERE privilege IN ( 'DBA', 'SYSDBA', 'SYSOPER', 'ALTER SYSTEM', 'ALTER DATABASE', 'CREATE ANY TABLE', 'DROP ANY TABLE', 'SELECT ANY TABLE', 'INSERT ANY TABLE', 'UPDATE ANY TABLE', 'DELETE ANY TABLE', 'GRANT ANY OBJECT PRIVILEGE', 'GRANT ANY PRIVILEGE', 'AUDIT ANY', 'EXEMPT ACCESS POLICY')AND grantee NOT IN ('SYS', 'SYSTEM', 'DBA')AND grantee NOT LIKE '%SYS%'ORDER BY grantee, privilege;
-- ------------------------------------------------------------------------------------- ROLE ASSIGNMENTS: Critical role assignments analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. CRITICAL ROLE ASSIGNMENTSPROMPT =============================
COLUMN grantee FORMAT A25COLUMN granted_role FORMAT A25COLUMN admin_option FORMAT A5COLUMN default_role FORMAT A5
SELECT grantee, granted_role, admin_option, default_roleFROM dba_role_privsWHERE granted_role IN ('DBA', 'RESOURCE', 'CONNECT', 'SELECT_CATALOG_ROLE', 'EXECUTE_CATALOG_ROLE') AND grantee NOT IN ('SYS', 'SYSTEM') AND grantee NOT LIKE '%SYS%'ORDER BY granted_role, grantee;
-- ------------------------------------------------------------------------------------- OBJECT PRIVILEGES: Sensitive object access analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. SENSITIVE OBJECT PRIVILEGESPROMPT ===============================
COLUMN owner FORMAT A15COLUMN table_name FORMAT A30COLUMN grantee FORMAT A20COLUMN privilege FORMAT A15COLUMN grantable FORMAT A5
SELECT owner, table_name, grantee, privilege, grantableFROM dba_tab_privsWHERE owner IN ('SYS', 'SYSTEM') AND table_name IN ('USER$', 'USER_HISTORY$', 'LINK$', 'SYSAUTH$', 'SYSPRIV$') AND UPPER('&1') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY owner, table_name, grantee;
-- ------------------------------------------------------------------------------------- AUDIT CONFIGURATION: Current audit settings and policies-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. AUDIT CONFIGURATION STATUSPROMPT ==============================
COLUMN audit_option FORMAT A40COLUMN success FORMAT A10COLUMN failure FORMAT A10
SELECT audit_option, success, failureFROM dba_stmt_audit_optsWHERE UPPER('&1') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY audit_option;
-- ------------------------------------------------------------------------------------- PASSWORD POLICIES: Password profile and policy analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. PASSWORD POLICY ANALYSISPROMPT ============================
COLUMN profile FORMAT A20COLUMN resource_name FORMAT A30COLUMN limit FORMAT A15COLUMN description FORMAT A50
SELECT profile, resource_name, limit, CASE resource_name WHEN 'PASSWORD_LIFE_TIME' THEN 'Days until password expires' WHEN 'PASSWORD_REUSE_TIME' THEN 'Days before password can be reused' WHEN 'PASSWORD_REUSE_MAX' THEN 'Number of password changes required' WHEN 'FAILED_LOGIN_ATTEMPTS' THEN 'Failed attempts before account lock' WHEN 'PASSWORD_LOCK_TIME' THEN 'Days account remains locked' ELSE 'Other password policy' END as descriptionFROM dba_profilesWHERE resource_type = 'PASSWORD' AND (profile = 'DEFAULT' OR profile IN ( SELECT DISTINCT profile FROM dba_users WHERE username LIKE '&2' ))ORDER BY profile, resource_name;
-- ------------------------------------------------------------------------------------- DATABASE LINKS: Database link security assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. DATABASE LINK SECURITY ASSESSMENTPROMPT =====================================
COLUMN owner FORMAT A15COLUMN db_link FORMAT A30COLUMN username FORMAT A20COLUMN host FORMAT A30COLUMN created FORMAT A12
SELECT owner, db_link, username, host, TO_CHAR(created, 'YYYY-MM-DD') as createdFROM dba_db_linksWHERE UPPER('&1') IN ('STANDARD', 'COMPREHENSIVE') AND (owner LIKE '&2' OR '&2' = '%')ORDER BY owner, db_link;
-- ------------------------------------------------------------------------------------- ENCRYPTION STATUS: TDE and encryption configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. ENCRYPTION STATUS ASSESSMENTPROMPT ================================
COLUMN wallet_status FORMAT A20COLUMN encryption_algorithm FORMAT A25COLUMN master_key_id FORMAT A30
BEGIN IF UPPER('&1') = 'COMPREHENSIVE' THEN DBMS_OUTPUT.PUT_LINE('Checking TDE and encryption status...'); END IF;END;/
SELECT status as wallet_status, wallet_type, CON_IDFROM v$encryption_walletWHERE UPPER('&1') = 'COMPREHENSIVE';
-- ------------------------------------------------------------------------------------- SECURITY FINDINGS SUMMARY: Consolidated security issues-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. SECURITY FINDINGS SUMMARYPROMPT =============================
COLUMN finding_level FORMAT A10COLUMN finding_description FORMAT A85COLUMN recommendation FORMAT A80
SELECT 'HIGH' as finding_level, 'User with DBA role: ' || grantee as finding_description, 'Review necessity of DBA role for user: ' || grantee as recommendationFROM dba_role_privsWHERE granted_role = 'DBA' AND grantee NOT IN ('SYS', 'SYSTEM')UNION ALLSELECT 'HIGH' as finding_level, 'Expired account: ' || username as finding_description, 'Review and reactivate or remove expired account: ' || username as recommendationFROM dba_usersWHERE account_status = 'EXPIRED' AND username NOT IN ('SYS', 'SYSTEM')UNION ALLSELECT 'MEDIUM' as finding_level, 'User with ANY privileges: ' || grantee || ' - ' || privilege as finding_description, 'Review ANY privileges for user: ' || grantee as recommendationFROM dba_sys_privsWHERE privilege LIKE '%ANY%' AND grantee NOT IN ('SYS', 'SYSTEM') AND grantee NOT LIKE '%SYS%'UNION ALLSELECT 'LOW' as finding_level, 'Default password profile in use' as finding_description, 'Consider implementing custom password policies' as recommendationFROM dualWHERE EXISTS ( SELECT 1 FROM dba_users WHERE profile = 'DEFAULT' AND username NOT IN ('SYS', 'SYSTEM'))ORDER BY 1 DESC, 2;
-- ------------------------------------------------------------------------------------- COMPLIANCE CHECKLIST: Security compliance verification-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. SECURITY COMPLIANCE CHECKLISTPROMPT ==================================
COLUMN check_item FORMAT A50COLUMN status FORMAT A15COLUMN details FORMAT A40
SELECT 'Default accounts are locked' as check_item, CASE WHEN COUNT(*) = 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as status, COUNT(*) || ' default accounts active' as detailsFROM dba_usersWHERE username IN ('SCOTT', 'HR', 'OE', 'SH', 'PM') AND account_status = 'OPEN'UNION ALLSELECT 'DBA role assignments reviewed' as check_item, CASE WHEN COUNT(*) = 0 THEN 'COMPLIANT' ELSE 'REVIEW NEEDED' END as status, COUNT(*) || ' non-SYS/SYSTEM users with DBA' as detailsFROM dba_role_privsWHERE granted_role = 'DBA' AND grantee NOT IN ('SYS', 'SYSTEM')UNION ALLSELECT 'Password policies enforced' as check_item, CASE WHEN COUNT(*) > 0 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as status, 'Password limits configured' as detailsFROM dba_profilesWHERE resource_type = 'PASSWORD' AND resource_name = 'FAILED_LOGIN_ATTEMPTS' AND limit != 'UNLIMITED'ORDER BY 1;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT SECURITY AUDIT REPORT COMPLETEDPROMPT ========================================PROMPT Audit Level: &1PROMPT Schema Filter: &2PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Address HIGH priority findings immediatelyPROMPT 2. Review MEDIUM priority security concernsPROMPT 3. Implement compliance recommendationsPROMPT 4. Schedule regular security auditsPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------