SQL Query to get the person security profile details

The following SQL will give the list of all data roles and the column 'PERSON_SECURITY_PROFILE' will give the list of person security profiles associated with them. You can use this a build a report and filter on that. Alternatively, you can use the SQL and modify it to fetch the list of all data roles which use a particular security profile.

SELECT

D.DATA_ROLE_NAME AS DATA_ROLE_CODE,

(SELECT RA.ROLE_NAME FROM PER_ROLES_DN_TL RA WHERE RA.LANGUAGE = 'US' AND RA.ROLE_ID = D.DATA_ROLE_ID) AS DATA_ROLE_NAME,

(SELECT R.ROLE_COMMON_NAME FROM PER_ROLES_DN R WHERE R.ROLE_ID = D.BASE_ROLE_ID) AS JOB_ROLE_CODE,

(SELECT RA.ROLE_NAME FROM PER_ROLES_DN_TL RA WHERE RA.LANGUAGE = 'US' AND RA.ROLE_ID = D.BASE_ROLE_ID) AS JOB_ROLE_NAME,

(SELECT S.NAME FROM PER_PERSON_SECURITY_PROFILES S WHERE S.PERSON_SECURITY_PROFILE_ID = (SELECT DS.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DS WHERE DS.GENERATED_DATA_ROLE_ID = D.GENERATED_DATA_ROLE_ID AND DS.HR_SECURING_OBJECT = 'PERSON'

AND DS.START_DATE = (SELECT MAX(DS1.START_DATE) FROM PER_GEN_DATA_ROLE_PROFILES DS1 WHERE DS1.GEN_DATA_ROLE_PROFILES_ID = DS.GEN_DATA_ROLE_PROFILES_ID))) AS PERSON_SECURITY_PROFILE,

AND DS.START_DATE = (SELECT MAX(DS1.START_DATE) FROM PER_GEN_DATA_ROLE_PROFILES DS1 WHERE DS1.GEN_DATA_ROLE_PROFILES_ID = DS.GEN_DATA_ROLE_PROFILES_ID))) AS PUBLIC_SECURITY_PROFILE,

D.DELEGATION_ALLOWED,

D.CREATED_BY,

to_char(cast(D.CREATION_DATE as timestamp) at time zone 'US/Pacific','MM/dd/YYYY hh:mm AM') AS CREATION_DATE,

D.LAST_UPDATED_BY,

to_char(cast(D.LAST_UPDATE_DATE as timestamp) at time zone 'US/Pacific','MM/dd/YYYY hh:mm AM') AS LAST_UPDATE_DATE

FROM PER_GENERATED_DATA_ROLES D

ORDER BY D.DATA_ROLE_NAME

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.

Technical Contents - SQL Query

Posted by : Mohammad

Follow on Linkedin