User, Role - Privileges || User and Roles Audit Queries.

 ================   Role Audit   ======================= 

  SELECT RLB.ROLE_NAME

             AS PARENT_ROLE_NAME,

         RB.CODE

             AS PARENT_ROLE_CODE,

         RTB.ROLE_TYPE_NAME

             AS PARENT_ROLE_TYPE,

         RLA.ROLE_NAME

             CHILD_ROLE_NAME,

         RA.CODE

             AS CHILD_ROLE_CODE,

         RTA.ROLE_TYPE_NAME

             AS CHILD_ROLE_TYPE,

         RM.CREATED_BY,

         (SELECT L.FIRST_NAME || ' ' || L.LAST_NAME

            FROM PER_LDAP_USERS L

           WHERE     L.USERNAME = RM.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 (RM.CREATION_DATE, 'MM/DD/YYYY hh:mm AM')

             AS CREATION_DATE_UTC,

         RM.LAST_UPDATED_BY,

         (SELECT L.FIRST_NAME || ' ' || L.LAST_NAME

            FROM PER_LDAP_USERS L

           WHERE     L.USERNAME = RM.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 (RM.LAST_UPDATE_DATE, 'MM/DD/YYYY hh:mm AM')

             AS LAST_UPDATE_DATE_UTC,

         CASE

             WHEN TRUNC (RM.LAST_UPDATE_DATE) = TRUNC (RM.CREATION_DATE)

             THEN

                 'UPDATE(ADD)'

             WHEN (    TRUNC (RM.LAST_UPDATE_DATE) <> TRUNC (RM.CREATION_DATE)

                   AND NVL (RM.EFFECTIVE_END_DATE, TO_DATE ('4712-12-31')) =

                       TO_DATE ('4712-12-31'))

             THEN

                 'UPDATE(NEW)'

             ELSE

                 'UPDATE(OLD)'

         END

             AS ACTION,

         TO_CHAR (RM.EFFECTIVE_START_DATE, 'MM/DD/YYYY')

             AS EFFECTIVE_START_DATE,

         TO_CHAR (RM.EFFECTIVE_END_DATE, 'MM/DD/YYYY')

             AS EFFECTIVE_END_DATE

    FROM ASE_ROLE_ROLE_MBR RM,

         ASE_ROLE_TL      RLA,

         ASE_ROLE_TL      RLB,

         ASE_ROLE_B       RA,

         ASE_ROLE_B       RB,

         PER_USERS        UA,

         PER_USERS        UB,

         ASE_ROLE_TYPE_VL RTA,

         ASE_ROLE_TYPE_VL RTB

   WHERE     RM.PARENT_ROLE_ID = RLA.ROLE_ID

         AND RM.PARENT_ROLE_ID = RA.ROLE_ID /*Parent is child, child is parent*/

         AND RLA.LANGUAGE = 'US'

         AND RM.CHILD_ROLE_ID = RLB.ROLE_ID

         AND RM.CHILD_ROLE_ID = RB.ROLE_ID

         AND RLB.LANGUAGE = 'US'

         AND UA.USERNAME = RM.CREATED_BY

         AND UB.USERNAME = RM.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 RA.EFFECTIVE_START_DATE = (SELECT MAX (RA1.EFFECTIVE_START_DATE)

                                          FROM ASE_ROLE_B RA1

                                         WHERE RA1.ROLE_ID = RA.ROLE_ID)

         AND RB.EFFECTIVE_START_DATE = (SELECT MAX (RB1.EFFECTIVE_START_DATE)

                                          FROM ASE_ROLE_B RB1

                                         WHERE RB1.ROLE_ID = RB.ROLE_ID)

         AND RTA.LANGUAGE = 'US'

         AND RTA.ROLE_TYPE_CODE = RA.ROLE_TYPE_CODE

         AND RTB.LANGUAGE = 'US'

         AND RTB.ROLE_TYPE_CODE = RB.ROLE_TYPE_CODE

         /*AND (   (RM.CREATION_DATE BETWEEN :PARAM_FROM_DATE AND :PARAM_TO_DATE)

              OR (RM.LAST_UPDATE_DATE BETWEEN :PARAM_FROM_DATE

                                          AND :PARAM_TO_DATE))

         AND (   (   RM.CREATED_BY IN ( :PARAM_USERS)

                  OR LEAST ( :PARAM_USERS) IS NULL)

              OR (   RM.LAST_UPDATED_BY IN ( :PARAM_USERS)

                  OR LEAST ( :PARAM_USERS) IS NULL))

         AND (   (   RLB.ROLE_NAME IN ( :PARAM_ROLENAME)

                  OR LEAST ( :PARAM_ROLENAME) IS NULL)

              OR (   RLA.ROLE_NAME IN ( :PARAM_ROLENAME)

                  OR LEAST ( :PARAM_ROLENAME) IS NULL))*/

ORDER BY RM.LAST_UPDATE_DATE DESC

==========   Function Security Policy  ===============

  SELECT 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 (   (PM.CREATION_DATE BETWEEN :PARAM_FROM_DATE AND :PARAM_TO_DATE)

              OR (PM.LAST_UPDATE_DATE BETWEEN :PARAM_FROM_DATE

                                          AND :PARAM_TO_DATE))

         AND (   (   PM.CREATED_BY IN ( :PARAM_USERS)

                  OR LEAST ( :PARAM_USERS) IS NULL)

              OR (   PM.LAST_UPDATED_BY IN ( :PARAM_USERS)

                  OR LEAST ( :PARAM_USERS) IS NULL))

         AND ((   RL.ROLE_NAME IN ( :PARAM_ROLENAME)

               OR LEAST ( :PARAM_ROLENAME) IS NULL))*/

ORDER BY PM.LAST_UPDATE_DATE DESC

========= User and Privileges Query  ============

SELECT DISTINCT

    pu.username         username,

pea.EMAIL_ADDRESS  Mail_id,

    pu.active_flag      active_flag,

    au.user_id          user_id,

    au.user_login       user_login,

    r.role_name         role_name,

    r.description       description,

priv.code privilege_code,

    priv.name Privilege_name

FROM

    per_persons            pp,

    per_all_people_f       papf,

    per_person_names_f_v   ppf,

PER_EMAIL_ADDRESSES pea,

    hr_locations_all_f_vl  hl,

    per_departments        pd,

    per_all_assignments_m  paaf,

    per_users              pu,

    ase_user_vl            au,

    ase_user_role_mbr      aurm,

    ase_role_vl            r,

ASE_PRIVILEGE_VL        priv

WHERE

        1 = 1

    AND au.user_guid = pu.user_guid (+)

and pu.person_id= pea.person_id

    AND pu.person_id = papf.person_id (+)

    AND papf.person_id = pp.person_id (+)

    AND pp.person_id = ppf.person_id (+)

    AND ppf.person_id = paaf.person_id (+)

    AND paaf.location_id = hl.location_id (+)

    AND paaf.organization_id = pd.organization_id (+)

AND priv.DATA_SYNC_ID = au.DATA_SYNC_ID

    AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate))

    AND trunc(sysdate) BETWEEN nvl(papf.effective_start_date, trunc(sysdate)) AND nvl(papf.effective_end_date, trunc(sysdate))

    AND trunc(sysdate) BETWEEN nvl(paaf.effective_start_date, trunc(sysdate)) AND nvl(paaf.effective_end_date, trunc(sysdate))

    AND trunc(sysdate) BETWEEN nvl(hl.effective_start_date, trunc(sysdate)) AND nvl(hl.effective_end_date, trunc(sysdate))

    AND trunc(sysdate) BETWEEN nvl(pd.effective_start_date, trunc(sysdate)) AND nvl(pd.effective_end_date, trunc(sysdate))

    AND trunc(sysdate) BETWEEN nvl(au.effective_start_date, trunc(sysdate)) AND nvl(au.effective_end_date, trunc(sysdate))

    AND aurm.user_id = au.user_id

    AND r.role_id = aurm.role_id

   --AND r.role_name = 'Team Collaborator'

   AND r.role_name = NVL(:P_role_name,  r.role_name)

   

===========================   Privileges   ==================================

SELECT role.code           Role_Code,

       role.role_type_code,

       role.role_name,

       role.is_seeded      role_is_seeded,

       priv.code           privilege_code,

       priv.NAME           Privilege_name,

       mappr.creation_date priv_add_date

FROM   ase_privilege_vl priv,

       ase_priv_role_mbr mappr,

       ase_role_vl role

WHERE

  --role.code like coalesce (:P_ROLE_CODE,:DEFAULT_CODE)

  ( sysdate BETWEEN priv.effective_start_date AND priv.effective_end_date

     OR priv.effective_end_date IS NULL )

  AND ( sysdate BETWEEN role.effective_start_date AND role.effective_end_date

         OR role.effective_end_date IS NULL )

  AND priv.privilege_id = mappr.privilege_id

  AND mappr.role_id = role.role_id

  AND role.language = 'US'

  AND priv.language = 'US'

ORDER  BY role_code,

          privilege_code