Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Custom Authentication Schemes

This article gives an example of how you could develop your own custom authentication scheme for Oracle REST Data Services (ORDS).

• Warnings
• Create Test User
• Create a Security Package
• Manage Authorization Tokens
• Enable ORDS
• Create an Authentication Service
• Create a Service Requiring Authentication

Related articles.

• Oracle REST Data Services (ORDS) : Authentication
• Oracle REST Data Services (ORDS) : All Articles

Warnings

Let me start with some warnings.

• Writing your own authentication scheme is a really bad idea. You have to make sure they are 100% bullet proof or you're in trouble!
• This is a really basic example of how you might go about coding your own authentication scheme. It is not production-ready code.
• Please don't do this. Use OAuth2 or one of the other supported authentication schemes provided by ORDS, described here.

Create Test User

Create a test user that can create the necessary objects and has access to the DBMS_CRYPTO package.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;


CREATE USER test IDENTIFIED BY test
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO test;
GRANT EXECUTE ON dbms_crypto TO test;

Create a Security Package

We need something to manage user credentials. For this example I'm going to use the security package described in the following article, so create the objects in the TEST user before you continue.

• Storing Passwords in an Oracle Database

https://oracle-base.com/articles/9i/storing-passwords-in-the-database-9i

With that in place let's create a user to test with.

CONN test/test@pdb1

EXEC app_user_security.add_user('tim_hall','MyPassword');

SET LINESIZE 100
SELECT * FROM app_users;

        ID USERNAME                       PASSWORD
---------- ------------------------------ ----------------------------------------
         1 TIM_HALL                       EC07D20ACEDA612D12190599B25C7FBFF220BBEE

SQL>

Manage Authorization Tokens

You could pass in the credentials with each call, but that's probably not a good idea as the risk of someone getting hold of them is higher. Instead we will use our own forms of authorization token.

The following table holds valid tokens generated using the SYS_GUID() function, which we will store as a VARCHAR2 rather than a RAW column.

--DROP TABLE valid_tokens PURGE;

CREATE TABLE valid_tokens (
  id            VARCHAR2(32),
  updated_date  DATE
);

The following package manages the tokens. When a service calls the AUTHENTICATE procedure they are returned a token, which has been added to the VALID_TOKENS table. The CHECK_TOKENS procedure is used in a service to see if the specified token is present and it's less than 15 minutes since it was last used. If it's valid it is updated. If not an error is raised. The PURGE_TOKENS procedure can be called from a database job to clean up the table and stop is expanding endlessly with old tokens.

CREATE OR REPLACE PACKAGE auth_api AS

  PROCEDURE authenticate (p_username  IN  VARCHAR2,
                          p_password  IN  VARCHAR2,
                          p_token     OUT VARCHAR2);

  PROCEDURE check_token (p_token IN VARCHAR2);

  PROCEDURE purge_tokens;
  
END;
/


CREATE OR REPLACE PACKAGE BODY auth_api AS

  PROCEDURE authenticate (p_username  IN  VARCHAR2,
                          p_password  IN  VARCHAR2,
                          p_token     OUT VARCHAR2)
  IS
  BEGIN
    app_user_security.valid_user(p_username, p_password);
    
    -- If you got here user is valid.
    INSERT INTO valid_tokens (id, updated_date)
    VALUES (SYS_GUID(), SYSDATE)
    RETURNING id INTO p_token;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      -- Invalid user.
      p_token := 'INVALID';
  END;


  PROCEDURE check_token (p_token IN VARCHAR2) IS
  BEGIN
    UPDATE valid_tokens
    SET    updated_date = SYSDATE
    WHERE  id = p_token
    AND    updated_date >= SYSDATE - (1/24/4);
    
    IF SQL%ROWCOUNT = 0 THEN
      RAISE NO_DATA_FOUND;
    END IF;
    COMMIT;
  END;


  PROCEDURE purge_tokens IS
  BEGIN
    DELETE FROM valid_tokens
    WHERE  updated_date < SYSDATE - (1/24/4);
    COMMIT;
  END purge_tokens;

END;
/

Enable ORDS

We need to enable ORDS for the TEST schema, associating the path "/ws/" with the schema.

CONN test/test@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'TEST',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'ws',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Create an Authentication Service

The following example defines a POST service that calls the AUTHENTICATE procedure to return a token.

BEGIN
  ORDS.delete_module('auth');
  COMMIT;
END;
/

BEGIN
  ORDS.define_module(
    p_module_name    => 'auth',
    p_base_path      => 'auth/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'auth',
   p_pattern        => 'session/');

  ORDS.define_handler(
    p_module_name    => 'auth',
    p_pattern        => 'session/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[BEGIN
                             auth_api.authenticate(
                               p_username => :username,
                               p_password => :password,
                               p_token    => :token
                             );
                           END;
                        ]',
    p_items_per_page => 0);

  ORDS.define_parameter(
    p_module_name        => 'auth',
    p_pattern            => 'session/',
    p_method             => 'POST',
    p_name               => 'token',
    p_bind_variable_name => 'token',
    p_source_type        => 'RESPONSE',
    p_param_type         => 'STRING',
    p_access_method      => 'OUT'
  );
   
  COMMIT;
END;
/

Let's put some credentials in files so we can post them to the service using the curl command.

cat > /tmp/good_credentials.txt <<EOF
{"username":"tim_hall","password":"MyPassword"}
EOF

cat > /tmp/bad_credentials.txt <<EOF
{"username":"tim_hall","password":"Banana"}
EOF

Now let's try calling the service, passing the credentials.

$ curl -k -X POST --data-binary @/tmp/good_credentials.txt \
       -H "Content-Type: application/json" \
       https://localhost:8443/ords/ws/auth/session/
{"token":"6D21B5097CA90A9AE053020012AC7FE2"}
$

$ curl -k -X POST --data-binary @/tmp/bad_credentials.txt \
       -H "Content-Type: application/json" \
       https://localhost:8443/ords/ws/auth/session/
{"token":"INVALID"}
$

We can see a good token has been returned when we use good credentials, and it is now present in the database.

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
SELECT * FROM valid_tokens;

ID                               UPDATED_DATE
-------------------------------- --------------------
6D2120C0BA020856E053020012AC4CC4 26-MAY-2018 18:42:24

SQL>

Create a Service Requiring Authentication

The following service is really simple, and a little stupid, accepting an authentication token and just returning the system date if the authentication succeeds. The authentication check is performed using the CHECK_TOKEN procedure.

.

BEGIN
  ORDS.delete_module('util');
  COMMIT;
END;
/

BEGIN
  ORDS.define_module(
    p_module_name    => 'util',
    p_base_path      => 'util/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'util',
   p_pattern        => 'date/');
 
  ORDS.define_handler(
    p_module_name    => 'util',
    p_pattern        => 'date/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[BEGIN
                             auth_api.check_token(p_token => :token);
                             :result := SYSDATE;
                           EXCEPTION
                             WHEN NO_DATA_FOUND THEN
                               :status := 'Authentication Failure';
                           END;
                        ]',
    p_items_per_page => 0);
   
  ORDS.define_parameter(
    p_module_name        => 'util',
    p_pattern            => 'date/',
    p_method             => 'GET',
    p_name               => 'result',
    p_bind_variable_name => 'result',
    p_source_type        => 'RESPONSE',
    p_param_type         => 'TIMESTAMP',
    p_access_method      => 'OUT'
  );

  ORDS.define_parameter(
    p_module_name        => 'util',
    p_pattern            => 'date/',
    p_method             => 'GET',
    p_name               => 'status',
    p_bind_variable_name => 'status',
    p_source_type        => 'RESPONSE',
    p_param_type         => 'STRING',
    p_access_method      => 'OUT'
  );
   
  COMMIT;
END;
/

Test the service by retrieving a new authentication token, then using it to call the new service. Also try calling it with an invalid token.

$ curl -k -X POST --data-binary @/tmp/good_credentials.txt \
       -H "Content-Type: application/json" \
       https://localhost:8443/ords/ws/auth/session/
{"token":"6D21B5097CAE0A9AE053020012AC7FE2"}
$

$ curl -k -X GET https://localhost:8443/ords/ws/util/date/?token=6D21B5097CAE0A9AE053020012AC7FE2
{"result":"2018-05-26T20:24:34Z"}
$

$ curl -k -X GET https://localhost:8443/ords/ws/util/date/?token=BANANA
{"status":"Authentication Failure"}
$

For more information see:

• Oracle REST Data Services (ORDS) : Authentication
• Oracle REST Data Services (ORDS) : All Articles

Hope this helps. Regards Tim...

Back to the Top.

Created: 2018-05-26  Updated: 2018-05-26

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer