I want to limit the number of times a certain user can execute a stored procedure per hour. To this end I am trying to use the performance stats to determine how many times the stored procedure has been executed in the last hour. If its 0 then I will allow the query in the stored procedureto be executed otherwise it skips it.
I create a user access to an Azure SQL Server database (not master just the DB itself):
CREATE USER sp_only_user WITH PASSWORD = 'blabla12345!@#$'
I only grant them permission to execute a specific stored procedure:
GRANT EXECUTE ON OBJECT::sp_Get_Clients to sp_only_user
Now I want this stored procedure to execute either a performance stats query or a function to get performance stats (whatever is easier) in order to find out if the stored procedure has been executed in the last hour. The user does not have rights to access performance stats or execute the function directly. How do I give the stored procedure such rights without giving them to the user?
This would be the performance stats query I was referring to, which I don't want the user to be able to execute directly:
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'
Alternatively, I can put the query in a function and return the number of times it has been executed :
SELECT
@ExecutionCntPerHour = dbo.fn_CountSpExecutionsPerHour('sp_Get_Clients')
Bottom line is that I need the SP to have rights that the user does not (unless of course there is a different way to limit the number of times the stored procedure is executed per hour which is easier).
Thanks for the help in advance
As I mentioned in the comments, I would suggest using some basic logging. Firstly, let's set up the tables that would be needed with minimal columns:
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);
Indexing is going to be important here, if you want a performant solution, as well as some kind of clean up process is you need it.
Then I'm going to create a couple of USERs and give one of them an execution limit (note that the procedure isn't created yet, but that's fine here):
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
So SomeUser can only run the procedure 5 times within an hour interval, but AnotherUser can run it as often as they want (due to having no entry).
Now for the procedure. Here, you'll want to use an EXISTS to check if too many executions have been done within the procedure. As I mentioned, if too many executions have occured then I would THROW an error; I just use a generic one here, but you may want some more complex logic here. note I use ROWLOCK here to stop multiple simultaneous executions pushing over the limit; if this isn't likely to occur, you can remove that hint.
Then, after the check, I INSERT a row into the log, and COMMIT, so that the ROWLOCK is released. Then your procedure code can go afterwards.
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
You can then test (and clean up) the set up with the following:
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;
If this is something you need in a lot of procedures (and the design I have here allows this) you might find it better to use a procedure to check, and THROW the error:
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