检查存储过程内部的权限

Question

我有一个存储过程z，它读取表a并写入表B + C在B和C之间的过程不能停止是必要的(金融交易，就像你不能在不从C中取钱的情况下增加B)

为了避免这种情况，并且因为运行该过程的用户不应该对A、B或C有任何权限，并且因为有时服务器连接可能会中断，我构建了以下代码:

ALTER PROCEDURE [INV].[Z]
AS
BEGIN
DECLARE @CanAcessAllTables int = 
(SELECT IIF( (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME in (A, B, C) ) = 3, 1, 0))
IF      @CanAcessAllTables <> 1
RAISERROR('Looks like we either can not connect to the server or you lack some aditional permissions', 16, 16)
ELSE
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO [INV].[B] ( col1,  col2) VALUES (1, 2)
INSERT INTO [INV].[C] ( col3,  col4) VALUES (1, 2)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT 'Please report the following table for support:'
SELECT
ERROR_MESSAGE()   as ErrorMessage,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE()      as ErrorLine,
ERROR_STATE()     as ErrorState,
ERROR_SEVERITY()  as ErrorSeverity,
ERROR_NUMBER()    as ErrorNumber
END CATCH
END
GO

我的想法是sp会检查他(sp)是否可以访问所有3个表，而不是检查用户运行Z是否可以访问所有3个表，自然用户没有对所有3个表的权限，并导致"看起来我们要么无法连接到服务器，要么你缺乏一些额外的权限"错误这有点讽刺，因为通过删除引发错误，sp将运行。

我的问题是，我应该用什么来确保在任何情况下B和C一起运行或根本不运行(即使有人将表B重命名为B_1或撤销sp对C的写权限)

Answer 1

在T-SQL中处理TRYCATCH的错误通常是浪费时间，除非你有特定的方法来处理某个错误。

使用SELECT ERROR_MESSAGE()只会破坏完整的异常跟踪，因为可能有多个异常。

如果你有一个特定的业务错误，你想引发(不只是一个权限错误，无论如何都会被抛出)，使用THROW

最好的方法是使用SET XACT_ABORT ON;这将保证任何异常都会导致立即回滚，而不管连接是否断开，或者是否存在权限问题。

为了确保事务中的隔离和原子性，可以使用隔离级别SERIALIZABLE，代价是需要更多更长的锁，因此锁定和阻塞的风险更高。

ALTER PROCEDURE [INV].[Z]
AS
SET XACT_ABORT, NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF(NOT EXISTS (SELECT 1 FROM SomeTableToCheck WHERE Something = 1))
THROW 50000, 'Some business error', 0;
INSERT INTO [INV].[B] ( col1,  col2) VALUES (1, 2);
INSERT INTO [INV].[C] ( col3,  col4) VALUES (1, 2);
COMMIT TRANSACTION
GO