database – SQL Server – How to guarantee SELECT permission for View without giving permission on base tables?

Question:

I need help with an issue involving permissioning objects in the database.

I have the following scenario:

1 Database

4 different layouts with the following owners:

  • schemaA ; dbo owner

  • schemaB ; owner ownerX

  • schemaC ; owner ownerX

  • schemaD ; owner ownerX

I have a viewABC view that is in schemaD and gathers information from tables and views of schemaA , schemaB and schemaC schemas .

A userX user will have SELECT permission on viewABC .

To guarantee such access ownerX uses:

GRANT SELECT ON schemaD.viewABC TO userX;

When userX tries to execute the SELECT in the view, like this:

SELECT * FROM schemaD.viewABC;

We have the following error:

SELECT permission denied on object 'tableA', database 'MyBank', schema 'schemaA'.

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

If dbo gives the view access permission then the error will also happen because dbo does not own the schemaB and schemaC schemas .

How to solve this without giving userX access permission in tableA ?

Comments:

Answer:

Apparently there is no way, at least in the above scenario, to grant SELECT permission to userX on viewABC without also granting SELECT permission on tableA .

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

The solution looked like this:

  • in schemaD I created a function that returns the same set of records that viewABC returned – even though ownerX is not the owner of schemaA , he has SELECT permission on tableA

  • the EXECUTE AS clause was used to ensure that any execution of the function will use the permissions of the schema owner (in this case, the owner of schemaD , ownerX ) – in this context it no longer matters what permissions userX has or does not have on tableA

  • userX is granted SELECT permission on the created function – although it is a function, it is not the EXECUTE permission that should be used, as the function returns a table

To illustrate, an example of the 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 SELECT permission is given on 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 SELECT permission is given on the view , as before:

GRANT SELECT ON schemaD.viewABC TO userX;

Using this approach it is not necessary to give permission directly on the role and userX will use the view normally.

I got the solution from suggestions received in the post I made about this same issue on https://dba.stackexchange.com/ and a similar issue I found there too.

Post Views: 1
Scroll to Top