This post shows a way to make REST API calls to Oracle Identity Cloud Service from an Oracle Database using PL/SQL. The idea is that a PL/SQL application can manage and search for user and group entities directly in Identity Cloud Service.
In the sample code we'll see how to obtain an access token from Identity Cloud Service and make calls to create users, query group membership, and retrieve user profile attributes. The PL/SQL code uses APEX 5.1 with the packages APEX_WEBSERVICE to call Identity Cloud Service and APEX_JASON to parse the JSON response.
exec dbms_network_acl_admin.create_acl (acl => 'idcs_apex_acl.xml',description => 'IDCS HTTP ACL',principal => 'APEX_05XXXX', is_grant => TRUE,privilege => 'connect',start_date => null,end_date => null);
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'idcs_apex_acl.xml',principal => 'APEX_05XXXX',is_grant => true,privilege => 'resolve');
exec dbms_network_acl_admin.assign_acl (acl => 'idcs_apex_acl.xml',host => 'myidcshost.com',lower_port => 8943,upper_port => 8943);
commit;
Replace the following values accordingly:
Verify the ACLs if you see the error "ORA-24247: network access denied by access control list (ACL)" when submitting a request.
The sample code is in the form of a PL/SQL Package. The script to create the package can be downloaded here. The package specification is as follows:
CREATE or REPLACE PACKAGE IDCS_CLIENT as
g_base_url VARCHAR2(500):= 'https://myidcs.oracle.com:8943'; -- Replace with Identity Cloud Service Base URL
g_client_id VARCHAR2(100):='8105a4f266c745b09a7bbed42ff151eb'; -- Replace with Client ID
g_client_pwd VARCHAR2(100):='a664583b-2115-4921-bd48-8e4a84b0c7a3'; -- Replace with Client Secret
g_wallet_path VARCHAR2(200):= 'file:/home/oracle/wallet'; -- Replace with DB Wallet location
g_wallet_pwd VARCHAR2(50):= 'Welcome1'; -- Replace with DB Wallet password
g_users_uri VARCHAR2(200):='/admin/v1/Users';
g_groups_uri VARCHAR2(200):='/admin/v1/Groups';g_bearer_token VARCHAR2(32767); -- Stores Access token
-- Used to return a list of groups on function get_user_membership
TYPE group_list_t
IS TABLE OF VARCHAR2(100);-- Used to return list of users and their profiles
TYPE user_list_t
IS TABLE OF idcs_user_t;-- Create the following TYPE outside of the package using SQLPLUS with SYSDBA account
-- TYPE idcs_user_t is used to store a user's profile
/*
CREATE TYPE idcs_user_t
AS OBJECT (
username VARCHAR2(100),
displayname VARCHAR2(100),
firstname VARCHAR2(50),
lastname VARCHAR2(50),
email VARCHAR2(100)
);
*/
Note that the variables g_client_id and g_client_pwd need to have the respective values obtained during the DB Client registration above. The variable g_base_url is the Identity Cloud Service Base URL for a specific tenant. Also, since communication with Identity Cloud Service is with SSL a wallet is needed for the database using g_wallet_path, and g_wallet_pwd for that purpose.
Three types are defined to return multiple groups, and users. The type idcs_user_t is an object to store user profile information. This kind of Type can not be created inside the package so it has to be done in SQLPlus before creating the package. The type user_list_t is a table type to hold user profiles and the type group_list_t is another table type to hold group names.
-- Obtain access token from Identity Cloud Service
PROCEDURE get_authz_token;-- Creates user in Identity Cloud Service
PROCEDURE create_user (
username varchar2,
first_name varchar2,
last_name varchar2,
email varchar2);-- Assigns group groupname to user username
PROCEDURE grant_group (
username varchar2,
groupname varchar2);-- Returns list of all Identity Cloud Service groups a user is a member of
FUNCTION get_user_membership (
username varchar2)
RETURN group_list_t;-- Returns internal user id from username
FUNCTION get_user_id (
username varchar2)
RETURN VARCHAR2;-- Table Function to retrieve username, displayname, firstname, lastname, and email for all users
/* Sample usage for Table Function user_profiles:SELECT * from TABLE((idcs_client.user_profiles));
SELECT email from TABLE((idcs_client.get_user_profiles)) where username='myuser1';
*/
FUNCTION user_profiles -- Table function to query user profiles
RETURN user_list_t PIPELINED;END idcs_client;
The use for procedures and functions is self explanatory. Just the function user_profiles is different in the sense that it's defined as an Oracle Table Function that is used to issue SELECT statements to retrieve Identity Cloud Service user profile attributes. For example, the query:
SELECT last_name from TABLE((idcs_client.get_user_profiles)) where username='myuser1@demo.com';
would retrieve, in real time, the last name of the user with username myuser1@demo.com directly from Identity Cloud Service.
The actual code is in the Package Body below
CREATE or REPLACE PACKAGE BODY idcs_client AS
-- Gets access token from Identity Cloud Service
PROCEDURE get_authz_token ISv_token_request_uri VARCHAR2(50):='/oauth2/v1/token';
v_creds VARCHAR2(500):=g_client_id||':'||g_client_pwd; --Client credentials unencoded
v_client_creds VARCHAR2(1000):=replace(replace(replace(utl_encode.text_encode(v_creds,'WE8ISO8859P1', UTL_ENCODE.BASE64),chr(9)),chr(10)),chr(13)); -- BASE64 - encodes credentials
l_idcs_response_CLOB CLOB; -- JSON response from Identity Cloud Service
l_idcs_url VARCHAR2(500);BEGIN
--Build request Headers
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded; charset=UTF-8';apex_web_service.g_request_headers(2).name := 'Authorization';
apex_web_service.g_request_headers(2).value := 'Basic '||v_client_creds;l_idcs_url := g_base_url||v_token_request_uri ; --Request URL
-- Sends a POST SSL Request to /oauth2/v1/token with grant_type=client_credentials and appropiate scope
l_idcs_response_clob := apex_web_service.make_rest_request
( p_url => l_idcs_url,
p_http_method => 'POST',
p_wallet_path => g_wallet_path,
p_wallet_pwd => g_wallet_pwd,
p_body => 'grant_type=client_credentials'||'&'||'scope=urn:opc:idm:__myscopes__');
dbms_output.put_line('IDCS Response getting token: '||l_idcs_response_clob);-- APEX_JSON Package used to parse response
apex_json.parse(l_idcs_response_clob); -- Parse JSON response. No ERROR Checking for simplicity.
-- Implement verification of response code and error checkg_bearer_token := apex_json.get_varchar2(p_path => 'access_token'); -- Obtain access_token from parsed response and set variable with token value
--dbms_output.put_line('Bearer Token: '||g_bearer_token);END get_authz_token;
The function get_autz_token obtains the access token from Identity Cloud Service using the credential obtained during the application registration. The credentials in v_creds are in the form 'clientID:clientSecret' and then BASE64 encoded in v_client_creds. The local variable l_idcs_response_CLOB will have the JSON response from Identity Cloud Service. After setting the request headers the request is sent using apex_web_service.make_rest_request with the URL l_idcs_url. The code is not checking for errors in the response, the entire response can be seen in DBMS Output. The response is parsed using apex_json.parse from l_idcs_response_CLOB and the access token is retrieved from the response resource 'access_token' and stored in g_bearer_token.
-- Creates user in Identity Cloud Service
PROCEDURE create_user (
username varchar2,
first_name varchar2,
last_name varchar2,
email varchar2) IS -- work emaill_idcs_url VARCHAR2(1000);
l_authz_header APEX_APPLICATION_GLOBAL.VC_ARR2;
l_idcs_response_clob CLOB; -- JSON Identity Cloud Service response
-- Quickly build a JSON Request Body for Create User Request from parameter values
l_users_body VARCHAR2(1000):='{
"schemas": [
"urn:ietf:params:scim:schemas:core:2.0:User"
],
"userName": "'||username||'",
"name": {
"familyName": "'||last_name||'",
"givenName": "'||first_name||'"
},
"emails": [
{
"value": "'||email||'",
"type": "work",
"primary": true
}
]
}';BEGIN
IF g_bearer_token IS NULL THEN
idcs_client.get_authz_token; -- Get an access token to be able to make request
END IF;
IF g_bearer_token IS NOT NULL THEN
-- Build Request Headers
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/scim+json';
apex_web_service.g_request_headers(2).name := 'Authorization';
apex_web_service.g_request_headers(2).value := 'Bearer ' || g_bearer_token; -- Access Token
l_idcs_url := g_base_url||g_users_uri ; -- Identity Cloud Service URL-- Sends a POST SSL Request to /admin/vi/Users with new user in Body
l_idcs_response_clob := apex_web_service.make_rest_request
( p_url => l_idcs_url,
p_http_method => 'POST',
p_wallet_path => g_wallet_path,
p_wallet_pwd => g_wallet_pwd,
p_body => l_users_body);
dbms_output.put_line('Identity Cloud Service Response creating user: '||l_idcs_response_clob);
apex_json.parse(l_idcs_response_clob); -- Parse JSON response. No ERROR Checking for simplicity.
-- Implement verification of response code and error check
--dbms_output.put_line(l_idcs_response_clob);
END IF;
END create_user;
The procedure create_user creates a user in Identity Cloud Service with specified username, first name, last name, and work email values. The variable l_users_body is the request body to create a user with the provided parameters. It first requests the access token that is stored in g_bearer_token. After building the headers it invokes apex_web_service.make_rest_request and the response in parsed. The code is not checking for code or errors in the response, the entire response can be seen in DBMS Output.
-- Returns list of groups user username is a member of
FUNCTION get_user_membership (
username varchar2)
RETURN group_list_t ISl_idcs_url VARCHAR2(1000);
l_idcs_response_clob CLOB; --JSON Identity Cloud Service Response
-- Request filter to return the displayname user username is a member of
l_groups_filter VARCHAR2(100):='?attributes=displayName&filter=members+eq+%22'||get_user_id(username)||'%22';
l_group_count PLS_INTEGER; --Number of group the user is a member of
l_group_names group_list_t:=group_list_t(); -- List of user's groups to returnBEGIN
IF g_bearer_token IS NULL THEN
idcs_client.get_authz_token; -- Get access token
END IF;
IF g_bearer_token IS NOT NULL THEN
-- Build Request Headers
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/scim+json';
apex_web_service.g_request_headers(2).name := 'Authorization';
apex_web_service.g_request_headers(2).value := 'Bearer ' || g_bearer_token;
l_idcs_url := g_base_url||g_groups_uri||l_groups_filter ;-- Sends a GET SSL Request to /admin/vi/Groups?attributes=displayName&filter=members+eq+%22'||get_user_id(username)||'%22';
l_idcs_response_clob := apex_web_service.make_rest_request
( p_url => l_idcs_url,
p_http_method => 'GET',
p_wallet_path => g_wallet_path,
p_wallet_pwd => g_wallet_pwd);dbms_output.put_line('IDCS Response getting membership: '||l_idcs_response_clob);
apex_json.parse(l_idcs_response_clob); -- Parse JSON response. No ERROR Checking for simplicity.
-- Implement verification of response code and error check
--dbms_output.put_line(l_idcs_response_clob);
l_group_count:=apex_json.get_count(p_path=>'Resources'); -- Obtained number of Resources (groups) to extract groups below--List of groups is returned as l_group_names. This loop populates the table variable.
FOR i in 1..l_group_count LOOP -- Through all returned groups.
l_group_names.extend;
--Find displayname for current group %d
l_group_names(l_group_names.last):=apex_json.get_varchar2(p_path=>'Resources[%d].displayName',p0=>i);
--dbms_output.put_line(l_group_names(i)); -- Print group displayName
END LOOP;RETURN l_group_names; -- Returns list of the user's groups
END IF;
RETURN null;
END get_user_membership;
The function get_user_membership returns all groups a user belongs to. A filter is specified in the variable l_groups_filter to retrieve the group displayname and a filter to retrieve only the groups with the specified user_id as a value in the members attribute. The user id is retrieve using username from another call to Identity Cloud Service using the function get_user_id. After building the headers it invokes apex_web_service.make_rest_request and the response in parsed. The group count is returned from apex_json.get_count into l_grouip_count which is used in a Loop to populate the variable l_group_names with the displayname for each of the Resources in the response. The table variable l_group_names is returned with the results.
-- This is a Table Function, can be queried as SELECT * from TABLE((idcs_client.get_user_profiles));
FUNCTION user_profiles
RETURN user_list_t PIPELINED ISl_idcs_url VARCHAR2(1000);
l_idcs_response_clob CLOB; -- JSON Identity Cloud Service Response
-- Filter to get displayname, username, active, firstname, lastname and primary email
l_users_filter VARCHAR2(100):='?attributes=displayname,username,active,name.givenName,name.familyName,emails.value,emails.primary';
l_user_count PLS_INTEGER; -- Number of users returned.
l_user_profile idcs_user_t:=idcs_user_t(NULL,NULL,NULL,NULL,NULL); --initialize variable that holds user profilesBEGIN
IF g_bearer_token IS NULL THEN
idcs_client.get_authz_token; -- Get Access Token
END IF;
IF g_bearer_token IS NOT NULL THEN
-- Build Request Headers
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/scim+json';
apex_web_service.g_request_headers(2).name := 'Authorization';
apex_web_service.g_request_headers(2).value := 'Bearer ' || g_bearer_token;
l_idcs_url := g_base_url||g_users_uri||l_users_filter ;-- Sends a GET SSL Request to /admin/vi/Users?attributes=displayname,username,active,name.givenName,name.familyName,emails.value,emails.primary to retrieve ALL USERS.
-- No Paging is done
l_idcs_response_clob := apex_web_service.make_rest_request
( p_url => l_idcs_url,
p_http_method => 'GET',
p_wallet_path => g_wallet_path,
p_wallet_pwd => g_wallet_pwd);
--dbms_output.put_line('Identity Cloud Service Response getting profiles: '||l_idcs_response_clob);
apex_json.parse(l_idcs_response_clob); -- Parse JSON response. No ERROR Checking for simplicity.
-- Implement verification of response code and error checkl_user_count:=apex_json.get_count(p_path=>'Resources'); -- Number of Resources (users) in response
-- LOOP through all returned users and idcs_user_t table with the profile attributes for each user
-- No Paging implemented
FOR i in 1..l_user_count LOOP
l_user_profile:=idcs_user_t(apex_json.get_varchar2(p_path=>'Resources[%d].userName',p0=>i),
apex_json.get_varchar2(p_path=>'Resources[%d].displayName',p0=>i),
apex_json.get_varchar2(p_path=>'Resources[%d].name.givenName',p0=>i),
apex_json.get_varchar2(p_path=>'Resources[%d].name.familyName',p0=>i),
apex_json.get_varchar2(p_path=>'Resources[%d].emails[1].value',p0=>i)
);-- dbms_output.put_line(l_user_profile.username);
PIPE ROW(l_user_profile); -- Pipe out rows to invoking select statement
END LOOP;END IF;
RETURN;
END user_profiles;
The table function user_profiles as mentioned above is invoked from a select statement to retrieve user profiles. With the variable l_users_filter it can limit the data that comes from Identity Cloud Service. As declared is only retrieving a list of attributes per user and it's not filtering users by attribute, so it will retrieve all users. An example of SCIM filters when searching users is in this tutorial. After building the headers it invokes apex_web_service.make_rest_request, the response in parsed. The number of users returned by the request are stored in l_user_count by calling apex_json.get_count to get the number of Resources returned. The table type variable l_user_profile is populated with the attributes from each user returned in a Loop. Finally, the rows are piped out to the select statement that was issued. Here's a sample of the result of a select statement on user_profiles.
Previous Post