OAUTH PL/SQL Package Reference

The OAUTH PL/SQL package contains procedures for implementing OAuth authentication using Oracle REST Data Services.

Note: Deprecation of OAUTH and OAUTH_ADMIN PL/SQL packages: Starting from Oracle REST Data Services release (ORDS) 24.3, the OAUTH and OAUTH_ADMIN PL/SQL packages are deprecated in favor of the ORDS_SECURITY and ORDS_SECURITY_ADMIN PL/SQL packages. Oracle will maintain backward compatibility with the earlier packages through ORDS release 25.2, after which the packages will be desupported starting from ORDS release 25.3 (October 2025).

• OAUTH.CREATE_CLIENT

• OAUTH.DELETE_CLIENT

• OAUTH.GRANT_CLIENT_ROLE

• OAUTH.RENAME_CLIENT

• OAUTH.REVOKE_CLIENT_ROLE

• OAUTH.UPDATE_CLIENT

• OAUTH.ROTATE_CLIENT_SECRET

• OAUTH.UPDATE_CLIENT_SECRET

• OAUTH.IMPORT_CLIENT

• OAUTH.CREATE_JWT_PROFILE

• OAUTH.DELETE_JWT_PROFILE

Related Topics

• Using the Oracle REST Data Services PL/SQL API

See Also: ORDS_SECURITY PL/SQL Package Reference

OAUTH.CREATE_CLIENT

Format

OAUTH.CREATE_CLIENT(
   p_name            IN VARCHAR2,
   p_grant_type      IN VARCHAR2,
   p_owner           IN VARCHAR2 DEFAULT NULL,
   p_description     IN VARCHAR2 DEFAULT NULL,
   p_origins_allowed IN VARCHAR2 DEFAULT NULL,
   p_redirect_uri    IN VARCHAR2 DEFAULT NULL,
   p_support_email   IN VARCHAR2 DEFAULT NULL,
   p_support_uri     IN VARCHAR2  DEFAULT NULL,
   p_privilege_names IN VARCHAR2
   p_token_duration   IN NUMBER,
   p_refresh_duration IN NUMBER,
   p_code_duration    IN NUMBER,
   p_token_endpoint_auth_method IN VARCHAR2 DEFAULT NULL);

Description

Creates an OAuth client registration.

Parameters

p_name

Name for the client, displayed to the end user during the approval phase of three-legged OAuth. Must be unique.

p_grant_type

Must be one of authorization_code, implicit, or client_credentials.

p_owner

Name of the party that owns the client application.

p_description

Description of the purpose of the client, displayed to the end user during the approval phase of three-legged OAuth. May be null if p_grant_type is client_credentials; otherwise, must not be null.

p_origins_allowed

A comma-separated list of URL prefixes. If the list is empty, then any existing origins are removed.

p_redirect_uri

Client-controlled URI to which redirect containing an OAuth access token or error will be sent. May be null if p_grant_type is client_credentials; otherwise, must not be null.

p_support_email

The email where end users can contact the client for support.

p_support_uri

The URI where end users can contact the client for support. Example: http://www.myclientdomain.com/support/

p_privilege_names

List of comma-separated privileges that the client wants to access. This parameter is only applicable for implicit and authorization_code flows.

p_token_duration

Duration of the access token in seconds. NULL duration fallsback to the value in the ORDS instance. By default, it can be set through a property or set to 3600 seconds.

p_refresh_duration

Duration of refresh token in seconds. NULL duration fallsback to the value in the ORDS instance. By default, it can be set through a property or set to 86400 seconds.

p_code_duration

Duration of the code token in seconds applicable only when grant_type value is authorization code. If the value is set to NULL or the grant_type value is not authorization_code, then the lifetime is the one defined in the ORDS instance. By default, the value is 300.

p_token_endpoint_auth_method

Token endpoint authentication method. Supported values are CLIENT_SECRET_BASIC and NONE. Use CLIENT_SECRET_BASIC for confidential clients that authenticate at /oauth/token using Authorization: Basic. Use NONE only for public authorization_code clients that cannot safely store a client secret and therefore authenticate using client_id plus PKCE proof. For non-authorization_code flows, ORDS enforces CLIENT_SECRET_BASIC. When omitted, the default is CLIENT_SECRET_BASIC.

Usage Notes

To have the operation take effect, use the COMMIT statement after calling this procedure.

Examples

The following example creates an OAuth client registration.

BEGIN
  OAUTH.create_client(
    'CLIENT_TEST',
    'authorization_code',
    'test_user',
    'This is a test description.',
    '',
    'https://example.org/my_redirect/#/',
    '[e-mail]',
    'https://example.org/help/#/',
    'MyPrivilege',
    NULL,
    NULL,
    NULL
    );
    COMMIT;
END;
/

OAUTH.DELETE_CLIENT

Format

OAUTH.DELETE_CLIENT(
   p_name IN VARCHAR2);

Description

Deletes an OAuth client registration.

Parameters

p_name

Name of the client registration to be deleted.

Usage Notes

To have the operation take effect, use the COMMIT statement after calling this procedure.

Examples

The following example deletes an OAuth client registration.

BEGIN
  OAUTH.delete_client(
    'CLIENT_TEST'
    );
  COMMIT;
END;
/

OAUTH.GRANT_CLIENT_ROLE

Format

OAUTH.GRANT_CLIENT_ROLE(
   p_client_name IN VARCHAR2,
   p_role_name   IN VARCHAR2);

Description

Grant an OAuth client the specified role. For client_credentials clients, directly granted client roles are preserved during bearer-token authorization and can authorize access to protected resources even when p_privilege_names is null. This role-grant path is distinct from privilege-backed scope resolution.

Parameters

p_client_name

Name of the OAuth client.

p_role_name

Name of the role to be granted.

Usage Notes

To have the operation take effect, use the COMMIT statement after calling this procedure.

Use GRANT_CLIENT_ROLE when a client_credentials client must carry a role directly. Do not document p_privilege_names as a substitute for direct client role grants.

Examples

The following example creates a role and grants that role to an OAuth client.

BEGIN
  ORDS.create_role(p_role_name => 'CLIENT_TEST_ROLE');

  OAUTH.grant_client_role(
    'CLIENT_TEST',
    'CLIENT_TEST_ROLE'
    );
  COMMIT;
END;
/

OAUTH.RENAME_CLIENT

Format

OAUTH.RENAME_CLIENT(
   p_name     IN VARCHAR2,
   p_new_name IN VARCHAR2);

Description

Renames a client.

Parameters

p_name

Current name for the client.

p_new_name

New name for the client.

Usage Notes

The client name is displayed to the end user during the approval phase of three-legged OAuth.

To have the operation take effect, use the COMMIT statement after calling this procedure.

Examples

The following example renames a client.

BEGIN
  OAUTH.rename_client(
    'CLIENT_TEST',
    'CLIENT_TEST_RENAMED'
    );
  COMMIT;
END;
/

OAUTH.REVOKE_CLIENT_ROLE

Format

OAUTH.REVOKE_CLIENT_ROLE(
   p_client_name  IN VARCHAR2,
   p_role_name    IN VARCHAR2);

Description

Revokes the specified role from an OAuth client, preventing the client from accessing privileges requiring the role through two-legged OAuth.

Parameters

p_client_name

Name of the OAuth client.

p_role_name

Name of the role to be revoked

Usage Notes

To have the operation take effect, use the COMMIT statement after calling this procedure.

Examples

The following example revokes a specified role from an OAuth client.

BEGIN
  OAUTH.revoke_client_role(
    'CLIENT_TEST_RENAMED',
    'CLIENT_TEST_ROLE'
    );
  COMMIT;
END;
/

OAUTH.UPDATE_CLIENT

Format

OAUTH.UPDATE_CLIENT(
  p_name                       IN VARCHAR2,
  p_description                IN VARCHAR2 DEFAULT NULL,
  p_origins_allowed            IN VARCHAR2 DEFAULT NULL,
  p_redirect_uri               IN VARCHAR2 DEFAULT NULL,
  p_support_email              IN VARCHAR2 DEFAULT NULL,
  p_support_uri                IN VARCHAR2 DEFAULT NULL,
  p_privilege_names            IN t_ords_vchar_tab DEFAULT NULL,
  p_token_endpoint_auth_method IN VARCHAR2 DEFAULT NULL
);

Description

Updates the client information (except name). Any null values will not alter the existing client property.

Parameters

p_name

Name of the client that requires the owner, description, origins allowed, support e-mail, support URI, and/or privilege modification.

p_description

Description of the purpose of the client, displayed to the end user during the approval phase of three-legged OAuth.

p_origins_allowed

A comma-separated list of URL prefixes. If the list is empty, then any existing origins are removed.

p_redirect_uri

Client-controlled URI to which a redirect containing the OAuth access token/error will be sent. If this parameter is null, the existing p_redirect_uri value (if any) is not changed.

p_support_email

The email address where end users can contact the client for support.

p_support_uri

The URI where end users can contact the client for support. Example: http://www.myclientdomain.com/support/

p_privilege_names

List of names of the privileges that the client wishes to access. This parameter is only applicable for implicit and authorization_code flows.

p_token_duration

Duration of the access token in seconds. NULL duration fallsback to the value in the ORDS instance. By default, it can be set through a property or set to 3600 seconds.

p_refresh_duration

Duration of refresh token in seconds. NULL duration fallsback to the value in the ORDS instance. By default, it can be set through a property or set to 86400 seconds.

p_code_duration

Duration of the code token in seconds applicable only when grant_type is authorization code. If the value is set to NULL or the grant_type is not authorization_code, then the lifetime is the one defined in the ORDS instance. By default, the value is 300.

p_token_endpoint_auth_method

Token endpoint authentication method. Supported values are CLIENT_SECRET_BASIC and NONE. Use CLIENT_SECRET_BASIC for confidential clients that authenticate at/oauth/token using Authorization: Basic. Use NONE only for public authorization_code clients that cannot safely store a client secret and therefore authenticate using client_id plus PKCE proof. For non-authorization_code flows, ORDS enforces CLIENT_SECRET_BASIC. When omitted, the default is CLIENT_SECRET_BASIC.

Usage Notes

To have the operation take effect, use the COMMIT statement after calling this procedure.

If you want to rename the client, use the OAUTH.RENAME_CLIENT procedure.

Example to Update the Description of the Specified Client

The following example updates the description of the client with the name matching the value for p_name.

BEGIN
  ORDS_METADATA.OAUTH.update_client(
    p_name => 'CLIENT_TEST_RENAMED',
    p_description => 'The description was altered',
    p_origins_allowed => null,
    p_redirect_uri => null,
    p_support_email => null,
    p_support_uri => null,
    p_privilege_names => null,
    p_token_duration => null,
    p_refresh_duration => null,
    p_code_duration    => null);
  COMMIT;
END;
/

Example 7-1 Example to Add Multiple Privileges

The following example adds a second privilege:

declare
 my_privs t_ords_vchar_tab  := t_ords_vchar_tab ();
begin
 my_privs.EXTEND (3);
 my_privs(1):='tst.privilege1';
 my_privs(2):='tst.privilege2';

 oauth.update_client(
    p_name => 'Test_Client',
    p_description => 'Description altered.',
    p_origins_allowed => NULL,
    p_redirect_uri => '/abc/efg/',
    p_privilege_names => my_privs,
    p_token_duration => NULL,
    p_refresh_duration => NULL,
    p_code_duration    => NULL);
commit;
end;

Related Topics

• OAUTH.RENAME_CLIENT

OAUTH.ROTATE_CLIENT_SECRET

Format

OAUTH.ROTATE_CLIENT_SECRET(
      p_client_id       IN NUMBER,
      p_editing_user    IN VARCHAR2,
      p_revoke_sessions IN BOOLEAN DEFAULT TRUE);

Description

ROTATE_CLIENT_SECRET regenerates a new client secret and deletes all existing client sessions by default.

Parameters

p_client_id

The ID of the client modified.

p_editing_user

The user requesting this change.

p_revoke_sessions

Controls if the approval for the existing client sessions must be revoked. Default value is TRUE.

Example

The following example rotates a client secret:

BEGIN
  OAUTH.ROTATE_CLIENT_SECRET(
  p_client_id => 1234567890,
  p_editing_user => 'USERA',
  p_revoke_sessions => TRUE
  );
END;
/

OAUTH.UPDATE_CLIENT_SECRET

Format

OAUTH.UPDATE_CLIENT_SECRET(
      p_client_name  IN VARCHAR2,
      p_editing_user  IN VARCHAR2,
      p_client_secret  IN VARCHAR2);

Description

UPDATE_CLIENT_SECRET sets a new value for the secret of the client. By default, it deletes all the existing client sessions.

Parameters

p_client_name

The name of the client in the current schema.

p_editing_user

The user requesting this change.

p_client_secret

The value of the new secret for the client.

Usage Notes

For the operation to take effect, use the COMMIT statement after calling this procedure.

Example

The following example updates the secret of a particular client:

BEGIN
  OAUTH.UPDATE_CLIENT_SECRET(
        p_client_name   => 'CLIENT_TEST',
        p_editing_user   => 'USERA ',
        p_client_secret  => 'RaFhM690PA6cN1ffpkNx3Q..');
END;
/

OAUTH.IMPORT_CLIENT

Format

OAUTH.IMPORT_CLIENT(
  p_name                       IN VARCHAR2,
  p_client_id                  IN VARCHAR2,
  p_client_secret              IN VARCHAR2 DEFAULT NULL,
  p_grant_type                 IN VARCHAR2,
  p_owner                      IN VARCHAR2 DEFAULT NULL,
  p_description                IN VARCHAR2 DEFAULT NULL,
  p_origins_allowed            IN VARCHAR2 DEFAULT NULL,
  p_redirect_uri               IN VARCHAR2 DEFAULT NULL,
  p_support_email              IN VARCHAR2 DEFAULT NULL,
  p_support_uri                IN VARCHAR2 DEFAULT NULL,
  p_privilege_names            IN VARCHAR2,
  p_token_duration             IN NUMBER DEFAULT NULL,
  p_refresh_duration           IN NUMBER DEFAULT NULL,
  p_code_duration              IN NUMBER DEFAULT NULL,
  p_token_endpoint_auth_method IN VARCHAR2 DEFAULT NULL
);

Description

Imports an existing client into this schema, preserving the identifier and optionally a secret. If the secret is not provided, then a new one is generated.

Parameters

p_name

Name for the client displayed to the end user during the approval phase of three-legged OAuth. The name must must be unique.

p_client_id

A unique client identifier.

p_client_secret

Optional parameter. If not provided, then a random secret is generated.

p_grant_type

The value must be one of authorization_code, implicit, or client_credentials.

p_owner

Name of the party that owns the client application.

p_description

Description of the purpose of the client. Displayed to the end user during the approval phase of three-legged OAuth. Can be null if p_grant_type value is client_credentials. Otherwise, it must not be null.

p_origins_allowed

A comma-separated list of URL prefixes.

p_redirect_uri

Client-controlled URI with a redirect containing an OAuth access token or error is sent. Can be a null if the value of p_grant_type is client_credentials. Otherwise, it must not be null.

p_support_email

The email where the end users can contact the client for support.

p_support_uri

The URI where the end users can contact the client for support.

Example URI:http://www.myclientdomain.com/support/

p_privilege_names

List of comma-separated privileges that the client wants to access. This parameter is only applicable for implicit and authorization_code flows.

p_token_duration

Duration of the access token in seconds. NULL duration fallsback to the value in the ORDS instance. By default, it can be set through a property or set to 3600 seconds.

p_refresh_duration

Duration of refresh token in seconds. NULL duration fallsback to the value in the ORDS instance. By default, it can be set through a property or set to 86400 seconds.

p_code_duration

Duration of the code token in seconds is applicable only when grant_type value is authorization code. If the value is set to NULL or if the value of grant_type is not authorization_code, then the lifetime is the one defined in the ORDS instance. By default, the value is 300.

p_token_endpoint_auth_method

Token endpoint authentication method. Supported values are CLIENT_SECRET_BASIC and NONE. Use CLIENT_SECRET_BASIC for confidential clients that authenticate at/oauth/token using Authorization: Basic. Use NONE only for public authorization_code clients that cannot safely store a client secret and therefore authenticate using client_id plus PKCE proof. For non-authorization_code flows, ORDS enforces CLIENT_SECRET_BASIC. When omitted, the default is CLIENT_SECRET_BASIC.

Usage Notes

For this operation to take effect, use the COMMIT statement after calling this procedure.

Example

The following example, imports an OAuth client without custom durations or origins:

BEGIN
  OAUTH.IMPORT_CLIENT(
      p_name                  => 'CLIENT_TEST',
      p_client_id             => 'awVMtPlqullIqPXhAwh4zA..',
      p_grant_type            => 'authorization_code',
      p_owner                 => 'RESTEASY',
      p_description           => 'This is a test description.',
      p_origins_allowed       => NULL,
      p_redirect_uri          => 'https://example.org/my_redirect/',
      p_support_email         => '[e-mail]',
      p_support_uri           => 'https://example.org/help/',
      p_privilege_names       => 'MyPrivilege');

  COMMIT;
END;
/

OAUTH.CREATE_JWT_PROFILE

Format

OAUTH.CREATE_JWT_PROFILE (
       p_issuer       IN VARCHAR2,
       p_audience     IN VARCHAR2,
       p_jwk_url      IN VARCHAR2,
       p_description  IN VARCHAR2 DEFAULT NULL,
       p_allowed_skew IN NUMBER DEFAULT NULL,
       p_allowed_age  IN NUMBER DEFAULT NULL
       p_role_claim_name IN VARCHAR2 DEFAULT NULL
   )

Description

Creates a new JWT Profile for the schema if it does not already exist. If a JWT Profile already exists, then it must be deleted first.

Parameters

p_issuer

The issuer of acceptable JWT access tokens. This value must match the iss claim provided in the JWT.

p_audience

The audience of acceptable JWT access tokens. This value must match the aud claim provided in the JWT.

p_jwk_url

This is the url to the jwk(s) used to validate acceptable JWT access tokens. It must start with “https://”

p_desciption

A description of the JWT Profile. This value can be null.

p_allowed_skew

The number of seconds allowed to skew time claims provided in the JWT. This can help mediate issues with differences in the clock used by ORDS and the token issuer. The default value of null, specifies that the ORDS global setting security.jwt.allowed.skew is taken. A value less than or equal to 0 means, it is disabled. A max of 60 seconds can be specified.

p_allowed_age

The maximum allowed age of a JWT in seconds, regardless of expired claim. The age of the JWT is taken from the JWT issued at claim. The default value of null means the ORDS global setting of security.jwt.allowed.age is taken. A value less than or equals to 0 means, it is disabled.

p_role_claim_name

The JSON pointer that locates the claim in the JWT payload containing the role information. This value is required for role based JWT profiles. The JSON pointer must be formatted according to the RFC 6901 specification. For example: /claimName or /nestedObject/claimName.

The default value is null, indicating that the JWT profile is scope-based.

Usage Notes

For this operation to take effect, use the COMMIT statement after calling this procedure.

Example 1: Scope based JWT profile

The following example, deletes any existing JWT Profile for the schema and creates a new scope based JWT Profile for the schema. Any requests made to the resources in this schema can use a JWT bearer token for authorization. The JWT token must be signed and its signature must be verifiable using a public key provided by p_jwk_url. The JWTs issuer and audience claims must also match the p_issuer and p_audience values. The JWT must provide a scope that matches the ORDS Privilege protected by the resource.

BEGIN
  OAUTH.DELETE_JWT_PROFILE();
  OAUTH.CREATE_JWT_PROFILE(
      p_issuer => 'https://identity.oraclecloud.com/',
      p_audience => 'ords/myapplication/api' ,
      p_jwk_url =>'https://idcs-10a10a10a10a10a10a10a10a.identity.oraclecloud.com/admin/v1/SigningCert/jwk'
  );
  COMMIT;
END;
/

Example 2: Role based JWT profile

The following example deletes any existing JWT profile for the schema and creates a new role based JWT profile for the schema. Any requests made to the resources in this schema can use a JWT bearer token for authorization. The JWT token must be signed and its signature must be verifiable using a public key provided by p_jwk_url. The issuer and audience claims of JWT must also match the p_issuer and p_audience values. The JWT must provide a claim located at the JSON pointer specified by {p_role_claim_name}, containing roles that match the ORDS roles.

BEGIN
  OAUTH.DELETE_JWT_PROFILE();
  OAUTH.CREATE_JWT_PROFILE(
      p_issuer => 'https://identity.oraclecloud.com/',
      p_audience => 'ords/myapplication/api' ,
      p_jwk_url =>'https://idcs-10a10a10a10a10a10a10a10a.identity.oraclecloud.com/admin/v1/SigningCert/jwk',
      p_role_claim_name => '/roles'
  );
  COMMIT;
END;
/

OAUTH.DELETE_JWT_PROFILE

Format

OAUTH.DELETE_JWT_PROFILE ()

Description

Deletes the JWT Profile for the schema if one exists.

Usage Notes

For this operation to take effect, use the COMMIT statement after calling this procedure.

Example

The following example, deletes any existing JWT Profile for the schema:

BEGIN
  OAUTH.DELETE_JWT_PROFILE();
  COMMIT;
END;
/

JWT bearer tokens are not be accepted when authorizing requests to the protected resources.