November 20, 2019 at 3:33 pm
So the scenario is that I have a user with a role of db_datareader and db_executor. They get an error when they try and run a plain delete statement, however I have a stored procedure that has delete statements in it and they are not blocked. I have tried denying delete on the role and the login, revoking delete on the role and the login but they still have the delete right inside the procedure.
How can I allow them execute but not update or delete?
Thank you.
November 20, 2019 at 3:49 pm
Can you show a repro with some code?
November 20, 2019 at 3:55 pm
GRANT EXECUTE TO exectest
REVOKE DELETE FROM exectest
DENY DELETE TO exectest
SELECT dp.class_desc,
dp.permission_name,
dp.state_desc,
OBJECT_NAME(major_id) as ObjectName,
grantee.name as Grantee,
grantor.name as Grantor
FROM sys.database_permissions dp
JOIN sys.database_principals grantee
ON dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor
ON dp.grantor_principal_id = grantor.principal_id
WHERE grantee.name like 'testdelete' or grantee.name like 'exectest'
ORDER BY grantee
class_desc permission_name state_desc ObjectName Grantee Grantor
DATABASE DELETE DENY NULL exectest dbo
DATABASE EXECUTE GRANT NULL exectest dbo
DATABASE CONNECT GRANT NULL TestDelete dbo
November 20, 2019 at 5:35 pm
i am pasting a full example below, with what is undoubtedly your issue with object ownership chaining.
for SQL server, if you grant execute permissions to a procedure, underlying objects permissions are not checked, if all the objects are in the same schema([dbo] for example)
to interrupt that logic, either the objects need to be in a different schema, or you need to use dynamic SQL or sp_executesql inside the proc. THEN the individual permissions are checked.
the first proc executes without an issue, even if the user does not have SELECT or DELETE.
the second example raises an error, due to the permissions being checked.
I included the EXECUTE AS clause just to show it has no affect if you change it to CALLER/SELF/OWNER
IF OBJECT_ID('[dbo].[Example]') IS NOT NULL
DROP TABLE [dbo].[Example];
GO
CREATE TABLE [dbo].[Example] (
[ExampleId] INT NOT NULL,
[ExampleText] VARCHAR(30) NULL,
CONSTRAINT [PK__Example] PRIMARY KEY CLUSTERED ([ExampleId] asc) );
INSERT INTO [Example]([ExampleId],[ExampleText]) VALUES (1,'Red'),(2,'Orange'),(3,'Green');
GO
IF OBJECT_ID('[dbo].[pr_exectest]') IS NOT NULL
DROP PROCEDURE [dbo].[pr_exectest]
GO
--object ownership chaining, anyone with execute can make this delete, even if individually denied, ownership is not checked.
CREATE PROCEDURE [pr_exectest](@ID int)
WITH EXECUTE AS CALLER
AS
BEGIN
DELETE FROM [dbo].[Example] WHERE [ExampleId] = @ID;
END; --PROC
GO
--dynamic sql or sp_executesql, ownership is tested
IF OBJECT_ID('[dbo].[pr_exectest2]') IS NOT NULL
DROP PROCEDURE [dbo].[pr_exectest2]
GO
CREATE PROCEDURE [pr_exectest2](@ID int)
WITH EXECUTE AS CALLER
AS
BEGIN
execute sys. sp_executesql N'DELETE FROM [dbo].[Example] WHERE [ExampleId] = @ExampleID',N'@ExampleID int',@ExampleID = @ID;
END; --PROC
GO
--#############################################################################
CREATE USER [exectest] WITHOUT LOGIN;
GO
GRANT EXECUTE TO [exectest];
REVOKE DELETE FROM [exectest];
DENY DELETE TO [exectest];
GO
execute as user='exectest';
SELECT suser_name(),user_name(); --exectest
EXECUTE [pr_exectest] 2
EXECUTE [pr_exectest2] 3
REVERT
SELECT * FROM [dbo].[Example]
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply