This chapter describes the Adaptive Server encrypted column feature.
Adaptive Server authentication and access control mechanisms ensure that only properly identified and authorized users can access data. Data encryption further protects sensitive data against theft and security breaches.
The Adaptive Server encryption column enables you to encrypt column-level data that is at rest, without changing your applications. This native support provides the following capabilities:
Column-level granularity
Use of a symmetric, National Institute of Standards and Techology (NIST)-approved algorithm: Advanced Encryption Standard (AES)
Optimized for performance
Enforced separation of duties
Fully integrated and automatic key management
Application transparency: no application changes are needed
Protects data privacy from the power of the system administrator
Data encryption and decryption is automatic and transparent. If you have insert or update permission on a table, any data you insert or modify is automatically encrypted prior to storage. Daily tasks are not interrupted.
Selecting decrypted data from an encrypted column requires decrypt permission in addition to select permission. Decrypt permission can be granted to specific database users, groups, or roles. Sybase gives you more control by providing you with granular access capability to sensitive data. Sybase also automatically decrypts selected data for users with decrypt permission.
Encryption keys are stored in the database in encrypted form. You can encrypt an encryption key using a system-level or a user-supplied password (which can be the user’s login password). The password you select reflects your ability to preserve data privacy, even from system administrators.
Encrypting columns in Adaptive Server is more straightforward than using encryption in the middle tier, or in the client application. You use SQL statements to create encryption keys and to specify columns for encryption, and existing applications continue to run without change.
When data is encrypted, it is stored in an encoded form called “cipher text.” Cipher text increases the length of the encrypted column from a few bytes to 32 extra bytes. See “Length of encrypted columns”. Unencrypted data is stored as plain text.
Figure 1-1 describes encryption and decryption processing in Adaptive Server. In this example, a client is updating and encrypting a Social Security Number (SSN).
Figure 1-1: Encryption and decryption in Adaptive Server
Column encryption uses a symmetric encryption algorithm, which means that the same key is used for encryption and decryption. Adaptive Server tracks the key that is used to encrypt a given column.
When you insert or update data in an encrypted column, Adaptive Server transparently encrypts the data immediately before writing the row. When you select from an encrypted column, Adaptive Server decrypts the data after reading it from the row. Integer and floating point data are encrypted in the following form for all platforms:
Most significant bit format for integer data
Institute of Electrical and Electronics Engineers (IEEE) floating point standard with MSB format for floating point data
You can encrypt data on one platform and decrypt it on a different platform, provided that both platforms use the same character set.
Generally, using encrypted columns requires these administrative steps:
Install the license option ASE_ENCRYPTION. See the Adaptive Server Enterprise Installation Guide.
The system security officer (SSO) enables encryption in Adaptive Server:
sp_configure 'enable encrypted columns', 1
Use sp_encryption to set the system encryption password for a database.
Create one or more named encryption keys. See Chapter 2, “Creating and Managing Encryption Keys.” Consider using passwords to protect data even from the database administrator. See Chapter 5, Protecting Data Privacy from the Administrator.
Specify the columns for encryption. See “Specifying encryption on new tables” and “Encrypting data in existing tables”.
Grant decrypt permission to users who must see the data. You may choose to specify a default plain text value known as a “decrypt default.” The Adaptive Server returns this default, instead of the protected data, to users who do not have decrypt permission. See “Permissions for decryption”.
Once you perform these steps, you can run your existing applications against your existing tables and columns, but now the data in your database is securely protected against theft and misuse. Adaptive Server utilities and other Sybase products can process data in encrypted form, protecting your data throughout the enterprise. For example, you can:
Use the Sybase Central Adaptive Server Plug-in to manage encrypted columns using a graphical interface. See the online help for Sybase Central.
Use the bulk copy utility (bcp) to securely copy encrypted data in and out of the server. See the Utility Guide.
Use the Adaptive Server migration tool sybmigrate to securely migrate data from one server to another. See the Adaptive Server Enterprise System Administration Guide.
Use Sybase Replication Server to securely distribute encryption keys and data across servers and platforms. See the Replication Server Administration Guide for information on encryption when replicating.
