SOAP signing WS-Security with PL/SQL

Signing SOAP messages conforming to the WS-Security protocol with PL/SQL is performed via the ORA_XML.SOAP_WSS_ methods. This chapter will illustrate the various supported formats.

Table of contents

Signing with keys

Signing with UserName/Password/UserToken

Signing with a Private Key

When signing a SOAP message with a private key we have to load the private key in a BLOB field first.

The signature algorithm and the digest algorithm are specified through the constants in ORA_XML:

ORA_XML.HASH_SHA224
ORA_XML.HASH_SHA256
ORA_XML.HASH_SHA384
ORA_XML.HASH_SHA512

When signing a SOAP message an identifier of the public key is encoded in the message, so the recipient can pick the right public key for the verification process. Usually, this is something that is specified upfront before starting the data exchange with the other party. The supported identifiers are available as constants in ORA_XML:

ORA_XML.IDENTIFIER_SUBJECT_KEY -- #X509SubjectKeyIdentifier 
ORA_XML.IDENTIFIER_BINARY_SECUTITY_TOKEN -- BinarySecurityToken
ORA_XML.IDENTIFIER_ISSUER_SERIAL --  X509IssuerSerial
ORA_XML.IDENTIFIER_X509_KEY -- #X509v3
ORA_XML.IDENTIFIER_THUMBPRINT -- -- #X509ThumbprintSHA1

The method ORA_XML.SOAP_WSS_SIGN expects the private key to be in .pkcs12/.pfx format so the public certificate can be picked from it. Below is a complete example illustrating the signing:

DECLARE
  private_key_file_handle  BFILE;
  private_key  BLOB;  
  signed_soap CLOB;
  soap_data CLOB;
BEGIN    
    private_key_file_handle := BFILENAME('ORACLE_HOME', 'didisoft.inc.pfx');
 
    -- load the key into a BLOB
    DBMS_LOB.OPEN(private_key_file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.createtemporary(private_key, TRUE);
    DBMS_LOB.LoadFromFile( DEST_LOB => private_key,
                         SRC_LOB  => private_key_file_handle,
                         AMOUNT   => DBMS_LOB.GETLENGTH(private_key_file_handle) );
    DBMS_LOB.CLOSE(private_key_file_handle);
 
    soap_data := '...';
 
    signed_soap := ORA_XML.SOAP_WSS_SIGN(soap_message => soap_data,
                                         private_key => private_key,
                                         key_password => 'changeit',
                                         hash => ORA_XML.HASH_SHA256,
                                         key_identifier_type => ORA_XML.IDENTIFIER_SUBJECT_KEY);
 
  DBMS_OUTPUT.put_line('signed_data='||signed_soap);
END;

Verifying with a Public Key

Verifying a signed SOAP message requires that we pass the corresponding certificate (or its public key) to ORA_XML.SOAP_WSS_VERIFY:

DECLARE
  public_key_file_handle  BFILE;
  public_key  BLOB;
  signed_soap CLOB;
  soap_data CLOB;
  verified BOOLEAN;
BEGIN
    public_key_file_handle := BFILENAME('ORACLE_HOME', 'cert.pem'); -- Note: directory name must be Upper case
 
    -- load the key into a BLOB
    DBMS_LOB.OPEN(public_key_file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.createtemporary(public_key, TRUE);
    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);
 
    signed_soap := '...';
 
    verified := ORA_XML.SOAP_WSS_VERIFY(soap_message => signed_soap, public_key => public_key) ;
 
    IF verified THEN
      DBMS_OUTPUT.put_line('Signature verified');      
    ELSE
      DBMS_OUTPUT.put_line('Signature not verified');      
    END IF;
END;

Signing with a UserName/UserToken

Signing a SOAP message can also be done with a username/password. A random UsernameToken element is appended to the signed data, and it is used to derive a key for the authentication using the password.

Shall the password be left inside the signed SOAP or not is specified with the last parameter of the SOAP_WSS_USERNAME_SIGN method:

DECLARE
  username  VARCHAR(255);
  userpass  VARCHAR(255);
  encode_password BOOLEAN;
 
  signed_soap CLOB;
  soap_data CLOB;
BEGIN
    soap_data := '...';
 
    -- when TRUE only a digest of password is encoded, 
    -- when FALSE the password appears inside the message
    encode_password := FALSE; 
 
    username := 'me';
    userpass := 'changeit';
    signed_soap := ORA_XML.SOAP_WSS_USERNAME_SIGN(soap_data, username, userpass, encode_password);
 
    DBMS_OUTPUT.put_line('signed_data=' || signed_soap);
END;

Signing with a Timestamp

An additional Timestamp can be added to the SOAP signature and the timestamp can also have a validity period (in seconds). If we don’t want the timestamp to have an expiration period we can simply put 0:

-- signing with a private key and TimeStamp
signed_sopa := ORA_XML.SOAP_WSS_SIGN_TIMESTAMP(soap_message => '...', 
                                               private_key => key, 
                                               key_password => pass, 
                                               signature => ORA_XML.HASH_SHA256, 
                                               digest => ORA_XML.HASH_SHA256, 
                                               key_identifier_type => ORA_XML.IDENTIFIER_ISSUER_SERIAL, 
                                               seconds_valid => 0); -- valid forever
-- signing with a username/password and TimeStamp
signed_soap := ORA_XML.SOAP_WSS_USERNAME_SIGN_TIMESTAMP(soap_data, 
                                                        username, 
                                                        userpass, 
                                                        encode_password, 
                                                        seconds_valid);

Verifying with Password

Verifying a SOAP message signed with a username/password can be performed by passing the password for the verification:

DECLARE
  userpass  VARCHAR(255);
  signed_soap CLOB;
  verified BOOLEAN;
BEGIN
    signed_soap := '...';
 
    userpass := 'changeit';
    verified := ORA_XML.SOAP_WSS_USERNAME_VERIFY(signed_soap, userpass);    
    IF verified THEN
        DBMS_OUTPUT.put_line('Verified');
    ELSE 
        DBMS_OUTPUT.put_line('Wrong password or signature broken!');
    END IF;
END;

Summary

This article demonstrated SOAP messages signing according to the OASIS Web Services Security protocol. This is a more specific case of the general XML signing with PL/SQL inside the Oracle(c) DB.