从2005开始提供了数据库层面的数据加密与解密。其实现方式主要有以下:
1、 利用CONVERT改变编码方式:
利用该函数把文字或数据转换成VARBINARY。但该方式不具备保护数据的能力,仅避免浏览数据的过程中能直接看到敏感数据的作用。
2、 利用对称密钥:
搭配EncryptByKey进行数据加密。使用DecryptByKey函数进行解密。这种方式比较适合大数据量。因为对称密钥的过程耗用资源较少。
3、 利用非对称密钥:
搭配EncryptByAsymKey进行数据加密。使用DecryptByAsymKey函数进行解密。用于更高安全级别的加解密数据。因为耗用资源叫多。
4、 利用凭证的方式:
搭配EncryptByCert进行加密和DecryptByCert函数进行解密。比较类似非对称密钥。
5、 利用密码短语方式:
搭配EncryptBypassPhrase进行加密,使用DecryptByPassPhrase函数来解密。可以使用有意义的短语或其他数据行,当成加密、解密的关键字,比较适合一般的数据加解密。
案例:
1、 Convert方式:
- 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)
- q)
- r)
- s) SELECT * ,
- t) CONVERT(VARBINARY(512), userSalary)
- u) FROM test
- v)
- w) SELECT * ,
- x) CONVERT(INT, userSalary)
- y) FROM test
2、对称密钥:
- a)
- b) USE AdventureWorks
- c) GO
- d) CREATE SYMMETRIC KEY SymKey123
- e) WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY PASSWORD='P@ssw0rd'
- f) GO
- g)
- h)
- i) OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd';
- j)
- k) SELECT * ,ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
- l) FROM Person.Address
- m)
- n)
- o) SELECT DATALENGTH(ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1)))
- p) FROM Person.Address
- q) GO
- r)
- s) UPDATE Person.Address
- t) SET AddressLine2=ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
- u)
- 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、非对称密钥:
- a)
- b) USE AdventureWorks
- c) GO
- d) CREATE ASYMMETRIC KEY AsymKey123 WITH ALGORITHM=RSA_2048 ENCRYPTION BY PASSWORD='P@ssw0rd';
- e) GO
- f)
- g)
- h) ALTER TABLE Person.Address ADD AddressLine3 nvarchar(MAX)
- i) GO
- j)
- k) SELECT *,ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1))
- l) FROM Person.Address
- m) GO
- n)
- o)
- p) UPDATE Person.Address
- q) SET AddressLine3=ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1))
- r)
- s)
- t) SELECT *
- u) FROM Person.Address
- v)
- w)
- x) SELECT TOP 10 AddressID,CONVERT(VARCHAR(MAX),CONVERT (VARCHAR(MAX ),DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey123'),AddressLine3,N'P@ssw0rd'))) AS Decryptedata
- y) FROM Person.Address
4、证书加密:
- a)
- b) CREATE CERTIFICATE certKey123
- c) ENCRYPTION BY PASSWORD='P@ssw0rd'
- d) WITH SUBJECT='Address Certificate',
- e) START_DATE='2012/06/18',
- f) EXPIRY_DATE='2013/06/18' ;
- g) GO
- h)
- i) SELECT *,ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1)) cyberAddress
- j) FROM Person.Address
- k)
- l)
- m) ALTER TABLE Person.Address ADD AddressLine4 Nvarchar(MAX )
- n)
- o)
- p) UPDATE Person.Address
- q) SET AddressLine4=ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1))
- r)
- s)
- t) SELECT AddressID,CONVERT(VARCHAR(MAX ),CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('certKey123'),AddressLine4,N'P@ssw0rd'))) DecryAddress
- u) FROM Person.Address
5、短语加密:
- a)
- b) SELECT *,AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID)
- c) FROM Person.Address
- d)
- e)
- f) ALTER TABLE Person.Address ADD AddressLine5 VARBINARY(256)
- g)
- h)
- i)
- j) UPDATE Person.Address
- k) SET AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID)
- l)
- m) SELECT * FROM Person.Address
问题二:如何保护数据库对象定义,避免发生过渡暴露敏感信息?
一般的保护措施是在创建对象时使用WITH ENCRYPTION来把对象加密,这样就无法查看定义。但是问题是对于维护来说就成了问题,而且备份还原时这部分对象是会丢失的。
其中一个解决方法是把定义语句放到对象的【扩展属性】中保存,这样能解决上面的问题。
下面举个例子 copy
-
- USE AdventureWorks
- GO
- CREATE PROC test
- WITH ENCRYPTION
- AS
- SELECT SUSER_SNAME() ,
- USER_NAME()
- GO
-
- USE AdventureWorks
- GO
- DECLARE @sql VARCHAR(MAX)
- SET @sql = 'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO'
-
-
- DECLARE @bsql SQL_VARIANT
- SET @bsql = ( SELECT CONVERT(SQL_VARIANT, ENCRYPTBYPASSPHRASE('P@ssw0rd',
- CONVERT(VARCHAR(MAX), @sql)))
- )
-
-
- EXEC sys.sp_addextendedproperty @name = N'test定义', @value = N'System.Byte[]',
- @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'PROCEDURE',
- @level1name = N'test'
- GO
- EXEC sys.sp_addextendedproperty @name = N'代码内容',
- @value = N'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO',
- @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'PROCEDURE',
- @level1name = N'test'
- GO
-
-
- DECLARE @pwd VARCHAR(100)= 'P@ssw0rd'
-
-
- DECLARE @proc VARCHAR(100)= 'test'
-
-
- DECLARE @exName NVARCHAR(100)= '代码内容'
-
-
-
-
- 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'
- )
- )