DBA Hub

📋Steps in this guide1/4

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

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

oracle miscconfigurationintermediate
by OracleDba
15 views
1

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.

Code/Command (click line numbers to comment):

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

Create an APEX Web Credential

We check to see if there are any existing credentials. We can see there are not. We create a new OAuth credential, specifically one for a Client Credentials flow. We use to set our workspace. We use to create the credential. Finally we use to save the client ID and secret for the credential. We can see the credential has been created. We can create a number of types of web credentials, but I only ever use the OAuth client credentials flow. - APEX_CREDENTIAL.C_TYPE_BASIC - APEX_CREDENTIAL.C_TYPE_OAUTH_CLIENT_CRED - APEX_CREDENTIAL.C_TYPE_JWT - APEX_CREDENTIAL.C_TYPE_OCI - APEX_CREDENTIAL.C_TYPE_HTTP_HEADER - APEX_CREDENTIAL.C_TYPE_HTTP_QUERY_STRING

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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>

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>

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>
3

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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>
4

Reset the Credentials

Companies often reset secrets for security reasons. We can reset the client ID and secret for an existing credential using the procedure show below. For more information see: - APEX_CREDENTIAL - APEX_UTIL - Azure AD Authentication for Oracle APEX Applications : Social Sign In - APEX Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!