8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | 26ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle Application Express (APEX) : Manage Web Credentials From the Command Line

This article describes how to manage APEX web credentials from the command line.

We can managed credentials from the APEX interface (see here), but it is useful to manage credentials from the command line also.

We use Azure AD and Multi-Factor Authentication (MFA) to authenticate to our APEX apps, and we do lots of cloning of pluggable databases. Each environment has a different enterprise application definition in Azure AD, and therefore unique client IDs and secrets, so cloning an environment breaks the authenitcation. To prevent this we need to reset the web credentials as part of our cloning process. It is much more convenient to automate this, rather than updating credentials manually.

Related articles.

Assumptions

It is assumed you have a working APEX installation.

All actions will be issued from the workspace user unless otherwise stated. In this case the workspace is called DEV_WS, with the underlying schema having the same name.

 conn dev_ws/dev_ws@//localhost:1521/freepdb1

Create an APEX Web Credential

We check to see if there are any existing credentials. We can see there are not.

 set linesize 100 column workspace format a20 column name format a20 column static_id format a20 column credential_type format a30 select workspace, name, static_id, credential_type from apex_workspace_credentials order by 1,2; no rows SQL>

We create a new OAuth credential, specifically one for a Client Credentials flow. We use APEX_UTIL.SET_WORKSPACE to set our workspace. We use APEX_CREDENTIAL.CREATE_CREDENTIAL to create the credential. Finally we use APEX_CREDENTIAL.SET_PERSISTENT_CREDENTIALS to save the client ID and secret for the credential.

 declare l_workspace varchar2(30) := 'DEV_WS'; l_cred_name varchar2(30) := 'My Web Credential'; l_static_id varchar2(30) := 'my_web_credential'; l_client_id varchar2(30) := 'sdfg6757fdgddg8d5ffgdg..'; l_secret varchar2(30) := '767978689dfgdf6798876d..'; begin apex_util.set_workspace(p_workspace => l_workspace); apex_credential.create_credential ( p_credential_name => l_cred_name, p_credential_static_id => l_static_id, p_authentication_type => apex_credential.c_type_oauth_client_cred ); apex_credential.set_persistent_credentials ( p_credential_static_id => l_static_id, p_client_id => l_client_id, p_client_secret => l_secret ); commit; end; / PL/SQL procedure successfully completed. SQL>

We can see the credential has been created.

 select workspace, name, static_id, credential_type from apex_workspace_credentials order by 1,2; WORKSPACE NAME STATIC_ID CREDENTIAL_TYPE -------------------- -------------------- -------------------- ------------------------------ DEV_WS My Web Credential my_web_credential OAuth2 Client Credentials flow SQL>

We can create a number of types of web credentials, but I only ever use the OAuth client credentials flow.

Display the Client ID

We can display the client ID from a priviledged user. The secret is present in the same table, but it is encrypted, so there is little point displaying it.

 conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba set linesize 100 column name format a20 column client_id format a30 column client_secret format a40 select name, client_id, client_secret from apex_240200.wwv_credentials order by 1; NAME CLIENT_ID CLIENT_SECRET -------------------- ------------------------------ ---------------------------------------- My Web Credential sdfg6757fdgddg8d5ffgdg.. ShRSHR/UO74I2ZQWaFlV7Dypvn//WDPM SQL>

Reset the Credentials

Companies often reset secrets for security reasons. We can reset the client ID and secret for an existing credential using the APEX_CREDENTIAL.SET_PERSISTENT_CREDENTIALS procedure show below.

 conn dev_ws/Dev1Workspace2!@//localhost:1521/freepdb1 declare l_workspace varchar2(30) := 'DEV_WS'; l_static_id varchar2(30) := 'my_web_credential'; l_client_id varchar2(30) := 'sdfg6757fdgddg8d5ffgdg..'; l_secret varchar2(30) := '4567575ijyugu565675675..'; begin apex_util.set_workspace(p_workspace => l_workspace); apex_credential.set_persistent_credentials ( p_credential_static_id => l_static_id, p_client_id => l_client_id, p_client_secret => l_secret ); commit; end; /

For more information see:

Hope this helps. Regards Tim...

Back to the Top.