主页 / dba / 问题 / 252828
Accepted
sepupic
Asked: 2019-11-09 03:13:52 +0800 CST

SET IDENTITY_INSERT 的权限是否低于 db_ddladmin？

  • 772

我继承了一个应用程序在sysadmin帐户下运行的系统。我将此帐户限制为所有数据库上的db_datareader++并设置会话以在服务器上捕获。即使用户是其中的成员并且对表没有应用粒度限制， 看到一些失败也让我感到非常惊讶。db_datawriterEXECUTEextended eventspermission errors
insertsdb_datawriter

然后我注意到只有插入该集合IDENTITY_INSERT失败。有问题的数据库已经满了，identity而且他们的代码太多了SET IDENTITY_INSERT。代码不仅意味着modules存储在服务器上，还意味着C#代码。

为了能够设置identity_insert用户必须拥有该表或对该表具有ALTER权限。但事实是你不能ALTER只授予表，我被迫授予ALTER整个数据库给这个user （我会让这个用户db_ddladmin角色的成员只添加 42 个权限（！！！） vs 51 个权限，可以通过授予ALTER数据库添加到用户权限）

我对重构数据库不感兴趣sequence（服务器版本是2014这样我理论上可以做到）而且我不能只添加execute as dbo到每个使用的 spidentity_insert因为还有要重写的应用程序代码，我只是想知道为什么微软会做这么奇怪db_datawriter无法设置的权限设计identity_insert？

是否有其他方法可以使用户能够设置identity_insert添加的权限少于db_ddladmin？

更新

我尝试了LowlyDBA提供的解决方案synonyms：

create table dbo.t(id int identity);
go

alter schema sch transfer dbo.t;
go

create synonym dbo.t for sch.t;
go

set IDENTITY_INSERT dbo.t on;

这会导致错误

消息 1088，级别 16，状态 11，第 1 行找不到对象“dbo.t”，因为它不存在或您没有权限。

在此处输入图像描述

Antoine Hernandez仅在模式上授予用户ALTER的解决方案似乎是邪恶的，所以我接受它

sql-server sql-server-2014

2 个回答

  1. Best Answer

    我建议您通过右键单击数据库角色文件夹来创建自定义数据库角色。

    在此处输入图像描述

    这将调出您可以自定义的数据库角色。在第一个屏幕上，您可以为角色指定您选择的名称和所有者。您还可以添加您希望成为该角色成员的用户。在此处输入图像描述

    完成后，您可以单击 Securables，然后您可以选择要授予的内容。在此处输入图像描述

    添加对象将显示，我建议选择“所有类型的对象...”选项，然后单击确定。在此处输入图像描述

    在 Select Object Types 中，向下滚动以查找 Schemas，选中该框，然后单击 OK。在此处输入图像描述

    现在您将看到一个架构列表，因此请选择具有您要授予权限的对象的架构，它将在下面显示该架构的权限。在此处输入图像描述

    对于此示例，我选择了 dbo 模式。我调整了默认屏幕的大小以一次显示更多。在此处输入图像描述

    在这种情况下，我很可能会选择授予 Alter，因为根据Microsoft的说法，“当授予范围时，ALTER 还赋予更改、创建或删除（强调我的）该范围内包含的任何安全对象的能力。” 根据关于数据库权限的信息图，位于此处和下方，在架构上授予 Alter 将在 Aggregate、Default、Function、Procedure、Queue、Rule、Synonym、Table 和 View 上授予 alter：微软链接 https://aka.ms/sql-permissions-poster由于我处于这个阶段，对于同一个角色，我可能也会授予 Execute 权限，因此角色成员也可以执行任何存储过程，因此当新表或存储过程添加到数据库时，我不必修改任何安全性以允许新对象像现有对象一样工作。因此，一旦完成，选择将如下所示：在此处输入图像描述

    I could have taken it a step further and also granted Select, Insert, Update, and Delete, and it most likely would have covered everything the role would require to read, insert, delete, and update any table as well as execute any stored procedure without needing to grant all the other permissions that the db_ddladmin role grants. By doing this at the schema level it can ease the management of security in some ways, especially when a particular user needs to have a lot of if not all access at the schema level, but also satisfies the requirement of not granting alter to the whole database. If there were other permissions this configuration grants that the role does not need, you could then go add DENY permissions as needed.

    • 6

  2. 在问题中，OP 声称不可能仅将 ALTER 授予表。也许我误解了什么。但我发现，在 Securables 下的数据库用户的 SQL Management Studio 中，我可以添加一个表，然后为行中的权限更改激活授予。IDENTITY_INSERT ON 在那之后愉快地运行。这是 SQL-Server2012 (v13) 上的“IIS APPPOOL\xyz”数据库用户，默认情况下可以选择、执行、更新、插入、删除。

    我远不是这方面的专家。刚试过。如果这真的只增加了修改相应表的模式的能力，有人可以评论吗？我可以忍受这个。

    • 0

相关问题