• Post category:Security
  • Post comments:0 Comments
  • Post last modified:March 17, 2022
  • Reading time:4 mins read
You are currently viewing How to fetch the data role and attached security profiles?
How to fetch the data role and attached security profiles?
- How to fetch the data role and attached security profiles?
Total Visits: 32

In this article we will try to fetch the data roles and their corresponding security profiles.

While creating the data roles for job roles, we specify the security profiles using which the users will be getting the access to view the data (ex employee data, departments data, payroll flows, etc.)

SQL Query to extract data roles and their attached security profiles:

SELECT 
D.DATA_ROLE_DISPLAY_NAME AS ROLE_NAME,
D.DATA_ROLE_NAME AS ROLE_CODE,
R.ABSTRACT_ROLE,
R.JOB_ROLE,
R.DATA_ROLE,
R.DELEGATION_ALLOWED,
(SELECT ORG.NAME FROM PER_ORG_SECURITY_PROFILES ORG WHERE ORG.ORG_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'ORGANIZATION')) AS ORGANIZATION_SEC_PROFILE,
(SELECT POS.NAME FROM PER_POSITION_SECURITY_PROFILES POS WHERE POS.POSITION_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'POSITION')) AS POSITION_SEC_PROFILE,
(SELECT CNT.NAME FROM PER_COUNTRY_SECURITY_PROFILES CNT WHERE CNT.COUNTRY_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'COUNTRY')) AS COUNTRY_SEC_PROFILE,
(SELECT LDG.NAME FROM PER_LDG_SECURITY_PROFILES LDG WHERE LDG.LDG_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'LDG')) AS LDG_SEC_PROFILE,
(SELECT PER.NAME FROM PER_PERSON_SECURITY_PROFILES PER WHERE PER.PERSON_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'PERSON')) AS PERSON_SEC_PROFILE,
(SELECT PUB.NAME FROM PER_PERSON_SECURITY_PROFILES PUB WHERE PUB.PERSON_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'PUBLIC_PERSON')) AS PUBLIC_SEC_PROFILE,
(SELECT DOR.NAME FROM PER_DOC_TYPE_SECURITY_PROFILES DOR WHERE DOR.DOC_TYPE_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'DOR')) AS DOCUMENT_SEC_PROFILE,
(SELECT PAY.NAME FROM PAY_PAY_SECURITY_PROFILES PAY WHERE PAY.PAY_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'PAYROLL')) AS PAYROLL_SEC_PROFILE,
(SELECT FLO.NAME FROM PAY_FLW_SECURITY_PROFILES FLO WHERE FLO.FLW_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'FLOWPATTERN')) AS FLOW_PATTERN_SEC_PROFILE,
(SELECT TRA.NAME FROM HRC_TXN_SEC_PROFILE TRA WHERE TRA.TXN_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'TRANSACTION')) AS TRANSACTION_SEC_PROFILE,
(SELECT REQ.NAME FROM IRC_REQ_SEC_PROFILES REQ WHERE REQ.REQ_SECURITY_PROFILE_ID = 
	(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'REQUISITION')) AS REQUISITION_SEC_PROFILE
FROM PER_GENERATED_DATA_ROLES D, PER_ROLES_DN R
WHERE D.DATA_ROLE_ID = R.ROLE_ID
ORDER BY R.ABSTRACT_ROLE DESC, R.JOB_ROLE DESC, R.DATA_ROLE DESC, D.DATA_ROLE_NAME

If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.