Useful Tables:
- ASE_PRIV_ROLE_MBR
- ASE_ROLE_TL
- ASE_ROLE_B
- ASE_PRIVILEGE_VL
- PER_USERS
- ASE_ROLE_TYPE_VL
- fusion.PER_USERS
- fusion.PER_USER_ROLES
- fusion.PER_ROLES_DN_VL
- fusion.PER_USER_ROLES
- fusion.PER_ROLES_DN_VL
- fusion.FUN_USER_ROLE_DATA
ASE_PRIV_ROLE_MBR PM ASE_ROLE_TL RL ASE_ROLE_B R ASE_PRIVILEGE_VL PL PER_USERS UA PER_USERS UB ASE_ROLE_TYPE_VL RT fusion.PER_USERS fusion.PER_USER_ROLES fusion.PER_ROLES_DN_VL fusion.PER_USER_ROLES fusion.PER_ROLES_DN_VL fusion.FUN_USER_ROLE_DATA
Table FUN_USER_ROLE_DATA_ASGNMNTS is used for “Manage data access for users”.
it will store use and role assignment to data security.
Table name – FUN_USER_ROLE_DATA_ASGNMNTS
1.user– username to find out from which base table through USER_GUID (FUN_USER_ROLE_DATA_ASGNMNTS)
2.role — rolename column FUN_USER_ROLE_DATA_ASGNMNTS
3.security context –security context to find out from which base table through ACCESS_SET_ID (FUN_USER_ROLE_DATA_ASGNMNTS)
Based on Security Context set in UI, Column will be populated.
For Example, If context is set as Business Unit then field Org Id is populated with Business Unit ID. LEDGER_ID for Security Context Ledger.
Please refer below link for Table information
— Query that returns User and Role Assignment
SELECT
a.USERNAME,
c.ROLE_NAME,
b.CREATION_DATE,
b.CREATED_BY,
b.LAST_UPDATE_DATE
b.last_updated_by
FROM fusion.PER_USERS a,
fusion.PER_USER_ROLES b,
fusion.PER_ROLES_DN_VL c
WHERE a.USER_ID = b.USER_ID
AND b.ROLE_ID = c.ROLE_ID;
— Query that returns User, Role assignment and Security Roles
SELECT
a.USERNAME,
c.ROLE_COMMON_NAME,
c.ROLE_DISTINGUISHED_NAME,
b.CREATION_DATE,
b.CREATED_BY,
b.LAST_UPDATE_DATE
b.last_updated_by
d.*
FROM fusion.PER_USERS a,
fusion.PER_USER_ROLES b,
fusion.PER_ROLES_DN_VL c,
fusion.FUN_USER_ROLE_DATA_ASGNMNTS d
WHERE a.USER_ID = b.USER_ID
AND b.ROLE_ID = c.ROLE_ID
and d.USER_GUID=a.user_guid
and d.ROLE_NAME=c.ROLE_COMMON_NAME
SELECT
UA.username AS USER_NAME,
RL.ROLE_NAME AS ROLE_NAME,
R.CODE AS ROLE_CODE,
RT.ROLE_TYPE_NAME AS ROLE_TYPE,
PL.CODE AS PRIVILEGE_CODE,
PL.NAME AS PRIVILEGE_NAME,
PM.CREATED_BY,
(
SELECT L.FIRST_NAME || ‘ ‘ || L.LAST_NAME
FROM PER_LDAP_USERS L
WHERE L.USERNAME = PM.CREATED_BY
AND L.USER_GUID = UA.USER_GUID
AND L.CREATION_DATE = (
SELECT MAX(L1.CREATION_DATE)
FROM PER_LDAP_USERS L1
WHERE L1.USERNAME = L.USERNAME
AND L1.USER_GUID = L.USER_GUID
)
) AS CREATED_BY_NAME,
TO_CHAR(PM.CREATION_DATE, ‘MM/DD/YYYY hh:mm AM’) AS CREATION_DATE_UTC,
PM.LAST_UPDATED_BY,
(
SELECT L.FIRST_NAME || ‘ ‘ || L.LAST_NAME
FROM PER_LDAP_USERS L
WHERE L.USERNAME = PM.LAST_UPDATED_BY
AND L.USER_GUID = UB.USER_GUID
AND L.CREATION_DATE = (
SELECT MAX(L1.CREATION_DATE)
FROM PER_LDAP_USERS L1
WHERE L1.USERNAME = L.USERNAME
AND L1.USER_GUID = L.USER_GUID
)
) AS LAST_UPDATED_BY_NAME,
TO_CHAR(PM.LAST_UPDATE_DATE, ‘MM/DD/YYYY hh:mm AM’) AS LAST_UPDATE_DATE_UTC,
CASE
WHEN TRUNC(PM.LAST_UPDATE_DATE) = TRUNC(PM.CREATION_DATE) THEN ‘UPDATE(ADD)’
WHEN TRUNC(PM.LAST_UPDATE_DATE) <> TRUNC(PM.CREATION_DATE)
AND NVL(PM.EFFECTIVE_END_DATE, TO_DATE(‘4712-12-31’)) = TO_DATE(‘4712-12-31’) THEN ‘UPDATE(NEW)’
ELSE ‘UPDATE(OLD)’
END AS ACTION,
TO_CHAR(PM.EFFECTIVE_START_DATE, ‘MM/DD/YYYY’) AS EFFECTIVE_START_DATE,
TO_CHAR(PM.EFFECTIVE_END_DATE, ‘MM/DD/YYYY’) AS EFFECTIVE_END_DATE
FROM
ASE_PRIV_ROLE_MBR PM,
ASE_ROLE_TL RL,
ASE_ROLE_B R,
ASE_PRIVILEGE_VL PL,
PER_USERS UA,
PER_USERS UB,
ASE_ROLE_TYPE_VL RT
WHERE
PM.ROLE_ID = RL.ROLE_ID
AND PM.ROLE_ID = R.ROLE_ID
AND RL.LANGUAGE = ‘US’
AND PL.LANGUAGE = ‘US’
AND UA.USERNAME = PM.CREATED_BY
AND UB.USERNAME = PM.LAST_UPDATED_BY
AND UA.START_DATE = (
SELECT MAX(UA1.START_DATE)
FROM PER_USERS UA1
WHERE UA1.USER_ID = UA.USER_ID
)
AND UB.START_DATE = (
SELECT MAX(UB1.START_DATE)
FROM PER_USERS UB1
WHERE UB1.USER_ID = UB.USER_ID
)
AND R.EFFECTIVE_START_DATE = (
SELECT MAX(R1.EFFECTIVE_START_DATE)
FROM ASE_ROLE_B R1
WHERE R1.ROLE_ID = R.ROLE_ID
)
AND PL.EFFECTIVE_START_DATE = (
SELECT MAX(PL1.EFFECTIVE_START_DATE)
FROM ASE_PRIVILEGE_VL PL1
WHERE PL1.PRIVILEGE_ID = PL.PRIVILEGE_ID
)
AND PM.PRIVILEGE_ID = PL.PRIVILEGE_ID
AND RT.LANGUAGE = ‘US’
AND RT.ROLE_TYPE_CODE = R.ROLE_TYPE_CODE
AND (
RL.ROLE_NAME IN (:PARAM_ROLENAME)
OR LEAST(:PARAM_ROLENAME) IS NULL
)
ORDER BY
PM.LAST_UPDATE_DATE DESC;
These queries will only work for FSCM and not HCM. HCM roles, security model and underlying tables are different so you may need another sql.
Enterprise Resource Planning and Integrations BlogSpot 