MS SQL Server 2012公共权限用户追踪视图变更方法咨询

由于你是Public角色，权限受限，无法直接对底层表启用变更追踪或在视图上创建触发器，下面提供几个可行的解决方案，按实现复杂度和权限要求排序：

方案一：定期快照对比法（无需高权限，自行可实现） #

这个方法的核心是定期保存视图的快照，通过对比当前视图数据和历史快照来识别变更。如果你的账号有创建表的权限，可以自行操作；如果没有，可请求DBA帮忙创建快照表并赋予你读写权限。

步骤1：创建快照表 #

CREATE TABLE dbo.VIEW_CREDENTIAL_SNAPSHOT (
    USERID nvarchar(48) NOT NULL PRIMARY KEY,
    VALID_FROM DATETIME NULL,
    EXPIRED_AT DATETIME NULL,
    CREDENTIAL_ID int NOT NULL,
    SNAPSHOT_TIME DATETIME NOT NULL DEFAULT GETDATE()
);

步骤2：初始化快照数据 #

第一次执行，把当前视图的数据全量导入快照表：

INSERT INTO dbo.VIEW_CREDENTIAL_SNAPSHOT (USERID, VALID_FROM, EXPIRED_AT, CREDENTIAL_ID)
SELECT USERID, VALID_FROM, EXPIRED_AT, CREDENTIAL_ID FROM dbo.VIEW_OF_USER_CREDENTIAL;

步骤3：查询变更记录 #

每次需要检查变更时，执行以下查询：

• 新增记录：

SELECT 'INSERT' AS CHANGE_TYPE, v.*
FROM dbo.VIEW_OF_USER_CREDENTIAL v
LEFT JOIN dbo.VIEW_CREDENTIAL_SNAPSHOT s ON v.USERID = s.USERID
WHERE s.USERID IS NULL;

• 修改记录：

SELECT 'UPDATE' AS CHANGE_TYPE, v.*
FROM dbo.VIEW_OF_USER_CREDENTIAL v
JOIN dbo.VIEW_CREDENTIAL_SNAPSHOT s ON v.USERID = s.USERID
WHERE v.VALID_FROM != s.VALID_FROM 
   OR v.EXPIRED_AT != s.EXPIRED_AT 
   OR v.CREDENTIAL_ID != s.CREDENTIAL_ID;

• 删除记录：

SELECT 'DELETE' AS CHANGE_TYPE, s.*
FROM dbo.VIEW_CREDENTIAL_SNAPSHOT s
LEFT JOIN dbo.VIEW_OF_USER_CREDENTIAL v ON s.USERID = v.USERID
WHERE v.USERID IS NULL;

步骤4：更新快照表 #

查询完变更后，更新快照表以匹配当前视图数据：

-- 删除已从视图中消失的记录
DELETE s
FROM dbo.VIEW_CREDENTIAL_SNAPSHOT s
LEFT JOIN dbo.VIEW_OF_USER_CREDENTIAL v ON s.USERID = v.USERID
WHERE v.USERID IS NULL;

-- 更新已修改的记录
UPDATE s
SET s.VALID_FROM = v.VALID_FROM,
    s.EXPIRED_AT = v.EXPIRED_AT,
    s.CREDENTIAL_ID = v.CREDENTIAL_ID,
    s.SNAPSHOT_TIME = GETDATE()
FROM dbo.VIEW_CREDENTIAL_SNAPSHOT s
JOIN dbo.VIEW_OF_USER_CREDENTIAL v ON s.USERID = v.USERID
WHERE v.VALID_FROM != s.VALID_FROM 
   OR v.EXPIRED_AT != s.EXPIRED_AT 
   OR v.CREDENTIAL_ID != s.CREDENTIAL_ID;

-- 插入新增的记录
INSERT INTO dbo.VIEW_CREDENTIAL_SNAPSHOT (USERID, VALID_FROM, EXPIRED_AT, CREDENTIAL_ID)
SELECT USERID, VALID_FROM, EXPIRED_AT, CREDENTIAL_ID 
FROM dbo.VIEW_OF_USER_CREDENTIAL v
LEFT JOIN dbo.VIEW_CREDENTIAL_SNAPSHOT s ON v.USERID = s.USERID
WHERE s.USERID IS NULL;

你可以把这些查询封装成存储过程（如果权限允许），或者定期手动执行，也可以请求DBA用SQL Server Agent作业配置自动执行。

方案二：请求DBA启用底层表变更追踪并创建变更视图 #

变更追踪只能针对表，所以你需要让DBA对底层的USER_CREDENTIAL表启用变更追踪，然后创建一个供你查询的变更视图。

DBA需执行的操作： #

1. 启用数据库级变更追踪：

ALTER DATABASE YourDatabaseName SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

2. 对底层表启用变更追踪：

ALTER TABLE dbo.USER_CREDENTIAL ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

3. 创建供你查询的变更视图：

CREATE VIEW dbo.VIEW_USER_CREDENTIAL_CHANGES
AS
SELECT 
    ct.SYS_CHANGE_OPERATION AS CHANGE_TYPE, -- 'I'=插入, 'U'=更新, 'D'=删除
    ct.SYS_CHANGE_VERSION AS CHANGE_VERSION,
    ct.SYS_CHANGE_CREATION_VERSION AS CREATION_VERSION,
    uc.*
FROM CHANGETABLE(CHANGES dbo.USER_CREDENTIAL, 0) ct
JOIN dbo.USER_CREDENTIAL uc ON ct.USERID = uc.USERID;

4. 赋予你对该视图的查询权限：

GRANT SELECT ON dbo.VIEW_USER_CREDENTIAL_CHANGES TO YourUserName;

你查询变更的方式： #

之后你就可以直接查询这个视图获取所有变更记录，这些变更会直接反映到VIEW_OF_USER_CREDENTIAL的内容变化：

SELECT * FROM dbo.VIEW_USER_CREDENTIAL_CHANGES;

方案三：请求DBA创建底层表触发器和审计表 #

如果DBA不愿意启用变更追踪，可以让他们在USER_CREDENTIAL表上创建触发器，把所有变更记录到一个审计表，然后给你查询该审计表的权限。

DBA需执行的操作： #

1. 创建审计表：

CREATE TABLE dbo.USER_CREDENTIAL_AUDIT (
    AUDIT_ID INT IDENTITY(1,1) PRIMARY KEY,
    CHANGE_TYPE CHAR(1) NOT NULL, -- 'I'=插入, 'U'=更新, 'D'=删除
    USERID nvarchar(48) NOT NULL,
    OLD_VALID_FROM DATETIME NULL,
    NEW_VALID_FROM DATETIME NULL,
    OLD_EXPIRED_AT DATETIME NULL,
    NEW_EXPIRED_AT DATETIME NULL,
    OLD_CREDENTIAL_ID int NULL,
    NEW_CREDENTIAL_ID int NULL,
    CHANGE_TIME DATETIME NOT NULL DEFAULT GETDATE(),
    CHANGED_BY SYSNAME NOT NULL DEFAULT SUSER_SNAME()
);

2. 创建插入/更新/删除触发器：

-- 插入触发器
CREATE TRIGGER TRG_USER_CREDENTIAL_INSERT
ON dbo.USER_CREDENTIAL
AFTER INSERT
AS
BEGIN
    INSERT INTO dbo.USER_CREDENTIAL_AUDIT (CHANGE_TYPE, USERID, NEW_VALID_FROM, NEW_EXPIRED_AT, NEW_CREDENTIAL_ID)
    SELECT 'I', USERID, VALID_FROM, EXPIRED_AT, CREDENTIAL_ID FROM inserted;
END;

-- 更新触发器
CREATE TRIGGER TRG_USER_CREDENTIAL_UPDATE
ON dbo.USER_CREDENTIAL
AFTER UPDATE
AS
BEGIN
    INSERT INTO dbo.USER_CREDENTIAL_AUDIT (CHANGE_TYPE, USERID, OLD_VALID_FROM, NEW_VALID_FROM, OLD_EXPIRED_AT, NEW_EXPIRED_AT, OLD_CREDENTIAL_ID, NEW_CREDENTIAL_ID)
    SELECT 'U', i.USERID, d.VALID_FROM, i.VALID_FROM, d.EXPIRED_AT, i.EXPIRED_AT, d.CREDENTIAL_ID, i.CREDENTIAL_ID 
    FROM inserted i
    JOIN deleted d ON i.USERID = d.USERID;
END;

-- 删除触发器
CREATE TRIGGER TRG_USER_CREDENTIAL_DELETE
ON dbo.USER_CREDENTIAL
AFTER DELETE
AS
BEGIN
    INSERT INTO dbo.USER_CREDENTIAL_AUDIT (CHANGE_TYPE, USERID, OLD_VALID_FROM, OLD_EXPIRED_AT, OLD_CREDENTIAL_ID)
    SELECT 'D', USERID, VALID_FROM, EXPIRED_AT, CREDENTIAL_ID FROM deleted;
END;

3. 赋予你对审计表的查询权限：

GRANT SELECT ON dbo.USER_CREDENTIAL_AUDIT TO YourUserName;

你查询变更的方式： #

直接查询审计表就能获取所有历史变更，还能看到变更时间和操作人：

SELECT * FROM dbo.USER_CREDENTIAL_AUDIT ORDER BY CHANGE_TIME DESC;

内容的提问来源于stack exchange，提问作者JC Ahn