DALL·E 2024-03-22 15.25.59 - Design a header image that centers around the Oracle REST Data Services (ORDS) icon, prominently featuring the stylized database symbol in white at th

Rest API Basic Authentication

Now we have the following true actions defined for our Dynamic Action.

Introduction

ORDS does not support Basic Authentication in the sense of calling a web service with a non-static username and password. However, this requirement exists and must be implemented in practice. Authentication methods that rely on a database schema or Basic Authentication with hardcoded usernames/passwords at the Webserver level are eventually not an option. In this example, we explain how this can still be set up in practice using a small workaround.

Understanding the Security Risks

In our case, the service is not strictly protected because it can be called at any time. In an authenticated service, the credentials are first checked, and only if they are valid, the actual service is called. In the example below, this is not the case – the service is called, and within the process, the provided information about the credentials is checked. If they are correct, the actual web service is invoked. If they are not correct, an exception is thrown, and the corresponding HTTP error code is returned.

Description of our Scenario

We want to provide a POST service that gives us a JSON file of the employees from the EMP table. We expect the caller to provide a username and a password. In our example, we check this information hardcoded in the package. However, it is easy to imagine that the provided function could be more extensive and, for example, perform a dynamic check of the credentials – possibly against Active Directory or another authentication source.

Solution Overview - Package

Let us create a package to handle our POST-Request.

				
					create or replace PACKAGE rest_basic_auth_pkg AS
  

    PROCEDURE get_emp_json (
        p_json          OUT VARCHAR2,
        p_status_code   OUT VARCHAR2,
        p_error_message OUT VARCHAR2
    );

END rest_basic_auth_pkg;
/
				
			
				
					CREATE OR REPLACE PACKAGE BODY rest_basic_auth_pkg AS
    -- This function checks the hardcoded username / password
    -- it can be any function to any authentication service (LDAP / Active Directory)
    FUNCTION check_username_password (
        p_username IN VARCHAR2,
        p_password IN VARCHAR2
    ) RETURN VARCHAR2 IS
    BEGIN
        IF
            upper(p_username) = 'REST_API_TEST'
            AND p_password = 'P04\wUQzYEAVQWC'
        THEN
            RETURN NULL;
        ELSE
            RETURN 'Invalid Username / Password.';
        END IF;
    END check_username_password;
    
   -- This procedure exctracts the information about the authentication method
   -- If the Authentication Method is Bacis, it will try to extract the information
   -- about the username and password
   -- If the Authentication Method is Basic, it will return TRUE, FALSE if not
    PROCEDURE authenticate_basic (
        p_username OUT VARCHAR2,
        p_password OUT VARCHAR2,
        p_method   OUT BOOLEAN
    ) IS

        v_authorization VARCHAR2(400);
        v_raw           RAW(32767);
        v_base64        VARCHAR2(4000);
        v_token         VARCHAR2(400);
    BEGIN
        v_authorization := owa_util.get_cgi_env('Authorization');
        IF v_authorization LIKE 'Basic%' THEN
            v_authorization := ltrim(
                                    v_authorization,
                                    'Basic '
                               );
            v_raw := utl_raw.cast_to_raw(v_authorization);
            v_raw := utl_encode.base64_decode(v_raw);
            v_base64 := utl_raw.cast_to_varchar2(v_raw);
            v_token := v_base64;
            p_username := substr(
                                v_token,
                                1,
                                instr(
                                     v_token,
                                     ':'
                                ) - 1
                          );

            p_password := substr(
                                v_token,
                                instr(
                                     v_token,
                                     ':'
                                ) + 1
                          );
            p_method := TRUE;
        ELSE
            p_method := FALSE;
        END IF;

    END authenticate_basic;
    
    -- This procedure returns a Json-Output for the EMP Table
    PROCEDURE create_json (
        p_json OUT VARCHAR2
    ) IS
        v_cursor SYS_REFCURSOR;
    BEGIN
        OPEN v_cursor FOR SELECT
                              e.empno  AS "employee_number",
                              e.ename  AS "employee_name",
                              e.deptno AS "department_number"
                          FROM
                              emp e;

        apex_json.initialize_clob_output;
        apex_json.open_object;
        apex_json.write(
                       'employees',
                       v_cursor
        );
        apex_json.close_object;
        p_json := JSON_QUERY(apex_json.get_clob_output,
           '$' RETURNING VARCHAR2 ( 4000 ) pretty);
        apex_json.free_output;
    END create_json;
    
    -- This procedure is used in the POST-Service call
    PROCEDURE get_emp_json (
        p_json          OUT VARCHAR2,
        p_status_code   OUT VARCHAR2,
        p_error_message OUT VARCHAR2
    ) IS

        v_username VARCHAR2(400);
        v_password VARCHAR2(400);
        v_method   BOOLEAN;
        invalid_authentication_method EXCEPTION;
        invalid_credentials EXCEPTION;
    BEGIN
        authenticate_basic(
                          v_username,
                          v_password,
                          v_method
        );
        IF NOT v_method THEN
            RAISE invalid_authentication_method;
        ELSE
            p_error_message := check_username_password(
                                                      v_username,
                                                      v_password
                               );
            IF p_error_message IS NOT NULL THEN
                RAISE invalid_credentials;
            END IF;
        END IF;

        create_json(p_json);
    EXCEPTION
        WHEN invalid_authentication_method THEN
            p_status_code := 405;
            p_error_message := 'Invalid Authentication Method.';
        WHEN invalid_credentials THEN
            p_status_code := 401;
        WHEN OTHERS THEN
            p_status_code := 500;
            p_error_message := 'Internal Server Error. Please contact your administrator.';
    END get_emp_json;
    
END rest_basic_auth_pkg;
/
				
			

Solution Overview - Webservice

Let us create a POST-Webservice for downloading Json-File.

				
					BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'WKSP_BLOG',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'trn_apex23',
      p_auto_rest_auth      => FALSE);    

  ORDS.DEFINE_MODULE(
      p_module_name    => 'basic_auth',
      p_base_path      => '/kubicek-consulting-blog/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => 'This is a basic authentication example.');      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'basic_auth',
      p_pattern        => 'emp_json',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'basic_auth',
      p_pattern        => 'emp_json',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
    v_json          VARCHAR2(32767);
    v_status_code   VARCHAR2(400);
    v_error_message VARCHAR2(400);
    v_exception EXCEPTION;
BEGIN
    rest_basic_auth_pkg.get_emp_json(v_json, v_status_code, v_error_message);
    IF v_status_code IS NOT NULL THEN
        RAISE v_exception;
    ELSE
        htp.prn(v_json);
    END IF;

EXCEPTION
    WHEN v_exception THEN
        :string_out := v_error_message;
        :status_code := v_status_code;
END;'
      );
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'basic_auth',
      p_pattern            => 'emp_json',
      p_method             => 'POST',
      p_name               => 'message',
      p_bind_variable_name => 'string_out',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);      
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'basic_auth',
      p_pattern            => 'emp_json',
      p_method             => 'POST',
      p_name               => 'X-ORDS-STATUS-CODE',
      p_bind_variable_name => 'status_code',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);      
      
  COMMIT; 
END;
/
				
			

Please note: There are two parameters in our method. The first one is used to display the message at the specific position and the other one to show the status code.

Oracle APEX Rest API Basic Authentication

Testing the Webservice

If we now use for example Postman, we can test our web service. We use the following URL:

https://blog.kubicek-consulting.de/ords/kc/kubicek-consulting-blog/emp_json

For the testing purposes, we have set up the following credentials:

  – Username: REST_API_TEST

  – Password: P04\wUQzYEAVQWC

Kubicek-consulting_blog_Rest_API_Basic_Authentication_Bild3

If we try to run the request using the specified username and password, we should receive the following output

Kubicek-consulting_blog_Rest_API_Basic_Authentication_Bild4

If we try to use a different username or password and we run the request, the request should fail, and we should receive the following output

Kubicek-consulting_blog_Rest_API_Basic_Authentication_Bild5

This example does not adhere to recommended practices. However, it is a way to still provide solutions when no other alternative is available. It is always necessary to weigh to what extent this solution aligns with the security requirements in the company.

You can view the example in our Blog-Application here:

Comments are closed.