SAP Sybase ASE – Keeping Private Data Private
Peter Dobler – Dobler Consulting
Data security is one of the hot topics when you ask top executives about what keeps them up at night. Keeping private data private is a never-ending challenge that needs constant attention and dedication. Data encryption is your best defense for protecting sensitive data. Common access control mechanisms and limiting the power of database administrators will make it harder to access the sensitive data, but once this hurdle is overcome the data is freely accessible. Data encryption will supplement and enhance these security measures by protecting sensitive data at rest in backup files and database files that could be exploited by powerful OS users.
SAP Sybase ASE already contains powerful data access control features like granular permissions to enforce the separation of duties. The data encryption option will extend and enhance these features with sophisticated, flexible algorithms and methodologies.
Data Encryption with SAP Sybase ASE
SAP Sybase ASE first introduced data encryption on the column level in release 12.5. In data encryption terminology there are two phases that compose the process. Encryption will obfuscate the data with the help of an encryption key and store the data in the database. The same encryption key is used to read the obfuscated data from the database and decrypt the data back into its original value.
SAP Sybase ASE 15.7 uses Advanced Encryption Standard (AES) with 128, 192, or 256-bit encryption key sizes for its data encryption methodology. The longer the bit string, the more difficult it is for an unauthorized person to decrypt the data.
Data encryption in SAP Sybase ASE uses a symmetric encryption algorithm, which means that the same key is used for encryption and decryption. When you insert or update data in an encrypted column, SAP Sybase ASE transparently encrypts the data immediately before writing the row. The more complicated the encryption, the more CPU resources will be taken up by the encryption algorithm. This methodology is called Transparent Data Encryption (TDE), and is used by Oracle and SQL Server as well. Both Oracle and SQL Server implemented TDE in their data encryption features, using an encryption key to encrypt and decrypt data. The difference is in the way they do the implementation.
Data encryption in SAP Sybase ASE is governed by a system security officer (SSO) or a Key Custodian. Both roles need to have the sso_role granted to their login profiles and no other user, including database administrators, should have this role granted to them.
Data encryption is an internal affair with SAP Sybase ASE. The master encryption key can be stored in the database itself. SAP Sybase ASE features key encryption keys and key encryption values in its data encryption algorithm. The key encryption key (KEK), formerly known as system encryption key (SEK), now has four levels of security. The most secure option requires a password that is not saved in the database. The loss of this password will make it impossible to decrypt the data in any way whatsoever. As a best practice procedure, the Key Custodian should appoint a trusted user as the key recoverer and in case of a lost password; this recovery password can be used to recover master key access.
SAP Sybase ASE 15.7’s data encryption also allows you to encrypt and store passwords from other systems in the database by using a “Service” key. This includes passwords from CIS, LDAP, SSL and Rep Server.
Implementing Data Encryption
Generally, enabling SAP Sybase ASE data encryption consists of a few steps, but requires in-depth planning to ensure that no data loss occurs due to lost passwords.
The steps involved are:
- The SSO (assigned sso_role with an admin login or a Key Custodian) enables column encryption with this command: sp_configure ‘enable encrypted columns’, 1
- Depending on the protection method of your column encryption keys, you need to create a database-level master key or set the system encryption password. This is the key encryption key (KEK) that is being used to create the column encryption keys (CEK).
- Create one or more column encryption keys (CEK). If additional passwords are assigned to encryption keys, data will be protected, even from database administrators.
- Specify the columns in a table to be encrypted.
- Grant decrypt permission to users who must see the data. You can choose a default plain text to mask data for unauthorized users.
Once these steps have been completed, existing applications can run against existing tables and columns, but now the data is securely protected against theft and misuse.
The control over the encryption process, when choosing a database-level master key, can be delegated into an application layer. There is no external protection mechanism to be considered, and the application retains full control. This is important to 3rd party applications as the SAP Sybase ASE data encryption is a licensed option. If a client choses to implement data encryption later on, the application must be able to dynamically enable encryption for its data without manual intervention. If one or more encryption keys are located outside the database, the application loses control over the encryption admin process.
SAP Sybase ASE gives applications the authority to take control of data encryption with database-level master keys and SQL statements based column encryption.
SAP Sybase ASE is able to encrypt most of its data types. For obvious reasons, encrypting text and image data types is not possible. You also cannot encrypt computed columns or specify encrypted columns in the partition_clause of a table. Other than that, you’re free to apply data encryption to as many columns as you prefer.
In the simple encryption sample below, you can see how to create two encryption keys, and encrypt two columns with two different keys. This is a new table encryption sample.
create encryption key default_key as default for AES
create encryption key cc_key
create table emp_table (ssn char(15) encrypt, ename char(50),…)
create table customer (creditcard char(20) encrypt with cc_key, cc_name char(50),…)
The ssn column will be encrypted with the default key, and the creditcard column will be encrypted with the cc_key. The column encryption keys (CEK) are being created using the key encryption keys (KEK). If a password is required you will be prompted to enter the password to create the CEK.
To apply encryption to a column in an existing table, use the alter table / modify command to modify the column. You will use either the default key or a named encryption created in the current database or another database. The exception for this encryption is that you cannot modify a column if there is a trigger in place, or the column is a key in a clustered or placement index. In this case, the trigger / index has to be dropped, the column encrypted, and the trigger / index re-created.
One important part of encrypting data in a column is to ensure that the values can be decrypted. The decrypt permission can be granted either to a role or individually to a user on specific tables. It is advisable to combine select and decrypt permissions at the same time to a user or role. Be careful when granting DML (update, insert and delete) permissions without the decrypt permission to a user or role. This can cause unintended consequences and potentially lead to data loss. Encrypted data can be overwritten with trash so that it is impossible to decrypt the data. In this case, encryption constraints or planning carefully for data access permissions are the best solutions to prevent this scenario.
Data Encryption vs. Data Masking
One of the main distinctions between data encryption and data masking is that encryption has to be reversible and a decryption method has to ensure that encrypted data can be successfully decrypted into the correct value. Data masking applies data scrambling or obfuscation methods to render sensitive data unusable. One of the most famous data masking examples is displaying only the last four digits of a social security number. All other digits have been replaced by the letter x.
Data masking is divided into static and dynamic masking. Obfuscation either happens in real time when accessing the data, or the data has been store in an obfuscated form into the database. Static data masking is not possible in production databases; it is commonly used to protect sensitive data in test and development environments.
As an added bonus with data encryption, basic data masking can be achieved via the default value setting when encrypting data columns. If a user has not been granted decrypt permission, a default value will be displayed instead.
Data Encryption Consequences
When implementing data encryption with SAP Sybase ASE, special attention to index strategies is required to offset negative performance impacts. That’s why it is important to choose the encrypted columns wisely. SAP Sybase ASE has built-in optimization indexing encrypted columns. You can access data effectively via an index, but there are certain rules that need to be followed. The same holds true when encrypting constraints.
The biggest consequence when implementing data encryption is the potential loss of data if passwords are lost. SAP Sybase ASE offers four options to manage the key encryption keys (KEK), each one of them with a larger degree of protection. There is still the standard system encryption key (SEK) option available, but all other options require one or more passwords, and these passwords are not stored in the database. Keeping track of these passwords is one of the duties of the SSO or the Key Custodian.
Based on independent performance benchmarks, the impact of data encryption on performance largely depends on how many columns are being encrypted. There is a storage increase associated with data encryption, which gets easily overlooked in encryption rollout projects, but could create unintended bottlenecks.
Creating or re-creating indexes will take a performance hit. This is expected behavior; however SAP Sybase ASE implemented performance improvements in its latest version that will mitigate this performance impact.
If encrypted columns are part of a table join in a query, the largest performance impact will be noted, especially if non-encrypted columns are being joined with encrypted columns. Be sure to thoroughly check your encryption plan with the data model and the foreign key reference model.
Expanding Data Encryption beyond Data
SAP Sybase ASE provides strong data encryption methodologies for external login passwords and hidden text, using the AES-256 symmetric encryption algorithm. By using this option, you will be able to apply strong data encryption for external passwords to:
- Replication Agents and replicated databases
- CIS for remote descriptors and logins
- The job scheduler agent
- Real Time Messaging (RTMS)
- Secure Socket Layers (SSL) and Lightweight Directory Access Protocol (LDAP) protecting SSL and LDAP access accounts. Passwords are being managed by the stored procedures sp_ldapadmin and sp_ssladmin, and can be secured.
Securing external passwords and hidden text requires the use of a service key. Service keys are 256-bit, persistent encryption keys and are stored in sysencryptkeys. Service keys are encrypted by either using a static key or the master key. Using the master key requires the password to decrypt the database-specific master key.
Encrypting data will protect data at rest from unauthorized access. In case of backup files getting stolen or privileged OS users gaining access to the database files, data encryption renders these security breach attempts useless. Data encryption is required to comply with certain government and industry regulations such as PCI, HIPPA, etc. SAP Sybase ASE implemented data encryption in a secure and flexible way that enables applications to take control over the data encryption process.
SAP Sybase ASE enables data encryption by storing the key encryption keys inside the database. It also added an additional security layer requiring granting special decrypt permission to users in order to make the data readable again. This effectively blocks database administrators from accessing sensitive data in the database and on the backup files.
Based on benchmark tests, data encryption adds very limited overhead to the data processing if used for selective columns in selective tables. The CPU overhead logically increases when encrypting more columns. But as a tradeoff for stellar security, this is a small price to pay.
About the Author
Peter Dobler is an accomplished IT database professional who delivers results by building sophisticated data systems, by implementing advanced application software, and by improving efficiencies and reducing costs. He founded Dobler Consulting, (www.doblerconsulting.com) a Tampa, Florida consulting firm that delivers implementation expertise for Oracle, SAP Sybase, and MS SQL Server in 2000. His 25 plus years in technology started in Basle, Switzerland in 1985. Peter uses his extensive experience to hone his talent as a proven resource for producing streamlined IT solutions. He currently engages in strategic alliances and special projects with SAP Sybase, the enterprise software and services company. He can be reached at: email@example.com or 813-322-3240.
Read his Database Trends Blog (Behind the Scenes of Database Evolution) at: http://www.doblerconsulting.com/db-tech-trends, and his Technology Tips Blog (Step-by- step Instructions on Today’s Challenging Technology) at http://www.doblerconsulting.com/tech-tips-tricks.