sql-server - 是否有一种模式/配置文件/权限可以自动回滚用户提交的所有请求？

Question

在工作中，我们拥有开发人员具有读取权限的生产数据库。当开发人员必须修复数据库中的某些内容时，他们必须在生产数据库的副本中测试脚本，然后要求 DBA 团队在生产中执行它。

然而，有时，必须修复的数据并不在测试数据库中。然后，开发人员要求提供生产数据库的新副本，这可能需要花费大量时间。

当然，我们可以授予他们更新权限，并要求他们使用BEGIN TRANSACTION/ROLLBACK，但这风险太大。没有人希望这样，甚至开发人员也不希望这样。

我的问题:是否可以在 SQL Server 上创建配置文件 - 或授予特殊权限 - 允许执行更新和删除命令，但总是，不可以无论开发人员写了什么，在 GO 之后还是在 session 中发出最后一个命令之后回滚？

这对于在将脚本发送到生产之前测试脚本非常有帮助。

Answer 1

您可以创建一个存储过程并仅向该存储过程上的开发人员授予 EXEC 访问权限，解决方案#1 - SPROCS。这可能是最优雅的解决方案，因为您希望他们有一种简单的方法来运行查询，并且还希望在生产环境中控制其权限。执行命令的示例为:EXEC [dbo].[usp_rollback_query] 'master', 'INSERT INTO table1 SELECT * FROM table2

解决方案#1

USE [DATABASENAME]
GO

ALTER PROC dbo.usp_rollback_query
(
    @db VARCHAR(128),
    @query NVARCHAR(max)
)
AS
BEGIN
    DECLARE @main_query NVARCHAR(max) = 'USE [' + @db + ']

    ' + @query;

    BEGIN TRAN
        EXEC sp_executesql @main_query;
    ROLLBACK TRAN
END

如果您有能力每次创建和删除快照，解决方案 #2 - DB SNAPSHOTS 是最好的方法。它非常快，唯一的两个缺点是您需要在恢复之前将人们踢出数据库，并且它将恢复自创建快照以来所做的所有更改。

解决方案#2

-- CREATE SNAPSHOT
CREATE DATABASE [DATABASENAME_SS1]
ON
    (
        NAME = DATABASENAME,
        FILENAME = 'your\path\DATABASENAME_SS1.ss'
    ) AS SNAPSHOT OF [DATABASENAME];
GO

-- let devs run whatever they want

-- CLOSE CONNECTIONS
USE [master];
GO
ALTER DATABASE [DATABASENAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

-- RETORE DB
RESTORE DATABASE [DATABASENAME]
FROM DATABASE_SNAPSHOT = 'DATABASENAME_SS1';
GO

-- CLEANUP SNAPSHOT COPY
DROP DATABASE [DATABASENAME_SS1];

我不认为每个查询上的回滚是一个好主意或一个好的设计，但如果你必须走这条路，你将需要使用触发器。触发器的限制是数据库或服务器级别的触发器只能用于 DDL 而不能用于 DML。在您认为正在更改的每个 TABLE 对象上创建触发器是可行的，但是，这里的缺点是您需要知道哪些表正在被修改，即使这样也很困惑。无论如何，请查看下面的解决方案 #3 - 表触发器。为了使这一点更好，您可以创建一个角色并检查用户是否属于该角色，然后回滚。

解决方案#3

USE DATABASENAME
GO

ALTER TRIGGER dbo.tr_rollback_devs
   ON  dbo.table_name
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF SYSTEM_USER IN ('dev1', 'dev2')
        ROLLBACK
END
GO