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:

• sources: tables with data
• reports: views build on top of the tables in sources

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 [[email protected]] FOR LOGIN [[email protected]];

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 [[email protected]];

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

-- Connect to sqldb-ingestion01
CREATE USER [[email protected]] FOR LOGIN [[email protected]];

-- Grant SELECT permission on the ALLOWED schema
GRANT SELECT ON SCHEMA::allowed TO [[email protected]];

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.