主页 / dba / 问题 / 260260
Accepted
Nick Haslam
Asked: 2020-02-22 08:08:44 +0800 CST

如何防止删除表但允许在模式中创建它们

  • 772

是否可以在特定架构中允许用户创建表并从中进行选择，但不允许他们在初始创建后删除或更新行，并防止他们删除它？

Create Table as Select需要 Select 的选择权限，但是Create Table需要 Create table 和 Alter Schema on Schema。

Alter Schema 权限还包括删除表的权限，这是我们不想要的。

我们正在使用 Azure SQL 数据仓库，所以不能使用触发器..

sql-server azure-sql-data-warehouse

1 个回答

  1. Best Answer

    扩展@Dominique Boucher 的建议。

    您可以通过创建一个充当 create 语句代理的过程并在其中指定一个 execute as 子句来做到这一点。如果您然后只允许您想要的用户执行权限，他们只能创建表，不能编辑它们。这种方式不会成为所有者（以用户身份执行成为所有者）并且他们不能编辑过程本身。

    GRANT EXECUTE ON [dbo].[TableBuilder] TO [Plebby]

    下面是一个示例，带有一些检查/错误处理，但您需要对其进行扩展以满足您的需求。您将在具有高度特权的用户上创建此过程。

    CREATE PROCEDURE TableBuilder
@schemaName sysname,
@tableName sysname,
@tableScript nvarchar(max)
WITH EXECUTE AS 'dbo'
AS
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000);

IF SCHEMA_ID(@schemaName) IS NULL
BEGIN
    SET @ErrorMessage = 'Schema with name ' + @schemaName + ' does not exist.';
    RAISERROR(@ErrorMessage, 16, 0);
    RETURN
END

IF OBJECT_ID(@schemaName + '.' + @tableName, 'U') IS NOT NULL
BEGIN
    SET @ErrorMessage = 'Defined table ' + @schemaName + '.' + @tableName + ' already exists';
    RAISERROR(@ErrorMessage, 16, 0)
    RETURN
END

IF (@schemaName + '.' + @tableName <> (SELECT SUBSTRING(@tableScript, (CHARINDEX('TABLE', @tableScript) + 6), (LEN(@schemaName) + LEN(@tableName) + 1))))
BEGIN
    SET @ErrorMessage = 'Schema and/or tablenames in creation script do not match with ' + @schemaName + '.' + @tableName;
    RAISERROR(@ErrorMessage, 16, 0)
    RETURN
END

EXEC(@tableScript)

RETURN 0
END

    对上述过程具有执行权限的用户可以使用它来执行有效的 CREATE TABLE 语句。并且您可以添加更多检查/约束（即允许的模式）。并且应该这样做，因为执行是动态 SQL。根据您的用户权限：

    以“用户”身份执行

    如果您不控制输入，您可能会获得一些令人讨厌的权利升级。我已经在 SQL Server 2008、2012 和 2017 上测试了上述过程。但我没有可用于测试的 AzureDB 实例。

    • 1

相关问题