Sandy's simple note

My Insight

,

Oracle UDF For Thales CipherTrust Tokenization

Hello there!

I just continue what i have done in my prior note, i did create some simple HTTP JSON POST written in C/C++, you may find it here.

Today, i’m going to explain, the process and creating Oracle UDF to execute HTTPS in Oracle to Thales CipherTrust Tokenization (CT-VL).

Before you begin, ensure your APEX is installed in your Oracle database and setup your Oracle Wallet for each HTTPS where data interchange is required, please see my previous note for more detail here.

Oracle APEX provides you with all the tools you need to build apps in a single, extensible platform, which runs as a part of Oracle Database. For APEX installation you may find it here and download the APEX here.

1. START YOUR ORACLE DATABASE

First, login to your database by using sysdba account and run the startup command to boot-up Oracle database. Wait for a while, until database properly mounted and opened.

[oracle@oracle ~]$ sqlplus / as sysdba 

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 30 20:18:36 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area 1191181696 bytes
Fixed Size		    8895872 bytes
Variable Size		  436207616 bytes
Database Buffers	  738197504 bytes
Redo Buffers		    7880704 bytes
Database mounted.
Database opened.
2. START THE LISTENERS

Database is already mounted and opened as in above step. But it is only database part. Now, we need to start the lsnrctl to access our database from client side as well.

What is lsnrctl? lsnrctl is a utility provided by Oracle that enables you to start, stop, and monitor the status of an listener, as well as to modify its configuration while it’s running. A listener is essential for facilitating connections from remote clients to the Oracle database over a network.

[oracle@oracle ~]$ lsnrctl start

Below is the complete command…

[oracle@oracle ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-JUN-2024 20:23:35

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3/db_home/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3/db_home/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORACLE.localdomain)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=0.0.0.0)(PORT=5500))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORACLE.localdomain)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORACLE.localdomain)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORACLE.localdomain)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                30-JUN-2024 20:23:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3/db_home/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORACLE.localdomain)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=0.0.0.0)(PORT=5500))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORACLE.localdomain)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORACLE.localdomain)))
The listener supports no services
The command completed successfully

As you can see above, it said the command completed successfully. That means, your client side able to access Oracle database by using port 1521 which that port is Oracle Net Listener.

3. CREATE A PROCEDURE

To prevent repetition code over and over again, you need store credential information on your procedure syntax. Here is an example of a procedure statement in Oracle database.

CREATE OR REPLACE PROCEDURE token_auth(usr out varchar2, cred out varchar2)
is
begin
    -- https://www.sandy.id
    -- this is to hold a parameter for credentials for our CT-VL
    usr  := 'your_ctvl_user';  -- CT-VL user
    cred := 'your_ctvl_password'; -- CT-VL password
end token_auth;

CREATE OR REPLACE PROCEDURE token_auth_with_mask(usr out varchar2, cred out varchar2)
is
begin
    -- https://www.sandy.id
    -- this is to hold a parameter for credentials for our CT-VL with masking feature  
    usr   := 'your_ctvl_masking_user';      -- CT-VL user
    cred  := 'your_ctvl_password';          -- CT-VL password
end token_auth_with_mask;

CREATE OR REPLACE PROCEDURE wallet_auth(walletFile out varchar2, walletPass out varchar2)
is
begin
    -- https://www.sandy.id
    -- this is to hold a parameter for Oracle Wallet Manager (OWM)
    walletFile := '/your/wallet/path';
    walletPass := 'your_wallet_password';
end wallet_auth;
4. CREATE A TOKENIZATION FUNCTION
CREATE OR REPLACE FUNCTION TOKEN_ALPHANUM(theInput in varchar2)
return varchar2
is
    /***
    -- https://www.sandy.id
    ***/
   
    cts_req utl_http.req;
    cts_resp utl_http.resp;
    
    --define the url   
    cts_url varchar2(1000) := 'https://your_cts_url/vts/rest/v2.0/tokenize';
    
    cts_username varchar2(30);
    cts_password cts_username%type;

    --specify the template here
    cts_msg_beg varchar2(100) := '{"tokengroup" : "the_group_name", "data" : "';
    cts_msg_end cts_msg_beg%type := '", "tokentemplate" : "token_template_name" }';

    cts_line varchar2(100);
    cts_token cts_url%type; 
    v_content cts_url%type;
    
    wallet_file varchar2(200);
    wallet_pass varchar2(50);
    
    
begin

    -- initiate the token_auth and wallet auth from procedure to prevent loop
    token_auth(usr => cts_username, cred => cts_password);
    wallet_auth(walletFile=>wallet_file, walletPass=>wallet_pass );

    --authenticate with oracle wallet
    utl_http.set_wallet('file:' || wallet_file, wallet_pass);
  
    v_content := cts_msg_beg || theInput || cts_msg_end;
    cts_req := utl_http.begin_request(cts_url, 'POST', 'HTTP/1.1');
    
    --authenticate to our CT-VL
    utl_http.set_authentication(cts_req, cts_username, cts_password);
    
    --setting header with utl_http.set_header()
    utl_http.set_header(cts_req, 'user-agent', 'mozilla/4.0');
    utl_http.set_header(cts_req, 'content-type', 'application/json');
    utl_http.set_header(cts_req, 'Content-Length', length(v_content));
    
    -- calling utl_http.write_text()
    utl_http.write_text(cts_req, v_content);
    
    -- collecting response with utl_http.get_response()
    cts_resp := utl_http.get_response(cts_req); 
    
    -- Process the response from the HTTP call
begin
    utl_http.read_line(cts_resp, cts_token);
loop
    utl_http.read_line(cts_resp, cts_line);
    cts_token := cts_token || trim(cts_line);
end loop;
    utl_http.end_response(cts_resp);
exception
    when utl_http.end_of_body then
    utl_http.end_response(cts_resp);
    
    -- get the data and parse it into desired json key
    apex_json.parse(cts_token);
    cts_token := apex_json.get_varchar2('token');

    when OTHERS then
        dbms_output.put_line('[ERROR]: ' || sqlerrm);
    end;
    return cts_token;
exception
    when OTHERS then
        dbms_output.put_line('[ERROR]: ' || sqlerrm);
    return 'ORA'||sqlcode;
end TOKEN_ALPHANUM;
5. EXECUTE THE QUERY

Ok, now call the UDF and execute the query, as simple as : 

SELECT TOKEN_ALPHANUM(Name) AS NAME,Name FROM YOUR_TABLE;

Here is the complete one.

[oracle@oracle ~]$ sqlplus sandy/your_password@oracle.localdomain

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 1 18:36:13 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name: sandy
Enter password: 
Last Successful login time: Mon Jul 01 2024 18:27:28 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT * FROM TABLE1;

YEAR		     MONTH		  NAME			      PAN
-------------------- -------------------- -------------------- ----------
2020		     11 		  jonsnow	       3.7828E+14
2021		     8			  jamielannister   4.0129E+15
2021		     8			  nedstark	       5.1051E+15

SQL> SELECT TOKEN_ALPHANUM(Name) AS NAME,Name FROM TABLE1;       

NAME
--------------------------------------------------------------------------------
NAME
--------------------
U1sg4oi
jonsnow

tK13fDbTQZAzGb
jamielannister

6h9Wrgmm
nedstark

It appears above results are unclear. I will use Oracle Developer to obtain a clearer outcome.

Finally, your Oracle UDF has been executed to Thales CipherTrust Tokenization via HTTPS in your Oracle database.

That’s all folks! Happy coding! 🥰😍

Note : Tested on Oracle 19c

Views: 265
Previous
Next

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts