Invoking Oracle Identity Cloud Service REST API from PL/SQL

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.

Setup

 

  1. 1- Since the Oracle Database is acting as an Identity Cloud Service client we need to register it using Client Credentials as grant type and with permission to invoke Administratio APIs with Identity Domain Administrator.  The Client ID and Client Secret returned by the registration are used in the sample code to request an access code.

Screenshot 2017-02-08 14.35.43

Screenshot 2017-02-08 14.41.40

  1. 2- Now, we give the Database the appropriate ACLs so it can resolve and call the Identity Cloud Service URL. Note that DB Cloud instances seem to have existing ACLs for any external host (*).  If needed, execute the existing commands to create ACL’s for the Identity Cloud Service Host and Port:

 

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:

  1. 1. ‘APEX_05XXXX’: the APEX schema owner (it varies with version)
  2. 2. ‘myidcshost.com’: Identity Cloud Service host for one tenant.
  3. 3. 8943: Identity Cloud Service SSL Port

Verify the ACLs if you see the error “ORA-24247: network access denied by access control list (ACL)” when submitting a request.

 

  1. 3- In a Database Schema of your choice create the PL/SQL Package using this SQL Script.  Before executing the script replace all ocurrences of idcs_app for your schema name.  Run the script as a SYSDBA user or with the permissions to create procedures and types.

 

  1. 4- Add the appropiate root certificate chain to the Database Wallet (trusted certificates) for SSL communication with Identity Cloud Service.

 

  1. 5- Test requests to Identity Cloud Service using curl with SSL to verify proper access.

 

PL/SQL Code

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 IS

v_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 check

g_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 email

l_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 IS

l_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 return

BEGIN
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 IS

l_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 profiles

BEGIN
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 check

l_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.

 

Screenshot 2017-02-14 11.37.00

 

Add Your Comment