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

9.1 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
  );
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 9-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 of security.jwt.allowed.age 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_ADMIN PL/SQL Package Reference

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

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

Parent topic: CREATE_JWT_PROFILE

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.3 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
  );
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 9-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.
Usage
Use the COMMIT statement after calling this procedure for the operation to take effect.

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.3.1 Examples

Example 9-1 

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   => '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 9-2

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   => '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 9-3

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   => '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

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.5 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
  );
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 9-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.
Usage Notes
Use the COMMIT statement after calling this procedure for the operation to take effect.

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.5.1 Examples

Example 9-4

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

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

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

Parent topic: IMPORT_CLIENT

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.7.1 Examples

Example 9-7

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

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

Parent topic: REGISTER_CLIENT_SECRET

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.8.1 Examples

Example 9-9

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 9-10

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

Parent topic: GRANT_CLIENT_ROLE

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.10.1 Examples

Example 9-11

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

Example 9-12

The following example renames an OAuth client and updates all client fields, 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   => 'test@example.org',
      p_support_uri     => 'https://example.org/help/'
  );
  COMMIT;
END;
/

Example 9-13

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    => '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 9-14

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    => '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

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.12.1 Examples

Example 9-15

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 9-16

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

Parent topic: UPDATE_CLIENT_LOGO

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.14.1 Examples

Example 9-17

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

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

Parent topic: UPDATE_CLIENT_PRIVILEGES

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.16.1 Examples

Example 9-19

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

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

Parent topic: UPDATE_CLIENT_TOKEN_DURATION

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.18.1 Examples

Example 9-21

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

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

Parent topic: RENAME_CLIENT

9.19 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 view USER_ORDS_CLIENTS does not return the value either. The view USER_ORDS_CLIENTS cannot return secrets that are not stored.

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.20.1 Examples

Example 9-23 

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred.client_key.name := 'CLIENT_TEST';
   
  l_client_cred := ORDS_SECURITY.ROTATE_CLIENT_SECRET(
      p_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 9-24

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

Parent topic: ROTATE_CLIENT_SECRET

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.21.1 Examples

Example

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

BEGIN
  ORDS_SECURITY_ADMIN.rotate_all_security_keys;
  COMMIT;
END;
/

Parent topic: ROTATE_ALL_SECURITY_KEYS

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

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

Parent topic: ROTATE_SECURITY_KEYS

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.24 DELETE_CLIENT

Format

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

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.24.1 Examples

Example 9-25

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

Parent topic: DELETE_CLIENT

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.25.1 Examples

Example 9-26

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 9-27

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

Parent topic: REVOKE_CLIENT_ROLE

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.27 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 9-26 Parameters

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

Parent topic: ORDS_SECURITY_ADMIN PL/SQL Package Reference

9.27.1 Examples

Example 9-28

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 9-29

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

Parent topic: REVOKE_CLIENT_SECRETS