User and Roles Query in Oracle cloud : Sql Query to get Oracle User Roles in Oracle Cloud

User and Roles Query in Oracle cloud : SQL Query to get Oracle User Roles in Oracle Cloud

We should have the User accounts to access the Oracle Cloud application. In User accounts we do attach the application roles. roles are the privileges on the basis of that , we do access the Oracle Cloud application. Different different roles have different types of privileges so some user account will have different access in the cloud application and other user account will have different.

 

We can see the user account and the attached roles in the cloud application but if want to developed some reports then we can refer this below sql query. This SQL Query helps to extract the User and the Roles data in Oracle cloud. Please find below the SQL Query for Oracle User Roles in Oracle Cloud.

Detail User and Roles Query in Oracle cloud FOR extracting the User roles data.

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 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;