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的访问权限为允许访问外部资源。这样,存储过程就可以访问数据库了。

SQLServer控制用户访问权限表

一、需求 在管理数据库过程中,我们经常需要控制某个用户访问数据库的权限,比如只需要给这个用户访问某个表的权限,甚至是CRUD的权限,更小粒度的还可以去到某几个字段的访问权限。写这篇文章就是说明下...
  • delphiorang
  • delphiorang
  • 2017年03月15日 22:05
  • 317

SQL Server 2008数据库创建用户只读权限的两种方式

在SQL Server 2008中,为了保护数据库的安全,需要给不同的使用者开通不同的访问用户,那么如何简单的控制用户的权限呢?下面我们就创建一个只读用户,给大家学习使用。           一、...
  • English0523
  • English0523
  • 2015年08月10日 09:25
  • 5771

SQL Server中服务器角色和数据库角色权限详解

目录(?)[+] SQLServer中服务器角色和数据库角色权限详解 角色 当几个用户需要在某个特定的数据库中执行类似的动作时(这里没有相应的Windows用户组),就可以向该数据库中添加一个...
  • wangzhen209
  • wangzhen209
  • 2016年07月07日 17:00
  • 7075

Oracle数据库视图与权限问题

前几天客户遇上这样一个问题,某个用户A将视图的SELECT给予另一个用户B,但是用户B查询这个视图时,仍然报错:ORA-01031: 权限不足。这是怎么一回事呢?下面来模拟一下这个过程:   有...
  • dzf203
  • dzf203
  • 2014年08月21日 11:45
  • 1424

mysql管理数据库用户和访问权限

1:创建一个数据库用户: MariaDB [(none)]> create user jarry@localhost identified by "root"; Query OK, 0 rows af...
  • wuliowen
  • wuliowen
  • 2017年03月31日 15:24
  • 2011

关于对表的读写权限的控制示例演示

前言:       说数据库权限控制,首先简单了解下数据库中存在的两种角色:服务器角色和数据库角色。       服务器角色: 内置服务器角色 说明 sy...
  • u011015550
  • u011015550
  • 2014年12月31日 14:31
  • 929

访问数据库时如何解决并发问题

 在数据库访问时。如果处理并发访问的问题 或者当一个操作员对一个对象作读操作时。另一个操作员对此对象作写操作的时候 如何避免死锁发生 /**********  加锁   *******...
  • fengxu511
  • fengxu511
  • 2015年10月03日 09:36
  • 1101

MySql修改访问权限,允许他人访问自己的数据库和修改mysql的密码

1.MySql-Server 出于安全方面考虑只允许本机(localhost, 127.0.0.1)来连接访问. 这对于 Web-Server 与 MySql-Server 都在同一台服务器上的网站架...
  • spt_dream
  • spt_dream
  • 2017年05月15日 18:16
  • 1478

数据库连接异常的解决过程

首先先简述一下遇到的问题,今天早上来了之后,网站突然报404的问题,不废话,直接看tomcat的日志吧,日志如下: ### Error querying database. Cause: com....
  • ksdb0468473
  • ksdb0468473
  • 2017年03月01日 11:07
  • 559

android sqlite数据库并发问题的详细描述和解决方案

线程A打开数据,正在使用数据库,这时cpu片段分到线程B,线程A挂起。线程B进入执行获取打开db时没有问题,线程B进行操作,在片段时间内数据操作完成,最后关闭数据库database.close()。线...
  • nightcurtis
  • nightcurtis
  • 2015年01月22日 09:21
  • 4769
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQLServer存储过程访问数据库权限异常问题的解决方案
举报原因:
原因补充:

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