The following code has been tested on the SQLSERVER2008 sample database
question one: How to encrypt and decrypt data to avoid users stealing confidential data?
For sensitive data, such as passwords, card numbers, you can generally not use normal values to store. Otherwise there will be security risks. Previous encryption and decryption have front-end applications to assist with the completion. The database can only be encrypted and cannot be decrypted.
Data encryption and decryption at the database level has been provided since 2005. The main methods of implementation are as follows:
1. Change the encoding mode by convert:
Use this function to convert text or data into varbinary. But this method does not have the ability to protect the data, only to avoid the process of browsing data can directly see the role of sensitive data.
2, the use of symmetric key:
Paired with EncryptByKey for data encryption. Decrypts using the DecryptByKey function. This method is more suitable for large amount of data. Because the process of symmetric key is easy to use less resources.
3, the use of asymmetric key:
Paired with Encryptbyasymkey for data encryption. Decrypts using the DecryptByAsymKey function. Encryption and decryption data for a higher security level. Because of the consumption of resources called more.
4, the use of the way the voucher:
With EncryptByCert for encryption and DecryptByCert functions to decrypt. Compares a similar asymmetric key.
5, the use of Pass phrase method:
With Encryptbypassphrase for encryption, using the Decryptbypassphrase function to decrypt. can use meaningful phrases or other data lines, as the encryption, decryption keyword, more suitable for general data encryption and decryption.
Case:
1. Convert mode:
Copy Code code as follows:
A) Use tempdb
b) Go
c) CREATE TABLE test
D) (
e) UserID INT IDENTITY (1, 1),
f) userName VARCHAR (10),
g) Usersalary FLOAT,
h) cyberalary NVARCHAR (MAX)
i));
J
k) INSERT into TEST
L) (UserName, Usersalary)
m) VALUES (' Taici ', 1234),
N) (' Hailong ', 3214),
O) (' Meiyuan ', 1111)
p)--alter TABLE test
Q)--add usernewsalary VARBINARY (512)
R)--Convert the data into varbinary using the conversion function to change the encoding method.
s) SELECT *,
T) CONVERT (VARBINARY (), usersalary)
u) from test
V)--converts data to int to restore the original encoding
W) SELECT *,
x) CONVERT (INT, Usersalary)
Y) from Test
2. Symmetric key:
Copy Code code as follows:
A)--Create a symmetric key
b) Use AdventureWorks
c) Go
d) CREATE symmetric KEY SymKey123
e) with Algorithm=triple_des encryption by password= ' P@ssw0rd '
f) Go
g)--note: When enabled, you need to open symmetric key with the key password, otherwise the resulting data will be null values. And it needs to be paired with the Key_GUID function.
h)--open symmetric key
i) OPEN symmetric KEY SymKey123 decryption by password= ' P@ssw0rd ';
j)--Data encryption
k) SELECT *, EncryptByKey (key_guid (' SymKey123 '), CONVERT (VARCHAR (max), AddressLine1)
L) from Person.Address
M
N)--check the length of the encryption, using the DATALENGTH () function
O) SELECT datalength (EncryptByKey (Key_GUID (' SymKey123 '), CONVERT (VARCHAR (MAX), AddressLine1))
P) from Person.Address
Q) Go
R)--Update the encrypted data to the original column
s) UPDATE person.address
T) SET Addressline2=encryptbykey (Key_GUID (' SymKey123 '), CONVERT (VARCHAR (max), AddressLine1)
u)--decryption: The decryption process also requires open symmetric KEY and requires the use of DecryptByKey and CONVERT functions
V) OPEN symmetric KEY SymKey123 decryption by password= ' P@ssw0rd ';
W
x) SELECT Addressid,convert (VARCHAR (max), CONVERT (VARCHAR (max), DecryptByKey (AddressLine2))
Y) from Person.Address
3. Asymmetric key:
Copy Code code as follows:
A)--asymmetric keys use two different keys, so encryption is not required to enter password authentication, but the decryption requires
b) Use AdventureWorks
c) Go
D CREATE Asymmetric KEY AsymKey123 with algorithm=rsa_2048 encryption by password= ' P@ssw0rd ';
e) Go
F
g)--Add new columns to store encrypted data
h) ALTER TABLE person.address ADD AddressLine3 nvarchar (MAX)
i) go
j)--for encryption
k) SELECT *,encryptbyasymkey (asymkey_id (' AsymKey123 '), CONVERT (VARCHAR (MAX), AddressLine1)
L) from Person.Address
m) Go
N
O)--update data to a new column
P) UPDATE person.address
Q) SET Addressline3=encryptbyasymkey (asymkey_id (' AsymKey123 '), CONVERT (VARCHAR (MAX), AddressLine1)
R
S
T) SELECT *--addressline3
u) from person.address
V
W)--decryption: This process must be decrypted with a password, where the type to be the same as the encryption, such as encryption with varchar, and here with nvarchar words can not be decrypted.
x) SELECT top Addressid,convert VARCHAR (max), CONVERT (VARCHAR (max), DecryptByAsymKey (asymkey_id (' AsymKey123 '), Addressline3,n ' P@ssw0rd ')) as Decryptedata
Y) from Person.Address
4, Certificate encryption:
Copy Code code as follows:
A)--Certificate encryption: First establish the certificate (certificate)
b) CREATE certificate certkey123--card title
c) encryption by password= ' P@ssw0rd '--password
d) subject= ' address certificate ',--certificate description
e) start_date= ' 2012/06/18 ',--certificate Effective Date
f) expiry_date= ' 2013/06/18 ';--Certificate expiration date
g) Go
h)--Use certificate encryption
i) SELECT *,encryptbycert (cert_id (' certKey123 '), CONVERT (VARCHAR (MAX), AddressLine1) cyberaddress
j) from Person.Address
K
L)--Add new columns to store encrypted data
m) ALTER TABLE person.address ADD AddressLine4 Nvarchar (MAX)
N
O)--Put the encrypted data into the new column
P) UPDATE person.address
Q) SET Addressline4=encryptbycert (cert_id (' certKey123 '), CONVERT (VARCHAR (MAX), AddressLine1)
R
s)--decryption
T) SELECT Addressid,convert (VARCHAR max), CONVERT (VARCHAR (max), DecryptByCert (cert_id (' certKey123 '), AddressLine4, N ' P@ssw0rd ')) decryaddress
u) from person.address
5. Phrase encryption:
Copy Code code as follows:
A)-phrase encryption: This process is simpler, only need to use the Encryptbypassphrase function, the use of phrase encryption, the reference data navigation can not be changed, or decryption failure.
b) SELECT *,addressline5=encryptbypassphrase (' P@ssw0rd ', CONVERT (varbinary,addressline1), Addressid)
c) from Person.Address
D
e)--Add a new column to store the data, note that the Encryptbypassphrase function returns the varbinary type
f) ALTER TABLE person.address ADD AddressLine5 VARBINARY (256)
G
h)--Update the data using P@ssw0rd and ADDRESSID data rows as passphrase
I
j) UPDATE Person.Address
k) SET addressline5=encryptbypassphrase (' P@ssw0rd ', CONVERT (varbinary,addressline1), Addressid)
L
m) SELECT * from Person.Address
question two: How to protect the definition of database objects to avoid transient exposure sensitive information?
The general protection is to encrypt the object using the with encryption when the object is created, so that the definition cannot be viewed. But the problem is that it is a problem for maintenance, and that part of the object is lost when the backup is restored.
One solution is to keep the definition statements in the extended properties of the object, which solves the problem above.
Here's an example:
Copy Code code as follows:
--1, establishing an encrypted stored procedure
Use AdventureWorks
Go
CREATE PROC Test
With encryption
As
SELECT SUSER_SNAME (),
USER_NAME ()
Go
--2, remove this definition, use phrase encryption with encryptbypassphrase function encryption, and then use Sys.sp_addextendedproperty stored procedure, specify an extension name.
Use AdventureWorks
Go
DECLARE @sql VARCHAR (MAX)
SET @sql = ' CREATE PROC Test with encryption as SELECT Suer_sname (), user_name () go '
--3, encrypt content and convert to sql_variant data type
DECLARE @bsql sql_variant
SET @bsql = (SELECT CONVERT (sql_variant, encryptbypassphrase (' P@ssw0rd '),
CONVERT (VARCHAR (MAX), @sql))
)
--4, new to the extended properties of the specified stored procedure:
EXEC Sys.sp_addextendedproperty @name = n ' Test definition ', @value = N ' System. Byte[] ',
@level0type = N ' SCHEMA ', @level0name = n ' dbo ', @level1type = N ' PROCEDURE ',
@level1name = N ' Test '
Go
EXEC sys.sp_addextendedproperty @name = N ' Code content ',
@value = N ' CREATE PROC Test with encryption as SELECT Suer_sname (), user_name (),
@level0type = N ' SCHEMA ', @level0name = n ' dbo ', @level1type = N ' PROCEDURE ',
@level1name = N ' Test '
Go
--5, restoring
DECLARE @pwd VARCHAR = ' P@ssw0rd '
--Pass phrase
DECLARE @proc VARCHAR = ' Test '
--Stored Procedure name
DECLARE @exName NVARCHAR (100) = ' Code content '
--Extended attribute name
--Query the original results
SELECT value
From Sys.all_objects as SP
INNER JOIN sys.extended_properties as P on p.major_id = sp.object_id
and p.minor_id = 0
and P.class = 1
WHERE (p.name = @exName)
and (Sp.type = N ' P '
OR Sp.type = N ' RF '
OR sp.type = ' PC '
)
and (Sp.name = @proc
and schema_name (sp.schema_id) = N ' dbo '
)
)
question three, how to allow a specified user to be able to truncate the data table operation?
Truncate is significantly faster and more efficient when you delete a large table, but because it does not require a log and must be a full table deletion, it causes the data to be unrecoverable. It also shows the danger.
However, executing truncate requires one of the high privilege roles within the table owner, system administrator, db_owner, db_ddladmin, to perform.
For this, you can use the EXECUTE AS expression after 05 to toggle permission content:
1. Switch login: EXECUTE as Login
2. Toggle User: EXECUTE as user
3. Toggle Execute Permission: EXECUTE as owner/' user name ' to perform the job with high user privileges. This step can perform a high privilege operation under a low privilege entity and can also avoid security vulnerabilities.
In addition, only execute AS caller can be executed across the database, while permission switching in other ways is limited to this database.
Note: When executing execute AS user impersonation, you need to obtain the authorization of the impersonated user first.
You can use revert to restore the original identity before the content is executed.
question four, how do I get information about the front-end connection, such as IP address and computer name?
For DBA work or for certain special applications, you need to obtain system information for front-end applications. This information, if stored using a user table, will cost more than directly reading the database system information. Therefore, it is recommended that you read the system tables appropriately:
Session information can be found in the master database during sessions that connect to the database, but starting from 05, there are many DMV/DMF to implement these features:
L Master.dbo.sysprocesses or master.sys.sysprocesses: The SPID that provides the execution phase, the computer name, the application name, and so on.
L sys.dm_exec_sessions: Records the basic information of each session, including ID, computer name, program name, application name, etc.
L Sys.dm_exec_connections: Log each front-end information connected to the SQL Server instance, including network location, connection time, and so on.
L Select client_net_address ' client IP address ', local_net_address ' SQL serverip address ', *
L from Sys.dm_exec_connections
L where session_id=@ @spid
After 2005, it is recommended that the DMV be used instead of system tables.
question Five, how do I avoid SQL injection attacks?
For database applications, either that kind of dbms,sql injection is a big risk.
To avoid SQL injection, you should at least do the following:
1. Check the input data, the application do not trust user input data, must be tested before entering the database. To exclude special symbols such as% 、--.
2. Avoid the error message of fruit exposure. It is recommended that you convert to a Windows event or convert to an application internal error message.
3. Using parameterized queries or stored procedures
Attention:
Dynamic SQL is the main culprit that causes SQL injection
Author: Huang Ji
