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

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

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.1.1 Examples

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:

Example 8-1 

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;
/

Parent topic: create_jwt_profile

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

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.2.1 Examples

Example 8-2

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 8-3

The following example, deletes any existing JWT Profile for the schema: BEGIN
BEGIN
  ORDS_SECURITY.DELETE_JWT_PROFILE;
  COMMIT;
END;
/

Parent topic: delete_jwt_profile

8.3 delete_client

Format

PROCEDURE delete_client(
      p_client_key IN ords_types.t_client_key
  );
Description
Deletes an OAuth client registration.

Table 8-1 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.4 delete_client

Format

PROCEDURE delete_client(
      p_name IN VARCHAR2
  );
Description
Deletes an OAuth client registration.

Table 8-2 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.4.1 Examples

The following examples deletes an OAuth client registration:

Example 8-4 

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

Example 8-5 

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

Parent topic: delete_client

8.5 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 8-3 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 must not be null.
Usage Notes
Use the example COMMIT statement after calling this method for this operation to take effect.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.6 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 8-4 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.6.1 Examples

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

Example 8-6 

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

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;
/

Parent topic: grant_client_role

8.7 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
  ) 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 8-5 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_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.
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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.8 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 8-6 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_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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.8.1 Examples

Example 8-8

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   => 'test@example.org',
      p_support_uri     => 'https://example.org/help/',
      p_privilege_names => 'oracle.dbtools.sqldev');
  COMMIT;
END;
/

Parent topic: import_client

8.9 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
      );
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 8-7 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.
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.
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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.10.1 Examples

Example 8-9

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   => 'test@example.org',
      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 8-10

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   => 'test@example.org',
      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 8-11

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   => 'test@example.org',
      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;
/

Parent topic: register_client

8.11 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 8-9 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.11.1 Examples

Example 8-12

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 8-13

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;
/

Parent topic: register_client_secret

8.12 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 8-10 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.13 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 8-11 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.13.1 Examples

The following examples renames an OAuth client:

Example 8-14 

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 8-15 

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

Parent topic: rename_client

8.14 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 8-12 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.15.1 Examples

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

Example 8-16 

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 8-17 

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 8-18

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;
/

Parent topic: rotate_client_secret

8.16 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 8-14 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.17 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 8-15 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.17.1 Examples

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

Example 8-19 

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 8-20 

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

Parent topic: revoke_client_role

8.18 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 8-16 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.19 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 8-17 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.19.1 Examples

Example 8-21

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 8-22

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;
/

Parent topic: revoke_client_secret

8.20 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
  );
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-18 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/
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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.22 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
  ) 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 8-20 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/
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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.23 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
  );
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-21 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_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.
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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.23.1 Examples

Example 8-23

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   => 'test@example.org',
      p_support_uri     => 'https://example.org/help/'
  );
  COMMIT;
  sys.dbms_output.put_line('ID:' || l_client_key.id);
END;
/

Example 8-24

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   => 'test@example.org',
      p_support_uri     => 'https://example.org/help/'
  );
  COMMIT;
END;
/

Example 8-25

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    => 'test@example.org',
      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 8-26

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    => 'test@example.org',
      p_support_uri      => 'https://example.org/help/',
      p_token_duration   => 3600,
      p_refresh_duration => 86400,
      p_code_duration    => 300
  );
  COMMIT;
END;
/

Parent topic: update_client

8.24 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 8-22 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.24.1 Examples

Example 8-27

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 8-28

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;
/

Parent topic: update_client_logo

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

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.26 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 8-24 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.26.1 Examples

The following examples update the privileges of an OAuth client:

Example 8-29 

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 8-30 

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

Parent topic: update_client_privileges

8.27 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 8-25 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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.28 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 8-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_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.

Parent topic: ORDS_SECURITY PL/SQL Package Reference

8.28.1 Examples

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

Example 8-31 

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 8-32 

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;
/

Parent topic: update_client_token_duration