Use-Case:
This document serves as a guide to frequently used Segregation of Duties (SOD) Reports. These reports can be adapted for use in various customer environments.
Scope:
This document provides solutions for the following scenarios:
- SOD Violations With Expiring Mitigation Controls (30 days)
- SOD Violations by Business Process, Violation Status
- SOD Violations With Mitigations
- Top 5 Business Processes With SOD Violations
- Top 5 Users With Most SOD Violations By Department
Steps / Solution Description:
SOD Violations With Expiring Mitigation Controls (30 days):
This query fetches a report of SOD Violations with Expiring Mitigation Controls in the next 30 days.
SELECT r.riskname as 'Risk Name', u.username as 'User Name', u.firstname as 'User First Name', u.lastname as 'User Last Name', acc.name as 'Account', ep.ENDPOINTNAME as 'Application', mco.MITIGATINGCONTROL as 'Mitigating Control', mc.enddate as 'Mitigating Control End date' FROM mc_risk_account mc, risks r, users u, mitigatingcontrols mco, accounts acc, endpoints ep WHERE mc.status = 1 AND mc.userkey = u.userkey AND mc.accountkey = acc.accountkey AND acc.ENDPOINTKEY = ep.endpointkey AND r.riskid = mc.riskid AND mc.mitigatingcontrolkey = mco.mitigatingcontrolid AND MONTH(mc.enddate) <= MONTH(CURRENT_DATE + INTERVAL 1 MONTH);
SOD Violations by Business Process, Violation Status:
This query fetches a report of SOD Violations by Business Process, along with Violation Status.
SELECT sd.riskcode as 'Risk Name', r.description as 'Risk Description', CASE WHEN r.priority = 0 THEN 'Medium' WHEN r.priority = 1 THEN 'High' WHEN r.priority = 2 THEN 'Low' WHEN r.priority = 3 THEN 'Critical' WHEN r.priority = 4 THEN 'Very Low' ELSE 'None' END AS 'Risk Priority', ep.endpointname as 'Application', u.username as 'User Name', u.firstname as 'User First Name', u.lastname as 'User Last Name', CASE WHEN ro.owneruserkey is NULL THEN ug.user_groupname WHEN ro.ownerusergroupkey is NULL THEN u.username ELSE 'None' END AS 'Risk Owner', f.function_name as 'Function', se.PARENTROLEASCSV as 'Parent Entitlement', ev.entitlement_value as 'Child Entitlement', CASE WHEN sd.status = 1 THEN 'New' WHEN sd.status = 2 THEN 'In Progress' WHEN sd.status = 3 THEN 'Risk Accepted' WHEN sd.status = 4 THEN 'Closed' WHEN sd.status = 5 THEN 'Remediated' ELSE 'None' END AS 'Violation Status', bp.busprocname as 'Business Process' FROM sodrisks sd LEFT OUTER JOIN users u ON (sd.userkey = u.userkey) LEFT OUTER JOIN risks r ON (sd.riskkey = r.riskid) LEFT OUTER JOIN busprocs bp ON (r.bpkey = bp.busprockey) LEFT OUTER JOIN riskowners ro ON (r.riskid = ro.riskid) LEFT OUTER JOIN users uu ON (ro.OWNERUSERKEY = uu.userkey) LEFT OUTER JOIN user_groups ug ON (ro.OWNERUSERGROUPKEY = ug.USERGROUPKEY) LEFT OUTER JOIN sodrisk_entitlement se ON (sd.sodkey = se.sodkey) LEFT OUTER JOIN accounts acc on (se.accountkey=acc.accountkey) LEFT OUTER JOIN endpoints ep on (acc.endpointkey=ep.endpointkey) LEFT OUTER JOIN functions f ON (se.functionkey = f.functionkey) LEFT OUTER JOIN entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY);
SOD Violations With Mitigations:
This query fetches a report of SOD Violations with Mitigations.
SELECT r.riskname as 'Risk Name', r.description as 'Risk Description', rs.ruleset as 'Ruleset', CASE WHEN r.priority = 0 THEN 'Medium' WHEN r.priority = 1 THEN 'High' WHEN r.priority = 2 THEN 'Low' WHEN r.priority = 3 THEN 'Critical' WHEN r.priority = 4 THEN 'Very Low' ELSE 'None' END AS 'Risk Priority', mco.MITIGATINGCONTROL as 'Mitigating Control', mc.startdate as 'Start Date', mc.enddate as 'End Date', f1.function_name as 'Function1', f2.function_name as 'Function2', u.username as 'User Name', acc.name as 'User Account' FROM mc_risk_account mc INNER JOIN risks r ON (mc.riskid = r.riskid) INNER JOIN rulesets rs ON (rs.rulesetkey = r.rulesetkey) LEFT OUTER JOIN mitigatingcontrols mco on (mc.mitigatingcontrolkey = mco.mitigatingcontrolid) LEFT OUTER JOIN sodrisks sd ON (r.riskid = sd.riskkey) LEFT OUTER JOIN users u ON (sd.userkey = u.userkey) LEFT OUTER JOIN accounts acc on (mc.accountkey=acc.accountkey) LEFT OUTER JOIN functions f1 ON (r.FUNCTION2KEY = f1.functionkey) LEFT OUTER JOIN functions f2 ON (r.FUNCTION1KEY = f2.functionkey) WHERE mc.status=1 and sd.status=3 ORDER BY r.riskname desc;
Top 5 Business Processes With SOD Violations:
This query fetches a report of the Top 5 Business Processes with SOD Violations.
SELECT bp.busprocname businessprocess, r.riskname 'risk', r.riskid, sd.sodkey, sd.userkey, count(*) violationcount FROM sodrisks sd, busprocs bp, risks r WHERE sd.riskkey = r.riskid AND r.bpkey = bp.busprockey GROUP BY bp.busprocname ORDER BY count(*) desc;
Top 5 Users With Most SOD Violations By Department:
This query fetches a report of the Top 5 users with the most SOD Violations by Department.
SELECT username as 'User Name', firstname as 'User First Name', lastname as 'User Last Name', department as 'Department', violationcount as 'Violation Count' FROM (SELECT u.username username, u.firstname firstname, u.lastname lastname, u.DEPARTMENTNAME department, count(*) violationcount FROM sodrisks sd LEFT JOIN users u on (sd.userkey = u.userkey) GROUP BY u.userkey ORDER BY violationcount desc LIMIT 5) as topmaxviolation;
Please ensure that the queries provided match your specific system's version and configuration. Always test queries in a safe environment before deploying them to a production environment.
