Here’s my take on encryption and what it really means in the SAP Sybase IQ world…

 

Encryption is really the means to protect against unwanted access to data.  For SAP Sybase IQ (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 IQ as full database encryption (FDE).  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 IQ. 

 

In order for 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 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.

 

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 IQ is that it is up to the application (or DBA) to manage the keys.  In other engines (ASE) the keys are stored in the database.  In 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.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Uvernes Somarriba

    Nice article!

     

    Do I need an additional licensed option to use full database encryption (FDE)?

     

    Do I need and additional licensed option to use column level encrypts?

     

    When using column level encrypt, I guess the decrypt (using AES_DECRYPT
    functions call) is done at the server level and decrypted data travel on the
    medium, to have a complete secure solution I also need to have transport level
    encryption (like SSL), right?

    (0) 
    1. Mark Mumy Post author

      Full database encryption is included in the base product.

      Column level encryption is part of the Advanced Security Option (priced per core).  Column level encryption and decryption is done in the database.  If you have permissions to write to a table, you will then use the AES_ENCRYPT function to encrypt the data.  If you have read permissions on the table/column you will use the AES_DECRYPT function to see the data in plain text.

      To make a tightly secure system, yes you would want to use column level encryption and transport encryption so that the plain text data is sent back to the client over a secure connection.

      Check out this link for some database examples.

      For IQ 12: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00467.1270/html/iqencryp/BJEEEBIE.htm

      For IQ 15: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01015.1540/doc/html/san1276841309013.html

      For IQ 16: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01774.1600/doc/html/san1276841309013.html

      Mark

      (0) 
      1. Jason Froebe

        I have yet to see a valid reason for data encryption within IQ (data/tables/columns).  Even with encryption, as long as the private keys are handled at the IQ level, it is vulnerable to attack.  Anyone with access to machine and the IQ process can use a debugger and trace encryption and decryption of ‘known data’.  This security issue is common on any application that handles the private keys.

        What can be done?  H*ll if I know.  You could obfuscate the code but that is only a delaying factor.  The best that you could do is restrict access as much as possible, encrypt network traffic, etc. 

        A larger issue is that many of the private keys that are used are ‘self signed’ without strong passwords. Even if they use strong passwords the passwords themselves are often stored somewhere in clear text (e.g. an email, a printed document, a DBA cheatsheet).

        (0) 
  2. Liezl Nel

    Hey Mark,

    Is Column level encryption supported with NLS if we would to get the additional security license?

    As I’ve noticed this – In IQ, the keys must be sent from the application and used directly in the query via AES_ENCRYPT and AES_DECRYPT function calls. 

    L

    (0) 
    1. Mark Mumy Post author

      No, not that I am aware of.  It would mean that BW and HANA would have to know that the column is set for encryption when data is being loaded and queried.  It does not.  If you were to put encryption on after the fact, and outside of BW, it would likely break queries and data loading, too.

      Is this a strong requirement?

      Mark

      (0) 

Leave a Reply