Shulou(Shulou.com)05/31 Report--

This article introduces how to prevent developers from obtaining sensitive data in SQL Server. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Step * * create a database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SysKey' GO

Here the database master key is created, for example, the password is' SysKey'. The master key needs to be created only once.

Step two, create a certificate

CREATE CERTIFICATE MYCERT with SUBJECT ='My Cert' GO

Here, the certificate MYCERT is created, and the field in the metadata of the certificate is'My Cert'. The certificate is created once, and Open is required when decrypting it later.

Step 3: create a symmetric key

CREATE SYMMETRIC KEY MYKEY WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MYCERT; GO

Here the key MYKEY is created based on the certificate MYCERT, and the encryption algorithm is AES_256. There are several encryption algorithms.

After the first three steps are done, we can see our certificate and key information in Explorer, as shown in the following figure:

Step 4: encryption and decryption validity test

Let's first test the effectiveness of encryption and decryption with variables:

OPEN SYMMETRIC KEY MYKEY DECRYPTION BY CERTIFICATE MYCERT; declare @ key varbinary (MAX); set @ key=EncryptByKey (key_guid ('MYKEY'),' 20000.00'); select @ key; select convert (varchar (100), DecryptByKey (@ key))

Before decrypting, open the key we defined by:

OPEN SYMMETRIC KEY NCSK DECRYPTION BY CERTIFICATE NCSC

If you don't open the key first, you will return NULL.

Refer to the following figure to run the reference result:

Step 5, prepare the data to be used

For ease of description, let's create a payroll variable:

Declare @ Salary table (FName nvarchar (50), FSalary varbinary (MAX))

Note here that the field where the encrypted data is stored should be set to the varbinary type.

Step 6, insert encrypted data

When inserting, use the function EncryptByKey to encrypt the text, obtain the key through the function key_guid, and the encrypted data type is varbinary.

Insert into @ Salary (FName,FSalary) values ('Zhang San', EncryptByKey (key_guid ('MYKEY'),' 20000.00'), ('Li Si', EncryptByKey (key_guid ('MYKEY'),' 25000.00')), ('Wang er', EncryptByKey (key_guid ('MYKEY'),' 30000.00'))

Step 7: data reading

When reading the data, you need to call the function DecryptByKey to decrypt, and convert the decrypted Varbinary to varchar type through Convert.

Select *, convert (varchar, DecryptByKey (FSalary)) from @ Salary

For complete processing, please refer to the following figure:

So much for sharing about how to prevent developers from getting sensitive data in SQL Server. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.