Oracle Cloud User roles and privileges Query

Oracle Cloud User roles and privileges Query

Hi friends , we are going to discuss about the user roles and privileges query in oracle cloud. First thing i would like to clear you is this , Oracle cloud and oracle fusion both are same. So if we will say oracle fusion or cloud it means we are referring the same application. We will share the detail sql query which helps to extract the complete users details with roles information as well the privileges details in oracle cloud. Using this single query you would be able to extract the complete oracle cloud user roles and privileges details. In Oracle Cloud , Roles consist of privileges. One Oracle Role consist of multiple privileges which to provide the access of different operations and activities in cloud application. We will also try to share the oracle cloud important tables too which helps to store the user roles and privileges details in oracle cloud. Using this query , you can able to develop the custom BIP reports related to users and roles. So this is an very important sql query in day to day working. Please find below the complete detail about Oracle Cloud User roles and privileges Query.

5 Important Tables uses in Oracle Cloud User roles and privileges Query

Here below is the important table which helps to store the complete oracle cloud user , privilege's information's in oracle cloud.

1.per_user_roles 

2.per_users 

3.per_roles_dn_tl 

4.per_roles_dn

5.per_person_names_f

Detail SQL Query for Oracle Cloud User roles and privileges Query

Here below is the detail sql query which helps to extract the complete user roles and privileges information's in oracle fusion. 

SELECT pu.username "User Name",

         ppnf.full_name "Employee Name",

         prd.abstract_role "Absteact Role Name",

         prd.job_role "Job Role Name",

         prd.data_role "User Data Role",

         prd.duty_role "Duty Role",

         prd.active_flag,

         prdt.role_name,

         prd.role_common_name,

         prdt.description,

         TO_CHAR (pur.start_date, 'DD-MON-YYYY') user_role_start_date,

         TO_CHAR (pur.end_date, 'DD-MON-YYYY') user_role_end_date,

    FROM 

         per_roles_dn_tl   prdt,

         per_roles_dn      prd,

         per_person_names_f ppnf,

         per_user_roles    pur,

         per_users         pu

   WHERE     1 = 1

         AND pu.user_id = pur.user_id

         AND prdt.role_id = pur.role_id

         AND NVL (pu.suspended, 'N') = 'N'

         AND ppnf.person_id = pu.person_id

         AND ppnf.name_type = 'GLOBAL'

         AND NVL (pu.start_date, SYSDATE) <= SYSDATE

         AND NVL (pu.end_date, SYSDATE) >= SYSDATE

         AND pu.active_flag = 'Y'

         AND prdt.language = USERENV ('lang')

         AND prdt.role_id = prd.role_id

         AND pu.username =:p_user_name

ORDER BY 1,2

Oracle Cloud User roles and privileges Query

Query 2 for Oracle Cloud User roles and privileges Query

SELECT pu.username "User",

prdt.role_id "Role",

prdt.role_name "Role Name"

FROM per_user_roles pur,
per_users pu,
per_roles_dn_tl prdt,
per_roles_dn prd
WHERE pu.user_id = pur.user_id
AND pu.username =:puser_name
AND prdt.role_id = pur.role_id
AND prdt.role_id = prd.role_id
AND prdt.language = USERENV (‘lang’)
AND pu.active_flag = ‘Y’
ORDER BY 1

Oracle Cloud User roles and privileges Query