Here’s my take on encryption and what it really means in the SAP IQ world… Side note, while all of this is option for SAP IQ (on-premise) it is required for the cloud variation of IQ called SAP HANA Cloud, data lake relational engine.
Encryption is really the means to protect against unwanted access to data. For SAP IQ, though, this comes in many different flavors, from different access paths.
First, you have network encryption. This will cover unwanted access to data in flight from server to client and back for those that happen to be sniffing the network packets. IQ transport layer encryption will cover all unwanted access to data in flight between client and server. That ‘in-flight’ can be the traditional over the network style or same host client server communication where it is done via shared memory or by only partially traversing the network stack.
Second, you have the encryption of data in the database from someone who only has access to the storage. Call this ‘at rest’ encryption. This is implemented in SAP IQ as full database encryption (FDE). Generically, this is may be referred to as Transparent Data Encryption or TDE. It protects against a user who has solely has access to the storage and can somehow read direct from disk. FDE will guarantee that what is at rest on disk cannot be read by a program outside of SAP IQ. For more details see the following manuals and SAP Notes.
Database Encryption and Decryption
SAP Note 2611853 - How to encrypt the database - SAP IQ
SAP Note 2228977 - How to use a database encryption key - SAP IQ
In order for SAP IQ to read the data, the key is used at start-up to unlock the data for its use. The algorithm that is used is also not published, so even access to the key won’t allow full access to the data. FDE applies to all tables, columns, and indexes in SAP IQ. Consequently, all read and write operations will be impacted by FDE. This level of encryption has no security inside the database. If a user has SELECT permission from an object, the data can be read in plain text.
The one downside to full database encryption is that it must be defined when the database is created and it cannot be disabled. Additionally, the encryption key cannot be changed or rotated once the database is created. In order to enable or disable encryption or to rotate the key, you must build a new database and migrate your objects, data, and security to the new server.
Lastly, you have column level encryption. This is a surgical approach to at rest encryption as described previously where one simply encrypts the columns with sensitive data, not all columns. Column encryption also prevents unwanted access to sensitive data. Data access is still controlled by object permissions and role based access controls; you must have SELECT permission on the column to view it. A second security step is involved, though. The data that is read is gibberish unless the user possesses the key to unlock the encryption algorithm. If the user possesses the proper key, data can then be read in plain text for that user.
The downside to column encryption in SAP IQ is that it is up to the application (or DBA) to manage the keys. In other engines (SAP ASE) the keys are stored in the database. In SAP IQ, the keys must be sent from the application and used directly in the query via AES_ENCRYPT and AES_DECRYPT function calls. These calls can be hidden within a view so that the DBA can manage the keys inside the database rather than in the application. The overhead for column encryption is only on those reads and writes that need to access encrypted columns. Because of the focused nature of column encryption, overall system overhead is much smaller compared to full database encryption.
So, the answer to the question of which encryption is best and which do I need really depends on what the business needs are.
Do you need to protect data in flight? You need transport layer encryption, typically with stronger 256-bit AES encryption.
Do they need to protect data at rest on disk against someone outside the database reading it (solely at the OS level)? Full database encryption, defined when the database is created, is needed.
Or do they need to protect the data from all users inside or outside the database? Column level encryption is the best choice here. It can be turned on or off at will and as the business needs change.
Lastly, performance is secondary. I have yet to experience a customer that wants security, but is willing to sacrifice that security for performance. The customers that I have worked with make sure that security is in place first then worry about performance.
