Learning of SQL Day 37

 

Day 37: Data Encryption

Introduction:

• Data encryption is a method of protecting data by converting it into an unreadable format, which can only be read using a decryption key. It is an essential practice for safeguarding sensitive information and ensuring data security.

Key Concepts:

• Encryption: The process of converting plain text into ciphertext.

• Decryption: The process of converting ciphertext back into plain text using a key.

• Symmetric Encryption: Uses the same key for both encryption and decryption.

• Asymmetric Encryption: Uses a pair of keys (public key and private key) for encryption and decryption.

• Transparent Data Encryption (TDE): Encrypts the entire database, both at rest and in backups.

SQL Commands and Examples:

1. Symmetric Encryption:

   • Use symmetric encryption to protect data.

sql

-- Encrypt data using AES encryption
DECLARE @plainText NVARCHAR(50) = 'SensitiveData';
DECLARE @key NVARCHAR(32) = '12345678901234567890123456789012'; -- 32-byte key for AES-256
DECLARE @encrypted VARBINARY(MAX);

SET @encrypted = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), @plainText);
SELECT @encrypted AS EncryptedData;

2. Decrypting Symmetric Encryption:

   • Decrypt the encrypted data.

sql

-- Decrypt data using AES decryption
DECLARE @decrypted NVARCHAR(50);
SET @decrypted = CONVERT(NVARCHAR(50), DECRYPTBYKEY(@encrypted));
SELECT @decrypted AS DecryptedData;

3. Asymmetric Encryption:

   • Use asymmetric encryption to protect data (SQL Server example).

sql

-- Encrypt data using RSA encryption
DECLARE @plainText NVARCHAR(50) = 'SensitiveData';
DECLARE @encrypted VARBINARY(MAX);

SET @encrypted = ENCRYPTBYASYMKEY(ASYMKEY_ID('MyAsymmetricKey'), @plainText);
SELECT @encrypted AS EncryptedData;

4. Decrypting Asymmetric Encryption:

   • Decrypt the encrypted data.

sql

-- Decrypt data using RSA decryption
DECLARE @decrypted NVARCHAR(50);
SET @decrypted = CONVERT(NVARCHAR(50), DECRYPTBYASYMKEY(@encrypted, ASYMKEY_ID('MyAsymmetricKey'), N'your_private_key_password'));
SELECT @decrypted AS DecryptedData;

5. Transparent Data Encryption (TDE):

   • Enable TDE to encrypt the entire database (SQL Server example).

sql

-- Enable TDE on the database
USE master;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE YourDatabaseName
SET ENCRYPTION ON;
GO

Practice Exercise:

1. Create a symmetric key for encryption.

2. Write a query to encrypt sensitive data using symmetric encryption.

3. Write a query to decrypt the encrypted data.

4. Create an asymmetric key for encryption.

5. Write a query to encrypt sensitive data using asymmetric encryption.

6. Write a query to decrypt the encrypted data.

7. Enable Transparent Data Encryption (TDE) for the YourDatabaseName database.

sql

-- Create a symmetric key
CREATE SYMMETRIC KEY MySymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'your_password';

-- Open the symmetric key
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY PASSWORD = 'your_password';

-- Encrypt sensitive data using symmetric encryption
DECLARE @plainText NVARCHAR(50) = 'SensitiveData';
DECLARE @encrypted VARBINARY(MAX);

SET @encrypted = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), @plainText);
SELECT @encrypted AS EncryptedData;

-- Decrypt the encrypted data
DECLARE @decrypted NVARCHAR(50);
SET @decrypted = CONVERT(NVARCHAR(50), DECRYPTBYKEY(@encrypted));
SELECT @decrypted AS DecryptedData;

-- Create an asymmetric key
CREATE ASYMMETRIC KEY MyAsymmetricKey
WITH ALGORITHM = RSA_2048;

-- Encrypt sensitive data using asymmetric encryption
DECLARE @plainText NVARCHAR(50) = 'SensitiveData';
DECLARE @encrypted VARBINARY(MAX);

SET @encrypted = ENCRYPTBYASYMKEY(ASYMKEY_ID('MyAsymmetricKey'), @plainText);
SELECT @encrypted AS EncryptedData;

-- Decrypt the encrypted data
DECLARE @decrypted NVARCHAR(50);
SET @decrypted = CONVERT(NVARCHAR(50), DECRYPTBYASYMKEY(@encrypted, ASYMKEY_ID('MyAsymmetricKey'), N'your_private_key_password'));
SELECT @decrypted AS DecryptedData;

-- Enable Transparent Data Encryption (TDE) for the YourDatabaseName database
USE master;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE YourDatabaseName
SET ENCRYPTION ON;
GO

Important Tips:

• Always use strong encryption algorithms to ensure the security of your data.

• Protect encryption keys and passwords to prevent unauthorized access.

• Regularly review and update your encryption practices to keep up with evolving security standards.

Understanding and implementing data encryption is crucial for protecting sensitive information and ensuring data security.