代码之家 › 专栏 › 技术社区 › Matt Brunell

使用存储过程访问数据有哪些安全好处?

stored-procedures security sql-server sql

Matt Brunell · 技术社区 · 15 年前

我已经看到一些指南,其中建议您通过存储过程分层所有数据访问来保护数据库。

例如:

 --// Logged in as 'sa'
 USE AdventureWorks;
 GRANT SELECT ON Person.Address(AddressID, AddressLine1) to Matt;
 GRANT UPDATE ON Person.Address(AddressLine1) to Matt;

 --// Logged in as 'Matt'
 SELECT * from Person.Address;                       --// Fail
 SELECT AddressID, AddressLine1 from Person.Address; --// Succeed
 UPDATE Person.Address SET AddressLine1 = '#____ 2700 Production Way' 
        WHERE AddressID = 497;                       --// Succeed

假设您可以针对CRUD操作保护表甚至列,那么使用存储过程如何提供额外的安全性或安全性管理?

9 回复 | 直到 15 年前

Charles Bretana 8 年前

因为通过限制对这些存储过程的所有访问,您已经建立了数据库的定义接口,所有访问都必须通过该接口进行。。。由于您将对表和视图执行拒绝的直接选择、插入、更新和删除操作,因此没有人可以直接编写自己设计的sql来执行任何他们想执行的操作。。。如果您想将插入到员工表中的内容限制在员工被分配到三个以上项目的情况下,仅限于在熟练程度测试中得分大于85的员工,那么您可以将该约束写入SaveEmployee存储过程,并让它向任何尝试执行该操作的客户端代码抛出异常。。。

当然,您可以使用客户端代码做同样的事情,但是使用存储过程使流程更易于设计和管理,因为它位于一个位置,所有尝试访问此数据库系统的应用程序都必须符合您在存储过程中定义的任何约束和/或安全规定。。。如果存储过程是插入或更新记录的唯一方法,那么编写新的独立客户端应用程序并访问数据库的流氓开发人员不能忽略或绕过存储过程中的约束或安全规定。。。

Tom H 15 年前

您可能不想让Matt全权负责直接更新某些表或列。如果Matt决定这么做怎么办:

UPDATE Person.Address SET AddressLine1 = NULL

哎呀。Matt忘了WHERE子句,只是用水管冲洗了你的数据库。或者马特只是对他的老板很生气,决定在一天结束后辞职。或者也许马特的密码没有它应该有的那么安全,而现在一个黑客拥有了它。

这只是一个简单的例子。对表和列的控制可能会变得更加复杂,并且可能无法通过存储过程以外的任何方式实现。

Thomas Jones-Low 15 年前

存储过程允许用户执行CRUD操作(插入、更新、删除),但仅以有限的方式执行,从而提供了额外的安全性。例如,允许用户更新某些行的地址,但不更新其他行的地址。

它允许您添加数据检查,以确保插入的数据是有效数据,而不是随机垃圾。对于大多数情况,您可以使用约束和/或触发器来完成这项工作,但也有一些限制。存储过程通过确保用户允许执行操作来增强安全性。

HLGEM 15 年前

在SQL Server中,如果正确使用存储过程(这意味着没有动态SQL),则不必授予对表的任何直接访问权限。这意味着您的用户只能执行procs定义的操作。如果数据库中有任何财务数据或敏感数据,则只有尽可能少的人(通常只有DBA)可以直接访问这些表。这大大降低了欺诈或不满员工破坏您的关键业务数据或员工窃取个人信息以进行身份盗窃的风险。在会计术语中,这是一种必要的内部控制,开发人员的便利性或个人从用户界面动态完成一切的愿望应该被数据的不安全性所压倒。不幸的是,在太少的公司中,情况并非如此。大多数开发人员似乎只担心数据受到外部威胁,但内部威胁往往更为关键。

如果将用户限制在表级别,然后用户发出查询以执行合法插入,则不会发生这种情况。如果您授予他们进行插入的权限,那么他们可以进行任何他们想要的临时插入,而不仅仅限于来自用户界面的插入。对于存储的proc,它们只能执行proc专门定义的操作。

Frank Flynn 15 年前

您可能可以对视图执行类似的检查,但存储过程通常更灵活,因为它们可以运行几乎任何SQL—比较结果并决定返回哪些行。

casperOne 15 年前

存储过程更好,因为存储过程(IIRC)上的安全性将胜过表/列上的安全性。

对于单表访问,这不是什么大问题。但是,如果您的操作涉及多个表上的多个列,那么为一个表/列使用一个访问/拒绝标志可能并不适用于所有情况。

但是,存储过程可以执行复合操作,您可以在该操作上适当地设置安全性。

dkretz 15 年前

简单地说,它允许您从功能上而不是从结构上定义安全性。换句话说,它限制了用户可以做什么(粒度更大),而不是可以访问什么数据库对象(粒度非常粗)

AviD 15 年前

这里详细讨论的第一个好处是更好地控制权限——用户可以被限制在特定的行中,而不仅仅是每列(顺便说一句,在大型系统中,这是很难管理的);SPs可以强制执行业务逻辑和事务逻辑;只能根据其他数据(例如连接)检索数据;一次只能更新一行;等

第二,这可以提供一个额外的保护层,防止SQL注入(尽管它不是完全和自动的)。虽然SP内部的动态SQL或错误的连接调用可能会破坏这一点,但SP会强制执行参数类型等,将代码与数据分离。

第三,它可以归结为控制,在开发阶段-通常您会培训DBA编写SP,而不是程序员(他们接受过代码方面的培训…)

Ken Yao 15 年前

例如,你有一个反馈系统。只有在管理员启动反馈活动后才能提交反馈。它只是更新某个表中的标志。

Select @IsFeedbackDefined = IsFeedbackDefined From sometable where ID = @ID

IF @IsFeedbackDefined is Null or @IsFeedbackDefined = false 
Begin
    Return -2   --can not submit feedback
End