Database encryption is a proactive measure that is used to protect data in a database. Database encryption helps prevent data leaks that are caused by plaintext storage or data theft by privileged users. Database encryption also helps defend against attackers that break through the security boundary. This way, the issue of sensitive data leaks can be fundamentally resolved. The data that is encrypted on clients by using Encryption SDKs can be stored in a relational database or a non-relational database. This topic describes the scenarios of sensitive data encryption for a database and how to encrypt and decrypt data. This topic also provides examples on how to encrypt sensitive data for a database.

Scenarios

  • Prevent sensitive data leaks caused by plaintext storage after a data breach.

    In most cases, data in a database is stored and used in plaintext. If data files or backup files are disclosed, serious data leaks may occur. In data breach attacks, data stored in plaintext cannot be protected. To resolve this issue, you must encrypt your data to prevent data leaks.

  • Prevent sensitive data leaks caused by data theft by privileged users.

    Database encryption is independent of the permission control system of a database. Database encryption helps enhance permission control. You can use a dedicated encryption system to configure access permissions on sensitive data. This ensures data security by effectively controlling the access to sensitive data from privileged users, such as database superusers.

Encrypt and decrypt data

  • Encryption
    1. Create a data key.

      Encryption SDKs call the GenerateDataKey operation to request a data key from Key Management Service (KMS). KMS returns the data key and the encrypted data key.

    2. Encrypt and store data.
      1. Use the data key to encrypt data and encode the ciphertext by using the Base64 algorithm.
      2. Store the Base64-encoded ciphertext in a database.
  • Decryption

    Query and decrypt data.

    1. Read the Base64-encoded ciphertext from the database.
    2. Decode the Base64-encoded ciphertext by using the Base64 algorithm. Encryption SDKs call the Decrypt operation of KMS to decrypt the encrypted data key. KMS returns the decrypted data key.
    3. Decrypt the ciphertext. Encryption SDKs use the data key to decrypt the ciphertext and obtain the plaintext.

Examples

Encryption SDKs encrypt data to be transmitted from applications to databases. KMS generates and manages data keys.

The following sample codes provide examples on how to encrypt and decrypt the email field in the User table by using Spring Data JPA or Python. In these examples, the email field uses a data key, which is cached during decryption. If you want to query the email field for multiple times, you can use the data key that is cached.

To ensure that the email field can store encrypted data, you must increase the size of the email field to three times that of the original size.

  • Define an entity class.
    @Entity
    public class User {
        @Id
        @GeneratedValue
        private Long id;
    
        private String name;
        private String email;
    
        // getters and setters ...
    }
  • Define the UserRepository class.
    public interface UserRepository extends CrudRepository<User, Long> {
     }
  • Implement the AttributeConverter interface of Spring Data JPA.

    The EncryptionConverter class calls Encryption SDKs to obtain a data key and uses the data key to encrypt and decrypt specified data.

    @Converter
     public class EncryptionConverter implements AttributeConverter<String, String> {
         private static String ACCESS_KEY_ID = "<AccessKeyId>";
         private static String ACCESS_KEY_SECRET = "<AccessKeySecret>";
         private static String CMK_ARN = "acs:kms:RegionId:UserId:key/CmkId";
         private static AliyunConfig config;
         static {
             config = new AliyunConfig();
             config.withAccessKey(ACCESS_KEY_ID, ACCESS_KEY_SECRET);
         }
     
         @Override
         public String convertToDatabaseColumn(String plainText) {
             BaseDataKeyProvider dataKeyProvider = new DefaultDataKeyProvider(CMK_ARN);
             AliyunCrypto crypto = new AliyunCrypto(config);
     
             try {
                 CryptoResult<byte[]> encryptResult = crypto.encrypt(dataKeyProvider, plainText.getBytes(StandardCharsets.UTF_8), Collections.singletonMap("sample", "context"));
                 return Base64.getEncoder().encodeToString(encryptResult.getResult());
             } catch (InvalidAlgorithmException e) {
                 System.out.println("Failed.");
                 System.out.println("Error message: " + e.getMessage());
             }
             return null;
         }
     
         @Override
         public String convertToEntityAttribute(String cipherText) {
             BaseDataKeyProvider dataKeyProvider = new DefaultDataKeyProvider(CMK_ARN);
             AliyunCrypto crypto = new AliyunCrypto(config);
             // *** Cache a customer master key (CMK) ***
             CryptoKeyManager ckm = new CachingCryptoKeyManager(new LocalDataKeyMaterialCache());
             crypto.setCryptoKeyManager(ckm);
             try {
                 CryptoResult<byte[]> decryptResult = crypto.decrypt(dataKeyProvider, Base64.getDecoder().decode(cipherText));
                 return new String(decryptResult.getResult(), StandardCharsets.UTF_8);
             } catch (InvalidAlgorithmException | UnFoundDataKeyException e) {
                 e.printStackTrace();
             }
             return null;
         }
     }
  • Add the @Convert annotation.

    Add the @Convert annotation to the attribute that needs to be encrypted. The attribute is a column in the database.

    @Entity
     public class User {
         @Id
         @GeneratedValue
         private Long id;
     
         private String name;
         @Convert(converter = EncryptionConverter.class)
         private String email;
         
         // getters and setters ...
     }
  • Define an object.
    class User(object):
         def get_name(self):
             return self.name
     
         def set_name(self, value):
             self.name = value
     
         def get_email(self):
             return self.email
     
         def set_email(self, value):
             self.email = value
  • Implement the functions that are used to manage data in the database.
    def user_add(user):
         # Connect to the database. 
         conn = db_connection()
         # Obtain a cursor object that can be used to execute an SQL statement and return the result as a dictionary. 
         cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
         # Define the SQL statement to be executed. 
         sql = 'insert into user(name, email) values(%s,%s);'
         # Execute the SQL statement. 
         cursor.execute(sql, [user.name, user.email])
         print("insert name: " + user.name)
         print("insert email: " + user.email)
         # Commit the write operation. 
         conn.commit()
         last_id = cursor.lastrowid
         # Close the cursor object. 
         cursor.close()
         # Close the database connection. 
         conn.close()
         return last_id
     
     
     def user_select_by_id(id):
         # Connect to the database. 
         conn = db_connection()
         # Obtain a cursor object that can be used to execute an SQL statement and return the result as a dictionary. 
         cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
         # Define the SQL statement to be executed. 
         sql = 'select * from user where id = %s;'
         # Execute the SQL statement. 
         cursor.execute(sql, [id])
         result = cursor.fetchone()
         print("select result: " + str(result))
         user = User()
         user.__dict__ = result
         # Close the cursor object. 
         cursor.close()
         # Close the database connection. 
         conn.close()
         return user
  • Implement decorators for data encryption and decryption.
    def enc_convert():
         def enc_(func):
             def wrapper(self, plain_text):
                 provider = DefaultDataKeyProvider(AES_KEY_ARN)
                 client = build_aliyun_crypto(False)
                 cipher_text, enc_material = client.encrypt(provider, plain_text.encode("utf-8"), ENCRYPTION_CONTEXT)
                 cipher_text_str = base64.standard_b64encode(cipher_text).decode("utf-8")
                 f = func(self, cipher_text_str)
                 return f
     
             return wrapper
     
         return enc_
     
     
     def dec_convert(column_name):
         def dec_(func):
             def wrapper(self):
                 user = self.__dict__
                 cipher_text = user.get(column_name)
                 cipher_text_bytes = base64.standard_b64decode(cipher_text.encode("utf-8"))
                 provider = DefaultDataKeyProvider(AES_KEY_ARN)
                 client = build_aliyun_crypto(False)
                 plain_text, dec_material = client.decrypt(provider, cipher_text_bytes)
                 user[column_name] = bytes.decode(plain_text)
                 result = User()
                 result.__dict__ = user
                 f = func(result)
                 return f
     
             return wrapper
     
         return dec_
  • Add the decorators to the email field that you want to encrypt.
    class User(object):
         def get_name(self):
             return self.name
     
         def set_name(self, value):
             self.name = value
     
         @dec_convert(column_name="email")
         def get_email(self):
             return self.email
     
         @enc_convert()
         def set_email(self, value):
             self.email = value

For more information about the examples of sensitive data encryption for databases, visit alibabacloud-encryption-sdk-java and alibabacloud-encryption-sdk-python.