How to encrypt tables in sql

This article uses easy-to-understand examples to introduce how to encrypt the table in sql, the code is very detailed, interested friends can refer to, I hope it can be helpful to you.

Sql encrypts the table by first testing the environment and then creating the database master key, then creating the certificate and creating a symmetric key, and finally encrypting the data.

How to set the encryption in the table by sql server

SQL Server supports database-level encryption (TDE) and column-level data encryption. Database-level encryption is carried out in the database, which is transparent to the program, and no additional operations are needed in the development process. Compared with database-level encryption, column-level data encryption process is a bit troublesome, the program needs to do some additional operations. The following figure is the encryption hierarchy of SQL Server. You can see that the encryption of SQL Server is hierarchical, and the encryption of the upper level protects its sub-level encryption. This paper describes a process of encrypting / decrypting data using the four-level method of ①②③④ in the diagram.

(1) Test environment description

The test uses SQL Servre 2012 R2 to create the following data table:

-- create a user table for testing

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

(2) create a database master key

The database master key (Database Master Key) is encrypted by the service master key under 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 have only one database master key, which is created by the T-SQL statement. The specific code is as follows:

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

(3) create a certificate

Create a certificate to encrypt the symmetric key, 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, symmetric keys can be created by passwords, or by other symmetric keys, asymmetric keys, and certificates. This article uses the certificate to create a symmetric key for testing. The specific code is as follows:

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

(5) encrypt data

First open the certificate symmetry key, then use the ENCRYPTBYKEY function to encrypt the data, and then close the symmetry key when you are finished. The specific code is as follows:

OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;INSERT INTO TBLUser values ('Zhang San', ENCRYPTBYKEY (Key_Guid (nasty TestSymmetric`), '123456'); CLOSE SYMMETRIC KEY TestSymmetric;GO

(6) View encrypted data

Directly go to SQL to query the encrypted data, you can see that the content of password is a string of hexadecimal characters that cannot be read.

SELECT * FROM TBLUserGO

(7) decrypt data

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

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

On how to encrypt the table in sql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.