X509.Certificate propeties

OraRSA can inspect some properties from an X.509 certificate which may be useful in some scenarios, for example in JWT headers where information for the verification key can be specified. In this article, we will observe the methods provided by ORA_RSA for this.

Functions

Serial number of an X.509 certificate in decimal format

FUNCTION X509_SERIAL(public_key RAW) RETURN VARCHAR2

Serial number of an X.509 certificate in hexadecimal format

FUNCTION X509_SERIAL_HEX(public_key RAW) RETURN VARCHAR2

SHA-1 thumbprint of an X.509 certificate

FUNCTION X509_SHA1_THUMBPRINT(public_key RAW) RETURN VARCHAR2

SHA-256 thumbprint of an X.509 certificate

FUNCTION X509_SHA256_THUMBPRINT(public_key RAW) RETURN VARCHAR2

Certificate chain of an X.509 certificate (Base-64 encoded)

FUNCTION X509_CHAIN_BASE64(public_key RAW) RETURN CLOB

Modulus of an RSA public key (Base-64 encoded)

FUNCTION RSA_KEY_N_BASE64(public_key RAW) RETURN VARCHAR2

Public exponent of an RSA public key (Base-64 encoded)

FUNCTION RSA_KEY_E_BASE64(public_key RAW) RETURN VARCHAR2

Common JWT headers

These are some of the common JWT header fields and their relation to the above X.509 certificate properties:

  • x5t – The X.509 certificate SHA-1 thumbprint (ORA_RSA.X509_SHA1_THUMBPRINT)
  • x5t256 – The X.509 certificate SHA-256 thumbprint (ORA_RSA.X509_SHA256_THUMBPRINT)
  • x5u – The X.509 serial number (ORA_RSA.X509_SERIAL or X509_SERIAL_HEX)
  • x5c – The X.509 certificate chain (ORA_RSA.X509_CHAIN_BASE64)
  • n – Modulus of the RSA key (ORA_RSA.RSA_KEY_N_BASE64)
  • e – Public exponent of the RSA key (ORA_RSA.RSA_KEY_E_BASE64)

Example code

The example PL/SQL block below illustrates the usage of the above methods:

DECLARE
  public_key_file_handle  BFILE;
  public_key  BLOB;  
BEGIN
    -- initialize the public key BLOB storage
    DBMS_LOB.createtemporary(public_key, TRUE);
 
    public_key_file_handle := BFILENAME('KEYS_DIR', 'DidiSoftEood.crt'); 
 
    -- load the key into a BLOB
    DBMS_LOB.OPEN(public_key_file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( DEST_LOB => public_key,
                         SRC_LOB  => public_key_file_handle,
                         AMOUNT   => DBMS_LOB.GETLENGTH(public_key_file_handle) );
    DBMS_LOB.CLOSE(public_key_file_handle);
 
    DBMS_OUTPUT.put('X509 certificat serial number:  ');
    DBMS_OUTPUT.put_line(ORA_RSA.X509_SERIAL(public_key));
    DBMS_OUTPUT.put('X509 SHA-1 thumbprint (base-64):  ');    
    DBMS_OUTPUT.put_line(ORA_RSA.X509_SHA1_THUMBPRINT(public_key));
    DBMS_OUTPUT.put('X509 SHA-256 thumbprint (base-64):  ');    
    DBMS_OUTPUT.put_line(ORA_RSA.X509_SHA256_THUMBPRINT(public_key));            
    DBMS_OUTPUT.put('X509 chain (base-64):  ');    
    DBMS_OUTPUT.put_line(ORA_RSA.X509_CHAIN_BASE64(public_key));    
 
END;