T-SQL问题解决集锦 数据加解密全集（2）

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

一般的保护措施是在创建对象时使用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'
)
)



问题三、如何让指定用户可以对数据表进行Truncate操作？

Truncate在对大表全删除操作时，会明显比Delete语句更快更有效，但是因为它不需要存放日志，并且一定是全表删除，所以造成数据的不可恢复性。也说明了它的危险性。
但是，执行Truncate需要有表拥有者、系统管理员、db_owner、db_ddladmin这些里面的其中一种高权限角色才能执行。
对此，可以使用05之后的EXECUTE AS表达式来实现权限内容的切换：

1. 切换登录：EXECUTE AS LOGIN

2. 切换用户：EXECUTE AS USER

3. 切换执行权限：EXECUTE AS owner/'user name'，利用高用户权限来执行作业。此步骤可以在低权限实体下执行高权限操作，也能避免安全性漏洞。

另外，只有EXECUTE AS Caller可以跨数据库执行，而其他方式进行的权限切换仅限制于本数据库。

注意：执行EXECUTE AS USER模拟使用者切换时，需要先获得被模拟用户的授权。

可以使用REVERT来还原执行内容前的原始身份。


问题四、如何获取前端连接的信息，如IP地址和计算机名？

对于DBA工作或者某些特殊的应用程序，需要获取前端应用的系统信息。而这些信息如果用用户表来存储，代价会比直接读取数据库系统信息要大。所以建议适当读取系统表：

在连接数据库的session期间，都可以在master数据库中找到session信息，但是从05开始，有了很多DMV/DMF来实现这些功能：

l Master.dbo.sysprocesses或者master.sys.sysprocesses：提供执行阶段的SPID、计算机名、应用程序名等。

l Sys.dm_exec_sessions：记录每个session的基本信息，包括id、计算机名、程序名、应用程序名等

l Sys.dm_exec_connections：记录每个连接到SQLServer实例的前端信息，包括网络位置、连接时间等等。

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

在2005以后，建议使用DMV取代系统表。

问题五、如何避免SQL注入的攻击？

对于数据库应用程序，无论是那种DBMS，SQL注入都是一大隐患。

要避免SQL注入，应该最起码做到以下几点：

1. 检查输入的数据，应用程序不要相信用户输入的数据，必须经过检验后才能输入数据库。要排除％、--等特殊符号。

2. 避免果度暴露错误信息。建议可以转换成Windows事件或者是转换成应用程序内部错误信息。

3. 使用参数化查询或者存储过程

注意：
动态SQL是导致SQL注入的主凶

作者： 黄钊吉