The original idea is to login to Oracle APEX using WeChat SSO, because WeChat is the most popular social app in China and login with WeChat is a must-have feature for most apps. However, registration of web application in WeChat open platform seems to be available only for company, so we are looking for some workaround.

This article is used to introduce how to integrate Oracle APEX and Authing, which is an identification cloud (also known as IDaaS) product. It is very similar to Oracle’s IDCS, while in China IDCS is not that popular and Authing is an alternative solution. Most importantly, Authing natively supports WeChat scanning login (free of charge) and SSO (chargeable), as of now.

Table of Contents

Overall Solution

Both Oracle APEX and Authing supports OIDC (OpenID Connect), which makes it easy to integrate them by configuration rather than programming. This solution consists of two sections:

Integrate APEX and Authing through OIDC
Configure Authing to support WeChat scanning login

Integrate APEX and Authing through OIDC

By definition of OIDC, there are some key roles. They are listed in following

Party	Role in OIDC
End User (WeChat App)	Resource Owner (资源持有者)
Web Browser	Application (应用)
Authing	Authentication Server (认证服务器)
Oracle APEX Website	Resource Owner (资源服务器)

Roles in OIDC

As per document of Authing, the Authorization Code login sequence are described as following

By configuring the integration, all activities in the diagram above can be auto executed during user login.

The configuration includes following steps:

Enable access of Oracle APEX to Authing website

Although not being mentioned explicitly in the diagram, Oracle APEX as resource server (资源服务器) has implicit interaction with authentication server (Authing), through web services. For example, during first time login, Oracle APEX redirects browser to /auth endpoint of Authing. In above step 1, a more typical scenario is, user inputs URL of Oracle APEX in browser, then it returns a 301 status to browser so that the browser auto redirects to Authing’s login page.

Oracle APEX is relying on its database utility UTL_HTTP for web service invocation. To facilitate the interaction, Oracle APEX have to accept SSL/TLS certificate of Authing. By default UTL_HTTP doesn’t recognize certificate of any website. In order to make Authing’s certificate acceptable by Oracle APEX, we are adding the certificate into Oracle’s wallet.

Download certificate of Authing

Open “authing.cn” in browser like Firefox, view its certification

Download certificate of root CA

Create an Oracle wallet directory. This directory is used to keep wallet files. Then import the certificate downloaded in above steps. Follow similar steps in this post.

Assign ACL to permit access to Authing’s domain

declare
  l_username varchar2(30) := 'APEX_230200';
begin
  dbms_network_acl_admin.append_host_ace(
    host => 'clark-apex.authing.cn',
    lower_port => 443,
    upper_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                        principal_name => l_username,
                        principal_type => xs_acl.ptype_db));

  commit;
end;

Here “clark-apex” is the sub domain configured in Authing’s app. This step is described later in this article.

Verify if the OpenID discovery URL is accessible by Oracle database. Make sure it can get HTTP response without error. OpenID discovery URL can be found in Service Discovery Address in Authing’s app after creation.

select apex_web_service.make_rest_request(
    p_url         => 'https://clark-apex.authing.cn/oidc/.well-known/openid-configuration',
    p_http_method => 'GET' ) as response from dual;

Create web credential in APEX

Follow this step to specify wallet which has contains web credential.

Create web credential in APEX

For Client ID and Client Secret, get them from App ID and App Secret in Authing Self-built App.

Create authentication schema in APEX application

For Discovery URL, get it from Service Discovery Address in Authing Self-built App.

Set the newly created authentication schema as current

Configure Authing to support WeChat scanning login

Configure several components in Authing, including Self-built App, Social IDP, etc.

Create Authing Self-built App

Some key fields, including App ID, App Secret and Service Discovery Address, are auto generated after creation.

Enable authorization of the Self-built App to all user

Create Social IDP

Link the social IDP to Self-built app

Test The Social Sign On Process

After comletion of the configuration, we test the Oracle APEX application login by entering its home URL in browser.

Redirect has failed because we haven’t set redirect_uri of Oracle APEX in Self-built App. To resolve it, we get the redirect_uri from browser’s URL. Here we can get it as “https://www.linyinghao.cn/ords/apex_authentication.callback”.
Then we set it in the Login Callback URL of Self-built App.