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;
/
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.
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
If we try to run the request using the specified username and password, we should receive the following output
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
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: