ORDS_SECURITY PL/SQL Package Reference

This package provides an API to manage the ORDS schema security for the current user. This package is purely an interface and should not contain any application logic.

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

• CREATE_JWT_PROFILE

• REGISTER_CLIENT

• REGISTER_CLIENT

• IMPORT_CLIENT

• IMPORT_CLIENT

• REGISTER_CLIENT_SECRET

• GRANT_CLIENT_ROLE

• GRANT_CLIENT_ROLE

• UPDATE_CLIENT

• UPDATE_CLIENT

• UPDATE_CLIENT

• UPDATE_CLIENT

• UPDATE_CLIENT_LOGO

• UPDATE_CLIENT_PRIVILEGES

• UPDATE_CLIENT_PRIVILEGES

• UPDATE_CLIENT_TOKEN_DURATION

• UPDATE_CLIENT_TOKEN_DURATION

• RENAME_CLIENT

• RENAME_CLIENT

• ROTATE_CLIENT_SECRET

• ROTATE_CLIENT_SECRET

• DELETE_JWT_PROFILE

• DELETE_CLIENT

• DELETE_CLIENT

• REVOKE_CLIENT_ROLE

• REVOKE_CLIENT_ROLE

• REVOKE_CLIENT_SECRETS

• REVOKE_CLIENT_SECRET

CREATE_JWT_PROFILE

Format

PROCEDURE create_jwt_profile(
      p_issuer       IN oauth_jwt_profile.issuer%type,
      p_audience     IN oauth_jwt_profile.audience%type,
      p_jwk_url      IN oauth_jwt_profile.jwk_url%type,
      p_description  IN oauth_jwt_profile.description%type  DEFAULT NULL,
      p_allowed_skew IN oauth_jwt_profile.allowed_skew%type DEFAULT NULL,
      p_allowed_age  IN oauth_jwt_profile.allowed_age%type  DEFAULT NULL
      p_role_claim_name IN VARCHAR2 DEFAULT NULL
  );

Description

Creates a new JWT profile. JWT access tokens that can be validated using this profile, authorize the JWT subject as having the provided scope (ORDS privileges) for this schema.

Parameter	Description
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	The url to the jwk(s) used to validate acceptable JWT access tokens. It must start with "https://"
p_description	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 disabled.
p_role_claim_name	Specifies the JSON pointer that is used to identify the claim in the JWT payload that holds the role information.This field is mandatory for Role-Based JWT profiles and must exactly match the JSON pointer referencing the claim in the JWT. The pointer must follow the RFC 6901 specification. For example: /claimName or /nestedObject/claimName. If the value is set to default value, null, then the JWT Profile is considered Scope-Based.

Usage Notes

If a JWT profile already exists, then it must be deleted first. For this operation to take effect, use the example COMMIT statement after calling this procedure.

• Examples

Examples

Example 9-1 Scope Based JWT PROFILE

The following example, deletes any existing JWT Profile for the HR schema and creates a new Scope based JWT Profile for the HR schema.

BEGIN
  ORDS_SECURITY.DELETE_JWT_PROFILE;
  ORDS_SECURITY.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;
/

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:

Example 9-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 HR schema:

BEGIN
ORDS_SECURITY.DELETE_JWT_PROFILE(p_schema=>'HR');
ORDS_SECURITY.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 the resources in this schema can use a JWT bearer token for authorization. The JWT must be signed, and its signature must be validated with a public key provided by p_jwk_url. The JWT’s issuer and audience claims must 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.

REGISTER_CLIENT

Format

PROCEDURE register_client(
      p_name             IN VARCHAR2,
      p_grant_type       IN VARCHAR2,
      p_support_email    IN VARCHAR2,
      p_description      IN VARCHAR2 DEFAULT NULL,
      p_privilege_names  IN VARCHAR2 DEFAULT NULL,
      p_origins_allowed  IN VARCHAR2 DEFAULT NULL,
      p_redirect_uri     IN VARCHAR2 DEFAULT NULL,
      p_support_uri      IN VARCHAR2 DEFAULT NULL,
      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

Registers an OAuth client. By default, no client_secret is registered. To register a client secret either set any field in parameter p_client_secret (apart from issued_on)) or call REGISTER_CLIENT_SECRET or ROTATE_CLIENT_SECRET following client registration.

Table 1 Parameters

Parameter	Description
p_name	The name for the client, displayed to the end user during the approval phase of three-legged OAuth. This value must be unique and must not be null.
p_grant_type	Must be one of authorization_code, implicit or client_credentials. This value must not be null.
p_support_email	The URI where end users can contact the client for support. For example: www.myclientdomain.com/support/. This value must not be null.
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_client_secret	The client secret defaults. Any of the fields can be set except issued_on. By default, no secret is registered.
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_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. Can be null if it is p_support_email, client_credentials; otherwise, must not be null.
p_support_uri	The URI where the end users can contact the client for support. For example: www.myclientdomain.com/support/
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 authorization code is . * If the value is set to NULL or the grant_type value is not authorization_code 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 method.

Returns

The client key (id|name|client_id) and client_secret, if any, of the registered client.

REGISTER_CLIENT

Format

FUNCTION register_client(
      p_schema           IN VARCHAR2,
      p_name             IN VARCHAR2,
      p_grant_type       IN VARCHAR2,
      p_support_email    IN VARCHAR2,
      p_description      IN VARCHAR2 DEFAULT NULL,
      p_client_secret    IN ords_types.t_client_secret DEFAULT ords_constants.oauth_client_secret_skip,
      p_privilege_names  IN VARCHAR2 DEFAULT NULL,
      p_origins_allowed  IN VARCHAR2 DEFAULT NULL,
      p_redirect_uri     IN VARCHAR2 DEFAULT NULL,
      p_support_uri      IN VARCHAR2 DEFAULT NULL,
      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
  ) RETURN ords_types.t_client_credentials;

Description

Registers an OAuth client. By default, no client_secret is registered. To register a client secret either set any field in parameter p_client_secret (apart from issued_on)) or call REGISTER_CLIENT_SECRET or ROTATE_CLIENT_SECRET followed by client registration.

Table 2 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_name	The name for the client, displayed to the end user during the approval phase of three-legged OAuth. This value must be unique and not be null.
p_grant_type	Must be one of ‘authorization_code’, ‘implicit’ or ‘client_credentials’. This value must not be null.
p_support_email	The URI where end users can contact the client for support. Example: www.myclientdomain.com/support/ This value must not be null.
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_client_secret	The client secret defaults. Any of fields can be set except issued_on. By default,no secret is registered.
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_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 is p_support_email client_credentials; otherwise, must not be null.
p_support_uri	The URI where end users can contact the client for support. Example: www.myclientdomain.com/support/
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_code_duration	Duration of the code token in seconds applicable only when the value is authorization code. If the value is set to NULL or the grant_type value is not authorization_code 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 method.

Returns

The client key (id|name|client_id) and client_secret, if any, of the registered client.

• Examples

Examples

Example 9-3

The following example registers an OAuth client:

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred := ORDS_SECURITY.REGISTER_CLIENT(
      p_name            => 'CLIENT_TEST',
      p_grant_type      => 'authorization_code',
      p_description     => 'This is a test description.',
      p_redirect_uri    => 'https://example.org/my_redirect/',
      p_support_email   => '[e-mail]',
      p_support_uri     => 'https://example.org/help/',
      p_privilege_names => 'oracle.dbtools.sqldev');
  COMMIT;
  sys.dbms_output.put_line('CLIENT_ID:' || l_client_cred.client_key.client_id);
END;
/

Example 9-4

The following example registers an OAuth client with a client secret:

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred := ORDS_SECURITY.REGISTER_CLIENT(
      p_name            => 'CLIENT_TEST',
      p_grant_type      => 'authorization_code',
      p_description     => 'This is a test description.',
      p_client_secret   => ords_types.oauth_client_secret(p_secret=>'RaFhM690PA6cN1ffpkNx3Q..'),
      p_redirect_uri    => 'https://example.org/my_redirect/',
      p_support_email   => '[e-mail]',
      p_support_uri     => 'https://example.org/help/',
      p_privilege_names => 'oracle.dbtools.sqldev');
  COMMIT;
  sys.dbms_output.put_line('CLIENT_ID:'     || l_client_cred.client_key.client_id);
  sys.dbms_output.put_line('CLIENT_SECRET:' || l_client_cred.client_secret.secret);
END;
/

Example 9-5

The following example registers an OAuth client:

DECLARE
  l_client_id user_ords_clients.client_id%TYPE;
BEGIN
  ORDS_SECURITY.REGISTER_CLIENT(
      p_name            => 'CLIENT_TEST',
      p_grant_type      => 'authorization_code',
      p_description     => 'This is a test description.',
      p_redirect_uri    => 'https://example.org/my_redirect/',
      p_support_email   => '[e-mail]',
      p_support_uri     => 'https://example.org/help/',
      p_privilege_names => 'oracle.dbtools.sqldev');
  COMMIT;
  SELECT client_id INTO l_client_id FROM user_ords_clients WHERE name = 'CLIENT_TEST';
END;
/

IMPORT_CLIENT

Format

FUNCTION import_client(
      p_name             IN VARCHAR2,
      p_grant_type       IN VARCHAR2,
      p_support_email    IN VARCHAR2,
      p_description      IN VARCHAR2 DEFAULT NULL,
      p_client_id        IN VARCHAR2 DEFAULT NULL,
      p_privilege_names  IN VARCHAR2 DEFAULT NULL,
      p_origins_allowed  IN VARCHAR2 DEFAULT NULL,
      p_redirect_uri     IN VARCHAR2 DEFAULT NULL,
      p_support_uri      IN VARCHAR2 DEFAULT NULL,
      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
  ) RETURN ords_types.t_client_key;

Description

To register a client secret call REGISTER_CLIENT_SECRET or ROTATE_CLIENT_SECRET following client import. By default, no client_secret is registered.

Table 3 Parameters

Parameters	Description
p_name	The name for the client displayed to the end user during the approval phase of three-legged OAuth. This value must be unique and must not be null.
p_grant_type	Value must be one of authorization_code, implicit or client_credentials. This value must not be null.
p_support_email	The URI to contact the client for support. For example: www.myclientdomain.com/support/ . This value must not be null.
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_owner	No longer in use (deprecated).
p_client_id	The original generated client identifier @see ORDS_EXPORT . When the value is null, a new client identifier is generated.
p_privilege_names	List of comma-separated privileges that the client wants to access. The privilege(s) must already exist. See ORDS.DEFINE_PRIVILEGE. <p>This parameter is only applicable for implicit and authorization_code flows. </p>
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 it is p_support_email client_credentials; otherwise, must not be null.
p_support_uri	The URI where to contact the client for support. For example: www.myclientdomain.com/support/
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 authorization code is authorization_code. If the value is set to NULL or the grant_type value is not authorization_code then 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 this operation to take effect, use the COMMIT statement after calling this method.

Returns

The client key (id|name|client_id) of the registered client.

IMPORT_CLIENT

Format

PROCEDURE import_client(
      p_name             IN VARCHAR2,
      p_grant_type       IN VARCHAR2,
      p_support_email    IN VARCHAR2,
      p_description      IN VARCHAR2 DEFAULT NULL,
      p_owner            IN VARCHAR2 DEFAULT NULL,
      p_client_id        IN VARCHAR2 DEFAULT NULL,
      p_privilege_names  IN VARCHAR2 DEFAULT NULL,
      p_origins_allowed  IN VARCHAR2 DEFAULT NULL,
      p_redirect_uri     IN VARCHAR2 DEFAULT NULL,
      p_support_uri      IN VARCHAR2 DEFAULT NULL,
      p_token_duration   IN NUMBER   DEFAULT NULL,
      p_refresh_duration IN NUMBER   DEFAULT NULL,
      p_code_duration    IN NUMBER   DEFAULT NULL
  );

Description

Imports an OAuth client. By default, no client_secret is registered. To register a client secret, call REGISTER_CLIENT_SECRET or ROTATE_CLIENT_SECRET followed by client import.

Table 4 Parameters

Parameter	Description
p_name	The name for the client, displayed to the end user during the approval phase of three-legged OAuth.
p_grant_type	Must be one of authorization_code, implicit or client_credentials. This value must not be null.
p_support_email	The URI where the end users can contact the client for support. For example: www.myclientdomain.com/support/. This value must not be null.
p_description	Description of the purpose of the client, displayed to the end user during the approval phase of the three-legged OAuth. Can be null if p_grant_type is client_credentials; otherwise, must not be null.
p_owner	No longer in use (deprecated).
p_client_id	The original generated client identifier. See ORDS_EXPORT. When null, a new client identifier is generated.
p_privilege_names	List of comma-separated privileges that the client wants to access. The privilege(s) must already exist. See ORDS.DEFINE_PRIVILEGE.<p>This parameter is only applicable for implicit and authorization_code flows. </p>
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. Can be null if it is p_support_email client_credentials; otherwise, must not be null.
p_support_uri	The URI where the end users can contact the client for support. For example: www.myclientdomain.com/support/
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 authorization code. If the value is set to NULL or the grant_type value is not authorization_code, then the value is 300.

Usage Notes

Use the COMMIT statement after calling this procedure for the operation to take effect.

• Examples

Examples

Example 9-6

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

BEGIN
  ORDS_SECURITY.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 => 'oracle.dbtools.sqldev');
  COMMIT;
END;
/

REGISTER_CLIENT_SECRET

Format

FUNCTION register_client_secret(
      p_client_key      IN ords_types.t_client_key,
      p_client_secret   IN ords_types.t_client_secret,
      p_revoke_existing IN BOOLEAN DEFAULT FALSE,
      p_revoke_sessions IN BOOLEAN DEFAULT FALSE
  ) RETURN ords_types.t_client_credentials;

Description

Registers an OAuth client secret and revokes exisitng secrets and sessions when required. By default, a generated client secret is registered (See ROTATE_CLIENT_SECRET) and the newest client secret and existing client seesions remain in effect. Note: A custom client secret can be registered when p_client_secret.secret is set. The registered client secret value is not persisted using this method unless the p_client_secret stored parameter is set. When the client secret is no longer persisted, the caller is required to save the returned value for future use. The view USER_ORDS_CLIENTS cannot return the secrets that are not stored.

Table 5 Parameters

Parameter	Description
p_client_key	The key (id|name|client_id) of the registered client. A minimum of one key must be supplied.
p_client_secret	The client secret defaults. Any fields can be set except issued_on. When set to null, the client secret is rotated with a generated value.
p_revoke_existing	Revokes any exisiting secrets. By default the most-current client secret is preserved.
p_revoke_sessions	Revokes all existing client sessions when set to TRUE.

Usage Notes

Changes are immediately commited. If two client secrets are already registered, then the oldest is overwritten unless a specific slot is set in the p_client_secret.slot parameter. Any existing client secrets remain in effect unless revoked using the p_revoke_existing parameter.

Returns

The client key (including client_id) and registered client_secret.

• Examples

Examples

Example 9-7

The following example registers a secret of an OAuth client. The existing client secret will continue to work until revoked:

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred.client_key.name      := 'CLIENT_TEST';
  l_client_cred.client_secret.secret := 'RaFhM690PA6cN1ffpkNx3Q..';

  l_client_cred := ORDS_SECURITY.REGISTER_CLIENT_SECRET(
      p_client_key    => l_client_cred.client_key,
      p_client_secret => l_client_cred.client_secret
  );
  -- No Commit Required
  sys.dbms_output.put_line('SLOT:'      || l_client_cred.client_secret.slot);
  sys.dbms_output.put_line('ISSUED ON:' || l_client_cred.client_secret.issued_on);
END;
/

Example 9-8

The following example registers a secret of an OAuth client. The existing client secret will continue to work until revoked:

BEGIN
  ORDS_SECURITY.REGISTER_CLIENT_SECRET(
      p_name          => 'CLIENT_TEST',
      p_client_secret => 'RaFhM690PA6cN1ffpkNx3Q..'
  );
  -- No Commit Required
END;
/

GRANT_CLIENT_ROLE

Format

PROCEDURE grant_client_role(
      p_client_key IN ords_types.t_client_key,
      p_role_name  IN VARCHAR2

Description

Grants a role to an OAuth client.

Table 6 Parameters

Parameter	Description
p_client_key	The key (id|name|client_id) of the client grantee. A minimum of one key must be supplied.
p_role_name	Name of the role to be granted that either belongs to the schema or is a built in role. This value must not be null.

Note: Direct client roles are preserved for client_credentials bearer authorization.

Usage Notes

Use the example COMMIT statement after calling this method for this operation to take effect.

Document direct client role grants as a valid positive authorization mechanism for client_credentials tokens. Avoid implying that p_privilege_names alone determines whether a client_credentials bearer token can access a protected resource.

Note: Granting a role directly to an OAuth client is part of the positive authorization model forclient_credentials bearer tokens. A client_credentials token can authorize access through directly granted client roles even when p_privilege_names is null. p_privilege_names and GRANT_CLIENT_ROLE are not interchangeable. p_privilege_names controls privilege-backed scope resolution, whereas GRANT_CLIENT_ROLE preserves direct client roles for bearer token authorization.

GRANT_CLIENT_ROLE

Format

PROCEDURE grant_client_role(
      p_client_name IN VARCHAR2,
      p_role_name   IN VARCHAR2
  );

Description

Grants a role to an OAuth client.

Table 7 Parameters

Parameter	Description
p_client_name	The name of the client grantee.
p_role_name	Name of the role to be granted that either belongs to the schema or is a built in role. This value must not be null.

Usage Notes

Use the example COMMIT statement after calling this procedure for this operation to take effect.

Document direct client role grants as a valid positive authorization mechanism for client_credentials tokens. Avoid implying that p_privilege_names alone determines whether a client_credentials bearer token can access a protected resource.

Note: Granting a role directly to an OAuth client is part of the positive authorization model forclient_credentials bearer tokens. A client_credentials token can authorize access through directly granted client roles even when p_privilege_names is null. p_privilege_names and GRANT_CLIENT_ROLE are not interchangeable. p_privilege_names controls privilege-backed scope resolution, whereas GRANT_CLIENT_ROLE preserves direct client roles for bearer token authorization.

• Examples

Examples

The following examples creates a role and grants that role to an OAuth client:

Example 9-9

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

  ORDS_SECURITY.GRANT_CLIENT_ROLE(
      p_client_key => ords_types.oauth_client_key(p_name=>'CLIENT_TEST'),
      p_role_name  => 'CLIENT_TEST_ROLE'
  );
  COMMIT;
END;
/

Example 9-10

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

  ORDS_SECURITY.GRANT_CLIENT_ROLE(
      p_client_name => 'CLIENT_TEST',
      p_role_name   => 'CLIENT_TEST_ROLE'
  );
  COMMIT;
END;
/

UPDATE_CLIENT

Format

PROCEDURE update_client(
     p_name            IN VARCHAR2,
     p_new_name        IN VARCHAR2 DEFAULT NULL,
     p_description     IN VARCHAR2,
     p_origins_allowed IN VARCHAR2,
     p_redirect_uri    IN VARCHAR2,
     p_support_email   IN VARCHAR2,
     p_support_uri     IN VARCHAR2,
     p_token_endpoint_auth_method IN VARCHAR2 DEFAULT NULL
 );

Description

Updates an OAuth client registration. Any new client name is displayed to the end user during the approval phase of three-legged OAuth. The client must be deleted and re-registered in order to change the grant type.

Table 8 Parameters

Parameter	Description
p_name	The name of the client to be modified. This value must not be null.
p_new_name	The name for the client, displayed to the end user during the approval phase of three-legged OAuth. When null, the old name is preserved.
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, the value 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. Can be null if is p_support_email client_credentials; otherwise, must not be null.
p_support_email	The URI where the end users can contact the client for support. For example: www.myclientdomain.com/support/. This value must not be null.
p_support_uri	The URI where the end users can contact the client for support. For example: www.myclientdomain.com/support/
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

All specified client attributes are updated. All other attributes remain unchanged. The client name may also be updated if a non-null value is provided for p_new_name. Use the COMMIT statement after calling this procedure for the operation to take effect.

UPDATE_CLIENT

Format

FUNCTION update_client(
      p_schema          IN VARCHAR2,
      p_client_key      IN ords_types.t_client_key,
      p_new_name        IN VARCHAR2 DEFAULT NULL,
      p_description     IN VARCHAR2,
      p_origins_allowed IN VARCHAR2,
      p_redirect_uri    IN VARCHAR2,
      p_support_email   IN VARCHAR2,
      p_support_uri     IN VARCHAR2,
      p_token_endpoint_auth_method IN VARCHAR2 DEFAULT NULL,
  ) RETURN ords_types.t_client_key;

Description

Updates an OAuth client registration. Any new client name is displayed to the end user during the approval phase of three-legged OAuth. The client must be deleted and re-registered in order to change the grant type.

Table 9 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_client_key	The key (id|name|client_id) of the client to be modified. A minimum of one key must be supplied.
p_new_name	The name for the client, displayed to the end user during the approval phase of three-legged OAuth. When null, the old name is preserved.
p_description	Human readable 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 == 'client_credentials', non null otherwise.
p_origins_allowed	Allowed origins
p_redirect_uri	Client controlled URI to which redirect containing OAuth access token/error will be sent. May be null if p_grant_type == 'client_credentials', non null otherwise.
p_support_email	Support e-mail for client’s users
p_support_uri	Support URI for client’s users
p_token_endpoint_auth_method IN VARCHAR2 DEFAULT NULL	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

All specified client attributes are updated. All other attributes remain unchanged. The client name may also be updated if a non-null value is provided for p_new_name. Use the COMMIT statement after calling this function foe the operation to take effect.

UPDATE_CLIENT

Format

FUNCTION update_client(
      p_client_key       IN ords_types.t_client_key,
      p_new_name         IN VARCHAR2 DEFAULT NULL,
      p_description      IN VARCHAR2,
      p_privilege_names  IN VARCHAR2,
      p_origins_allowed  IN VARCHAR2,
      p_redirect_uri     IN VARCHAR2,
      p_support_email    IN VARCHAR2,
      p_support_uri      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
  ) RETURN ords_types.t_client_key;

Description

Updates an OAuth client registration. Any new client name is displayed to the end user during the approval phase of three-legged OAuth. The client must be deleted and re-registered in order to change the grant type.

Table 10 Parameters

Parameter	Description
p_name	The name of the client to be modified. This value must not be null.
p_new_name	The name for the client displayed to the end user during the approval phase of three-legged OAuth. When the value is null, the old name is preserved.
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. can be null if it is p_support_email client_credentials; otherwise, must not be null.
p_support_email	The URI where the end users can contact the client for support. For example: www.myclientdomain.com/support/ . This value must not be null.
p_support_uri	The URI where end users can contact the client for support. For example: www.myclientdomain.com/support/
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

All specified client attributes are updated. All other attributes remain unchanged. The client name may also be updated if a non-null value is provided for p_new_name. Use the COMMIT statement after calling this function for the operation totake effect.

UPDATE_CLIENT

Format

PROCEDURE update_client(
      p_name             IN VARCHAR2,
      p_new_name         IN VARCHAR2 DEFAULT NULL,
      p_description      IN VARCHAR2,
      p_privilege_names  IN VARCHAR2,
      p_origins_allowed  IN VARCHAR2,
      p_redirect_uri     IN VARCHAR2,
      p_support_email    IN VARCHAR2,
      p_support_uri      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

Updates an OAuth client registration. Any new client name is displayed to the end user during the approval phase of three-legged OAuth. The client must be deleted and re-registered in order to change the grant type.

Table 11 Parameters

Parameter	Description
p_name	The name of the client to be modified. This value must not be null.
p_new_name	The new name for the client. When the value is null, the old name is preserved.
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_privilege_names	List of comma-separated privileges that the client wants to access. The privilege(s) must already exist. See ORDS.DEFINE_PRIVILEGE.<p>This parameter is only applicable for implicit and authorization_code flows. </p>
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. Can be null if it is p_support_email client_credentials; otherwise, must not be null.
p_support_email	The URI where the end users can contact the client for support. For example: www.myclientdomain.com/support/ This value must not be null.
p_support_uri	The URI where end users can contact the client for support. For example: www.myclientdomain.com/support/
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 authorization code. If the value is set to NULL or the grant_type value is not authorization_code, then 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

All client attributes (excluding the client name and including the client privileges)are updated as if registered from new. The client name may also be updated if a non-null value is provided for p_new_name. Use the COMMIT statement after calling this procedure for the operation to take effect.

• Examples

Examples

Example 9-11

The following example renames an OAuth client and updates all client fields except for the privileges and the durations which will remain unchanged:

DECLARE
  l_client_key ords_types.t_client_key;
BEGIN
  l_client_key := ORDS_SECURITY.UPDATE_CLIENT(
      p_client_key      => ords_types.oauth_client_key(p_name=>'CLIENT_TEST'),
      p_new_name        => 'CLIENT_TEST_RENAMED',
      p_description     => 'This is a test description.',
      p_origins_allowed => '*',
      p_redirect_uri    => 'https://example.org/my_redirect/',
      p_support_email   => '[e-mail]',
      p_support_uri     => 'https://example.org/help/'
  );
  COMMIT;
  sys.dbms_output.put_line('ID:' || l_client_key.id);
END;
/

Example 9-12

The following example renames an OAuth client and updates all client fields except for the privileges and the durations which will remain unchanged:

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT(
      p_name            => 'CLIENT_TEST',
      p_new_name        => 'CLIENT_TEST_RENAMED',
      p_description     => 'This is a test description.',
      p_origins_allowed => '*',
      p_redirect_uri    => 'https://example.org/my_redirect/',
      p_support_email   => '[e-mail]',
      p_support_uri     => 'https://example.org/help/'
  );
  COMMIT;
END;
/

Example 9-13

The following example updates all OAuth client fields:

DECLARE
  l_client_key ords_types.t_client_key;
BEGIN
  l_client_key := ORDS_SECURITY.UPDATE_CLIENT(
      p_client_key       => ords_types.oauth_client_key(p_name=>'CLIENT_TEST'),
      p_description      => 'This is a test description.',
      p_privilege_names  => 'oracle.dbtools.sqldev',
      p_origins_allowed  => '*',
      p_redirect_uri     => 'https://example.org/my_redirect/',
      p_support_email    => '[e-mail]',
      p_support_uri      => 'https://example.org/help/',
      p_token_duration   => 3600,
      p_refresh_duration => 86400,
      p_code_duration    => 300
  );
  COMMIT;
  sys.dbms_output.put_line('ID:' || l_client_key.id);
END;
/

Example 9-14

The following example updates all OAuth client fields:

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT(
      p_name             => 'CLIENT_TEST',
      p_description      => 'This is a test description.',
      p_privilege_names  => 'oracle.dbtools.sqldev',
      p_origins_allowed  => '*',
      p_redirect_uri     => 'https://example.org/my_redirect/',
      p_support_email    => '[e-mail]',
      p_support_uri      => 'https://example.org/help/',
      p_token_duration   => 3600,
      p_refresh_duration => 86400,
      p_code_duration    => 300
  );
  COMMIT;
END;
/

UPDATE_CLIENT_LOGO

Format

PROCEDURE update_client_logo(
      p_client_key   IN ords_types.t_client_key,
      p_content_type IN VARCHAR2,
      p_logo         IN BLOB
  );

Description

Updates the OAuth client logo file.

Table 12 Parameters

Parameter	Description
p_client_key	The key (id|name|client_id) of the client to be modified. A minimum of one key must be supplied.
p_content_type	The content type of the logo. This value must not be null.
p_logo	The logo binary. This value must not be null.

Usage Notes

Use the COMMIT statement after calling this procedure for the operation to take effect.

• Examples

Examples

Example 9-15

The following example adds or updates the logo of an OAuth client:

DECLARE
  l_image BLOB := ...;
BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_LOGO(
      p_client_key   => ords_types.oauth_client_key(p_name=>'CLIENT_TEST'),
      p_content_type => 'image/png',
      p_logo         => l_image
  );
  COMMIT;
END;
/

Example 9-16

The following example adds or updates the logo of an OAuth client:

DECLARE
  l_image BLOB := ...;
BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_LOGO(
      p_name         => 'CLIENT_TEST',
      p_content_type => 'image/png',
      p_logo         => l_image
  );
  COMMIT;
END;
/

UPDATE_CLIENT_PRIVILEGES

Format

PROCEDURE update_client_privileges(
      p_schema          IN VARCHAR2,
      p_client_key      IN ords_types.t_client_key,
      p_privilege_names IN VARCHAR2
  );

Description

Updates the OAuth client privileges. This procedure is only applicable for implicit and authorization_code flows.

Table 13 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_client_key	The key (id|name|client_id) of the client to be modified. A minimum of one key must be supplied.
p_privilege_names	Names of the privileges that the client wishes to access. Each privilege name must be separated by a comma character.

Usage Notes

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

UPDATE_CLIENT_PRIVILEGES

Format

PROCEDURE update_client_privileges(
      p_schema          IN VARCHAR2,
      p_name            IN VARCHAR2,
      p_privilege_names IN VARCHAR2
  );

Description

Updates the OAuth client privileges. This procedure is only applicable for implicit and authorization_code flows.

Table 14 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_name	The name of the client to be modified. This value must not be null.
p_privilege_names	List of comma-separated privileges that the client wants to access. The privilege(s) must already exist. (See ORDS.DEFINE_PRIVILEGE)

Usage Notes

Use the COMMIT statement after calling this procedure for the operation to take effect.

• Examples

Examples

The following examples update the privileges of an OAuth client:

Example 9-17

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_PRIVILEGES(
      p_client_key       => ords_types.oauth_client_key(p_name=>'CLIENT_TEST'),
      p_privilege_names  => 'oracle.dbtools.sqldev'
  );
  COMMIT;
END;
/

Example 9-18

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_PRIVILEGES(
      p_name             => 'CLIENT_TEST',
      p_privilege_names  => 'oracle.dbtools.sqldev'
  );
  COMMIT;
END;
/

UPDATE_CLIENT_TOKEN_DURATION

Format

PROCEDURE update_client_token_duration(
      p_schema           IN VARCHAR2,
      p_client_key       IN ords_types.t_client_key,
      p_token_duration   IN NUMBER,
      p_refresh_duration IN NUMBER,
      p_code_duration    IN NUMBER
  );

Description

Updates the OAuth client token durations.

Table 15 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_client_key	The key (id|name|client_id) of the client to be modified. A minimum of one key must be supplied.
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 the value is authorization code. If the value is set to NULL or the grant_type value is not authorization_code, then the value is 300.

Usage Notes

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

UPDATE_CLIENT_TOKEN_DURATION

Format

PROCEDURE update_client_token_duration(
      p_schema           IN VARCHAR2,
      p_name             IN VARCHAR2,
      p_token_duration   IN NUMBER,
      p_refresh_duration IN NUMBER,
      p_code_duration    IN NUMBER
  );
END ords_security_admin;

Description

Updates the OAuth client token durations.

Table 16 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_name	The name of the client to be modified. This value must not be null.
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 the value is authorization code. If the value is set to NULL or the grant_type value is value is not authorization_codethen the value is 300.

• Examples

Examples

The following examples update the token durations of an OAuth client:

Example 9-19

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_TOKEN_DURATION(
      p_client_key       => ords_types.oauth_client_key(p_name=>'CLIENT_TEST'),
      p_token_duration   => 3600,
      p_refresh_duration => 86400,
      p_code_duration    => 300
  );
  COMMIT;
END;
/

Example 9-20

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_TOKEN_DURATION(
      p_name             => 'CLIENT_TEST',
      p_token_duration   => 3600,
      p_refresh_duration => 86400,
      p_code_duration    => 300
  );
  COMMIT;
END;
/

RENAME_CLIENT

Format

PROCEDURE rename_client(
      p_name     IN VARCHAR2,
      p_new_name IN VARCHAR2
  );

Description

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

Table 17 Parameters

Parameter	Description
p_name	The current name of the client to be renamed. This value must not be null.
p_new_name	The new name for the client. This value must not be null.

Usage Notes

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

RENAME_CLIENT

Format

PROCEDURE rename_client(
      p_schema   IN VARCHAR2,
      p_name     IN VARCHAR2,
      p_new_name IN VARCHAR2
  );

Description

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

Table 18 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_name	The current name of the client to be renamed. This value must not be null.
p_new_name	The new name for the client. This value must not be null.

Usage Notes

Use the COMMIT statement after calling this function for the operation to take effect.

• Examples

Examples

The following examples renames an OAuth client:

Example 9-21

BEGIN
  ORDS_SECURITY.RENAME_CLIENT(
      p_client_key => ords_types.oauth_client_key(p_name=>'CLIENT_TEST'),
      p_new_name   => 'CLIENT_TEST_RENAMED'
  );
  COMMIT;
END;
/

Example 9-22

BEGIN
  ORDS_SECURITY.RENAME_CLIENT(
      p_name     => 'CLIENT_TEST',
      p_new_name => 'CLIENT_TEST_RENAMED'
  );
  COMMIT;
END;
/

ROTATE_CLIENT_SECRET

Format

FUNCTION rotate_client_secret(
      p_schema          IN VARCHAR2,
      p_client_key      IN ords_types.t_client_key,
      p_revoke_existing IN BOOLEAN DEFAULT FALSE,
      p_revoke_sessions IN BOOLEAN DEFAULT FALSE
  ) RETURN ords_types.t_client_credentials;

Description

Generates a new OAuth client secret and, if required, deletes all existing client sessions. If two client secrets are already registered then the oldest will be overwritten. Any existing client secrets will also remain in effect unless revoked using the p_revoke_existing parameter. Note: The generated client secret is not stored using this function and so requires the caller to save the returned value for future use. The view USER_ORDS_CLIENTS does not return the value either. The view USER_ORDS_CLIENTS cannot return secrets that are not stored.

Table 19 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_client_key	The key (id|name|client_id) of the client in the schema. A minimum of one key must be supplied.
p_revoke_existing	Revokes any exisiting secrets. Default value is FALSE.
p_revoke_sessions	Deletes all existing client sessions when TRUE. Default value is FALSE.

Usage Notes

Use the COMMIT statement after calling this function for this operation to take effect.

ROTATE_CLIENT_SECRET

Format

FUNCTION rotate_client_secret(
      p_schema          IN VARCHAR2,
      p_client_key      IN ords_types.t_client_key,
      p_revoke_existing IN BOOLEAN DEFAULT FALSE,
      p_revoke_sessions IN BOOLEAN DEFAULT FALSE
  ) RETURN ords_types.t_client_credentials;

Description

Generates a new OAuth client secret and, if required, deletes all existing client sessions. If two client secrets are already registered then the oldest will be overwritten. Any existing client secrets will also remain in effect unless revoked using the p_revoke_existing parameter.

Note: The generated client secret is not stored using this function and so requires the caller to save the returned value for future use. The view USER_ORDS_CLIENTS does not return the value either. The view USER_ORDS_CLIENTS cannot return secrets that are not stored.

Table 20 Parameters {: #unique_1886866389__TABLE_X52_MH2_NDC .titleintable}

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_client_key	The key (id|name|client_id) of the client in the schema. A minimum of one key must be supplied.
p_revoke_existing	Revokes any exisiting secrets. Default value is FALSE.
p_revoke_sessions	Deletes all existing client sessions when TRUE. Default value is FALSE.

Usage Notes

Use the COMMIT statement after calling this function for this operation to take effect.

• Examples

Examples

The following examples rotates an OAuth client. The existing client secret continues to work until revoked:

Example 9-23

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred.client_key.name := 'CLIENT_TEST';

  l_client_cred := ORDS_SECURITY.ROTATE_CLIENT_SECRET(
      p_client_key    => l_client_cred.client_key
  );
  -- No Commit Required
  sys.dbms_output.put_line('SLOT:'      || l_client_cred.client_secret.slot);
  sys.dbms_output.put_line('SECRET:'    || l_client_cred.client_secret.secret);
  sys.dbms_output.put_line('ISSUED ON:' || l_client_cred.client_secret.issued_on);
END;
/

Example 9-24

DECLARE
  l_client_secret user_ords_clients.client_secret%TYPE;
BEGIN
  l_client_secret := ORDS_SECURITY.ROTATE_CLIENT_SECRET(
      p_name => 'CLIENT_TEST'
  );
  -- No Commit Required
  sys.dbms_output.put_line('SECRET:' || l_client_secret);
END;
/

Example 9-25

The following example revokes an OAuth client secrets by value:

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred.client_key.name      := 'CLIENT_TEST';
  l_client_cred.client_secret.secret := 'RaFhM690PA6cN1ffpkNx3Q..';

  l_client_cred := ORDS_SECURITY.REVOKE_CLIENT_SECRETS(
      p_client_key => l_client_cred.client_key,
      p_filter     => l_client_cred.client_secret
  );
  -- No Commit Required
  sys.dbms_output.put_line('SLOT:'      || l_client_cred.client_secret.slot);
END;
/

DELETE_JWT_PROFILE

Format

PROCEDURE delete_jwt_profile;

Description

Deletes a JWT Profile. JWT bearer tokens are not accepted when authorizing requests to the protected resources.

Usage Notes

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

• Examples

Examples

Example 9-26

The following example, deletes any existing JWT Profile for the schema and creates a new 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
  ORDS_SECURITY.DELETE_JWT_PROFILE;
  ORDS_SECURITY.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 9-27

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

BEGIN
  ORDS_SECURITY.DELETE_JWT_PROFILE;
  COMMIT;
END;
/

DELETE_CLIENT

Format

PROCEDURE delete_client(
      p_client_key IN ords_types.t_client_key
  );

Description

Deletes an OAuth client registration.

Table 21 Parameters

Parameter	Description
p_client_key	The key (id|name|client_id) of the client registration to be deleted. A minimum of one key must be supplied.

Usage Notes

Use the example COMMIT statement after calling this method for this operation to take effect.

DELETE_CLIENT

Format

PROCEDURE delete_client(
      p_name IN VARCHAR2
  );

Description

Deletes an OAuth client registration.

Table 22 Parameters

Parameter	Description
p_name	The name of the client registration to be deleted. This value must not be null.

Usage Notes

For this operation to take effect, Use the example COMMIT statement after calling this procedure for the operation to take effect.

• Examples

Examples

The following examples deletes an OAuth client registration:

Example 9-28

BEGIN
  ORDS_SECURITY.DELETE_CLIENT(
      p_client_key => ords_types.oauth_client_key(p_name=>'CLIENT_TEST')
  );
  COMMIT;
END;
/

Example 9-29

BEGIN
  ORDS_SECURITY.DELETE_CLIENT(
      p_name => 'CLIENT_TEST'
  );
  COMMIT;
END;
/

REVOKE_CLIENT_ROLE

Format

PROCEDURE revoke_client_role(
      p_schema      IN VARCHAR2,
      p_client_key IN ords_types.t_client_key,
      p_role_name  IN VARCHAR2
  );

Description

Revokes the specified role from an OAuth client, preventing it from accessing the Privileges requiring the role via two-legged OAuth.

Table 23 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_client_key	The key (id|name|client_id) of the client grantee. A minimum of one key must be supplied.
p_role_name	The name of a role that was previously granted. This value must not be null.

Usage Notes

Use the COMMIT statement after calling this function for the operation to take effect.

REVOKE_CLIENT_ROLE

Format

PROCEDURE revoke_client_role(
      p_schema      IN VARCHAR2,
      p_client_name IN VARCHAR2,
      p_role_name   IN VARCHAR2
  );

Description

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

Table 24 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_client_name	The name of the client grantee. This value must not be null.
p_role_name	The name of a role that was previously granted. This value must not be null.

Usage Notes

Use the COMMIT statement after calling this function for the operation to take effect.

• Examples

Examples

The following examples revokes the grant of a role to an OAuth client:

Example 9-30

BEGIN
  ORDS_SECURITY.REVOKE_CLIENT_ROLE(
      p_client_key => ords_types.oauth_client_key(p_name=>'CLIENT_TEST'),
      p_role_name  => 'CLIENT_TEST_ROLE'
  );
  COMMIT;
END;
/

Example 9-31

BEGIN
  ORDS_SECURITY.REVOKE_CLIENT_ROLE(
      p_client_name => 'CLIENT_TEST',
      p_role_name   => 'CLIENT_TEST_ROLE'
    );
  COMMIT;
END;
/

REVOKE_CLIENT_SECRETS

Format

FUNCTION revoke_client_secrets(
      p_schema               IN VARCHAR2,
      p_client_key           IN ords_types.t_client_key,
      p_filter               IN ords_types.t_client_secret DEFAULT ords_constants.oauth_client_secret_default,
      p_revoke_sessions      IN BOOLEAN  DEFAULT FALSE
  ) RETURN ords_types.t_client_credentials;

Description

Revokes one or both OAuth client secrets and revokes all sessions when required. By default this will only revoke the oldest secret but can be used to revoke one or both secrets through the use of the p_filter parameter. The filter’s fields work independently of each other.

Table 25 Parameters

Parameters	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_client_key	The key (id|name|client_id) of the client. A minimum of one key must be supplied.
p_filter	Filter which secret(s) should be revoked. When the filter is null then only the oldest secret is revoked. When p_filter.slot = 3 then both slots will be revoked. When p_filter.stored = FALSE then this only matches when used in isolation.
p_revoke_sessions	Deletes all theexisting client sessions when the value is set to TRUE. Default value is FALSE.

Usage Notes

The special value 3 for the slot number indicates that both slots are to be revoked. Any changes are immediately commited.

Returns

The client key (including client_id) and the slot of the revoked client_secret of the client. For the returned slot number, a value of 3 indicates that both the slots were revoked and a null value indicates that no slots were revoked. All other client_secret fields are null.

REVOKE_CLIENT_SECRET

Format

PROCEDURE revoke_client_secret(
      p_schema          IN VARCHAR2,
      p_name            IN VARCHAR2,
      p_client_secret   IN VARCHAR2 DEFAULT NULL,
      p_revoke_sessions IN BOOLEAN  DEFAULT FALSE
  );

Description

Revokes a OAuth client secret and revokes all sessions when required. By default, this only revokes the oldest secret but may revoke one or both secrets if they match the client secret value.

Table 26 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_name	The name of the client to be modified. This value must not be null.
p_client_secret	The value of the client secret. When the value is NULL, the oldest secret is revoked.
p_revoke_sessions	Deletes all existing client sessions when TRUE. Default value is false.

USAGE NOTES

The changes are immediately commited.

• Examples

Examples

Example 9-32

The following example revokes an OAuth client secrets by value:

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred.client_key.name      := 'CLIENT_TEST';
  l_client_cred.client_secret.secret := 'RaFhM690PA6cN1ffpkNx3Q..';

  l_client_cred := ORDS_SECURITY.REVOKE_CLIENT_SECRETS(
      p_client_key => l_client_cred.client_key,
      p_filter     => l_client_cred.client_secret
  );
  -- No Commit Required
  sys.dbms_output.put_line('SLOT:'      || l_client_cred.client_secret.slot);
END;
/

Example 9-33

The following example revokes the oldest OAuth client secret, leaving only one in effect:

BEGIN
  ORDS_SECURITY.REVOKE_CLIENT_SECRET(
      p_name => 'CLIENT_TEST'
  );
  -- No Commit Required
END;
/