主页 / dba / 问题 / 239032
Accepted
ConstantineK
Asked: 2019-05-25 16:30:05 +0800 CST

UPDATE STATISTICS #table WITH ROWCOUNT = xxx 需要哪些权限？

  • 772

除了 SA 之外，谁能指出我需要的正确文档或其他权限来执行以下操作？

当我运行它时，我收到以下错误：

找不到对象“#test”，因为它不存在或您没有权限。

IF NOT EXISTS 
(
SELECT 
    name  
FROM sys.server_principals
WHERE 
    name = 'testlimiteduser'
)
BEGIN
    CREATE LOGIN [testlimiteduser] WITH PASSWORD=N'apassword', DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END

IF NOT EXISTS 
(
SELECT 
    name  
FROM sys.database_principals
WHERE 
    name = 'testlimiteduser'
)
BEGIN
    CREATE USER [testlimiteduser] FOR LOGIN [testlimiteduser] WITH DEFAULT_SCHEMA=[dbo]
END 

IF NOT EXISTS
(
    SELECT 1
    FROM sys.database_principals AS p
    WHERE 
        p.name like 'testlimiteduser_app'
)
BEGIN
    CREATE ROLE testlimiteduser_app
    GRANT EXECUTE TO testlimiteduser_app 
    ALTER ROLE [db_datareader] ADD MEMBER testlimiteduser_app
    ALTER ROLE [db_datawriter] ADD MEMBER testlimiteduser_app
    ALTER ROLE testlimiteduser_app ADD MEMBER [testlimiteduser]   
    ALTER ROLE db_owner ADD MEMBER testlimiteduser  --   https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017 says db_owner or SA
END

GO

exec as user='testlimiteduser'

drop table if exists #test
create table #test (id int )
exec('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000') 
select * from #test 
revert;

检查https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017中的文档说 db_owner 或 SA 是很好，但它甚至没有出现在我的测试中 db_owner 工作。

sql-server permissions

4 个回答

  1. Best Answer

    建立一个帐户db_owner，即使是[tempdb]，仍然是有风险的。幸运的是，没有必要为了实现目标而这样做。您可以简单地使用模块签名来允许驻留在[tempdb]使用db_owner数据库角色的存储过程：

    最初设定

    这只是让我们进入与问题中提供的代码相同的状态（减去testlimiteduser_app角色，这对于这个目标是不必要的）。

    IF (DB_ID(N'NotTempDB') IS NULL)
BEGIN
    CREATE DATABASE [NotTempDB];
    ALTER DATABASE [NotTempDB]
      SET RECOVERY SIMPLE;
END;
GO

USE [NotTempDB];

IF (SUSER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE LOGIN [testlimiteduser]
      WITH PASSWORD = N'apassword',
      DEFAULT_DATABASE = [tempdb],
      CHECK_EXPIRATION = OFF,
      CHECK_POLICY = OFF;
END;
GO

IF (USER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE USER [testlimiteduser]
      FOR LOGIN [testlimiteduser];
END;

    基本设置

    我们需要一个可以签名的模块（即席 SQL 不起作用），但我们还没有签名。

    在这里，我们：

    1. 创建存储过程
    2. 授予测试用户执行该存储过程的能力。
    GO
CREATE OR ALTER PROCEDURE dbo.[UpdateStats]
(
  @TableName sysname,
  @RowCount INT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX) = CONCAT(N'UPDATE STATISTICS ',
    QUOTENAME(@TableName),
    N' WITH ROWCOUNT = ',
    @RowCount);

EXEC (@SQL);
GO


GRANT EXECUTE ON dbo.[UpdateStats] TO [testlimiteduser];
GO

    测试 1

    请注意：最好使用EXECUTE AS LOGIN，不仅AS USER在LOGIN应用程序确实连接到 SQL Server 时会发生什么更准确，而且这样做USER会增加可能会扭曲行为/测试结果的限制。

    USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;

    模块签名设置

    1. 创建证书
    2. 使用该证书签署存储过程
    3. 将证书（仅限公钥）复制到[master]
    4. 从中创建基于证书的登录。
    IF (CERT_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE CERTIFICATE [Permissions$UpdateStats]
    ENCRYPTION BY PASSWORD = 'Super Bass-o-matic ''76'
    WITH SUBJECT = N'Permission to exec UPDATE STATISTICS ... WITH ROWCOUNT = ...',
    EXPIRY_DATE = '2099-12-31';
END;


-- Associate permissions (via future cert-based login) to stored procedure:
ADD SIGNATURE TO dbo.[UpdateStats]
  BY CERTIFICATE [Permissions$UpdateStats]
  WITH PASSWORD = 'Super Bass-o-matic ''76';


-- Copy certificate (public key only) to [master]
-- and create the cert-based login from it.
DECLARE @CopySQL NVARCHAR(MAX) = N'
USE [master];
    CREATE CERTIFICATE [Permissions$UpdateStats]
      FROM BINARY = ' + CONVERT(NVARCHAR(MAX),
      CERTENCODED(CERT_ID(N'Permissions$UpdateStats')), 1) + N';

    CREATE LOGIN [Permissions$UpdateStats]
      FROM CERTIFICATE [Permissions$UpdateStats];
';

EXEC(@CopySQL);

    测试 2

    我们对存储过程进行了签名并将其与登录名相关联，但我们尚未为该登录名授予必要的权限，因此结果是相同的。

    USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;

    最终设置：为登录或用户分配权限

    这被保留为一个单独的步骤，只是为了非常清楚地表明确实是模块签名让这个工作。你有两个选择（只选一个）：

    1. 将现有的基于证书的登录添加到sysadmin实例级角色 

      ALTER SERVER ROLE [sysadmin]
  ADD MEMBER [Permissions$UpdateStats];

      或者：

    2. 从基于证书的登录创建一个用户tempdb，然后将新用户添加到db_owner数据库级角色

      USE [tempdb];
IF (USER_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE USER [Permissions$UpdateStats]
      FOR LOGIN [Permissions$UpdateStats];

    ALTER ROLE [db_owner]
      ADD MEMBER [Permissions$UpdateStats];
END;

    测试 3

    USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- SUCCESS!!!!

REVERT;

    结论

    选项之间的主要区别是：

    1. 选项1
      • 好：没有对象tempdb（需要在每次启动 SQL Server 服务时创建）
      • 坏：sysadmin权限更强大（不完全是“最小特权”）
    2. 选项 2
      • 好：就力量而言，db_owner比 更包含sysadmin
      • 坏：对象tempdb（需要在每次启动 SQL Server 服务时创建）

    和 db_owner`之间的差异sysadmin很小，即使在这里甚至不相关，因为使用这些权限中的任何一个可以完成的唯一事情是存储过程中的代码所做的事情（这是模块签名的主要好处之一：它是高度颗粒化）。

    tempdb因此，在实际层面上，在每次启动 SQL Server 服务时创建用户并将其添加到其中的复杂性db_owner（这需要在中创建存储过程master，将其标记为“启动过程”，并启用实例级配置选项“扫描启动过程”）比登录更糟糕sysadmin。

    如果需要额外权限的数据库是用户数据库，那么我可能会选择选项 2，即使只是为了使其与预期用途更匹配，而不是真正关心sysadminvs db_owner。

    有关应用模块签名所采取步骤的详细说明，请参阅我的帖子：

    无需将高级权限授予任何人即可安全轻松地使用高级权限：服务器级

    有关模块签名的更多信息，请参阅我的帖子：

    请，请，请停止使用模拟、可信和跨数据库所有权链接

    • 4

  2. 您必须exec('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000')在 tempdb 数据库中运行。

    基本上，临时表存在于 tempdb 中，而不存在于其他中。

    • 1

  3. 呃，看来我错了。答案很简单：用户在 tempdb 数据库中缺乏执行此 update statistics 命令的权限

    在 tempdb 数据库中创建临时表，默认情况下权限是有限的。

    有四种类型的表：

    1. 物理表
      • 永久并保存最终值的表。所有权始终与创建它们的数据库相关。
    2. @变量表
      • Tempdb 仅存在于批处理语句中并在每个GO批处理分隔符处或脚本末尾进行清理的表
      • SQL Optimizer 没有统计信息，并将其视为在行上。
    3. #Temp 表
      • Tempdb 持续整个会话的表，而不仅仅是批处理语句。
      • 统计信息在创建时创建并持续存在。
      • 不要更改临时表
      • 在会话结束时由垃圾收集器清理，所以不要删除临时表
      • 始终在 dbo 模式中创建。甚至在 create 语句中对另一个模式的任何引用都会被忽略。
    4. ##全局表
      • 名称中的双##（##temptable vs #temptable）让你知道它的全局
      • 跨实例的所有用户都可以使用的 Tempdb 表。
      • 可以在批处理执行期间被另一个进程修改
      • 在关闭所有引用该表的会话后进行清理。
      • 始终在 dbo 模式中创建。甚至在 create 语句中对另一个模式的任何引用都会被忽略。

    值得注意的是 Temp 对象可以通过 引用tempdb..#tablename，尽管在普通语句中显式使用 tempdb 名称会被忽略，因为所有临时表都是在模式中Tempdb和dbo模式中创建的

    注意它们都在 tempdb 数据库中。yourstatement 失败的原因仅仅是由于 tempdb 中存在 #temp 表，并且您可能没有在 tempdb 中授予用户执行更新的权限。

    通常，您只需要它来授予对表的更改，尽管我发现临时表有点不同并且可能需要更高的权限。但由于这是同一个会话，只需执行以下操作：

    REVERT
UPDATE STATISTICS ON #TABLE
EXEC AS USER='User’

    现在您甚至不需要向用户授予提升的权限并满足您的问题。

    • 1

  4. 我做了一些额外的挖掘并发现了以下内容：临时表上的任何查询都可以正常工作，它的 WITH ROWCOUNT 导致了问题。标准的 UPDATE STATISTICS 也可以顺利进行。

    我浏览了 MSDN 文档，但没有找到与 UPDATE STATISTICS 相结合的 SET ROWCOUNT 权限的明确注释 - 经过一些推测和测试后，我发现DBCC UPDATEUSAGE的文档要求数据库中包含 sysadmin 或 db_owner桌子。

    如果将此添加到我的示例中，则代码有效：

    USE [tempdb]
GO
CREATE USER [testtwouser] FOR LOGIN [testtwouser]
GO
USE [tempdb]
GO
ALTER ROLE [db_owner] ADD MEMBER [testtwouser]
GO

    不幸的是，我的原始代码没有说明它在 tempdb 之外的另一个数据库中运行，但这是一个隐含的假设，如果你忽略，整个脚本都可以工作（因为他们现在有 tempdb db_owner。）

    • 1

相关问题