是否可以授予执行它的用户没有的存储过程执行权限

小编典典

是否可以授予执行它的用户没有的存储过程执行权限

javascript

我想限制某个用户每小时可以执行存储过程的次数。为此，我尝试使用性能统计信息来确定存储过程在过去一小时内执行了多少次。如果它为 0，那么我将允许执行存储过程中的查询，否则它会跳过它。

我创建了对 Azure SQL Server 数据库的用户访问权限（不仅仅是掌握数据库本身）：

sql CREATE USER sp_only_user WITH PASSWORD = 'blabla12345!@#$'

我只授予他们执行特定存储过程的权限：

sql GRANT EXECUTE ON OBJECT::sp_Get_Clients to sp_only_user

现在，我希望此存储过程执行性能统计查询或获取性能统计信息的函数（无论哪个更容易），以便确定存储过程是否在过去一小时内执行。用户无权访问性能统计信息或直接执行功能。如何在不给用户的情况下给存储过程这样的权限？

这将是我所指的性能统计查询，我不希望用户能够直接执行：

SELECT 
    @LastExecutionTime=  PS.last_execution_time
FROM 
    sys.dm_exec_procedure_stats PS 
INNER JOIN 
    sys.objects o ON O.[object_id] = PS.[object_id] 
WHERE
    name = 'sp_Get_Clients'

或者，我可以将查询放在一个函数中并返回它已执行的次数：

SELECT
    @ExecutionCntPerHour = dbo.fn_CountSpExecutionsPerHour('sp_Get_Clients')

底线是我需要 SP 拥有用户没有的权限（当然，除非有不同的方法来限制每小时执行存储过程的次数，这样更容易）。

我在这里先向您的帮助表示感谢

阅读 97

2022-07-21

共1个答案

小编典典

正如我在评论中提到的，我建议使用一些基本的日志记录。首先，让我们用最少的列设置需要的表：

CREATE TABLE dbo.ExecutionLimit (ProcedureSchema sysname NOT NULL,
                                 ProcedureName sysname NOT NULL,
                                 UserName sysname NOT NULL,
                                 ExecutionLimit int NOT NULL CONSTRAINT CK_ExecutionLimitMin CHECK (ExecutionLimit > 0),
                                 ExecutionTimeFrame int NOT NULL CONSTRAINT CK_ExecutionTimeFrameMin CHECK (ExecutionTimeFrame > 0), --This is in minutes, but you could use something else
                                 CONSTRAINT PK_ExecutionLimit_ProcedureUser PRIMARY KEY CLUSTERED(ProcedureSchema, ProcedureName, UserName));
GO

CREATE TABLE dbo.ProcedureExecution (ProcedureSchema sysname NOT NULL,
                                     ProcedureName sysname NOT NULL,
                                     UserName sysname NOT NULL,
                                     ExecutionTime datetime2(1) NOT NULL CONSTRAINT DF_ExecutionTime DEFAULT SYSDATETIME());
CREATE CLUSTERED INDEX CI_ProcedureExecution ON dbo.ProcedureExecution (ExecutionTime,ProcedureSchema,ProcedureName,UserName);

索引在这里很重要，如果你想要一个高性能的解决方案，以及某种清理过程是你需要的。

然后我将创建几个USERs 并给其中一个设置执行限制（请注意，该过程尚未创建，但在这里很好）：

CREATE USER SomeUser WITHOUT LOGIN;
CREATE USER AnotherUser WITHOUT LOGIN;
GO
INSERT INTO dbo.ExecutionLimit (ProcedureSchema,ProcedureName,UserName,ExecutionLimit,ExecutionTimeFrame)
VALUES(N'dbo',N'SomeProcedure',N'SomeUser',5, 60); --No more than 5 times in an hour

因此SomeUser只能在一个小时的间隔内运行该程序 5 次，但AnotherUser可以根据需要多次运行它（由于没有条目）。

现在进行程序。在这里，您将要使用 anEXISTS检查过程中是否执行了过多的执行。正如我所提到的，如果执行次数过多，那么我会THROW出错；我在这里只使用一个通用的，但你可能需要一些更复杂的逻辑。注意我ROWLOCK在这里使用来阻止多个同时执行超过限制；如果这不太可能发生，您可以删除该提示。

然后，检查后，我INSERT一行进入日志，并且COMMIT，这样就ROWLOCK被释放了。然后你的程序代码可以继续。

CREATE PROC dbo.SomeProcedure AS
BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;

    IF EXISTS (SELECT 1
               FROM dbo.ExecutionLimit EL
                    --Using ROWLOCK to stop simultaneous executions, this is optional
                    JOIN dbo.ProcedureExecution PE WITH (ROWLOCK) ON EL.ProcedureSchema = PE.ProcedureSchema
                                                                 AND EL.ProcedureName = PE.ProcedureName
                                                                 AND EL.UserName = PE.UserName
                                                                 AND DATEADD(MINUTE,-EL.ExecutionTimeFrame,SYSDATETIME()) <= PE.ExecutionTime
               WHERE EL.UserName = USER_NAME()
                 AND EL.ProcedureSchema = N'dbo'
                 AND EL.ProcedureName = N'SomeProcedure'
               GROUP BY EL.ExecutionLimit --Needs to be, or will error
               HAVING COUNT(PE.ExecutionTime) >= EL.ExecutionLimit) BEGIN

        DECLARE @Message nvarchar(2047);
        SELECT @Message = FORMATMESSAGE(N'The maximum number of executions (%i) within your allotted timeframe (%i minutes) has been reached. Please try again later.', EL.ExecutionLimit, EL.ExecutionTimeFrame)
        FROM dbo.ExecutionLimit EL
        WHERE EL.UserName = USER_NAME()
          AND EL.ProcedureSchema = N'dbo'
          AND EL.ProcedureName = N'SomeProcedure';

        THROW 62462, @Message, 16;
    END;

    INSERT INTO dbo.ProcedureExecution (ProcedureSchema, ProcedureName, UserName)
    VALUES(N'dbo',N'SomeProcedure',USER_NAME());
    COMMIT;

    --Do the stuff
    PRINT N'Congratulations! You have run the procedure! :)'; --Obviously this wouldn't be in there.

END;
GO

然后，您可以使用以下内容测试（并清理）设置：

GRANT EXECUTE ON dbo.SomeProcedure TO SomeUser,AnotherUser;

GO

EXECUTE AS USER = 'SomeUser';
GO

EXECUTE dbo.SomeProcedure;

GO 6

REVERT;
GO

EXECUTE AS USER = 'AnotherUser';
GO

EXECUTE dbo.SomeProcedure;

GO 6

REVERT;
GO

DROP TABLE dbo.ExecutionLimit;
DROP TABLE dbo.ProcedureExecution;
DROP PROC dbo.SomeProcedure;

GO
DROP USER SomeUser;
DROP USER AnotherUser;

如果这是您在很多程序中需要的东西（并且我在这里的设计允许这样做），您可能会发现使用程序进行检查会更好，并且THROW错误：

CREATE PROC dbo.CheckExecutions @Username sysname, @ProcedureSchema sysname, @ProcedureName sysname AS
BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;

    IF EXISTS (SELECT 1
               FROM dbo.ExecutionLimit EL
                    --Using ROWLOCK to stop simultaneous executions, this is optional
                    JOIN dbo.ProcedureExecution PE WITH (ROWLOCK) ON EL.ProcedureSchema = PE.ProcedureSchema
                                                                 AND EL.ProcedureName = PE.ProcedureName
                                                                 AND EL.UserName = PE.UserName
                                                                 AND DATEADD(MINUTE,-EL.ExecutionTimeFrame,SYSDATETIME()) <= PE.ExecutionTime
               WHERE EL.UserName = @Username
                 AND EL.ProcedureSchema = @ProcedureSchema
                 AND EL.ProcedureName = @ProcedureName
               GROUP BY EL.ExecutionLimit --Needs to be, or will error
               HAVING COUNT(PE.ExecutionTime) >= EL.ExecutionLimit) BEGIN

        DECLARE @Message nvarchar(2047);
        SELECT @Message = FORMATMESSAGE(N'The maximum number of executions (%i) within your allotted timeframe (%i minutes) has been reached on the procedure ''%s.%s''. Please try again later.', EL.ExecutionLimit, EL.ExecutionTimeFrame, EL.ProcedureSchema, EL.ProcedureName)
        FROM dbo.ExecutionLimit EL
        WHERE EL.UserName = @Username
          AND EL.ProcedureSchema = @ProcedureSchema
          AND EL.ProcedureName = @ProcedureName;

        THROW 62462, @Message, 16;
    END;

    INSERT INTO dbo.ProcedureExecution (UserName, ProcedureSchema, ProcedureName)
    VALUES(@UserName, @ProcedureSchema, @ProcedureName);
    COMMIT;
END
GO

CREATE PROC dbo.SomeProcedure AS
BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @UserName sysname = USER_NAME();
    EXEC dbo.CheckExecutions @UserName, N'dbo', N'SomeProcedure';

    --Do the stuff
    PRINT N'Congratulations! You have run the procedure! :)'; --Obviously this wouldn't be in there.

END;
GO

2022-07-21