SQLServer存储过程访问数据库权限异常问题的解决方案

原创 2006年05月31日 17:20:00
最近用ASP.net 2.0 + SQL Server做一个网页表单的提交,使用C#编写存储过程来完成向SQL Server数据库表中插入记录的操作。在调用这个存储过程时,出现了关于存储权限的一个异常。下面详述异常产生的过程和解决方案。

1.操作步骤:
1)使用ASP.net 2.0,用C#写了一个存储过程,对数据库test中的一个表进行操作,代码如下:

public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Submit(string strAnswer)
{
using (SqlConnection connection = new SqlConnection("Data Source=WS;Initial Catalog=test;Integrated Security=False;Trusted_Connection=yes;"))
{
connection.Open();     // ***执行到这一步出现异常,详见下文所述***
String cmdTxt = "INSERT INTO dbo.Response_SCL90 VALUES(" + strAnswer + ")";
SqlCommand command = new SqlCommand(cmdTxt, connection);
SqlDataReader reader = command.ExecuteReader();

}
}
}

2)编译生成了Submit_Answer.dll,接着,在SQL Server中注册这个dll,并创建存储过程,SQL脚本如下:

CREATE ASSEMBLY Submit_Answer
FROM 'D:/study/C#/测评系统/WebSite1/StoredProcedure/Submit_Answer/bin/Debug/Submit_Answer.dll';
GO

CREATE PROCEDURE dbo.Submit_Answer
(
@strAnswer nvarchar(256)
)
WITH EXECUTE AS OWNER
AS
EXTERNAL NAME Submit_Answer.StoredProcedures.Submit
GO

3)最后,在.net中调用这个存储过程,代码如下:
SqlConnection connection = new SqlConnection("Data Source=WS;Initial Catalog=test;Integrated Security=False;Trusted_Connection=yes;");

String cmdTxt = "dbo.Submit_Answer";
SqlCommand command = new SqlCommand(cmdTxt, connection);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@strAnswer", SqlDbType.NVarChar);
command.Parameters["@strAnswer"].Value = strAnswer;

command.Connection.Open();
SqlDataReader dr = command.ExecuteReader();

command.Connection.Close();

2. 异常描述:
在执行到存储过程 connection.Open();一句时,出现异常,异常描述和当时的堆栈信息如下:
异常详细信息: System.Data.SqlClient.SqlException: A .NET Framework error occurred during execution of user defined routine or aggregate 'Submit_Answer':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at StoredProcedures.Submit(SqlChars strAnswer)

3.简要分析:
看来是在存储过程中没有对数据库的访问权限,因为在数据库连接Open时就出错了,查找了一些资料,也没发现问题在哪。后来便在社区中提问了。

4.解决方案:
在“MS-SQL Server 疑难问题”版面,zlp321002(龙卷风2006)对这个问题进行解答,详情请见:
http://community.csdn.net/Expert/TopicView3.asp?id=4790457
现整理和总结如下:
(非常感谢zlp321002(龙卷风2006),下面描述的解决方案源自zlp321002(龙卷风2006))

1)打开数据库的外部访问选项(external_access_option)

Alter Database 数据库名
SET TRUSTWORTHY ON

reference:关于数据库外部访问选项(external_access_option)的描述(摘录自SQL Server Books Online)

TRUSTWORTHY { ON | OFF }

    ON

        Database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.

    OFF

        Database modules in an impersonation context cannot access resources outside the database.

    TRUSTWORTHY is set to OFF whenever the database is attached.

    By default, the master database has TRUSTWORTHY set to ON. The model and tempdb databases always have TRUSTWORTHY set to OFF, and the value cannot be changed for these databases.

    To set this option, requires membership in the sysadmin fixed server role.

    The status of this option can be determined by examining the is_trustworthy_on column in the sys.databases catalog view.


2)设置存储过程dll的PERMISSION_SET为EXTERNAL_ACCESS

将操作步骤第2)步中原来的
CREATE ASSEMBLY Submit_Answer
FROM 'D:/study/C#/测评系统/WebSite1/StoredProcedure/Submit_Answer/bin/Debug/Submit_Answer.dll'
GO
改为:
CREATE ASSEMBLY Submit_Answer
FROM 'D:/study/C#/测评系统/WebSite1/StoredProcedure/Submit_Answer/bin/Debug/Submit_Answer.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

reference:关于CREATE ASSEMBLY中PERMISSION_SET 设置(摘录自SQL Server Books Online)
PERMISSION_SET { SAFE | EXTERNAL_ACCESS | UNSAFE }

Specifies a set of code access permissions that are granted to the assembly when it is accessed by SQL Server. If not specified, SAFE is applied as the default.

We recommend using SAFE. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.

EXTERNAL_ACCESS enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.

UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code.

Security Note:

SAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing resources outside an instance of SQL Server. We recommend using EXTERNAL_ACCESS for assemblies that access resources outside of an instance of SQL Server. EXTERNAL_ACCESS assemblies include the reliability and scalability protections of SAFE assemblies, but from a security perspective are similar to UNSAFE assemblies. This is because code in EXTERNAL_ACCESS assemblies runs by default under the SQL Server service account and accesses external resources under that account, unless the code explicitly impersonates the caller. Therefore, permission to create EXTERNAL_ACCESS assemblies should be granted only to logins that are trusted to run code under the SQL Server service account. For more information about impersonation, see CLR Integration Security. Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL Server process space that can potentially compromise the robustness of SQL Server. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies. Only members of the sysadmin fixed server role can create and alter UNSAFE assemblies.



做完上述修改后,再次运行网页,提交表单,不再出现异常了。

5. 小结:

看来这个存储过程访问权限的解决是从下面两个方面进行:首先打开数据库的外部访问选项,允许数据库的模块访问外部资源;接着设置那个存储过程dll的PERMISSION_SET,即设置这个dll的访问权限为允许访问外部资源。这样,存储过程就可以访问数据库了。

相关文章推荐

Asp.net(C#)基于存储过程分页的完整解决方案

先贴出效果图: 具体过程如下:1.分页的存储过程 ( @Tables varchar( 1000 ) = ' ', --表名称或级联名称 @PrimaryKey varchar(100)= ' ...
  • lxiron
  • lxiron
  • 2011年02月28日 20:31
  • 916

执行存储过程时报“在尝试加载程序集 ID 65645 时 Microsoft .NET Framework 出错”,解决方案

新库是直接复制的模板库 执行存储过程时报如下错 消息 10314,级别 16,状态 11,过程sp_Sync_CmsArticleToSearchs,第 30 行在尝试加载程序集 ID 65645...

Oracle存储过程update受外键约束的主键值时完整性冲突解决方案

背景:虽然在数据库操作中我们并不提倡修改主键,但是确实在实际生活中有这样的业务需求:表A有主键KA,表B中声明了一个references A(KA)的外键约束,我们需要修改A中某条目KA的值并且更新B...

Oracle(存储过程,触发器等中) if语句中不能有子查询的原因和解决方案

如果我们输入并运行以下代码:   begin if 'U001' in (select userId from userInfo) then dbms_output.put_lin...

ThinkPHP调用存储过程不能返回结果集的解决方案

http://blog.csdn.net/cyd1919/article/details/8859898 对于逻辑比较复杂的增删改差来说,个人认为ThinkPHP的查询语句,连贯操作是不能满...

SQL Server 存储过程 sp_helptext的不足以及解决方案

介绍sp_helptext 前 先介绍下系统表sys.syscomments   以及  系统视图   sys.sql_modules。 sys.syscomments:包含数据库中每个视图、规...

C#中用Oracle 执行存储过程返回DataSet报[ORA-08103: 对象不再存在]解决方案

执行存储过程返回DataSet:  1.存储过程中 一定要有一个Output参数的游标,以便返回存储过程 --建立存储过程的返回临时表 create global temporary...

ThinkPHP调用存储过程不能返回结果集的解决方案

对于逻辑比较复杂的增删改差来说,个人认为ThinkPHP的查询语句,连贯操作是不能满足要求的。同样,熟悉存储过程的朋友们更加倾向于用存储过程来替代多次的增删改查操作,或者用以PHP代码实现。 我是一...
  • cyd1919
  • cyd1919
  • 2013年04月27日 20:59
  • 5796

关于SQLSERVER 事物的运用(3)嵌套事物以及嵌套存储过程的异常的处理

概述:这一部分才是应用的重点,掌握了前两部分,基本上可以应付80%的应用。存储过程为什么要嵌套,我们会把一些业务功能进行分离,解耦,可以单部执行(如PA),也可以以封装的方式执行(如PB),就涉及到P...
  • ylmhll
  • ylmhll
  • 2013年05月10日 17:53
  • 588

数据库(二)--数据约束 数据库设计 多表查询 MySQL存储过程 触发器 数据库权限问题

数据约束1.给表添加数据约束,从而约束用户操作表的行为。2.默认值约束(default):当一个字段有默认值约束时,如果给表中插入数据时没有给该字段插入,则给该字段分配一个默认值。CREATE TAB...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQLServer存储过程访问数据库权限异常问题的解决方案
举报原因:
原因补充:

(最多只允许输入30个字)