时间戳

首页 > 解决方案 > 是否有自动回滚用户提交的所有请求的模式/配置文件/权限？

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

问题描述

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

但是，有时必须修复的数据不在测试数据库中。然后，开发人员要求生产数据库的新副本，这可能需要很多时间。

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

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

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

标签： sql-serverdatabase-administration

解决方案

您可以创建一个 sproc 并仅授予 EXEC 访问该 sproc 上的开发人员的权限，解决方案 #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];

我不认为每个查询上的 ROLLBACK 都是一个好主意或一个好的设计，但如果你必须走这条路，你将需要使用触发器。触发器的限制是 DATABASE 或 SERVER 级别的触发器只能用于 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

推荐阅读