locked
EXECUTE permission for scalar function in view RRS feed

  • Question

  • Hi, I was found very odd behaviour of resolving EXECUTE permission for scalar function in view. Very often you don't need grant to EXECUTE for scalar function, when is used in the view and you have permission to SELECT on this view. I found one case where is really required permission to EXECUTE for used function.

    This behaviour it shows in specific combination usage of function in grouped SELECT with ABS(SUM(function)), SUM(function) suddenly. Here is script where can you simulate this behaviour. First SELECT from [dbo].[ViewTestFail] failed with error message *The EXECUTE permission was denied on the object 'Foo', database 'DB', schema 'dbo'.*

    It is bug or I missed some documentation, where is described this behaviour ?

    CREATE LOGIN [Test] WITH PASSWORD = 'test', CHECK_POLICY = OFF
    GO
    
    CREATE USER [Test] FOR LOGIN [Test]
    GO
    
    CREATE ROLE [db_test_role] AUTHORIZATION [dbo];
    GO
    
    ALTER ROLE [db_test_role] ADD MEMBER [Test];
    GO
    
    CREATE FUNCTION [dbo].[Foo](@I INT)
    RETURNS INT
    WITH SCHEMABINDING
    AS
    BEGIN
        RETURN 1 * @I - @I
    END
    GO
    
    CREATE VIEW [dbo].[ViewTest]
    AS 
        WITH 
        L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
        L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
        L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
        Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L2)
        SELECT i, [dbo].[Foo](i) AS Foo FROM Nums
    GO
    
    GRANT SELECT
        ON OBJECT::[dbo].[ViewTest] TO [db_test_role]
        AS [dbo];
    GO
    
    CREATE VIEW [dbo].[ViewTest2]
    AS 
        WITH 
        L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
        L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
        L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
        Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L2)
        SELECT 
    	   i, 
    	   [dbo].[Foo](i) AS Foo
    	   FROM Nums
        GROUP BY i
    GO
    
    GRANT SELECT
        ON OBJECT::[dbo].[ViewTest2] TO [db_test_role]
        AS [dbo];
    GO
    
    CREATE VIEW [dbo].[ViewTest3]
    AS 
        WITH 
        L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
        L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
        L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
        Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L2)
        SELECT 
    	   i, 
    	   SUM([dbo].[Foo](i)) AS FooSum
    	   FROM Nums
        GROUP BY i
    GO
    
    GRANT SELECT
        ON OBJECT::[dbo].[ViewTest3] TO [db_test_role]
        AS [dbo];
    GO
    
    CREATE VIEW [dbo].[ViewTest4]
    AS 
        WITH 
        L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
        L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
        L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
        Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L2)
        SELECT 
    	   i, 
    	   ABS(SUM([dbo].[Foo](i))) AS FooAbs
    	   FROM Nums
        GROUP BY i
    GO
    
    GRANT SELECT
        ON OBJECT::[dbo].[ViewTest4] TO [db_test_role]
        AS [dbo];
    GO
    
    CREATE VIEW [dbo].[ViewTest5]
    AS 
        WITH 
        L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
        L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
        L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
        Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L2)
        SELECT i, 
    	   ABS(SUM([dbo].[Foo](i))) AS FooAbs,
    	   [dbo].[Foo](i) AS Foo
    	   FROM Nums
        GROUP BY i
    GO
    
    GRANT SELECT
        ON OBJECT::[dbo].[ViewTest5] TO [db_test_role]
        AS [dbo];
    GO
    
    
    CREATE VIEW [dbo].[ViewTestFail]
    AS 
        WITH 
        L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
        L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
        L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
        Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L2)
        SELECT i, 
    	   ABS(SUM([dbo].[Foo](i))) AS FooAbs,
    	   SUM([dbo].[Foo](i)) AS FooSum 
    	   FROM Nums
        GROUP BY i
    GO
    
    GRANT SELECT
        ON OBJECT::[dbo].[ViewTestFail] TO [db_test_role]
        AS [dbo];
    GO
    
    
    EXECUTE AS LOGIN = 'TEST';
    SELECT * FROM [dbo].[ViewTest]
    REVERT;
    
    EXECUTE AS LOGIN = 'TEST';
    SELECT * FROM [dbo].[ViewTest2]
    REVERT;
    
    EXECUTE AS LOGIN = 'TEST';
    SELECT * FROM [dbo].[ViewTest3]
    REVERT;
    
    EXECUTE AS LOGIN = 'TEST';
    SELECT * FROM [dbo].[ViewTest4]
    REVERT;
    
    EXECUTE AS LOGIN = 'TEST';
    SELECT * FROM [dbo].[ViewTest5]
    REVERT;
    
    EXECUTE AS LOGIN = 'TEST';
    SELECT * FROM [dbo].[ViewTestFail]
    REVERT;
    
    GRANT EXECUTE
        ON OBJECT::[dbo].[Foo] TO [db_test_role]
        AS [dbo];
    GO
    
    EXECUTE AS LOGIN = 'TEST';
    SELECT * FROM [dbo].[ViewTestFail]
    REVERT;
    
    REVOKE EXECUTE
        ON OBJECT::[dbo].[Foo] TO [db_test_role]
        AS [dbo];
    GO
    
    GRANT EXECUTE TO [db_test_role] AS [dbo];
    GO
    
    EXECUTE AS LOGIN = 'TEST';
    SELECT * FROM [dbo].[ViewTestFail]
    REVERT;
    
    REVOKE EXECUTE TO [db_test_role] AS [dbo];
    GO
    
    DROP FUNCTION [dbo].[Foo]
    DROP VIEW [dbo].[ViewTest]
    DROP VIEW [dbo].[ViewTest2]
    DROP VIEW [dbo].[ViewTest3]
    DROP VIEW [dbo].[ViewTest4]
    DROP VIEW [dbo].[ViewTest5]
    DROP VIEW [dbo].[ViewTestFail]
    DROP USER [Test]
    DROP ROLE [db_test_role]
    DROP LOGIN [Test]
    GO
    


    Wednesday, April 13, 2016 2:42 PM

Answers

  • I can find no logic in it. I tested this in SQL Server 2016 RC2 and the behaviour is the same.
    I would advise you to submit a bug on http://connect.microsoft.com/SqlServer/Feedback
    and include your excellent repro.

    If this is something that is blocking your work and you need a fix, you would need to open a support case.

    Wednesday, April 13, 2016 9:19 PM

All replies

  • I can find no logic in it. I tested this in SQL Server 2016 RC2 and the behaviour is the same.
    I would advise you to submit a bug on http://connect.microsoft.com/SqlServer/Feedback
    and include your excellent repro.

    If this is something that is blocking your work and you need a fix, you would need to open a support case.

    Wednesday, April 13, 2016 9:19 PM
  • Thanks, I will report a bug as you advised.

    This isn't blocking issue, when existing simple workaround by grant permission to execute on function. This workaround only have consequences for me, this function was generated in LINQ to SQL data context.

    Thursday, April 14, 2016 5:55 AM