Privileges:
/*Query to fetch privileges assigned to the user*/
SELECT DISTINCT u.user_display_name AS UserName,
rt.role_name ,
pt.name privilege_name
FROM ASE_PRIVILEGE_B p ,
ASE_PRIVILEGE_TL pt,
ASE_PRIV_ROLE_MBR m ,
ASE_ROLE_B r ,
ASE_ROLE_TL rt,
ASE_USER_TL u ,
ASE_USER_ROLE_MBR rm
WHERE 1=1
AND ( SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
OR p.EFFECTIVE_END_DATE IS NULL)
AND ( SYSDATE BETWEEN r.EFFECTIVE_START_DATE AND r.EFFECTIVE_END_DATE
OR r.EFFECTIVE_END_DATE IS NULL)
AND p.PRIVILEGE_ID = m.PRIVILEGE_ID
AND m.role_id =r.role_id
AND u.LANGUAGE = 'US'
AND rm.user_id = u.user_id
AND rm.role_id = r.role_id
AND pt.PRIVILEGE_ID = p.PRIVILEGE_ID
AND pt.LANGUAGE = 'US'
AND rt.role_id = r.role_id
AND rt.LANGUAGE = 'US'
AND ( SYSDATE BETWEEN m.EFFECTIVE_START_DATE AND m.EFFECTIVE_END_DATE
OR m.EFFECTIVE_END_DATE IS NULL)
AND ( SYSDATE BETWEEN rm.EFFECTIVE_START_DATE AND rm.EFFECTIVE_END_DATE
OR rm.effective_end_date IS NULL)
AND u.user_display_name = :p_user_name
ORDER BY 1,2,3
Roles:
/*Query to fetch roles assigned to the user*/
SELECT pu.user_id,
pu.username,
ppnf.full_name,
prdt.role_id,
prdt.role_name,
prd.role_common_name,
prdt.description,
TO_CHAR (pur.start_date, 'DD-MON-YYYY') role_start_date,
TO_CHAR (pur.end_date, 'DD-MON-YYYY') role_end_date,
prd.abstract_role,
prd.job_role,
prd.data_role,
prd.duty_role,
prd.active_flag
FROM per_user_roles pur,
per_users pu,
per_roles_dn_tl prdt,
per_roles_dn prd,
per_person_names_f ppnf
WHERE 1 = 1
AND pu.user_id = pur.user_id
AND prdt.role_id = pur.role_id
AND prdt.language = USERENV ('lang')
AND prdt.role_id = prd.role_id
AND NVL (pu.suspended, 'N') = 'N'
AND ppnf.person_id(+) = pu.person_id
AND ppnf.name_type(+) = 'GLOBAL'
AND pu.active_flag = 'Y'
AND NVL (pu.start_date, SYSDATE) <= SYSDATE
AND NVL (pu.end_date, sysdate) >= sysdate
AND pu.username = :p_user_name
