How to Manage OAuth Clients and Roles in ORDS

OAuth clients need care after they are registered: secrets should be rotated, access withdrawn when a system no longer needs it, and old clients removed. ORDS_SECURITY has procedures for each step, and the USER_ORDS_ views show the current state. This guide manages a client from registration to deletion and shows what each step does to its calls.

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 client uses the hr.read privilege and hr.reader role from How to Protect ORDS Endpoints with Privileges and Roles; How to Secure ORDS APIs with OAuth2 Client Credentials covers getting and using tokens.

Syntax

ords_security.rotate_client_secret(p_name => 'client', p_revoke_existing => true)  -- returns the new secret
ords_security.grant_client_role(p_client_name => 'client', p_role_name => 'role');
ords_security.revoke_client_role(p_client_name => 'client', p_role_name => 'role');
ords_security.delete_client(p_name => 'client');

Register a Client with a Shorter Token Life

p_token_duration sets how many seconds its tokens live, here 10 minutes instead of the default hour.

Example:

declare
  v_client ords_types.t_client_credentials;
begin
  v_client := ords_security.register_client(
                p_name            => 'audit_app',
                p_grant_type      => 'client_credentials',
                p_support_email   => 'audit@nimbus.example',
                p_privilege_names => 'hr.read',
                p_client_secret   => ords_types.oauth_client_secret(),
                p_token_duration  => 600);                 -- tokens live 10 minutes
  ords_security.grant_client_role(p_client_name => 'audit_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;
/

Output:

client_id:     oNNm0RHa84XbHQGD3EkIoA..
client_secret: RNDnWG... (shortened)

PL/SQL procedure successfully completed.

List Clients, Privileges, and Roles

Example:

select c.name, c.auth_flow, c.token_duration, c.client_secret_issued_on
from   user_ords_clients c
order  by c.name;

select client_name, name as privilege from user_ords_client_privileges order by 1;

select client_name, role_name from user_ords_client_roles order by 1;

Output:

NAME           AUTH_FLOW         TOKEN_DURATION CLIENT_SECRET_ISSUED_ON
______________ ______________ _________________ __________________________
audit_app      CLIENT_CRED                  600 06-OCT-2026 04:29:27
payroll_app    CLIENT_CRED                      06-OCT-2026 04:28:50

CLIENT_NAME    PRIVILEGE
______________ ____________
audit_app      hr.read
payroll_app    hr.read

CLIENT_NAME    ROLE_NAME
______________ ____________
audit_app      hr.reader
payroll_app    hr.reader

A token request for audit_app now reports expires_in 600.

Rotate the Secret

ROTATE_CLIENT_SECRET generates a new secret; with p_revoke_existing set to TRUE, the old one stops working at once.

Example:

declare
  v_secret varchar2(200);
begin
  v_secret := ords_security.rotate_client_secret(p_name            => 'audit_app',
                                                 p_revoke_existing => true);
  commit;
  dbms_output.put_line('new client_secret: ' || v_secret);
end;
/

Output:

new client_secret: 6jUu7E... (shortened)

PL/SQL procedure successfully completed.

A token request with the old secret fails, and the new secret works:

Output (old secret):

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

Output (new secret, token shortened):

{
    "access_token": "N1l3uT... (shortened)",
    "token_type": "bearer",
    "expires_in": 600
}
HTTP 200

To rotate without downtime, keep the old secret valid with p_revoke_existing set to FALSE until the application uses the new one; a client can hold two secrets at a time.

Revoke a Role

Example:

begin
  ords_security.revoke_client_role(p_client_name => 'audit_app', p_role_name => 'hr.reader');
  commit;
end;
/

In the test, a token issued before the revoke kept working until ORDS refreshed its cache, about half a minute, and then got 401 Unauthorized. New tokens for the client got 401 as well: the privilege alone is not enough without the role it requires.

Delete the Client

Example:

begin
  ords_security.delete_client(p_name => 'audit_app');
  commit;
end;
/
select count(*) as audit_app_clients from user_ords_clients where name = 'audit_app';

Output:

PL/SQL procedure successfully completed.

   AUDIT_APP_CLIENTS
____________________
                   0

Its credentials no longer get tokens:

Output:

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

Things to Know

• Secrets are shown once, when they are generated; store them in a secrets manager on the client side.
• Shorter token lifetimes limit the damage of a leaked token, at the cost of more token requests.
• REVOKE_CLIENT_SECRET removes a secret without generating a new one.

Related Guides

• How to Secure ORDS APIs with OAuth2 Client Credentials
• How to Protect ORDS Endpoints with Privileges and Roles

Conclusion

ORDS_SECURITY manages OAuth clients for their whole life: set token lifetimes, rotate secrets, grant and revoke roles, and delete clients. Check the USER_ORDS_CLIENT views to see who can call what, and expect changes to apply after ORDS refreshes its cache.