Trying to deny delete inside a procedure

  • 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.

  • Can you show a repro with some code?

  • 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

     

     

    • This reply was modified 4 years, 11 months ago by  morbius82000.
  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply