Sybase ASE: column encryption

1. Sybase ASE: column encryption Rev. 11.2012 http://andrewmeph.wordpress.com mailto: andrew.me.ph@gmail.com

2. Note and disclaimer: • Information posted in this and other presentations found here by no mean represent Sybase official position. It is an independent opinion based on functionality tests performed in controlled testing environment. • Although the author made its best to represent data in an accurate and unbiased manner, there is always a possibility of an error. • The opinions expressed by this and other presentations must be taken with a degree of skepticism. Since SAP Sybase allows to test before purchasing most of its products and product options, it is strongly recommended to run performance and/or functionality tests on local systems. No two systems are identical. Things good for Zeus may be harmful for a bull.

3. column encryption: Quick info • Column encryption option was introduced into Sybase ASE in the 12.5.3a version, way back in the past. • The option allowed to elegantly encrypt data in a table on a column level. No need for 3DP tools. ASE takes care of all. • ASE used AES encryption algorithm, 128 bit, with or without initiation vector/padding applied to each row to prevent pattern hacking. • Encryption is a licensed option and must be enabled the same way compression, partitioning, &c options are enabled.

4. column encryption: setup • The encryption setup was quite simple: • Define your system encryption key (SEK, stored in DB encrypted). • Grant permission on it. • Define your column encryption key (CEK, stored in DB encrypted by SEK). • Apply encryption on your columns (alter/create/select into). • When a user had to decrypt the value all s/he needed was: • Be granted to see column values (grant select/&c). • Be granted to decrypt values (grant decrypt). • When a user had to encrypt the value all s/he needed was: • Be granted to use SEK (grant select key). • Be granted to create CEK (grant create key).

5. column encryption: setup In the scriptographic parlance: • sp_configure 'enable encrypted columns', 1 [+license, per ASE] • sp_encryptionsystem_encr_passwd, {password} [aka SEK, per DB] • grant select|create on {SEK} to {user/group/role} • create encryption key [[database.][owner].]keyname [as default] [for AES] [with {[key_lengthnum_bits] [init_vector {null | random}] [pad {null | random}] [aka CEK, SSO or the one granted the permission before]. • alter table {TBL} modify {COL} encrypt [with {CEK}] • grant select on {TBL.COL} to {user/group/role} • grant decrypt on {TBL.COL} to {user/group/role}

6. column encryption: setup Few things to remember: • If you need to encrypt a column used as index key, you can’t use padding/initiation vector for your encryption key (i.e. weaker encryption, pattern matching possible to hack, in theory). • If you have a trigger on encrypted column, recreate it. • Since the value in a column is obfuscated, beware of causing the value to be decrypted in joins/SARGs. Adds CPU overhead. • Hence ASE has internal: • Optimization to encrypt SARG rather than decrypt column on equijoins. • Optimization to avoid decryption when equivalently typed columns joined (unless padding/vector was used). • Optimization to BCP out w/o decrypting the value. • For any other operation on the encrypted column, the value has to be decrypted and sent to the user as plan text + error message on failed permission (fixed in 12.5.4 with decrypt_default option).

7. column encryption: Weaknesses Encrypted value accessible to power users. • Once DB holding the right key (advised to store separately from the DB with columns encrypted by that key) and the DB holding the value are recovered AND a power-user login available, encrypted value is compromised. • No way to keep the value away from the DBO: the user creating an object is de-facto the user able to decrypt its values.

8. column encryption: 15.7 15.7 made encryption more intricate. • There are still two keys: the key encrypting keys, and the key encrypting values. • System encryption key (SEK) is left as the weakest option still available for use, but supplemented with two stronger options, each requiring knowledge of a unique password NOT STORED IN DB in order to decrypt column values. PRICE: The loss of these passwords makes data impossible to decrypt in any way whatsoever.

9. column encryption: 15.7 SEK is now KEK – key encryption key – and has four options: • An old chap SEK, now with 256 bit AES (the weakest option). • Master key, password protected (“default” option). The key stored in DB, but for the key to work, SSO or Key Custodian must feed ASE with correct password ON EACH ASE STARTUP. • User-supplied password – holding for a session only (or user login password – stronger option, each user holds it’s own password-protected key). Each session needs the password to be specified (unless login password is used). • Combination of Master key AND User key (aka Dual Control, the strongest option – need two passwords, one system-wise on startup and another hold by a particular user). As a bonus, 15.7 allows to encrypt other passwords stored in DB (CIS/LDAP/SSL/Rep. Agents/Syscomments) with a “Service” Key.

10. column encryption: 15.7 setup In ASE 15.7 setup is a bit more complex: • One needs to take care of what happens on password loss: • There is an option to define users granted permission to recover the lost system password. • There is an option to create “automatic_startup” copy of the key which will allow ASE to startup unattended, in the case SSO/Key Custodian is not available when ASE starts up. • There is an option to generate “temporary” keys by SSO for specific users to be used and discarded. • There is an option to change temporary key passwords by the end-users themselves, to keep them unavailable even to SSO (SSO cannot decrypt the values).

11. column encryption: 15.7 setup In scriptographic terms (incomplete): • sp_configure 'enable encrypted columns', 1 • create encryption key master with passwd{“My2StrongPwd”} • set encryption passwd {“My2StrongPwd”}for key master • create encryption key MASTER_CEK with master key • alter table {TBL} modify {COL} encrypt with MASTER_CEK • grant select on {TBL} to {USER} Each time ASE is booted, until SSO sets encryption password ASE-wide, user cannot access decrypted value despite the grant: Msg15432, Level 16, State 8: Server 'ASE157W', Line 1: A validation check failed when Adaptive Server decrypted an encryption key. This error may indicate an incorrect password

12. column encryption: project I will not summarize here all the settings that need to be done in order to use the new 15.7 version of the option. It is VERYstrongly recommended to read the documentation carefully when the new “password-protected” version of encrypted column is chosen (which ideally should be). There are very clear instructions on what should be done to protect the “master” passwords. In 15.7 version, the option is to be planned carefully before being used (separation of duties, startup, password protection, recovery users &c). This is a project and should be treated as such. DO NOT encrypt your values until you setup the protection for your key passwords and understand precisely what you do and why. Data may be easily lost.

13. column encryption: A caveat Since 12.5.4 decrypt_default has been introduced that allows to see default value (say, ****) for those lacking permission. Note, that this improvement may skew calculations on encrypted columns, different users will see different/wrong values. Worse than that, a user that lacks decrypt permission but has DML permission, will overwrite correct value with the default value and thus make the real value lost forever. Therefore, it is absolutely necessary to setup check constraint on a table column in order to prevent data loss. E.G.: create table CLIENT (NAME varchar(30), SSN intcheck (SSN != -1) encrypt decrypt_default -1) This will save column values from being accidentally overwritten with trash.

14. column encryption: performance Performance check is, actually, the source of this presentation. Encryption is a strong feature, providing an overall protection for the values stored in ASE database and applicable to 90% on ASE data-types (since 15.7). The question is: what performance impact one is to expect? It is obvious that CPU overhead will be incurred by working with encrypted columns. As with 12.5.3a initial version of the option, the same rules apply performance-wise: the stronger the encryption the worse is performance. No vectors for keys. Use SARGs with care.

15. column encryption: performance I have set up a simple performance test for the encrypted column option. The setup: • Three tables (80K, 1M and 5M rows, 20MB, 500MB and 1GB). • 5 encrypted columns (~15-20% of table columns). • SEK type encryption setup (no impact on performance anyway). • Datetime/Varchar/Integer columns encrypted for each. • No vector/padding for indexed columns. • Performance comparison for different select / update clauses. • Checks for setup time / object size changes. The aim: • To see what encryption actually costs.

16. Performance: setup This is the impact for adding / dropping encryption and the impact on object’s size: We get 30 to 60% increase in size encrypting 15-20% of table columns. The time to setup/drop encryption is not bad at all: 84 sec for 5M rows/1 GB of data…

17. Performance: index creation This is the impact on creating indices: We get 70 % increase in create time for the clustered index and 40% increase for non-clustered index (indices are composed of up to two encrypted columns).

18. Performance: select/dml diff This is the impact on select/dml: • If we stick to the performance guidelines in documentation and use equijoins and joins with equal typed columns, we will pay up to ~50% CPU Timeper query price for encrypting our columns. • If we select ranges, use these columns to group by, join encrypted to non-encrypted columns, we may pay very heavy price – up to 1200% increase in CPU Time per query. As a result, ASE will report 100% utilization for ASE engines working on decrypting the values.

19. Performance: conclusion It is as important to treat encryption setup as a serious project that may cost data loss as to treat selection of columns to be encrypted as a project that may have grave performance implications if planned badly. If the end-user ends up joining encrypted values, aggregating them or scanning them in wide ranges, we may end up getting very grave performance issues AND bring our ASE to frequent 100% engine utilization peaks. If we select carefully what to encrypt and use the columns wisely, we may have a slight overhead on performance / storage and have the data safely stored within our ASE. The motto for the option should be “use it, but don’t abuse it.”

20. Addendum: performance raw data

21. feedbacks • Feedback and corrections may be either sent directly to andrew.me.ph@gmail.com or posted as comments in the blog space. • The blog is available at http://andrewmeph.wordpress.com. • More presentations/tools are available for download throughout the blog space. • You are welcome to post your own ideas there which may be later transformed into customized tools/feature tests and posted for the benefit of general public.