Oracle APEX – Let’s integrate with JIRA! Part 2

11 September 2019, Lech Cieślik

Jira is Atlassian’s software for error tracking and project management.  Users working with any APEX application often need to report an error regarding the application usage or data they work with. Instead of logging in to Jira, looking for a specified project, using a wizard that will allow to create a new request (name, type, description, request assignment, etc.), the user can easily use a solution implemented fully in Oracle APEX, which we will introduce step by step in a new Oracle APEX article series – Let’s integrate with Jira!

In the examples below we will use the Oracle Database 12c Enterprise Edition database and the Oracle SQL Developer Version 18.2. tool. 

This article is the second in a series of entries in which we describe the integration of Oracle APEX with Jira. In the first article we described in detail the authorization standard used by Jira – OAuth 1.0. We talked about so-called “OAuth Dance”, we presented examples of requests to get the right type of tokens, and we mentioned the encryption algorithms used to sign the specified requests. In this article, we’ll focus on implementing the digital signature algorithm and using Java in Oracle.

Java in Oracle

As we mentioned in our previous article, Jira imposes upon us the constraint of using solely the SHA1-RSA algorithm for encryption and digital signing. PL/SQL libraries don’t support its implementation, so we had to find another solution. To allow us to use SHA1-RSA in Oracle we will have to turn to Java, another language whose use is supported by Oracle database, and use the available Java implementation of SHA1-RSA. Before we proceed to the implementation of the digital signature using SHA1-RSA we will present a simple example of creating your own Java class in Oracle. 

The Oracle database supports Java from version 8i and its use is very simple. One method to create your own class is to use the instructions below:

In this way, we created the Example class containing the exampleMethod method which takes a String object as a parameter and returns it. All Java objects can be found in the side menu of the Oracle SQL Developer tool in the Java tab.

Next, we create a PL/SQL function that will allow us to use the previously created class:

The f_example function can be used anywhere in PL/SQL block code:

SHA1-RSA in PL/SQL

Since we now know how easy it is to create a Java classes and methods, and call them from PL/SQL code, we can move on to the more difficult issue of creating a digital signature using the SHA1-RSA algorithm. We wrote more about the digital signature itself and its use in so-called “OAuth Dance” in the previous article in this series.

A small reminder. OAuth 1.0 is an authorization standard that allows you to share information stored with other providers through token exchange. The token request must be signed with the private key (located on the side of our application), and then verified with the public key (located on the side of Jira). And most important, the signature algorithm must be implemented using the SHA1-RSA algorithm. 

You can generate private and public keys in many ways. We used OpenSSL for this article. It is an open implementation of SSL protocols and various cryptographic algorithms, available for Linux and Microsoft Windows systems. Key generation and configuration of Atlassian Jira are described in detail on the official Atlassian website: https://developer.atlassian.com/server/jira/platform/oauth/

To use the SHA1-RSA method in PL/SQL we will create our own class “RSASignature” which will use the “java.security.*” library namespace. The class will contain a “sign(String, String)” method which will take the base string and a private key as parameters and which will return an encrypted signature that can be used in “OAuth Dance”.

In addition, the “RSASignature” class implements the conversion of private and public keys from “String” objects to “PrivateKey” and “PublicKey” objects, and signature verification. All code is available at: https://github.com/Pretius/java-rsa-signature

Having prepared the required Java code, we will create a Java Source object according to the method described earlier. We then define the function wrapper, the method calling our Java functions through PL/SQL code as the “signRSA” function:

Summary

This article describes how to use Java in Oracle. Then we looked at key generation and digital signature implementation using SHA1-RSA algorithm, implemented in Java. The created signature can be used during the authentication process, which was described in the first article of the series. The next and last entry will be dedicated to using the Jira API to create your own Jira request.

Tagged with: , , , , ,

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!