6 OAUTH PL/SQL Package Reference

The OAUTH PL/SQL package contains procedures for implementing OAuth authentication using Oracle REST Data Services.

6.1 OAUTH.CREATE_CLIENT

Format

OAUTH.CREATE_CLIENT(
   p_name            IN VARCHAR2,
   p_grant_type      IN VARCHAR2,
   p_owner           IN VARCHAR2 DEFAULT NULL,
   p_description     IN VARCHAR2 DEFAULT NULL,
   p_origins_allowed IN VARCHAR2 DEFAULT NULL,  
   p_redirect_uri    IN VARCHAR2 DEFAULT NULL,
   p_support_email   IN VARCHAR2 DEFAULT NULL,
   p_support_uri     IN VARCHAR2  DEFAULT NULL,
   p_privilege_names IN VARCHAR2
   p_token_duration   IN NUMBER,
   p_refresh_duration IN NUMBER,
   p_code_duration    IN NUMBER)

Description

Creates an OAuth client registration.

Parameters

p_name

Name for the client, displayed to the end user during the approval phase of three-legged OAuth. Must be unique.

p_grant_type

Must be one of authorization_code, implicit, or client_credentials.

p_owner

Name of the party that owns the client application.

p_description

Description of the purpose of the client, displayed to the end user during the approval phase of three-legged OAuth. May 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 will be sent. May be null if p_grant_type is client_credentials; otherwise, must not be null.

p_support_email

The email where end users can contact the client for support.

p_support_uri

The URI where end users can contact the client for support. Example: http://www.myclientdomain.com/support/

p_privilege_names

List of comma-separated privileges that the client wants to access.

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 grant_type value is authorization code. If the value is set to NULL or the grant_type value is not authorization_code, then the lifetime is the one defined in the ORDS instance. By default, the value is 300.

Usage Notes

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

Examples

The following example creates an OAuth client registration.

BEGIN
  OAUTH.create_client(
    'CLIENT_TEST',
    'authorization_code',
    'test_user',
    'This is a test description.',
    '',
    'https://example.org/my_redirect/#/',
    '[e-mail]',
    'https://example.org/help/#/',
    'MyPrivilege',
    NULL,
    NULL,
    NULL
    );
    COMMIT;
END;
/

6.2 OAUTH.DELETE_CLIENT

Format

OAUTH.DELETE_CLIENT(
   p_name IN VARCHAR2);

Description

Deletes an OAuth client registration.

Parameters

p_name

Name of the client registration to be deleted.

Usage Notes

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

Examples

The following example deletes an OAuth client registration.

BEGIN
  OAUTH.delete_client(
    'CLIENT_TEST'
    );
  COMMIT;
END;
/

6.3 OAUTH.GRANT_CLIENT_ROLE

Format

OAUTH.GRANT_CLIENT_ROLE(
   p_client_name IN VARCHAR2,
   p_role_name   IN VARCHAR2);

Description

Grant an OAuth client the specified role, enabling clients performing two-legged OAuth to access privileges requiring the role.

Parameters

p_client_name

Name of the OAuth client.

p_role_name

Name of the role to be granted.

Usage Notes

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

Examples

The following example creates a role and grants that role to an OAuth client.

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

  OAUTH.grant_client_role(
    'CLIENT_TEST',
    'CLIENT_TEST_ROLE'
    );
  COMMIT;
END;
/

6.4 OAUTH.RENAME_CLIENT

Format

OAUTH.RENAME_CLIENT(
   p_name     IN VARCHAR2,
   p_new_name IN VARCHAR2);

Description

Renames a client.

Parameters

p_name

Current name for the client.

p_new_name

New name for the client.

Usage Notes

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

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

Examples

The following example renames a client.

BEGIN
  OAUTH.rename_client(
    'CLIENT_TEST',
    'CLIENT_TEST_RENAMED'
    );
  COMMIT;
END;
/

6.5 OAUTH.REVOKE_CLIENT_ROLE

Format

OAUTH.REVOKE_CLIENT_ROLE(
   p_client_name  IN VARCHAR2,
   p_role_name    IN VARCHAR2);

Description

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

Parameters

p_client_name

Name of the OAuth client.

p_role_name

Name of the role to be revoked

Usage Notes

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

Examples

The following example revokes a specified role from an OAuth client.

BEGIN
  OAUTH.revoke_client_role(
    'CLIENT_TEST_RENAMED',
    'CLIENT_TEST_ROLE'
    );
  COMMIT;
END;
/

6.6 OAUTH.UPDATE_CLIENT

Format

OAUTH.UPDATE_CLIENT(
  p_name             IN VARCHAR2,
  p_description      IN VARCHAR2,
  p_origins_allowed  IN VARCHAR2,
  p_redirect_uri     IN VARCHAR2,
  p_support_email    IN VARCHAR2,
  p_suppor_uri       IN VARCHAR2,
  p_privilege_names  IN t_ords_vchar_tab DEFAULT NULL,
  p_token_duration   IN NUMBER,
  p_refresh_duration IN NUMBER,
  p_code_duration    IN NUMBER
);

Description

Updates the client information (except name). Any null values will not alter the existing client property.

Parameters

p_name

Name of the client that requires the owner, description, origins allowed, support e-mail, support URI, and/or privilege modification.

p_description

Description of the purpose of the client, displayed to the end user during the approval phase of three-legged OAuth.

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 a redirect containing the OAuth access token/error will be sent. If this parameter is null, the existing p_redirect_uri value (if any) is not changed.

p_support_email

The email address where end users can contact the client for support.

p_support_uri

The URI where end users can contact the client for support. Example: http://www.myclientdomain.com/support/

p_privilege_names

List of names of the privileges that the client wishes to access.

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 grant_type is authorization code. If the value is set to NULL or the grant_type is not authorization_code, then the lifetime is the one defined in the ORDS instance. By default, the value is 300.

Usage Notes

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

If you want to rename the client, use the OAUTH.RENAME_CLIENT procedure.

Example to Update the Description of the Specified Client

The following example updates the description of the client with the name matching the value for p_name.

BEGIN
  ORDS_METADATA.OAUTH.update_client(
    p_name => 'CLIENT_TEST_RENAMED',
    p_description => 'The description was altered',
    p_origins_allowed => null,
    p_redirect_uri => null,
    p_support_email => null,
    p_support_uri => null,
    p_privilege_names => null,
    p_token_duration => null,     
    p_refresh_duration => null,    
    p_code_duration    => null);
  COMMIT;
END;
/

Example 6-1 Example to Add Multiple Privileges

The following example adds a second privilege:

declare 
 my_privs t_ords_vchar_tab  := t_ords_vchar_tab (); 
begin 
 my_privs.EXTEND (3); 
 my_privs(1):='tst.privilege1'; 
 my_privs(2):='tst.privilege2'; 
 
 oauth.update_client( 
    p_name => 'Test_Client', 
    p_description => 'Description altered.',
    p_origins_allowed => NULL,
    p_redirect_uri => '/abc/efg/', 
    p_privilege_names => my_privs,
    p_token_duration => NULL,
    p_refresh_duration => NULL,
    p_code_duration    => NULL); 
commit; 
end;

6.7 OAUTH.ROTATE_CLIENT_SECRET

Format

OAUTH.ROTATE_CLIENT_SECRET(
      p_client_id       IN NUMBER,
      p_editing_user    IN VARCHAR2,
      p_revoke_sessions IN BOOLEAN DEFAULT TRUE);

Description

ROTATE_CLIENT_SECRET regenerates a new client secret and deletes all existing client sessions by default.

Parameters

p_client_id

The ID of the client modified.

p_editing_user

The user requesting this change.

p_revoke_sessions

Controls if the approval for the existing client sessions must be revoked. Default value is TRUE.

Example

The following example rotates a client secret:

BEGIN
  OAUTH.ROTATE_CLIENT_SECRET(
  p_client_id => 1234567890,
  p_editing_user => 'USERA',
  p_revoke_sessions => TRUE
  );
END;
/

6.8 OAUTH.UPDATE_CLIENT_SECRET

Format

OAUTH.UPDATE_CLIENT_SECRET(
      p_client_name  IN VARCHAR2,
      p_editing_user  IN VARCHAR2,
      p_client_secret  IN VARCHAR2);

Description

UPDATE_CLIENT_SECRET sets a new value for the secret of the client. By default, it deletes all the existing client sessions.

Parameters

p_client_name

The name of the client in the current schema.

p_editing_user

The user requesting this change.

p_client_secret

The value of the new secret for the client.

Usage Notes

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

Example

The following example updates the secret of a particular client:

BEGIN
  OAUTH.UPDATE_CLIENT_SECRET(
        p_client_name   => 'CLIENT_TEST',
        p_editing_user   => 'USERA ',
        p_client_secret  => 'RaFhM690PA6cN1ffpkNx3Q..');
END;
/

6.9 OAUTH.IMPORT_CLIENT

Format

OAUTH.IMPORT_CLIENT(
    p_name                 IN VARCHAR2,
    p_client_id            IN VARCHAR2,
    p_client_secret        IN VARCHAR2 DEFAULT NULL,
    p_grant_type           IN VARCHAR2,
    p_owner                IN VARCHAR2 DEFAULT NULL,
    p_description          IN VARCHAR2 DEFAULT NULL,
    p_origins_allowed      IN VARCHAR2 DEFAULT NULL,
    p_redirect_uri         IN VARCHAR2 DEFAULT NULL,
    p_support_email        IN VARCHAR2 DEFAULT NULL,
    p_support_uri          IN VARCHAR2 DEFAULT NULL,
    p_privilege_names      IN VARCHAR2,
    p_token_duration       IN NUMBER DEFAULT NULL,
    p_refresh_duration     IN NUMBER DEFAULT NULL,
    p_code_duration        IN NUMBER DEFAULT NULL);

Description

Imports an existing client into this schema, preserving the identifier and optionally a secret. If the secret is not provided, then a new one is generated.

Parameters

p_name

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

p_client_id

A unique client identifier.

p_client_secret

Optional parameter. If not provided, then a random secret is generated.

p_grant_type

The value must be one of authorization_code, implicit, or client_credentials.

p_owner

Name of the party that owns the client application.

p_description

Description of the purpose of the client. Displayed to the end user during the approval phase of three-legged OAuth. Can be null if p_grant_type value is client_credentials. Otherwise, it must not be null.

p_origins_allowed

A comma-separated list of URL prefixes.

p_redirect_uri

Client-controlled URI with a redirect containing an OAuth access token or error is sent. Can be a null if the value of p_grant_type is client_credentials. Otherwise, it must not be null.

p_support_email

The email where the end users can contact the client for support.

p_support_uri

The URI where the end users can contact the client for support.

Example URI:http://www.myclientdomain.com/support/

p_privilege_names

List of comma-separated privileges that the client wants to access.

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 is applicable only when grant_type value is authorization code. If the value is set to NULL or if the value of grant_type is not authorization_code, then the lifetime is the one defined in the ORDS instance. By default, the value is 300.

Usage Notes

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

Example

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

BEGIN
  OAUTH.IMPORT_CLIENT(
      p_name                  => 'CLIENT_TEST',
      p_client_id             => 'awVMtPlqullIqPXhAwh4zA..',
      p_grant_type            => 'authorization_code',
      p_owner                 => 'RESTEASY',
      p_description           => 'This is a test description.',
      p_origins_allowed       => NULL,
      p_redirect_uri          => 'https://example.org/my_redirect/',
      p_support_email         => '[e-mail]',
      p_support_uri           => 'https://example.org/help/',
      p_privilege_names       => 'MyPrivilege');

  COMMIT;
END;
/

6.10 OAUTH.CREATE_JWT_PROFILE

Format

OAUTH.CREATE_JWT_PROFILE (
       p_issuer       IN VARCHAR2,
       p_audience     IN VARCHAR2,
       p_jwk_url      IN VARCHAR2,
       p_description  IN VARCHAR2 DEFAULT NULL,
       p_allowed_skew IN NUMBER DEFAULT NULL,
       p_allowed_age  IN NUMBER DEFAULT NULL
   )

Description

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

Parameters

p_issuer

The issuer of acceptable JWT access tokens. This value must match the iss claim provided in the JWT.

p_audience

The audience of acceptable JWT access tokens. This value must match the aud claim provided in the JWT.

p_jwk_url

This is the url to the jwk(s) used to validate acceptable JWT access tokens. It must start with "https://"

p_desciption

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 taken. A value less than or equals to 0 means, it is disabled.

Usage Notes

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

Example

The following example, deletes any existing JWT Profile for the 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
  OAUTH.DELETE_JWT_PROFILE(); 
  OAUTH.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;
/

6.11 OAUTH.DELETE_JWT_PROFILE

Format

OAUTH.DELETE_JWT_PROFILE ()

Description

Deletes the JWT Profile for the schema if one exists.

Usage Notes

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

Example

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

BEGIN
  OAUTH.DELETE_JWT_PROFILE();
  COMMIT;
END;
/

JWT bearer tokens are not be accepted when authorizing requests to the protected resources.