256-bit Encryption for ENCRYPT_AES SQL Function

This enhancement was delivered in November 2023 with IBM i 7.5 PTF Group level 5 and IBM i 7.4 PTF Group level 26.

A new built-in scalar function, ENCRYPT_AES256, has been added to SQL.It uses the AES encryption algorithm with a 256-bit encryption key.

You can read about it on the fact page here: https://www.ibm.com/support/pages/node/7031285
And in IBM Documentation here: https://www.ibm.com/docs/en/i/7.5?topic=functions-encrypt-aes256

Db2 for i development team
IBM Power Systems Development

I think the function provided with ENCRYPT_AES should be enhanced to be more in line with the capabilities of Qc3EncryptWithMAC API. The SQL function is actually pretty weak now that I see what the APIs can do that the SQL functions do not do. In particular:

* The API allows a random nonce to be provided so that when encrypting the same value multiple times, you don't get the same encrypted value for the same clear text. Attackers can see that a message is being repeated when the SQL approach is used, which reveals information. The SQL function could either accept a Nonce parameter or generate a random one (using a crypto class random number generator !)

* The APIs support the MAC (Message Authentication Code) which protects against an attacker tweaking the cipher text and still allowing it to be decrypted with a slightly modified clear text result. (Classic example being able to change a dollar amount in a banking transaction.) The API will refuse to decrypt a message that has been altered. The SQL function will happily do so, producing a degree of gibberish in the result (if only trailing few bytes are changed I believe only trailing 16 chars are affected at most, based upon my experimentation.)

The documentation for the SQL encrypt functions really ought to layout the lack of strong security and new stronger SQL functions more closely matching the APIs above should be created.

You should also document what AES mode is being used -- For example, in some AES modes, if the same 16 byte are repeated in the clear text, then the same 16 bytes will be repeated in the encrypted value. Other modes address this issue and should be used.

The CAAC has reviewed this requirement and recommends that IBM view this as a high priority requirement that is important to be addressed. Having better Security options is desirable.

Background: The COMMON Americas Advisory Council (CAAC) members have a broad range of experience in working with small and medium-sized IBM i customers. CAAC has a key role in working with IBM i development to help assess the value and impact of individual RFEs on the broader IBM i community, and has therefore reviewed your RFE.

For more information about CAAC, see www.common.org/caac

For more details about CAAC's role with RFEs, see http://www.ibmsystemsmag.com/Blogs/i-Can/May-2017/COMMON-Americas-Advisory-Council-%28CAAC%29-and-RFEs/

Nancy Uthke-Schmucki - CAAC Program Manager

Due to processing by IBM, this request was reassigned to have the following updated attributes:
Brand - Servers and Systems Software
Product family - Power Systems
Product - IBM i
Component - Db2 for i
Operating system - IBM i
Source - None

For recording keeping, the previous attributes were:
Brand - Servers and Systems Software
Product family - Power Systems
Product - IBM i
Component - Application Development
Operating system - IBM i
Source - None