mysql加密t,T-SQL问题解决集锦——数据加解密

a)--短语加密：该过程较为简单，只需要使用EncryptByPassPhrase函数，使用短语加密时，参考的数据航不可以变动，否则解密失败。

b)SELECT *,AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID)

c)FROM Person.Address

d)

e)--添加新列存放数据，注意，ENCRYPTBYPASSPHRASE函数返回的是VARBINARY类型

f)ALTER TABLE Person.Address ADD AddressLine5 VARBINARY(256)

g)

h)--将数据更新，过程中使用P@ssw0rd和AddressID数据行当成密码短语

i)

j)UPDATE Person.Address

k)SET AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID)

l)

m)SELECT * FROM Person.Address

问题二：如何保护数据库对象定义，避免发生过渡暴露敏感信息？

一般的保护措施是在创建对象时使用WITH ENCRYPTION来把对象加密，这样就无法查看定义。但是问题是对于维护来说就成了问题，而且备份还原时这部分对象是会丢失的。

其中一个解决方法是把定义语句放到对象的【扩展属性】中保存，这样能解决上面的问题。

下面举个例子:

--1、建立已加密的存储过程

USE AdventureWorks

GO

CREATE PROC test

WITH ENCRYPTION

AS

SELECT SUSER_SNAME() ,

USER_NAME()

GO

--2、将上述定义内容去除，利用短语加密搭配EncryptByPassPhrase函数加密，然后在用sys.sp_addextendedproperty存储过程，指定一个扩展名称。

USE AdventureWorks

GO

DECLARE @sql VARCHAR(MAX)

SET @sql = 'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO'

--3、将内容加密后转换成sql_variant数据类型

DECLARE @bsql SQL_VARIANT

SET @bsql = ( SELECT CONVERT(SQL_VARIANT, ENCRYPTBYPASSPHRASE('P@ssw0rd',

CONVERT(VARCHAR(MAX), @sql)))

)

--4、新增到指定存储过程的扩展属性中：

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

--5、还原

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'

)

)