OAUTH_ADMIN PL/SQL Package Reference

The OAUTH_ADMIN PL/SQL package contains subprograms (procedures and functions) for implementing OAuth authentication using Oracle REST Data Services for a privileged user.

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).

See Also: ORDS_SECURITY PL/SQL Package Reference

Before a database user can invoke the OAUTH_ADMIN package, they must be granted the ORDS_ADMINISTRATOR_ROLE database role.

The following example, grants the ORDS_ADMINISTRATOR_ROLE role to the ADMIN user:

GRANT ORDS_ADMINSTRATOR_ROLE TO ADMIN;

The OAUTH_ADMIN package is defined with the AUTHID CURRENT_USER right and each method requires a p_schema parameter where the target schema must be specified.

See also: Using the Oracle REST Data Services PL/SQL API

• OAUTH_ADMIN.CREATE_CLIENT

• OAUTH_ADMIN.CREATE_JWT_PROFILE

• OAUTH_ADMIN.DELETE_JWT_PROFILE

OAUTH_ADMIN.CREATE_CLIENT

Format

OAUTH_ADMIN.CREATE_CLIENT(
   p_schema                     IN VARCHAR2,
   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,
   p_support_uri                IN VARCHAR2 DEFAULT NULL,
   p_privilege_names            IN VARCHAR2 DEFAULT NULL,
   p_token_endpoint_auth_method IN VARCHAR2 DEFAULT NULL
);

Description

Creates an OAuth client registration in a target schema.

Parameters

p_schema

Name of the schema. This parameter is mandatory.

p_name

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

p_grant_type

Must be one of authorization_code, implicit, or client_credentials.

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. Can 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 is 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_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 the operation to take effect, use the COMMIT statement after invoking this procedure.

Example

The following example creates an OAuth client registration for authorization_code flow with PKCE:

BEGIN
  OAUTH_ADMIN.create_client(
    p_schema                     => 'HR',
    p_name                       => 'Authorization Code Client',
    p_grant_type                 => 'authorization_code',
    p_owner                      => NULL,
    p_description                => 'Auth code client for the HR web app',
    p_origins_allowed            => NULL,
    p_redirect_uri               => 'https://app.example.com/oauth/callback',
    p_support_email              => '[e-mail]',
    p_support_uri                => 'https://app.example.com/support',
    p_privilege_names            => NULL,
    p_token_endpoint_auth_method  => 'CLIENT_SECRET_BASIC'
  );
  COMMIT;
END;
/

OAUTH_ADMIN.CREATE_JWT_PROFILE

Format

OAUTH_ADMIN.CREATE_JWT_PROFILE (
       p_schema       IN VARCHAR2,
       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
   )

Description

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

Parameters

p_schema

Name of the schema. This parameter is mandatory.

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 the acceptable JWT access tokens. the url must start with “https://”.

p_desciption

A description of the JWT Profile. This can be nul.

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 that the ORDS global setting of security.jwt.allowed.age is taken. A value less than or equals to 0 means it is disabled.

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 HR schema and creates a new JWT Profile for the HR schema:

BEGIN
  OAUTH_ADMIN.DELETE_JWT_PROFILE(p_schema=>'HR');
  OAUTH_ADMIN.CREATE_JWT_PROFILE(
      p_schema =>'HR',
      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;
/

Any requests made to resources in the HR 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 of JWT and audience claims must match the p_issuer and p_audience values. The JWT must provide a scope that matches the ORDS Privilege protected by the resource.

OAUTH_ADMIN.DELETE_JWT_PROFILE

Format

OAUTH_ADMIN.DELETE_JWT_PROFILE ( p_schema       IN VARCHAR2) ;

Description

Deletes the JWT Profile for the specified schema, if it exists.

Parameters

p_schema

Name of the schema. This parameter is mandatory.

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 HR:

BEGIN
  OAUTH_ADMIN.DELETE_JWT_PROFILE(p_schema=>'HR');
  COMMIT;
END;
/

JWT bearer tokens are not accepted while authorizing requests to the protected resources in the HR schema.