Create pgp digital signatures in PL/SQL

In this chapter we will show how to perform OpenPGP signing inside the Oracle(c) database with the help of DidiSoft OraPGP.

OpenPGP signed content holds the data in unencrypted format combined with a digital signature for verification of both the data and the sender of the data. The digital signature is created with the private key of the sender. The password of the private key is also needed in order to unlock the private key.

1. Signing VERCHAR2 and CLOB
2. Signing BLOB
3. Invoking from Java

Signing VERCHAR2 data

The example below creates an OpenPGP signed data taking input as a CLOB field. As with the other methods of the ORA_PGP package the key can be provided either as an absolute file path, or as in ASCII armored format.

Note that everywhere where the variables are of type CLOB we can replace them with VARCHAR2 ensuring there is enough capacity.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DECLARE
  MESSAGE CLOB;
  PRIVATE_KEY CLOB;
  KEY_PASSWORD CLOB := NULL;
  v_Return CLOB;
BEGIN
  MESSAGE := 'Hello World!';
  PRIVATE_KEY := 'c:\Projects\PGPKeys\empty_pass_private.asc';
  KEY_PASSWORD := NULL; -- in this example we use an empty password
 
  v_Return := ORA_PGP.SIGN(
    MESSAGE => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => KEY_PASSWORD
  );
 
 DBMS_OUTPUT.PUT_LINE('Signed data = ' || v_Return);
END;
/

Signing BLOB

Signing a BLOB field is very similar to signing a VARCHAR2 field. We will use the ORA_PGP.SIGN_BLOB method which also allows to specify through its last parameter shall the output be binary (FALSE) or ASCII armored (TRUE).

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

DECLARE
  ascii_armor_output BOOLEAN := TRUE;
  MESSAGE BLOB;
  PRIVATE_KEY VARCHAR2(200);
  KEY_PASSWORD VARCHAR2(200);
  v_Return BLOB;
BEGIN
  MESSAGE := NULL;
  PRIVATE_KEY := 'c:\PGPKeys\private_key.asc';
  KEY_PASSWORD := 'key password';
 
  SELECT data_field INTO MESSAGE
  FROM my_data_table
  WHERE my_id = 1000;
 
  v_Return := ORA_PGP.SIGN_BLOB(
    message => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    KEY_PASSWORD => KEY_PASSWORD,
    ascii_armor => ascii_armor_output,
  );
 
  -- v_Return now contains the OpenPGP signed data
END;

Invoking from Java

Here is an example how we can call the signing PL/SQL code from java:

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

import java.sql.*;
 
public class OraPGPTests {
 
	private static final String JDBC_ORACLE_CONNECTION = "jdbc:oracle:thin:@localhost:1521:orcl";
 
	public void testSignString() throws SQLException {
		DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
 
		Connection conn = DriverManager.getConnection
				  (JDBC_ORACLE_CONNECTION, "user", "pass");		
 
		String dataString = "Hello World!";
 
		// ENCRYPT_STRING
		CallableStatement cstmt = conn.prepareCall("{?=call ORA_PGP.SIGN(?,?,?)}");
		cstmt.registerOutParameter(1, Types.VARCHAR);
		cstmt.setString(2, dataString);
		cstmt.setString(3, "c:\\Projects\\PGPKeys\\private.key");
		cstmt.setString(4, "key password");
		cstmt.executeUpdate();
		String signedString = cstmt.getString(1);
 
		System.out.println(signedString);
	}
}

 

Summary

This chapter illustrated how to produce digitally signed OpenPGP content inside the Oracle(c) database.