使用视图和安全触发器而不是存储过程
传统观点认为，安全的应用程序应该创建用于插入、更新和删除操作的存储过程。您还可以使用存储过程，这样就不必使用触发器，从而避免触发器的一些常见陷阱。
另一个想法是用视图覆盖整个数据库 - 因此几乎没有人可以访问表本身，他们只是对视图进行 CRUD 操作。这样，如果您想让某人访问某些列，您可以为他们创建一个包含这些列或仅包含计算的视图。如果您需要对更新和执行施加逻辑；删除操作（即防止某人影响表中超过 2% 的总行），您可以通过 instead of 触发器来执行此操作。
为了不陷入触发器的常见陷阱，（1）触发器应该只更新表，而不是其他视图。 (2) 触发器永远不会放在表上。 (3)视图不能访问其他视图。 (4) 如果由于某种原因您无法按照前三个规则执行您想要的操作，请创建一个存储过程。
我从这种方式实现安全性中看到的好处是，您只需要创建一个视图，也许还需要创建一些触发器（平均情况 - 2 个附加对象），而如果您采用存储过程路线，您将始终创建至少 3 或 4 个附加对象（取决于您是否为选择创建过程）。此外，我们的 NHibernate 映射会更简单，因为我们不必为每个对象映射三个过程。
问题是主要使用视图和触发器而不是存储过程是否存在重大安全漏洞或实际问题。
Conventional wisdom says that a secure application should create stored procedures for insert, update, and delete operations. You would also use stored procedures so you don't have to use triggers, thereby avoiding some common pitfalls of triggers.
Another thought is to cover the whole database with views - so hardly anyone has access to tables themselves, they just do CRUD operations against views. That way, if you want to give someone access to certain columns, you can create a view for them that contains those columns, or just a computation. If you need to impose logic on update & delete operations (i.e. preventing someone from affecting more than 2% of the total rows in a table) you can do this via an instead of trigger.
In order to not fall into common pitfalls of triggers, (1) triggers should only update tables, never other views. (2) Triggers are never put on tables. (3) Views can't access other views. (4) If for some reason you can't do what you want by following the first three rules, create a stored procedure.
The benefit I see from implementing security this way is that you only have to create a view and maybe some triggers (average case - 2 additional objects) whereas if you go the stored procedure route you will always be creating at least 3 or 4 additional objects (depending if you create procedures for select). Also, our NHibernate mappings would be simpler because we wouldn't have to map three procedures for every object.
The question is if there are significant security holes or practical problems with using mostly views and triggers rather than stored procedures.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
使用触发器和视图本质上并没有什么问题。与其他代码一样，它们必须正确编写，并且常常声誉不佳，因为许多糟糕的开发人员为它们编写了糟糕的代码。例如，您不希望视图调用其他视图，因为性能会受到影响。您不希望触发器假设在更新/删除/插入中仅影响一行。
Ther is nothing inherently wrong with using triggers and views. As with other code they have to be correctly written and often suffer a poor reputation because so many poor developers have written bad code for them. For instance you don't want views to call other views because performance can suffer. You don't want triggers that assume only one row will be affected in the update/delete/insert.
就我个人而言，我倾向于使用存储过程来应用程序访问数据库。我通常使用一组通用过程来管理每个表的操作并确保数据完整性。有关谁可以查看或编辑给定信息的所有决定均在应用程序本身中处理。只有应用程序凭据才能访问数据库，任何碰巧找到数据库的用户都会被锁定，或者最多具有只读访问权限。
我倾向于不喜欢实现业务逻辑的触发器，因为当您尝试调试某些内容时很容易错过它们。如果我的所有插入逻辑都包含在 SP 中，我可以提取该 SP 并跟踪它以诊断插入失败的原因。如果桌子上还有一个我忘记或没有意识到的触发器，我可能需要一段时间才能意识到并记得去看那里。 （虽然这可能只是我的环境的一个因素，如果这里使用更多的触发器，我相信它们在我的思维过程中会更加突出）
但我不完全确定我们是从相同的角度看待这个问题。如果您要为每组用户创建不同的视图，那么听起来他们可以直接访问数据存储并且不通过应用程序界面工作？如果这个结构有一个应用程序接口，是否必须为每组用户更新和重新编译才能使用正确的视图？
我想我是说存储过程可以很好地支持应用程序接口或预构建的报告，但是当用户对公共数据存储具有读/写访问权限且中间没有应用程序接口时，视图和触发器可能是更好的选择。
我还要指出的是，要考虑你的商店的标准是什么。如果其他人都专门使用存储过程，而您采取另一种方法，那么其他人稍后尝试维护您的解决方案将会遇到困难。
最后，如果它完成了工作，并且不会造成维护上的痛苦或导致其他问题，那么它就是一个很好的解决方案。
Personally I tend to be in favor of using stored procedures for application access to a database. I generally use a generic set of procedures which manage operations against each table and ensure data integrity. All decisions regarding who can see or edit a given piece of information are handled in the application itself. Only the app credentials have access to the database, any users that happen to find their way there are locked out, or have read only access at most.
I tend to dislike triggers that implement business logic because they are easy to miss when you are trying to debug something. If all of my insert logic is wrapped in a SP I can pull up that SP and trace through it to diagnose why an insert is failing. If there is also a trigger on the table which I have forgotten about or was unaware of, it may be a while before I realize it and remember to look there. (though this is likely just a factor of my environment, if more triggers were used here I am sure they would be more prominent in my thought processes)
But I am not entirely sure we are looking at this from the same perspective. If you are going to be creating different views each set of users then it sounds like they have direct access to the data store and are not working through an application interface? If there was an application interface for this structure wouldn't it have to be updated and recompiled for each group of users in order to make use of the correct views?
I guess I am saying that stored procs work well for supporting an application interface or pre built reports, but the views and triggers may be a better option when users have read/write access to a common data store without an application interface in the middle.
I would also point out that there is the consideration of what is the standard for your shop. If everyone else there uses stored procs exclusively, and you take another approach, then anyone else trying to come around later and maintain your solution is going to have a hard time.
In the end if it accomplishes the job without being a pain to maintain or causing other problems then it was a good solution.