permissionsazure-sql-databaseaccess-controlazure-sql-managed-instance

Azure SQL analysts can create views from forbidden cross database data

TLDR Problem

User can view forbidden data by creating a view to the forbidden data in his own personal schema where he has CONTROL permissions.

Introduction

I have a Azure SQL (Managed Instance) server with two databases:

Within the reports database, data analysts have their own personal schema with the permission to create views and tables. Due to the nature of some of the data, not every schema is allowed to be exposed to the data analyst.

Problem

I deny/revoke access to certain schemas, and direct access to these schemas works. But the data analyst can still create new views referencing the forbidden tables in the sources database and still view the data via the created view.

Question

This shouldn't be possible, as the data analyst should not be able to access the data in the sources database. But I can't seemingly find a way to prevent this.

Setup

The user's account are created in Azure's AD. I create the login for the user in the reports database.

CREATE USER [john@example.com] FOR LOGIN [john@example.com];

With his own schema:

USE [reports];

CREATE SCHEMA mypersonal;

-- Create a role
CREATE ROLE mypersonal_schema_creator;

-- Grant permissions to the role
GRANT CREATE TABLE TO mypersonal_schema_creator;
GRANT CREATE VIEW TO mypersonal_schema_creator;

-- Grant control on the schema to the role
GRANT CONTROL ON SCHEMA::mypersonal TO mypersonal_schema_creator;

-- Add the user to the role
ALTER ROLE mypersonal_schema_creator ADD MEMBER [john@example.com];

I grant the user access to a schema in sources database:

-- Connect to sqldb-ingestion01
CREATE USER [john@example.com] FOR LOGIN [john@example.com];

-- Grant SELECT permission on the ALLOWED schema
GRANT SELECT ON SCHEMA::allowed TO [john@example.com];

And when the user goes to sources database, he can't see the other schemas except the allowed schema. BUT when he creates a view that access a table in the forbidden schema, he can still see the data.

Solution

  • This is both expected and documented behaviour. What you are experiencing is known as Ownership Chaining.

    When an object, such as a VIEW or PROCEDURE, is called if the USER has permission to use that object they are also implicitly granted access to any objects that object references that have the same owner as the object, while in the scope of that object. In this case, you have a USER who has create a VIEW which SELECTs from other objects that have the same owner (presumably dbo or something), and so even though the USER doesn't have explicit access to the underlying table(s), the VIEW allows them implicit permission.

    Although I appreciate you've given us some of the detail, I'm going to set up my own example, with object DDL too. Firstly, let's create a test database and USER

    CREATE DATABASE YourDB;
GO
USE YourDB;
GO

CREATE USER SomeUser WITHOUT LOGIN; --Example User
GO

CREATE SCHEMA SomeSchema; --Example schema
GO

GRANT CONTROL ON SCHEMA::SomeSchema TO SomeUser;
GRANT CREATE TABLE TO SomeUser;
GRANT CREATE VIEW TO SomeUser;
GRANT SELECT ON SCHEMA::SomeSchema TO SomeUser;

    So the USER has permission to create VIEWs and TABLEs, they CONTROL "their" schema, and they can also SELECT from their schema.

    Now let's create a few sample objects:

    CREATE TABLE dbo.SomeTable (SomeID int);
CREATE TABLE dbo.AnotherTable (AnotherID int);
--We don't need any sample data, this is just permissions checking;
GO
GRANT SELECT ON dbo.SomeTable TO SomeUser;

    Note I grant the USER explicit access to SomeTable, but not to AnotherTable.

    Now, as the USER, I'm going to CREATE some VIEWs, and then do some test SELECTs:

    EXECUTE AS USER = 'SomeUser';
GO

SELECT SomeID
FROM dbo.SomeTable; --Works
GO

CREATE VIEW SomeSchema.SomeView AS
    SELECT SomeID AS ID
    FROM dbo.SomeTable;
GO

SELECT ID
FROM SomeSchema.SomeView; --Works
GO
SELECT AnotherID
FROM dbo.AnotherTable; --Doesn't work.
GO

CREATE VIEW SomeSchema.AnotherView AS
    SELECT AnotherID AS ID
    FROM dbo.AnotherTable;
GO

SELECT ID
FROM SomeSchema.AnotherView; --Works, due to permission chaining
GO

GO
REVERT; --back to normal

    As we can see, this did as I expectedL the SELECT against SomeSchema.AnotherView worked, and returned a dataset using dbo.AnotherTable and the SELECT directly against the object dbo.AnotherTable failed, with the error:

    The SELECT permission was denied on the object 'AnotherTable', database 'YourDB', schema 'dbo'.

    To get around this, as I mentioned, change the owner of the schema that the USER is using:

    --Let's change the owner of the schema
ALTER AUTHORIZATION ON SCHEMA::SomeSchema TO SomeUser;

    Now we can test again, just querying to 2 VIEWs:

    --Now let's try those VIEWs again
EXECUTE AS USER = 'SomeUser';
GO
SELECT SomeID
FROM dbo.SomeTable; --Works, we have explicit SELECT on the table
GO
SELECT ID
FROM SomeSchema.AnotherView; --Doesn't work, we don't have explicit permission and ownership chaining fails
GO
REVERT;

    This time the query against SomeSchema.AnotherView also fails, with the same permission error we got when trying to query dbo.AnotherTable directly.

    Cleanup:

    USE master;
GO
ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE YourDB;