Oracle Cloud User roles and privileges Query

Hi Friends, we are going to discuss about Oracle Cloud User roles and privileges Query. We will share the detail sql query which will help to extract the complete user roles and privileges information’s in oracle cloud. Using this query we can develop the custom bip report in oracle cloud which will help to extract the user roles and privileges information’s in oracle cloud. Using this query we can find out which application roles assigned to user in oracle cloud. This is one of the important query for extracting user roles details in oracle cloud. In oracle cloud, all user roles and privileges details stored in the separate LDAP directory inside cloud. We need to run or schedule the LDAP requests in terms of data sync between LDAP user roles directory and the oracle cloud itself. Please find below the complete detail about oracle cloud user roles and privileges query.

Oracle Cloud User roles and privileges Query

Important Tables uses in oracle cloud user roles and privileges query

1.per_user_roles
2.per_users

3.per_roles_dn_tl
4.per_roles_dn
5.per_person_names_f

Detail Oracle Cloud User roles and privileges Query

Here below is the detail Oracle Cloud User roles and privileges Query.

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
ORDER BY pu.username, prdt.role_name;

Oracle Cloud User roles and privileges Query