In the series titled "A Deeper Look At Sybase", I'd like to look at one specific feature of Sybase ASE for data security.
One of the many features of Sybase ASE is the ability to encrypt individual columns of a database table.
With Sybase's footprint in the financial industry, it is not surprising that Sybase customers require an in-database solution for data security. But frankly, it is hard to imagine any real-life system today where data security would not be important.
Indeed, data encryption may sound like a no-brainer: who would not want to improve his data security? But as we'll see, making such functionality available in a way that is practically usable in real-life systems is in fact not trivial at all.
First, let's observe that most data items in a database are usually not very sensitive. Typically, there are only a few columns worth protecting. Common examples are a credit card number (for obvious reasons), or (in the USA) a Social Security Number (since these are targets for identity thieves). You can probably think of a few other ones.
In contrast, things like an address or ZIP code are usually less sensitive. Although admittedly, depending on the context in which data is used, that could be different.
By encrypting only those columns that need protecting, we can avoid wasting computing resources. If we simply encrypted the entire database instead, this would cost more CPU (for encrypt/decrypt) and storage (since encrypted data occupies more space) than necessary for only the sensitive table columns.
One of the reasons why Sybase customers use ASE column encryption is the PCI DSS standard (short for "Payment Card Industry Data Security Standard"). At some point, the credit card industry got tired of indemnifying customers whose credit card details got stolen from someone's database, and they mandated that if you were to store details about their credit cards, you had to protect the data against theft. The PCI DSS standard lays out a number of measures you must take in order to comply. One aspect of the standard is in-database encryption of credit card data (more details about PCI are here).
In-database column encryption in Sybase ASE was designed to meet all of the following goals:
- Use industry-strength data encryption (AES128 & AES256).
- Column encryption is specified as a declarative, schema-level attribute.
- Encryption and decryption of data must be fully transparent to existing applications: no SQL should have to be changed in any application when encrypting a column.
- When encrypting a column, this should have minimal or no performance impact for database queries.
- Transactions involving encrypted columns can be replicated (with Sybase Replication Server) to remote ASE databases without ever decrypting the encrypted data.
- End-users should not require any additional passwords or passphrases to access encrypted data.
In addition, based on customer feedback on the first version of ASE column encryption, an additional requirement surfaced that was implemented in ASE version 15.0.2. Customers wanted to be able to protect data from being accessed by the all-powerful database administrators (DBA), who traditionally have full access rights to any data (makes you wonder: what did those DBAs do to earn this sort of confidence from their paymasters? I know I'm innocent!)
At first sight, some of the above goals would seem to be contradictory. It sounds almost too good to be true, but ASE's column encryption does indeed achieve all of the above. Rather than discussing all technical aspects in full detail, let me just give an example.
Ignoring some one-time configuration steps, the first thing to do is to create an encryption key. This creates an AES 256-bit key named cc_key:
create encryption key cc_key for AES with keylength 256
You can create as many keys as you like. Keys are generated inside the ASE server through certified 3rd-party crypto libraries. Also, the generated key is stored in the ASE database -- but it is itself encrypted first (simply put, with a database-level encryption key entered by the DBA). This way, the actual encryption itself cannot be easily extracted from the database which makes it much more difficult for bad-character folks to do any damage.
An encryption key is used to encrypt a column. Here, we're encrypting only the credit card number. In this example, the card type and the name on the card are not considered sensitive and therefore not encrypted. However, those columns could simply be encrypted as well (with the same encryption key or with a different one):
create table Ccard (
custname char(30),
cardtype char(10),
cardno char(19) encrypt with cc_key
decrypt_default 'xxxx-xxxx-xxxx-xxxx' )
I'll get back to the decrypt_default clause in a minute.
First, an essential part of ASE's encryption solution is a separate decrypt permission which is required in order to read encrypted data. This permission should obviously only be assigned to those users or roles that need this access.
For example:
grant select on Ccard to public
grant decrypt on Ccard(cardno) to fraud_detection_role
In this example, only users who have been granted the role fraud_detection_role will now be allowed to decrypt the encrypted credit card numbers.
At this point, any queries against the Ccard table will automatically encrypt the credit card number value when inserting a new row, and automatically decrypt it when an authorised user selects a credit card number. The SQL statements to access the table are exactly the same as when no columns would be encrypted.
The importance of this application transparency cannot be overstated. Just imagine you would be forced to modify all SQL code accessing a table when you decide to encrypt a column: this would not be realistic for most real-life systems.
So, what happens when a user without decrypt permission tries to retrieve a credit card number? Instead of an error, the unprivileged user will retrieve a value of 'xxxx-xxxx-xxxx-xxxx', as defined by the decrypt_defaultclause. This allows existing SQL code to run without disclosing sensitive data to non-authorised users.
The example above illustrates the basic concept of ASE column encryption.
Security can be improved further though. For example, you may need to ensure those rogue DBAs don't run off with a list of credit card numbers.
To achieve this, Sybase ASE allows you to restrict access to encrypted columns to specific users only. Anyone else, regardless of their privileges, will not be able to access the encrypted data.
This works as follows (I'm not including a code example here since this would require too many details to be explained for this blog):
- An encryption key can be protected with a password (the key cc_key in the example above is not password-protected); before the encryption key can be used, and encrypted data accessed, the ASE user must specify the password.
- This would seem to put a requirement on the end-user to remember the encryption key password (which usually means writing them down on a yellow sticky paper that's glued to the screen or keyboard, thus defying the purpose of encryption). Remember that one of the design goals was to avoid the need for any additional passwords by the ned-user.
- To avoid this, ASE's solution is a trick called "login association". This means that the encryption key password is stored inside ASE for a particular user. To protect the encryption key password, it is first encrypted by using the user's login password (which is itself encrypted) as an encryption key.
When this user logs in to the ASE server with his regular ASE password, this will be used to retrieve and decrypt the encryption password which is then used to automatically enable the encryption key. Thus, just by logging into ASE, a user gains access to the encrypted column. - To ensure that other users, like the all-powerful DBA, do not know the encryption key password, performing any encryption key-related functions requires a special ASE system role. This keycustodian_role should be assigned only to a trusted person in the organisation.
In a nutshell, this is how to tie access to encrypted columns to specific users only. There's much more to say about this topic -- search the ASE documentationfor "login association" if you want full details).
ASE column encryption is a truly unique feature; other database vendors provide in-database encryption functionality as well, but no database except Sybase ASE has managed to combine all of the requirements described earlier.
With ASE's column encryption feature, Sybase/SAP brings strong data security to mission-critical enterprise systems.
Some final notes:
- Sybase recommends setting things up with a small database containing only the encryption keys, and the actual encrypted data in the (big) production database. This means backups of the production database are secure: since they do not contain the encryption keys, encrypted data cannot be decrypted. The idea is that the small database with encryption keys rarely changes and therefore backups will rarely be made as well -- and those that are made, are stored in a high-security environment. This all matters since one of the simplest ways of stealing sensitive data is probably to grab a copy of a database backup -- and for a production database, many backups typically exist.
- The ASE query processing subsystem is encryption-aware. This means that most SQL queries in Sybase ASE will use the same query plan whether a column is encrypted or not. For example, when searching for a particular value in an indexed and encrypted column, the search argument value is first encrypted with the same encryption key as used for the column itself; this encrypted value is then used to look for the desired row. Also, when joining two tables on encrypted columns, the join can be performed on the ciphertext values provided the columns were encrypted with the same encryption key. This all means that in most cases, the performance impact from using column encryption is small or even negligible.
- Note that data security covers just one aspect of a secure system. Other aspects, like network security (e.g. SSL) and organisational structures (e.g. function separation) should get attention as well.
- ASE column encryption is a licensable option for Sybase ASE.
- ASE column encryption is not currently used by SAP Business Suite.
For more information on ASE column encryption, go to the product pages at sybase.com.
For full technical details, check out the ASE column encryption documentation.
1 Comment
