How to Secure ORDS APIs with OAuth2 Client Credentials

When another system calls your ORDS API, it should prove who it is without a database password. OAuth2 client credentials do that: you register a client in ORDS, the client trades its ID and secret for a short-lived access token, and sends the token with each request. This guide registers a payroll client for a protected employee API and calls it.

Before You Start

You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.

ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.

The employee API is the hr module, protected by the privilege hr.read and the role hr.reader in How to Protect ORDS Endpoints with Privileges and Roles.

Syntax

v_client := ords_security.register_client(
              p_name            => 'client_name',
              p_grant_type      => 'client_credentials',
              p_support_email   => '...',
              p_privilege_names => 'privilege',
              p_client_secret   => ords_types.oauth_client_secret());   -- generate a secret
ords_security.grant_client_role(p_client_name => 'client_name', p_role_name => 'role');

POST https://host:port/ords/schema_alias/oauth/token        -- client ID and secret as basic auth
     grant_type=client_credentials

Register the Client

REGISTER_CLIENT returns the generated client ID and secret. The secret is shown only here, so the client application must store it safely; it is shortened in the output below.

Example:

declare
  v_client ords_types.t_client_credentials;
begin
  v_client := ords_security.register_client(
                p_name            => 'payroll_app',
                p_grant_type      => 'client_credentials',
                p_support_email   => 'it@nimbus.example',
                p_description     => 'Monthly payroll export',
                p_privilege_names => 'hr.read',
                p_client_secret   => ords_types.oauth_client_secret());   -- generate one
  ords_security.grant_client_role(p_client_name => 'payroll_app',
                                  p_role_name   => 'hr.reader');
  commit;
  dbms_output.put_line('client_id:     ' || v_client.client_key.client_id);
  dbms_output.put_line('client_secret: ' || v_client.client_secret.secret);
end;
/
select name, auth_flow, token_duration from user_ords_clients where name = 'payroll_app';

select client_name, role_name from user_ords_client_roles where client_name = 'payroll_app';

Output:

client_id:     OqTD8BWKBjyYaw04EkW87Q..
client_secret: r4Wmmq... (shortened)

PL/SQL procedure successfully completed.

NAME           AUTH_FLOW         TOKEN_DURATION
______________ ______________ _________________
payroll_app    CLIENT_CRED

CLIENT_NAME    ROLE_NAME
______________ ____________
payroll_app    hr.reader

Without p_client_secret, the first test run returned no secret at all; ORDS_TYPES.OAUTH_CLIENT_SECRET() asks ORDS to generate one.

Get an Access Token

Example:

curl -k -u "CLIENT_ID:CLIENT_SECRET" \
  -d grant_type=client_credentials \
  https://localhost:8443/ords/nimbus/oauth/token

Output (token shortened):

{
    "access_token": "1twpRb... (shortened)",
    "token_type": "bearer",
    "expires_in": 3600
}

The token is valid for 3,600 seconds, one hour, by default.

Call the API with the Token

Example:

curl -k -H "Authorization: Bearer ACCESS_TOKEN" \
  "https://localhost:8443/ords/nimbus/hr/employees?limit=2"

Output (start of the response):

{
    "items": [
        {
            "employee_id": 100,
            "first_name": "Layla",
            "last_name": "Haddad",
            "salary": 48000
        },
        {
            "employee_id": 101,
            "first_name": "Omar",
            "last_name": "Al Mansoori",
            "salary": 36000
        }
    ...

The same request without a token, or with a made-up one, gets 401 Unauthorized:

Output (HTTP 401 Unauthorized):

{
    "code": "Unauthorized",
    "message": "Unauthorized",
    "type": "tag:oracle.com,2020:error/Unauthorized",
    "instance": "tag:oracle.com,2020:ecid/0Y2aDFtr0312giMgwVUiNw"
}

A wrong secret is refused at the token endpoint:

Output:

{"error":"invalid_client","error_description":"The related client is invalid"}
HTTP 401

Things to Know

• Request a new token when the old one expires; clients should not store tokens for longer than their lifetime.
• Send tokens only over HTTPS: anyone holding a token can call the API until it expires.
• USER_ORDS_CLIENTS, USER_ORDS_CLIENT_PRIVILEGES, and USER_ORDS_CLIENT_ROLES show the registered clients and what they may use.

Related Guides

• How to Protect ORDS Endpoints with Privileges and Roles
• How to Store Credentials with DBMS_CREDENTIAL

Conclusion

OAuth2 client credentials let systems call protected ORDS APIs without database passwords. Register the client with ORDS_SECURITY, grant it the role the privilege requires, exchange its ID and secret for a token at /oauth/token, and send the token as a Bearer header.