ownership chaining not working with sp_cursorfetch



  • ownership chaining is not working for me in some cases. I use Microsoft SQL Server 14.0.3370.1.

    This is the setup for my tests:

    create function fnTest() returns int
    begin
        return 3
    end
    go
    

    create table tblTest
    (
    Number1 int
    )
    go

    insert into tblTest (Number1)
    values (5)
    go

    create view vwTest
    as
    select
    tblTest.Number1,
    dbo.fnTest() as Number2
    from tblTest
    go

    create view vwTest2
    as
    select
    dbo.fnTest() as Number2
    go

    grant select on vwTest to test
    go

    grant select on vwTest2 to test
    go

    Running the following commands as user test works fine:

    select * from vwTest
    
    select * from vwTest2
    
    declare @p1 int
    exec sp_cursoropen @p1 output,N'select * from vwTest2'
    exec sp_cursorfetch @p1
    

    But this command is not working:

    declare @p1 int
    exec sp_cursoropen @p1 output,N'select * from vwTest'
    exec sp_cursorfetch @p1
    

    I get the following error:

    The EXECUTE permission was denied on the object 'fnTest', database 'Test' schema 'dbo'.
    

    If I grant explicit permissions on the function fnTest, the error message is gone.

    Why does ownership chaining not work in this case?



  • This appears to be peculiar to keyset cursors, where ownership chaining does not work as expected for scalar functions in the fetch portion of the cursor plan.

    The issue is not limited to API cursors. I have adjusted your demo slightly to work with a T-SQL cursor and permit dynamic, keyset, and static implementations:

    Setup

    DROP VIEW IF EXISTS dbo.vwTest;
    DROP FUNCTION IF EXISTS dbo.fnTest;
    DROP TABLE IF EXISTS dbo.tblTest;
    DROP USER IF EXISTS test;
    
    CREATE USER test WITHOUT LOGIN;
    GO
    CREATE FUNCTION dbo.fnTest() 
    RETURNS integer 
    WITH SCHEMABINDING
    AS
    BEGIN
        RETURN 3;
    END;
    go
    CREATE TABLE dbo.tblTest
    (
        Number1 integer NOT NULL PRIMARY KEY
    );
    GO  
    INSERT dbo.tblTest 
        (Number1)
    VALUES 
        (5);
    GO  
    CREATE VIEW dbo.vwTest 
    WITH SCHEMABINDING
    AS
    SELECT
        T.Number1,
        dbo.fnTest() AS Number2
    FROM dbo.tblTest AS T;
    GO
    GRANT SELECT ON dbo.vwTest TO test;
    GRANT SHOWPLAN TO test;
    

    Test

    DBCC FREEPROCCACHE;
    GO
    EXECUTE AS USER = 'test';
    

    -- Works as a plain SELECT
    SELECT VT.Number1, VT.Number2
    FROM dbo.vwTest AS VT;

    -- Fails as a keyset cursor only (dynamic and static are fine)
    DECLARE c CURSOR
    LOCAL
    --DYNAMIC
    KEYSET
    --STATIC
    READ_ONLY
    TYPE_WARNING
    FOR
    SELECT VT.Number1, VT.Number2
    FROM dbo.vwTest AS VT;

    OPEN c;

    BEGIN TRY
    FETCH c;
    END TRY
    BEGIN CATCH
    PRINT ERROR_MESSAGE();
    END CATCH;

    CLOSE c;
    DEALLOCATE c;

    REVERT;

    The test produces the permissions error only when a keyset cursor is used:

    The EXECUTE permission was denied on the object 'fnTest', database 'Sandpit', schema 'dbo'.

    Cursor plans

    There are two plans associated with a cursor, the population plan and the fetch plan.

    The population plan runs on OPEN to populate any cursor work table needed.

    For a static cursor, the work table contains the entire result set. For a keyset cursor, only the keys of the relation(s) are stored. A dynamic cursor does not use a work table so there is no population plan.

    The fetch plan runs on each call to fetch a row (or batch of rows in the case of an API cursor).

    Static plan

    static cursor plan

    No permission error occurs. Ownership chaining from the view works as expected for both the table and the function, as shown by a trace on the https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/audit-schema-object-access-event-class profiling event:

    Permission checks for static cursor

    Permissions are checked on the view first, followed by the function, then the table. The permission checked on the view and table is SELECT (1). The permission checked on the function is EXECUTE (32) as documented.

    No permissions are checked on the FETCH statement because it retrieves data only from the cursor work table.

    Dynamic plan

    dynamic cursor plan

    No permission error occurs. Ownership chaining from the view works as expected for both the table and the function:

    Permission checks for dynamic cursor

    Permissions are successfully checked on the view, table, and function for DECLARE CURSOR, OPEN, and FETCH.

    Keyset plan

    keyset cursor plan

    Permissions are successfully checked on the view, table, and function for DECLARE CURSOR and OPEN. Permissions are also successfully checked on the view for the FETCH statement, but a permission error occurs when checking the function:

    Permission checks for keyset cursor

    There is no particular reason I can see for this to work differently for keyset cursors, so it seems to be a product defect. Tested on SQL Server 2019 CU 15.

    Workaround

    Use either static or dynamic instead of a keyset when relying on ownership chaining to access a function through a cursor.

    You can specify which cursor types are acceptable in the call to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cursoropen-transact-sql via the scrollopt optional parameter:

    enter image description here

    The server happens to choose a static cursor for your select from vwTest2, but a keyset cursor for vwTest.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2