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.