使用存储过程访问数据有哪些安全优势？

Question

我看到一些建议认为，可以通过将所有的数据访问都通过存储过程来确保数据库的安全。

我知道对于SQL Server，可以通过对表甚至列进行CRUD操作来确保安全。

例如：

 --// 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操作，那么使用存储过程如何提供额外的安全性或安全管理？

- Matt Brunell

---

可能是因为它在最关键的安全类型中非常有效，如果你是一个数据库管理员 - 它可以保护数据库免受使用过程式代码编写的程序员查询的影响。（半开玩笑...） - dkretz

Answer 1

通过将所有访问限制在存储过程中，您已经为数据库建立了一个定义良好的接口，所有访问必须通过该接口进行。由于您已经对表和视图进行了选择、插入、更新和删除操作的拒绝，因此没有人可以直接编写自己设计的SQL来执行任何他们想要的操作...如果您想要限制将员工分配给超过三个项目的员工的插入操作，仅允许具有大于85分熟练度测试分数的员工执行，那么您可以将该约束写入SaveEmployee sproc中，并将其抛出异常以防止任何客户端代码这样做...

当然，您可以使用客户端代码完成相同的事情，但使用SProcs可以更轻松地设计和管理该过程，因为它集中在一个地方，所有尝试访问此数据库系统的应用程序都必须遵守您在SProcs中定义的任何约束或安全规定... 如果该SProc是插入或更新记录的唯一方法，则无法忽略或规避SProc中的约束或安全规定，这样就不会有任何恶意开发人员编写新的单独客户端应用程序来访问数据库。

- Charles Bretana

---

这里说的是“rogue”，不是“rouge”……除非他们正在开发新的化妆品系列 ;) - Tom H

哈哈！谢谢...我已经编辑了拼写错误...(我来自一个没有教我们打字的年代..而且我从未学过..所以我用三根手指打字，经常打错字！) - Charles Bretana

Answer 2

您可能不想让Matt直接更新某些表或列。如果Matt决定这样做怎么办：

UPDATE Person.Address SET AddressLine1 = NULL

糟糕了，Matt忘记了WHERE子句，把你的数据库搞崩了。或者Matt只是对他的老板感到愤怒，并决定在今天结束工作后离职。或者Matt的密码并不像应该有的那么安全，现在被黑客盗走了。

这只是一个简单的例子。对表和列的控制可能变得更加复杂，除了存储过程之外，其他方式可能无法承受。

- Tom H.

---

嗯...不确定那是否真的是安全上的好处...保护你的数据库免受开发人员犯错...备份呢？ - Peter Gfader

以上相同的情况也可以用于获取数据库中所有社会安全号码或信用卡号码。更不用说，恢复备份需要时间和金钱，可能会导致应用程序停机。 - Tom H

Answer 3

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

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

通过由应用程序控制的单个访问点更容易跟踪对数据库的更改，而不是通过任何数量的接口。并且该过程可以更新审核日志。

- Thomas Jones-Low

Answer 4

在SQL Server中，如果您正确使用存储过程（也就是不使用动态SQL），则不必直接授予任何对表的访问权限。这意味着您的用户只能执行存储过程定义的操作。如果您的数据库中包含任何财务数据或敏感信息，则应尽可能少地授权人员（通常仅限于DBA）直接访问表。这将大大降低欺诈风险，避免不满情绪的员工破坏关键业务数据或员工窃取个人信息以进行身份盗窃。从会计角度来看，这是一种必要的内部控制。开发人员为了方便或个人愿望而从用户界面动态执行所有操作，应该被数据安全性所取代。不幸的是，在太多公司中并非如此。大多数开发人员似乎只担心外部威胁，但内部威胁往往更加重要。

如果您在表级别限制用户，然后用户发出一个合法的插入查询，它将无法执行。如果您授予他们执行插入的权限，则他们可以执行任何自由插入而不仅仅限于来自用户界面的插入。使用存储过程，他们只能执行存储过程明确定义的操作。

- HLGEM

---

从会计角度来看，这是必要的内部控制，开发者为了用户界面的动态性而做出的方便或个人愿望应该被数据不安全性所取代。不幸的是，在许多公司中，这种情况并非如此。大多数开发人员似乎只担心外部对其数据的威胁，但内部威胁往往更为关键。没错！ - Joshua Drake

Answer 5

在大多数（全部？）关系型数据库管理系统中，您可以向特定用户授予对特定表的访问权限。存储过程可以作为不同的用户运行，其中一个用户具有更高的访问权限。但是，存储过程并不等同于给整个表授权，而是可以首先检查一些内容，仅返回符合您特定安全性问题的行。

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

- Frank Flynn

Answer 6

存储过程更好，因为存储过程上的安全性（如果我没记错的话）将胜过表/列上的安全性。

对于单表访问来说，这不是什么大问题。然而，如果您有一个涉及多个表和多个列的操作，那么一个表/列的访问/拒绝标志可能无法适用于所有情况。

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

- casperOne

Answer 7

简单来说，它让您以功能方式定义安全性，而不是结构方式。换句话说，它限制了用户被允许做什么（更细粒度），而不是哪些数据库对象可访问（非常粗略的粒度）。请注意，我们谈论的是“由DBA控制的安全性”，而不是由站点、系统管理员或软件模块控制的安全性，这些都是有用的并且是整体安全基础设施的一部分。

- le dorfier

Answer 8

首先，讨论的第一个好处是更好地控制权限 - 用户可以被限制在特定行上，而不仅仅是每列（在大型系统中管理起来非常麻烦）；存储过程可以强制执行业务逻辑和事务逻辑；数据可能仅在依赖其他数据时检索（例如连接）；更新可能仅限于一次更新单个行等。

其次，这可以提供额外的保护层，防止SQL注入（尽管它并不完全自动）。虽然这可能会被存储过程内部的动态SQL或错误的连接调用所破坏，但存储过程确实强制执行参数类型和其他内容，将代码与数据分离。

第三，这归结为在开发阶段的控制 - 通常您会有经过培训的DBA编写存储过程，而不是程序员（他们接受的是代码方面的培训...）

此外，还有非安全性的好处，例如更好的性能。

- AviD

Answer 9

在存储过程中，您可以添加逻辑控件。如果某些内容不正确，您可以返回错误代码，而不是直接更新表数据。

例如，您有一个反馈系统。只有在管理员启动反馈活动后才能提交反馈。它只是在某个表中更新标志。 然后，当用户来提交反馈时，存储过程可以检查标志是否设置。

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

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

- Ken Yao