Query to find privileges of user in oracle Fusion

Hi Friends, We are going to discuss about the query to find privileges of user in oracle fusion. In this post, we will share the detail oracle fusion sql query to extract the details of user and its roles, privileges details. This is complete sql query to extract the user and its privileges details in oracle fusion. If you want to develop the custom BIP reports in oracle fusion related to user roles and privileges, then you can refer this sql query. This query will really help to find out the user roles details in oracle fusion. We will also try to share the important tables of oracle fusion which help to store the user and its roles and privileges information’s. This is one of the usefully sql in oracle fusion related to user roles. Please find below the complete details about query to find privileges of user in oracle fusion.

Important Tables uses in the query to find privileges of user in oracle fusion

1.per_users

2.per_user_roles

3.per_roles_dn_tl

4.per_person_names_f

5.per_roles_dn

Complete SQL Query to find privileges of user in oracle fusion

Here below is the detail sql query to find out the user and roles privileges informations in oracle fusion

 

Query to find privileges of user in oracle Fusion

SELECT
         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') userrole_start_d,
         TO_CHAR (pur.end_date, 'DD-MON-YYYY') userrole_end_d,
         prd.abstract_role,

         prd.active_flag “Active Or Not Active”,

         prd.job_role,
         prd.data_role,
         prd.duty_role
        
    FROM per_user_roles    pur,

              per_roles_dn_tl   prdt,

               per_roles_dn      prd,
               per_person_names_f ppnf
               per_users         pu
   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 pu.username =:p_username
         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

query-to-find-privileges-of-user-in