主页 / dba / 问题 / 192920
Accepted
Evaldas Buinauskas
Asked: 2017-12-13 05:08:13 +0800 CST

SQL Server TRUNCATE TABLE 权限

  • 772

是否可以配置 SQL Server 用户，使他无权访问 DDL 语句但有权访问运行TRUNCATE TABLE命令。我知道在幕后它是一个 DDL。

我在搜索引擎上找不到这个。

sql-server permissions

4 个回答

  1. 您可以使用Jonathan Kehayias 的这项技术来创建允许用户访问TRUNCATE表的存储过程。

    正如乔哈坦所说：

    “您不能授予截断权限，因为无论出于何种原因，该权限都不存在。您可以做的是使用存储过程并使用 EXECUTE AS OWNER 来解决它。”

    这是来自上述帖子的示例代码。

    CREATE DATABASE foo
GO

CREATE LOGIN foobar
    WITH password = 'Pa$$w0rd';
GO

USE foo
GO

CREATE user foobar
FROM LOGIN foobar;
GO

CREATE TABLE test (rowid INT identity)
GO

INSERT INTO test DEFAULT
VALUES;
GO

SELECT *
FROM test
GO

CREATE PROCEDURE dbo.truncate_test
    WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE test
GO

GRANT EXECUTE
    ON dbo.truncate_test
    TO foobar
GO

EXECUTE AS LOGIN = 'foobar'

EXECUTE dbo.truncate_test

REVERT
GO

SELECT *
FROM test
GO

USE master
GO

DROP DATABASE foo

DROP LOGIN foobar
    • 6
  2. Best Answer

    如果不向用户授予 ALTER，就不可能授予 TRUNCATE。

    尽管您可以通过向用户授予 DELETE 来解决问题。

    • 2

  3. 我建议使用模块签名来实现这一点，因为最终，它是最细粒度和最安全的选择。这类似于@Scott 建议的 Impersonation 选项，因为它将所需的功能包装在存储过程中，但它比使用的问题更少，EXECUTE AS因为它不会更改安全上下文，并且如果有人更改了提升的权限，则会删除存储过程的单个字节，强制您查看更改并仅在您同意更改时重新应用权限。

    只需执行以下操作：

    1. 创建一个存储过程来完成TRUNCATE TABLE和其他任何需要完成的事情。

    2. 在有问题的数据库中，创建一个证书（我的偏好是指定一个密码，而不是依赖数据库主密钥 (DMK) 进行保护）：

      CREATE CERTIFICATE [TruncatePermission]
ENCRYPTION BY PASSWORD = 'password-123'
WITH SUBJECT = 'Grant temporary db_owner status';

    3. 使用以下证书对存储过程进行签名ADD SIGNATURE：

      ADD SIGNATURE
  TO dbo.[{new_Stored_Procedure_name}]
  BY CERTIFICATE [TruncatePermission]
  WITH PASSWORD = 'password-123';

    4. 将证书备份到：

      • 文件（一个.cer文件用于公钥，又名“证书”，一个.pvk文件用于私钥）：

        BACKUP CERTIFICATE [TruncatePermission]
TO FILE = 'path_to_public_key_(.cer)_file'  
WITH PRIVATE KEY
(
    FILE = 'path_to_private_key_(.pvk)_file',
    ENCRYPTION BY PASSWORD = 'password-123',
    DECRYPTION BY PASSWORD = 'password-123'
);

      或：

      • VARBINARY使用内置函数的文字：

        SELECT
  CERTENCODED(CERT_ID(N'TruncatePermission')) AS [Cert/PublicKey],
  CERTPRIVATEKEY(CERT_ID(N'TruncatePermission'),
                 'password-123', 'password-123') AS [PrivateKey];

    5. 删除证书的私钥，ALTER CERTIFICATE以防止使用证书签署任何其他内容（尽管风险不高，因为他们也需要密码）：

      ALTER CERTIFICATE [TruncatePermission]
    REMOVE PRIVATE KEY;

    6. 从该证书创建用户：

      CREATE USER [Mr.Truncate] FROM CERTIFICATE [TruncatePermission];

    7. 授予该用户实现目标所需的任何权限。尝试找到有效的最低特权/最严格的权限，但在这种情况下，它可能需要“dbo”（通过db_owner固定的数据库角色）：

      ALTER ROLE [db_owner] ADD MEMBER [Mr.Truncate];

    8. 将存储过程授予EXECUTE用户和/或角色的任何组合应该能够执行此操作。

    授予基于证书的用户的权限有点多，但该用户不能被模拟（即EXECUTE AS USER = N'Mr.Truncate';）并且无法登录；该用户只是额外权限的容器，并且这些权限仅适用于使用该证书签名的内容，在这种情况下，这只是一个存储过程。

    如果您需要更改该存储过程中的代码，或通过使用签名将这些权限授予另一个模块ADD SIGNATURE，那么如果要签名的新代码在另一个数据库中，您将需要在另一个数据库中创建相同的证书，或者您将需要使用该ALTER CERTIFICATE语句将私钥恢复到此证书中。

    • 2

  4. 您需要 ALTER 表的权限。

    你可以在这里找到它：https ://technet.microsoft.com/en-us/library/ms177570(v=sql.105).aspx

    下：权限。

    • 0

相关问题