只读访问执行SP/获取结果集的角色,而不间接修改db?
例如
如果指定了 SQL Server 用户帐户 仅 DataReader 角色和执行一个存储的能力 修改某些数据的过程 方式,应该执行该 由该用户导致的存储过程 发生修改吗?
总体,我希望为一个用户提供对整个数据库的只读能力,包括使用 SQL 语法、视图以及执行返回结果集的任何存储过程。但我不希望任何副作用导致数据库发生变化。因此,在上述存储过程示例中,理想情况下,尝试会出错以满足我的要求,以及副作用可能导致更改的所有类似场景。我想确保我的数据库免受这些攻击。
这仅在角色级别可行吗?
产品:SQL Server 2005 及更高版本
For example
if a SQL Server user account is given
only the DataReader role and ability to execute one stored
procedure that modifies data in some
way, should the execution of that
stored procedure by that user cause
the modification to occur?
Overall, I want to give one user only read ability to the entire database including use of SQL syntax, Views and to execute any store procedures that return result sets. But I don't want any side effects to cause changes in the database. Therefore in the aforementioned stored procedure example, the attempt would ideally error out to satisfy my requirement, and all similar scenarios where a side-effect might cause a change. I want to ensure my database is protected against those.
Is this doable simply at the role level?
Product: SQL Server 2005 and up
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
当然你可以做到这一点。只需在数据库级别创建一个数据库角色,并授予该角色读取表的权限,并仅执行您想要的存储过程(即读取的存储过程)。然后,将所需的用户添加到您的数据库角色。
但是,考虑到所有因素,如果您使用存储过程来读取数据,请完全执行此操作,并且不要向任何级别的用户授予对表的读取权限。通过存储过程(和视图)驱动所有数据访问。
编辑:刚刚注意到你说的是 SQL 2005“及更高版本”。如果您使用的是 SQL Server 2008,请查看应用程序角色而不是传统的数据库角色。
Sure you can do this. Simply create a database role at the database level, and grant that role read on the tables and execute on only the stored procedures you want (i.e. the ones that read). Then, add the desired user(s) to your database role.
However, all things considering, if you are using stored procedures to read data, do so completely and do not grant read on tables for users of any level. Drive all data access through stored procedures (and views).
EDIT: Just noticed you said SQL 2005 "and up." If you are using SQL Server 2008, look at application roles instead of the traditional database roles.