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

Question

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

I have the following scenario:

1 Database

4 Schemes different with the following owners:

• schemaA; proprietary dbo

• schemaB; proprietary ownerX

• schemaC; proprietary ownerX

• schemaD; proprietary ownerX

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

A user userX will be allowed to SELECT in viewABC.

To ensure such access ownerX uses:

GRANT SELECT ON schemaD.viewABC TO userX;

When userX tries to execute the SELECT in the vision, so:

SELECT * FROM schemaD.viewABC;

We have the following error:

The SELECT permission has been denied in the 'table' object, database 'Mydatabase', schemaA schema'.

I understand that the error occurs because table is in a scheme where ownerX is not the owner and so the Sqlserver applies the permissions of userX to determine access. How userX does not have explicit access to tableA the execution of query returns the error.

If dbo give access to view, then the mistake will also happen by dbo not own the schemes schemaB and schemaC.

How to solve this without giving access to userX in table?

Remarks:

• I’m using Sqlserver 2012
• does not apply to me the solutions proposed here...
• nor here...
• by what I researched my problem fits into breaking Ownership Chains

Answer 1

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

What can be done is circumvent the situation using a Table-Valued Function and make her is always executed by a user who is allowed to SELECT in all tables involved in query/view.

The solution was thus:

• in the schemaD I created a function that returns the same set of records that the viewABC returned - despite ownerX not being the owner of schemaA, he has permission to SELECT in table

• to clause EXECUTE AS has been used to ensure that any execution of the function will use the schema owner permissions (in this case, the owner of the schemaD, ownerX) - in this context does not matter anymore the permissions that userX has or has not in the table

• for userX permission is granted to SELECT function created - although being a function is not the permission of EXECUTE should therefore be used 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 permission of SELECT is given in function:

GRANT SELECT ON schemaD.udfABC TO userX;

And userX can retrieve desired information like this:

SELECT * FROM schemaD.udfABC();

---

If we still want to use the viewABC we can make her perform the function, so:

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

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

GRANT SELECT ON schemaD.viewABC TO userX;

Using this approach it is not necessary to give direct permission in the function and userX will use the view normally.

---

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