文件名称：存储过程的安全及性能优化
文件大小：133KB
文件格式：DOC
更新时间：2015-03-28 14:40:06
存储过程 安全 性能优化
存储过程的安全及性能优化
存储过程分类
系统存储过程
自定义存储过程
SQL Server使用者编写的存储过程
扩展存储过程
动态链接库(DLL)函数的调用看，主要用于客户端和服务器端之间进行通信
exec master..xp_cmdshell 'dir *.exe' -- 执行目录命令查询[sql2005\sql2008]
exec master..xp_fixeddrives --列出硬盘分区各自可用空间
xp_regwrite根键，子键，值名，值类型，值【sql2008拒绝访问】
写入注册表，例如：
exec master..db.xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion\run','TestValueName','reg_sz','hello'
xp_regdeletevalue 根键，子键，值名【sql2008拒绝访问】
删除注册表某个值
xp_regdeletekey键，值【sql2008拒绝访问】
删除该键下包括的所有值
xp_cmdshell语法
xp_cmdshell {'command_string'} [,no_output]
command_string是在操作系统命令行解释器上执行的命令字符串。command_string数据类型为varchar(255)或者nvarchar(4000),没有默认值
no_output为可选参数，可以控制是否想客户端返回信息
该存储过程一般情况下被禁用的，需要手动开启使用，如下：
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1—1表示启用，0表示禁用
go
reconfigure
go
删除xp_cmdshell
SQL SERVER200删除xp_cmdshell
use master
exec sp_dropextendedproc 'xp_cmdshell'
go
SQL SERVER2005以上禁用xp_cmdshell,但不能删除掉
exec sp_configure 'xp_cmdshell',0 —1表示启用，0表示禁用
go
reconfigure --让sp_configurre立即生效
go
exec sp_configure 'show advanced options',0
go
reconfigure
go
--注意：SQL SERVER2008考虑安全性很多存储过程直接被拒绝访问
恢复/启用扩展存储过程
SQLServer2000
use master
exec sp_addextendedproc xp_cmdshell,'xplog70.dll'
go
SQL Server2005或SQL Server2008启用xp_cmdshell
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1—1表示启用，0表示禁用
go
reconfigure
go
扩展存储过程的定义
扩展存储过程是SQL Server中的另一类存储过程，它是以其它语言编写的外部程序，是以动态链接库（DLL）形式存储在服务器上，最终SQLServer就可以动态加载并执行它们
编写好后使用SQLServer的固定角色sysadmin注册该扩展存储过程,并将执行权限授予其它用户,这个扩展存储过程只能添加到master数据库。
在编写扩展存储过程中可能要用到某些系统存储过程，这些系统存储过程如下：
利用OLE自动化存储过程调用dll
1.创建类库程序集
namespace PB_ExtendProcedure
{
public class ExtendProcedure
{
public string SayHi()
{
return "hello world";
}
}
}
2.生成动态链接库并注册到系统中
2.1.生成动态链接库使用VS2010命令行工具
使用sn命令生成一个强命名文件：
sn -k helpkey.snk
使用csc生成dll
csc /t:library /keyfile:helperkey.snk ExtendProcedure.cs
向系统注册这个dll
regasm /tlb:ExtendProcedure.tlb ExtendProcedure.dll /codebase
2.2.在SQL Server中编写扩展存储过程
--sp_OACreate
--sp_OAMethod
--sp_OADestroy
--sp_OAGetErrorInfo
流程：
DECLARE @object int --返回创建的对象
DECLARE @hr int --过程返回值
DECLARE @return varchar(255) --dll方法的返回值
DECLARE @src varchar(255),@desc varchar(255) ---过程的错误原因、描述
--1.创建对象
EXEC @hr = sp_OACreate 'PB_ExtendProcedure.ExtendProcedure',@object out
IF @hr<>0
BEGIN
EXEC sp_OAGetErrorInfo @object,@src,@desc out
select hr = convert(varchar(4),@hr),Source=@src,Description=@desc
return
END
--2.调用方法
EXEC @hr = sp_OAMethod @object,'SayHi',@return out
IF @hr<>0
BEGIN
EXEC sp_OAGetErrorInfo @object,@src,@desc out
select hr = convert(varchar(4),@hr),Source=@src,Description=@desc
return
END
print @return
--3.销毁对象实例
EXEC @hr = sp_OADestroy @object
IF @hr<>0
BEGIN
EXEC sp_OAGetErrorInfo @object,@src,@desc out
select hr = convert(varchar(4),@hr),Source=@src,Description=@desc
return
END
注意：默认情况sqlserver2008是禁止调用ole自动化存储过程的，解决方法如下：
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'ole automation procedures',1
go
reconfiugre
go
加密存储过程
创建加密存储过程语法
CREATE PROCEDURE