DBA Hub

📋Steps in this guide1/7

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

oracle miscconfigurationintermediate
by OracleDba
13 views
1

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

Create Test User

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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;
3

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 user before you continue. - Storing Passwords in an Oracle Database With that in place let's create a user to test with.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
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>
4

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 function, which we will store as a rather than a column. The following package manages the tokens. When a service calls the procedure they are returned a token, which has been added to the table. The 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 procedure can be called from a database job to clean up the table and stop is expanding endlessly with old tokens.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
--DROP TABLE valid_tokens PURGE;

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

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

Enable ORDS

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;
/
6

Create an Authentication Service

The following example defines a service that calls the procedure to return a token. Let's put some credentials in files so we can post them to the service using the command. Now let's try calling the service, passing the credentials. We can see a good token has been returned when we use good credentials, and it is now present in the database.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
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;
/

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

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

$ 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"}
$

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

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 procedure. 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. For more information see: - Oracle REST Data Services (ORDS) : Authentication - Oracle REST Data Services (ORDS) : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
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;
/

$ 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"}
$

Comments (0)

Please to add comments

No comments yet. Be the first to comment!