如何允许 SQL Server 用户插入/更新/删除数据，但不允许更改架构? [英] How to permit a SQL Server user to insert/update/delete data, but not alter schema?

问题描述

我的应用程序(C#、ASP.Net)需要在数据库中插入、更新和删除数据，并运行存储过程.我需要防止它修改数据库架构——不更改表、创建或删除、不更改存储过程.

My application (C#, ASP.Net) needs to insert, update and delete data in the DB, and run stored procedures. I need to prevent it from modifying the DB schema - no altering tables, creating or dropping, no changes to stored procedures.

我需要向应用程序用户授予哪些权限组合?只是选择"是行不通的，因为它需要在表中插入/更新/删除数据.

What permissions combination do I need to grant to the application user? Just 'select' isn't going to work, because it needs to insert/update/delete data in tables.

如何检查特定登录的权限和访问权限?如何授予或拒绝登录权限和访问权限?我需要授予新用户(登录)权限以仅访问一个数据库.

How do I check permissions and access for a particular login? How do I grant or deny permissions and access for a login? I need to give permissions to a new user (login) to access only one database.

使用带有 SSMS 的 SQL Server 2008 R2.

Using SQL Server 2008 R2, with SSMS.

推荐答案

如果你真的想在对象级别控制这个，你可以这样做:

If you really want to control this at the object level, you can do:

GRANT SELECT,UPDATE,INSERT,DELETE ON dbo.table TO user;

在架构级别:

GRANT SELECT,UPDATE,INSERT,DELETE ON SCHEMA::dbo TO user;

不过，理想情况下，您不允许针对您的表进行临时 DML，并通过存储过程控制所有 DML.在这种情况下，您只需要授予过程本身的 exec 权限，而不是它所接触的对象:

Ideally, though, you would not allow ad hoc DML against your tables, and control all DML through stored procedures. In which case you just need to grant exec on the procedure itself, and not to the objects it touches:

GRANT EXEC ON dbo.procedure TO user;

类似地，如果您想允许对特定架构中的所有过程执行 exec，您可以说:

Similarly if you want to allow exec on all procedures in a specific schema, you can say:

GRANT EXEC ON SCHEMA::dbo TO user;

一个例外是当您的存储过程组成动态 SQL 时.在这些情况下，您可能仍需要在动态 SQL 执行的上下文中对基础表应用权限，或者您可以使用 EXECUTE AS OWNER.

The one exception is when your stored procedure composes dynamic SQL. In those cases you might still need to apply permissions to the underlying tables in the context of the dynamic SQL execution, or you may be able to use EXECUTE AS OWNER.

这篇关于如何允许 SQL Server 用户插入/更新/删除数据，但不允许更改架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！