1. Introduction / Issue
In Oracle Fusion, managing user roles and permissions is crucial for maintaining security and ensuring that users have the appropriate access to perform their tasks. Extracting user and role information from a Fusion instance can be a complex task, especially when dealing with large organizations that have many users and roles. The provided PL/SQL script demonstrates how to automate this process by using Oracle APEX and RESTful web services to retrieve user and role data from a Fusion instance and store it in a local table.
The following technologies has been used to achieve the same.
- PL/SQL
2. Why we need to do / Cause of the issue
The primary use case for this script is to enable administrators to quickly access and analyze user roles within their Oracle Fusion environment. By storing this information in a local database, administrators can perform various tasks such as:
- Auditing user roles and permissions.
- Generating reports on role assignments.
- Identifying and addressing potential security issues.
- Automating the process of role management and verification.
This script is particularly useful in environments where manual retrieval of user role data is time-consuming and prone to errors.
3. How do we solve
Step1: Create a table named “USER_ROLES”. This will be used to store the user and role details extracted from the fusion instance.
CREATE TABLE “USER_ROLES”
( “USERID” VARCHAR2(100),
“USER_NAME” VARCHAR2(1000),
“ROLE_ID” VARCHAR2(100),
“ROLE_NAME” VARCHAR2(1000),
“ROLE_DESC” VARCHAR2(1000),
“ROLE_CODE” VARCHAR2(1000),
“USER_ID_NEW” NUMBER,
“CATEGORY” VARCHAR2(10)
)
/
Step2: Write a PL/SQL block like the below which will extract the user and role information from the fusion instance and store inside the user_roles table.
DECLARE
l_clob CLOB;
l_error_msg CLOB;
BEGIN
EXECUTE IMMEDIATE ‘truncate table USER_ROLES’;
l_clob := apex_web_service.make_rest_request(
p_url => ‘https://abcxyz.com/hcmRestApi/scim/Users’,
p_http_method => ‘GET’,
p_username => ‘username’,
p_password => ‘password’,
p_wallet_path => ‘file:/home/oraapexdb/wallet’,
p_wallet_pwd => ‘abc#123’
);
INSERT INTO USER_ROLES (userid, user_name, role_id, role_name, role_desc, role_code)
SELECT jt.id, jt.userName, jt.roleId, jt.roleName,jt.roleDesc,jt.roleCode
FROM JSON_TABLE(
l_clob,
‘$.Resources[*]’
COLUMNS (
id VARCHAR2(50) PATH ‘$.id’,
userName VARCHAR2(50) PATH ‘$.userName’,
nested path ‘$.roles[*]’ columns (
roleId VARCHAR2(50) PATH ‘$.id’,
roleName VARCHAR2(50) PATH ‘$.displayName’,
roleDesc VARCHAR2(50) PATH ‘$.description’,
roleCode VARCHAR2(50) PATH ‘$.value’
)
)
) jt;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
BEGIN
l_error_msg := UTL_HTTP.get_detailed_sqlerrm;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
END;
dbms_output.put_line(‘Error – ‘ || l_error_msg);
END;
Explanation:
Initialize Variables:
- The script begins by declaring variables to hold the JSON response from the REST API and any potential error messages.
Truncate the Existing Table:
- Before fetching new data, the script truncates the USER_ROLEStable to remove any outdated information.
Make REST API Request:
- The make_rest_requestfunction is used to send a GET request to the Fusion instance’s REST API. This request retrieves user and role data in JSON format.
- Authentication is handled using the provided username, password, and wallet for secure communication.
Parse the JSON Response:
- The JSON response is parsed using the JSON_TABLE This function extracts specific fields such as userid, user_name, role_id, role_name, role_desc, and role_codefrom the JSON and maps them to the corresponding columns in the USER_ROLES table.
Insert Data into the Table:
- The extracted data is inserted into the USER_ROLEStable, effectively updating the table with the latest user and role information from the Fusion instance.
Commit the Transaction:
- After inserting the data, the script commits the transaction to ensure that the changes are saved.
Handle Exceptions:
- The script includes an exception handling block to capture and display any errors that occur during the process, ensuring that issues can be diagnosed and addressed.
4. Conclusion
Automating the extraction of user and role information from an Oracle Fusion instance using PL/SQL and RESTful web services streamlines the process of managing user roles. This approach reduces manual effort, minimizes the risk of errors, and provides administrators with a reliable way to keep track of user permissions. By incorporating this script into their workflow, organizations can enhance their security posture and ensure that user roles are consistently monitored and managed.
