Database system providing encrypted column support for applications

• US 7,797,342 B2
• Filed: 08/17/2007
• Issued: 09/14/2010
• Est. Priority Date: 09/03/2004
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Chen, Shiping, Chitkara, Rajnish Kumar, Banks, Barbara J.
• Primary Examiner(s): Vo; Tim T.
• Assistant Examiner(s): Gortayo; Dangelino N
• Application Number:
  US11/840,306
• Publication Number:
  US 20080033960A1
• Time in Patent Office: 1,124 Days
• Field of Search: 707/1, 707/9, 707/10, 707/100, 707/104.1, 713/166, 713/182, 713/193
• US Class Current: 707/783
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: H04L 9/0894 Escrow, recovery or storing of secret information, e.g. secret key escrow or cryptographic key storage
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99939 Privileged access
• CPC Class Codes: Y10S 707/99948 Application of database or data structure, e.g. distributed, multimedia, or image

Abstract

A database system providing encrypted column data support with decrypt default functionality. In a database system, a method providing automated encryption support for column data comprises steps of: defining Structured Query Language (SQL) extensions for creating and managing column encryption keys and database tables with encrypted column data; receiving an SQL statement specifying creation of a column encryption key; receiving an SQL statement specifying creation of a database table having particular column data encrypted with the column encryption key; receiving an SQL statement specifying a default value to be provided in response to requests for the column data without decrypt permission; in response to a subsequent database operation requesting encrypted column data from a user with decrypt permission, automatically decrypting the column data; and in response to a subsequent database operation requesting the encrypted column data from a user without decrypt permission, returning the default value.

Claims

1. In a database system, a method for providing automated encryption support for column data which handles requests for encrypted column data from users without decrypt permission, the method comprising:

defining Structured Query Language (SQL) extensions for creating and managing column encryption keys, and for creating and managing database tables with encrypted column data;

receiving an SQL statement that uses said SQL extensions to specify creation of a particular column encryption key for encrypting column data;

receiving at least one SQL statement that uses said SQL extensions to specify creation of a database table having particular column data encrypted with said particular column encryption key and having a default value to be provided in response to requests for said particular column data from a user without decryption permission;

upon parsing said SQL statements, creating a database table having particular column data encrypted with said particular encryption key, and storing a decrypt default attribute that associates said default value with said particular column data;

in response to a subsequent database operation requesting the particular column data that has been encrypted from a user with decrypt permission on said particular column data, automatically decrypting the particular column data for use by the database operation; and

in response to a subsequent database operation requesting the particular column data that has been encrypted from a user without decrypt permission, returning the default value specified for the particular column data by the decrypt default attribute.

2. The method of claim 1, wherein columns that are not specified to be encrypted are stored in unencrypted format, for minimizing encryption overhead.

3. The method of claim 1, wherein the automated encryption support operates as an internal built-in feature of the database system, without use of an add-on library.

4. The method of claim 1, wherein the SQL statement specifying creation of a database table having particular column data encrypted comprises a CREATE TABLE command that allows specification of one or more columns to be encrypted.

5. The method of claim 4, wherein the CREATE TABLE command includes an attribute enabling a default value to set for the particular column data encrypted, said default value to be returned to users without decrypt permission.

6. The method of claim 1, further comprising:

receiving an SQL statement specifying alteration of a previously-created database table so as to encrypt particular column data.

7. The method of claim 6, wherein the SQL statement specifying alteration of a previously created database table comprises an ALTER TABLE command.

8. The method of claim 7, wherein the ALTER TABLE command includes an attribute enabling a default value to set for the particular column data encrypted, said default value to be returned to users without decrypt permission.

9. The method of claim 1, wherein the encryption support works with existing database applications without requiring changes to said existing database applications.

10. The method of claim 9, wherein the default value is returned to users of said existing database applications without decrypt permission, thereby avoiding return of an error message to users without decrypt permission.

11. The method of claim 1, further comprising:

after creation of the particular column encryption key, protecting the particular column encryption key with a user-supplied password.

12. The method of claim 11, wherein the user-supplied password must be supplied before the system allows use of the particular column encryption key for database operations.

13. The method of claim 11, wherein a user seeking to decrypt column data must supply said user-supplied password and must have necessary database privileges before decrypting the column data with the particular column encryption key.

14. The method of claim 1, wherein the database system internally stores in encrypted format any column encryption keys that have been created.

15. The method of claim 1, wherein a single column encryption key is used for each column to be encrypted.

16. The method of claim 1, wherein a single column encryption key may be shared by multiple columns to be encrypted.

17. The method of claim 1, wherein the particular column encryption key is itself encrypted to a key-encrypting key constructed from a user-supplied password.

18. The method of claim 17, wherein the particular column encryption key is itself stored on disk in encrypted format using Advanced Encryption Standard (AES) encryption.

19. The method of claim 1, wherein said Structured Query Language (SQL) extensions for creating and managing column encryption keys include a clause for instructing the database system to create a default key for encrypting columns.

20. A computer-readable medium having processor-executable instructions for performing the method of claim 1.

21. A database system providing automated encryption support for column data, the system comprising:

a parser that supports Structured Query Language (SQL) extensions for creating and managing column encryption keys, and for creating and managing database tables with encrypted column data; and

an execution unit, operating in response to SQL statements parsed by the parser,

for creating in response to SQL extensions present in the SQL statements a particular column encryption key,

for creating in response to SQL extensions present in the SQL statements a database table having particular column data encrypted with said particular column encryption key and having a default value to be provided in response to requests for said particular column data from a user without decryption permission,

for storing a decrypt default attribute that associates said default value with said particular column data, and

for automatically decrypting the particular column data for use by a subsequent database operation that requests the particular column data that has been encrypted from a user with decrypt permission on said particular column data, and which provides the default value specified for the particular column data by the decrypt default attribute in response to a subsequent database operation requesting the particular column data from a user without decrypt permission.

22. The system of claim 21, wherein columns that are not specified to be encrypted are stored in unencrypted format, for minimizing encryption overhead.

23. The system of claim 21, wherein the automated encryption support operates as an internal built-in feature of the database system, without use of an add-on library.

24. The system of claim 21, wherein the SQL statement specifying creation of a database table having particular column data encrypted comprises a CREATE TABLE command that allows specification of one or more columns to be encrypted.

25. The system of claim 24, wherein the CREATE TABLE command includes an attribute enabling a default value to set for the particular column data encrypted, said default value to be returned to users without decrypt permission.

26. The system of claim 21, further comprising:

a module for receiving an SQL statement specifying alteration of a previously created database table so as to encrypt particular column data.

27. The system of claim 26, wherein the SQL statement specifying alteration of a previously created database table comprises an ALTER TABLE command.

28. The system of claim 27, wherein the ALTER TABLE command includes an attribute enabling a default value to set for the particular column data encrypted, said default value to be returned to users without decrypt permission.

29. The system of claim 21, wherein the execution unit with encryption support with existing database applications without requiring changes to said existing database applications.

30. The system of claim 29, wherein the execution unit returns the default value to users of said existing database applications without decrypt permission, thereby avoiding return of an error message to users without decrypt permission.

31. The system of claim 21, wherein the system protects the particular column encryption key with a user-supplied password.

32. The system of claim 31, wherein the user-supplied password must be supplied before the system allows use of the particular column encryption key for database operations.

33. The system of claim 31, wherein a user seeking to decrypt column data must supply said user-supplied password and must have necessary database privileges before decrypting the column data with the particular column encryption key.

34. The system of claim 21, further comprising:

providing a command to grant decryption permission to others.

35. The system of claim 21, wherein the database system internally stores in encrypted format any column encryption keys that have been created.

36. The system of claim 21, wherein the database system stores encrypted column data internally as variable binary (VARBINARY) data.

37. The system of claim 21, wherein the database system presents users a user-defined field type for column data that has been encrypted, even though the column data is stored internally as variable binary data.

38. The system of claim 21, wherein the database system preserves any user-defined data type for the particular column data so that the database system employs a correct data type for processing queries and returning query results.

39. The system of claim 38, wherein the database system stores the user-defined data type for the particular column data in a system catalog of the database system.

40. The system of claim 21, wherein a single column encryption key is used for each column to be encrypted.

41. The system of claim 21, wherein the particular column encryption key is itself encrypted to a key-encrypting key constructed from a user-supplied password.

42. The system of claim 41, wherein the particular column encryption key is itself stored on disk in encrypted format using Advanced Encryption Standard (AES) encryption.

43. The system of claim 21, wherein said Structured Query Language (SQL) extensions for creating and managing column encryption keys include a clause for instructing the database system to create a default key for encrypting columns.