How to encrypt table in sql

How to encrypt SQL tables: first test the environment; then create the database master key; then create the certificate and a symmetric secret key; and finally encrypt the data.

sql server How to set up table encryption

SQL Server supports database-level encryption (TDE) and column-level encryption Data encryption, database-level encryption is performed in the database, which is transparent to the program, and no additional operations are required during the development process. Compared with database-level encryption, the column-level data encryption process is a bit troublesome, and the program needs to do some additional operations. The following figure is the encryption hierarchy of SQL Server. It can be seen that the encryption of SQL Server is hierarchical. The encryption of the upper level protects the encryption of its sub-levels. This article explains a method using the four levels of ①②③④ in the figure to encrypt/decrypt. data process.

(1) Test environment description

The test uses SQL Servre 2012 R2 and creates the following data table:

--Create the user table for testing

CREATE TABLE TBLUser 
(    
    Name             nvarchar(30),     
    Password         varbinary(1000),    
)
GO

(2) Create the database master key

The database master key is in Under the service master key, encryption is performed by the service master key. This is a database-level key that can be used to provide encryption for creating database-level certificates or asymmetric keys. Each database can only have one database master key, which is created through T-SQL statements. The specific code is as follows:

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='passW@ord'
GO

(3) Create certificate

Create a symmetric key certificate for encryption, the specific code is as follows :

CREATE CERTIFICATE TestCert with SUBJECT = 'Test Certificate'
GO

(4) Create a symmetric key

As can be seen from the SQL Server encryption hierarchy, the symmetric key It can be created by password, or other symmetric keys, asymmetric keys, and certificates. This article uses the certificate to create a symmetric secret key for testing. The specific code is as follows:

CREATE SYMMETRIC KEY TestSymmetric WITH ALGORITHM = AES_256
 ENCRYPTION BY CERTIFICATE TestCert 
GO

(5) Encrypt the data

First Open the certificate symmetric secret key, then use the ENCRYPTBYKEY function to encrypt the data, and then close the symmetric secret key. The specific code is as follows:

OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;
INSERT INTO TBLUser values('张三', ENCRYPTBYKEY(Key_Guid(N'TestSymmetric'), '123456'));
CLOSE SYMMETRIC KEY TestSymmetric;
GO

(6) View the encrypted data

Directly run SQL to query the encrypted data, you can I saw that the content of the password was a string of unreadable hexadecimal characters.

SELECT * FROM TBLUser
GO

# (7) Decrypt data

First open the certificate symmetric secret key, then use the DecryptByKey function to decrypt the data, and close it after completion Symmetric key. The specific code is as follows:

OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;
SELECT Name, CAST(DecryptByKey(password) as varchar(100)) Password FROM TBLUser;
CLOSE SYMMETRIC KEY TestSymmetric;
GO

The above is the detailed content of How to encrypt table in sql. For more information, please follow other related articles on the PHP Chinese website!