SQL Server - How to guarantee permission from SELECT to View without giving permission in the base tables?

Navigation
3

I need help with a problem involving permissioning objects in the database.

I have the following scenario:

1 Database

4 Schemes with the following owners:

  • schemaA ; owner dbo

  • schemaB ; owner ownerX

  • schemaC ; owner ownerX

  • schemaD ; owner ownerX

I have a viewABC view that is in schemaD and gathers table and view information from schemaA , schemaB schemas > and schemaC .

A user userX will have permission of SELECT in viewABC .

To ensure such ownerX access: 

GRANT SELECT ON schemaD.viewABC TO userX;

When userX attempts to execute SELECT on the view, like this: 

SELECT * FROM schemaD.viewABC;

We have the following error:

  

The SELECT permission was denied on object 'tableA', database   data 'MyBank', scheme 'schemaA'.

I understand that the error occurs because tableA is in a schema where ownerX is not the owner and so SQLServer applies the permissions of userX to determine access. As userX does not explicitly access tableA query execution returns the error.

If dbo gives view permission, then the error will also happen for dbo not owning the schemaB and schemaC .

How to resolve this without giving userX permission on tableA ?

Comments:

asked by anonymous 05.07.2016 / 15:06

1 answer

0

Apparently there is no way, at least in the exposed scenario, to grant permission of SELECT to userX in viewABC without also granting permission of SELECT in table .

What can be done is to work around the situation using an Table-Valued Function and make it always run by a user that has SELECT permission on all tables involved query / view .

The solution looks like this:

    I have created a function that returns the same recordset as the viewABC returned - even though ownerX does not own the schemaA , it has permission of SELECT in tableA

  • The clause EXECUTE AS was used to ensure that any function execution will use schema owner permissions (in this case, owner of schemaD , ownerX ) - in this context no matter what permissions userX has in tableA

  • userX permission is granted for SELECT in the function created - although it is a function it is not the EXECUTE permission that should be used, since the function returns a table

To illustrate, an example of Table-Valued Function 

CREATE FUNCTION schemaD.udfABC ()
RETURNS @tabABC TABLE (
  fieldA INT NOT NULL, fieldB INT NOT NULL, fieldC INT NOT NULL
)
WITH EXECUTE AS OWNER
AS
BEGIN
  INSERT INTO @tabABC (fieldA, fieldB, fieldC)
    SELECT a.fieldA, b.fieldB, c.fieldC
      FROM schemaA.tableA a
     INNER JOIN schemaB.tableB b ON a.id = b.idA
     INNER JOIN schemaC.tableC c ON b.id = c.idB;

  RETURN;  
END

Now the SELECT permission is given in the function: 

GRANT SELECT ON schemaD.udfABC TO userX;

And userX can retrieve the desired information like this: 

SELECT * FROM schemaD.udfABC();

If we still want to use viewABC we can make it execute the function, like this: 

CREATE VIEW schemaD.viewABC
AS
SELECT * 
  FROM schemaD.udfABC();

And then the permission of SELECT is given in view , as before: 

GRANT SELECT ON schemaD.viewABC TO userX;

Using this approach does not require direct permission on the role and userX will normally use view .

I got the solution from suggestions I received from the post in the link and in a similar question which I found there too.

    
08.07.2016 / 15:50
Close all Activities How can I specify a "transition" value to be animated when I use "transform"?