分享
 
 
 

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

王朝mssql·作者佚名  2007-01-16
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有