privileged_access_analysis.sql

-- Enterprise Access Risk & Privileged Access Exposure Analysis\
-- Flags: privileged access, SoD violations, terminated-but-active accounts, and behavioral risk indicators.\
\
SELECT\
  user_id,\
  username,\
  department,\
  role,\
  system,\
  privilege_level,\
  can_create,\
  can_approve,\
  last_login_days,\
  failed_logins,\
  terminated_flag,\
\
  -- Privilege score (simple numeric mapping)\
  CASE\
    WHEN privilege_level = 'High' THEN 3\
    WHEN privilege_level = 'Medium' THEN 2\
    WHEN privilege_level = 'Low' THEN 1\
    ELSE NULL\
  END AS privilege_score,\
\
  -- Segregation of Duties (SoD): can create + can approve\
  CASE\
    WHEN can_create = 'Yes' AND can_approve = 'Yes' THEN 'SoD Violation'\
    ELSE 'No Violation'\
  END AS sod_flag,\
\
  -- Critical systems (can change this list)\
  CASE\
    WHEN system IN ('ERP','IAM','GL') THEN 'Critical'\
    ELSE 'Non-Critical'\
  END AS system_criticality,\
\
  -- Risk classification logic\
  CASE\
    WHEN terminated_flag = 'Yes' AND privilege_level = 'High' THEN 'Critical'\
    WHEN can_create = 'Yes' AND can_approve = 'Yes' AND system IN ('ERP','IAM','GL') THEN 'High'\
    WHEN failed_logins >= 3 THEN 'High'\
    WHEN last_login_days <= 1 AND privilege_level = 'High' THEN 'High'\
    WHEN system IN ('ERP','IAM','GL') AND privilege_level = 'High' THEN 'High'\
    ELSE 'Normal'\
  END AS access_risk_level\
\
FROM user_access_logs;\
}