ORDS_SECURITY_ADMIN PL/SQL Package Reference

This package provides an API to manage the ORDS schema security for all users. This package is purely an interface and contains no application logic.

Note: Deprecation of OAUTH and OAUTH_ADMIN PL/SQL packages: Starting from ORDS release 24.3, the OAUTH and OAUTH_ADMIN PL/SQL packages have been deprecated. Oracle recommends that you migrate to the new ORDS_SECURITY and ORDS_SECURITY_ADMIN PL/SQL packages, which provide enhanced security. Backward compatibility with the legacy packages will be maintained through ORDS release 25.2, after which these packages (OAUTH and OAUTH_ADMIN PL/SQL packages) will be desupported.

• CREATE_JWT_PROFILE

• REGISTER_CLIENT

• REGISTER_CLIENT

• IMPORT_CLIENT

• IMPORT_CLIENT

• REGISTER_CLIENT_SECRET

• REGISTER_CLIENT_SECRET

• GRANT_CLIENT_ROLE

• UPDATE_CLIENT

• UPDATE_CLIENT

• UPDATE_CLIENT_LOGO

• 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

• ROTATE_ALL_SECURITY_KEYS

• ROTATE_SECURITY_KEYS

• DELETE_CLIENT

• DELETE_CLIENT

• REVOKE_CLIENT_ROLE

• REVOKE_CLIENT_SECRETS

• REVOKE_CLIENT_SECRETS

CREATE_JWT_PROFILE

Format

PROCEDURE create_jwt_profile(
      p_schema       IN ords_schemas.parsing_schema%type,
      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

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

Table 1 Parameters

Paramter	Description
p_schema	The name of the REST-enabled schema. This value must not be null
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 ofsecurity.jwt.allowed.age 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 10-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_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 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 10-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_ADMIN.DELETE_JWT_PROFILE(p_schema=>'HR');
ORDS_SECURITY_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 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

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 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 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 three-legged OAuth. Can 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 for issued_on field. By default,no secret is registered.
p_privilege_names	List of comma-separated privileges that the client wants to access.
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 an 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.
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

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

Returns

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

REGISTER_CLIENT

Format

PROCEDURE 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_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 call REGISTER_CLIENT_SECRET or ROTATE_CLIENT_SECRET followed by client registration.

Table 3 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 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 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 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.
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 an 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.
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

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

• Examples

Examples

Example 10-3

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred := ORDS_SECURITY.REGISTER_CLIENT(
      p_schema          => 'HR',
      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 10-4

The following example registers an OAuth client with a client secret for the schema:

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred := ORDS_SECURITY.REGISTER_CLIENT(
      p_schema          => 'HR',
      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 10-5

The following example registers an OAuth client for the schema:

DECLARE
  l_client_id user_ords_clients.client_id%TYPE;
BEGIN
  ORDS_SECURITY.REGISTER_CLIENT(
      p_schema          => 'HR',
      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_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_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

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 import_client function.

Table 4 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 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. Can be null if p_grant_type is client_credentials; otherwise, must not be null.
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_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 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 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

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

Returns

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

IMPORT_CLIENT

Format

PROCEDURE import_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_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,
      p_token_endpoint_auth_method IN VARCHAR2 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 import_client.

Table 5 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 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 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 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 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_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 an 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 grant_type value is 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

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

• Examples

Examples

Example 10-6

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

DECLARE
  l_client_key ords_types.t_client_key;
BEGIN
  l_client_key := ORDS_SECURITY.IMPORT_CLIENT(
      p_schema          => 'HR',
      p_name            => 'CLIENT_TEST',
      p_client_id       => 'awVMtPlqullIqPXhAwh4zA..',
      p_grant_type      => 'authorization_code',
      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;
  sys.dbms_output.put_line('ID:' || l_client_key.id);
END;
/

Example 10-7

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

DECLARE
  l_client_key ords_types.t_client_key;
BEGIN
  l_client_key := ORDS_SECURITY.IMPORT_CLIENT(
      p_schema          => 'HR',
      p_name            => 'CLIENT_TEST',
      p_client_id       => 'awVMtPlqullIqPXhAwh4zA..',
      p_grant_type      => 'authorization_code',
      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;
  sys.dbms_output.put_line('ID:' || l_client_key.id);
END;
/

Example 10-8

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

EGIN
  ORDS_SECURITY.IMPORT_CLIENT(
      p_schema          => 'HR',
      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_schema          IN VARCHAR2,
      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 and the newest clent secret and existing client seesions remain in effect. If two client secrets are already registered, then the oldest will be overwritten unless a specific slot is set in the p_client_secret.slot parameter. Any existing client secrets also remain in effect unless revoked using the p_revoke_existing parameter. See ROTATE_CLIENT_SECRET. 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 function 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 secrets that are not stored.

Table 6 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 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 the value is 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 the value is TRUE.

Usage Notes

Revokes all existing client sessions when TRUE.

Returns

The client key (including client_id) and registered client_secret.

REGISTER_CLIENT_SECRET

Format

PROCEDURE register_client_secret(
     p_schema          IN VARCHAR2,
     p_name            IN VARCHAR2,
     p_client_secret   IN VARCHAR2,
     p_revoke_existing IN BOOLEAN DEFAULT FALSE,
     p_revoke_sessions IN BOOLEAN DEFAULT FALSE
 );

Description

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

Note: The registered client secret value will not be persisted using this method. The caller is required to save the returned value for future use. The view USER_ORDS_CLIENTS cannot return secrets that are not stored.

Table 7 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema. This value must not be null.
p_name	The name of the registered client. This value must not be null.
p_client_secret	The new secret. The value must not be null.
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 TRUE.

Usage Notes

Any changes are commited immediately.

• Examples

Examples

Example 10-9

The following example registers a secret of an OAuth client for the schema. 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_schema        => 'HR',
      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 10-10

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

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

GRANT_CLIENT_ROLE

Format

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

Description

Grants a role to an OAuth client.

Table 8 Parameters

Parameter	Description
p_schema	The name of the REST-enabled schema.
p_client_name	The name of the client grantee. This value must not be null.
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 for client_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

Example 10-11

The following example creates a role and grants that role to an OAuth client for the schema:

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

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

Example 10-12

The following example creates a role and grants that role to an OAuth client for the schema:

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

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

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 the value is 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. Can be null if p_grant_type == 'client_credentials', non null otherwise.
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 OAuth access token/error is sent. Can 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	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 can 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 to take effect.

Returns

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

UPDATE_CLIENT

Format

PROCEDURE update_client(
      p_schema          IN VARCHAR2,
      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 10 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_new_name	The new name for the client. 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, 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_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 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.
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 they are 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 method for the operation to take effect.

• Examples

Examples

Example 10-13

The following example renames an OAuth client and updates all client fields, for the schema, 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_schema          => 'HR',
      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 10-14

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

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT(
      p_schema          => 'HR',
      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 10-15

The following example updates all OAuth client fields for the schema:

DECLARE
  l_client_key ords_types.t_client_key;
BEGIN
  l_client_key := ORDS_SECURITY.UPDATE_CLIENT(
      p_schema           => 'HR',
      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 10-16

The following example updates all OAuth client fields for the schema:

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT(
      p_schema           => 'HR',
      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_schema       IN VARCHAR2,
      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 11 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_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.

UPDATE_CLIENT_LOGO

Format

PROCEDURE update_client_logo(
      p_schema       IN VARCHAR2,
      p_name         IN VARCHAR2,
      p_content_type IN VARCHAR2,
      p_logo         IN BLOB
  );

Description

Updates the OAuth client logo file.

Table 12 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_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 10-17

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

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

Example 10-18

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

DECLARE
  l_image BLOB := ...;
BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_LOGO(
      p_schema       => 'HR',
      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.

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

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

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.

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 method for the operation to take effect.

• Examples

Examples

Example 10-19

The following example updates the privileges of an OAuth client for the schema:

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

Example 10-20

The following example updates the privileges of an OAuth client for the schema:

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_PRIVILEGES(
      p_schema           => 'HR',
      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 authorization code. If the value is set to NULL or the grant_type value is not authorization_code the value is 300.

Usage Notes

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

• Examples

Examples

Example 10-21

The following example updates the loken durations of an OAuth client for the schema:

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_TOKEN_DURATION(
      p_schema           => 'HR',
      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 10-22

The following example updates the loken durations of an OAuth client for the schema:

BEGIN
  ORDS_SECURITY.UPDATE_CLIENT_TOKEN_DURATION(
      p_schema           => 'HR',
      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_schema     IN VARCHAR2,
      p_client_key IN ords_types.t_client_key,
      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 17 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 renamed. A minimum of one key must be supplied.
p_new_name	The new name for the client. This value must not be null.

Usage Notes

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

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

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

• Examples

Examples

Example 10-23

The following example renames an OAuth client for the schema:

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

Example 10-24

The following example renames an OAuth client for the schema:

BEGIN
  ORDS_SECURITY.RENAME_CLIENT(
      p_schema   => 'HR',
      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 the existing client sessions. If two client secrets are already registered then the oldest is overwritten. Any existing client secrets also remain in effect unless revoked using the p_revoke_existing parameter. Note: The generated client secret is not stored using this method and so require the caller to save the returned value for future use. The viewUSER_ORDS_CLIENTS does not return the value either. The viewUSER_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 the value is TRUE. Default value is FALSE.

Usage Notes

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

Returns

The registered client secret value. This value must be saved by the caller for future reference.

ROTATE_CLIENT_SECRET

Format

FUNCTION rotate_client_secret(
      p_schema          IN VARCHAR2,
      p_name            IN VARCHAR2,
      p_revoke_existing IN BOOLEAN DEFAULT FALSE,
      p_revoke_sessions IN BOOLEAN DEFAULT FALSE
  ) RETURN VARCHAR2;

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 is overwritten. Any existing client secrets also remain in effect unless revoked using the p_revoke_existing parameter.

Note: The generated client secret is not stored using this method and so require 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

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_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

Any changes are commited immediately.

Returns

The registered client secret value. This value must be saved by the caller for future reference.

• Examples

Examples

Example 10-25

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_schema        => 'HR',
      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 10-26

The following example rotates an OAuth client for the schema. The existing client secret will continue to work until revoked:

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

ROTATE_ALL_SECURITY_KEYS

Format

PROCEDURE rotate_all_security_keys();

Description

Generates a new ENC_KEY and MAC_KEY for all the rest enabled schemas.

Usage Notes

The rotate_all_security_keys procedure requires ORDS_ADMINISTRATOR_ROLE role. Use the COMMIT statement after calling this procedure for the operation to take effect.

• Examples

Examples

Example

The following example rotate the encryptions keys for all the rest schema:

BEGIN
  ORDS_SECURITY_ADMIN.rotate_all_security_keys;
  COMMIT;
END;
/

ROTATE_SECURITY_KEYS

Format

PROCEDURE rotate_security_keys(
p_schema      IN VARCHAR2,
);

Description

Generates a new ENC_KEY and MAC_KEY for the specified REST enabled schema.

Parameters

Table 21 Parameters

Parameter	Description
p_schema	Specifies the name of the REST-enabled schema. This value must not be null.

Usage Notes

The rotate_security_keys procedure requires ORDS_ADMINISTRATOR_ROLE role. Use the COMMIT statement after calling rotate_security_keys procedure for the operation to take effect.

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

• Examples

Examples

The following example rotates the encryption keys for the admin REST schema:

BEGIN
  ORDS_SECURITY_ADMIN.rotate_security_keys (p_schema => 'admin');;
  COMMIT;
END;
/

DELETE_CLIENT

Format

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

Description

Deletes an OAuth client registration.

Table 22 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 registration to be deleted. A minimum of one key must be supplied.

Usage Notes

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

DELETE_CLIENT

Format

PROCEDURE delete_client(
      p_schema IN VARCHAR2,
      p_name   IN VARCHAR2
  );

Description

Deletes an OAuth client registration.

Table 23 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 registration to be deleted. This value must not be null.

Usage Notes

Use the example COMMIT statement after calling this procedure.

• Examples

Examples

Example 10-27

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

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

The following example deletes an OAuth client registration for the schema:

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

The following example deletes an OAuth client registration for the schema:

BEGIN
  ORDS_SECURITY.DELETE_CLIENT(
      p_schema => 'HR',
      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 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_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 must not be null.

Usage Notes

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

• Examples

Examples

Example 10-28

The following example revokes the grant of a role to an OAuth client for the schema:

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

Example 10-29

The following example revokes the grant of a role to an OAuth client for the schema:

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

---

REVOKE_CLIENT_SECRETS

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 25 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

Any changes are commited immediately.

REVOKE_CLIENT_SECRETS

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 {: #unique_1067853861__TABLE_SDM_W2Q_4DC .titleintable}

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

Any changes are commited immediately.

• Examples

Examples

Example 10-30

The following example revokes an OAuth client secrets by value for the schema:

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_schema     => 'HR',
      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 10-31

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

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