How to create a PolyBase user with only schema level access

[kevin_ngo]

PolyBase leverages the scale-out architecture of SQL Data Warehouse (SQL DW) for high throughput data ingestion and has been widely adopted as the primary loading utility. There are a few requirements when loading using PolyBase - one of which is CONTROL permission on the database. This permission grants the user access to all schemas and tables in the data warehouse where having such elevated permissions to load a subset of tables can pose a security concern for customers. This blog outlines how you can easily work around this issue when you have multiple projects and workloads in a single data warehouse and would like to grant the load user access to only a specific schema.

For context, PolyBase requires the following objects to be created prior to each load:

1. MASTER KEY – Requires database CONTROL permission
2. DATABASE SCOPED CREDENTIAL – Requires database CONTROL permission
3. EXTERNAL DATA SOURCE – Requires database CONTROL permission
4. EXTERNAL FILE FORMAT - Requires ALTER ANY EXTERNAL FILE FORMAT
5. EXTERNAL TABLE – Requires: CREATE TABLE, ALTER on the SCHEMA, ALTER ANY EXTERNAL DATA SOURCE, and ALTER ANY EXTERNAL FILE FORMAT

Only objects 1 – 3 require CONTROL permission at the database level. To enable and grant the load user access to only a specific schema, you can create these 3 objects in advance by a user who has CONTROL permission on the database and permissions for objects 4 and 5 can be granted to the load user for a specific schema. For example,

• Create the 3 objects in advance by a user who has database CONTROL permissions:

-- Log in as user with database CONTROL permission
CREATE MASTER KEY;

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
    IDENTITY = '',
    SECRET = ''
;
 
CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfs[s]://',
    CREDENTIAL = 
);

• Grant permissions to create external tables to a load user (loaduser_schema1) for a specific default schema (schema1):

CREATE LOGIN loaduser_schema1 WITH PASSWORD = ‘’;
CREATE SCHEMA schema1;
CREATE USER loaduser_schema1 for LOGIN loaduser_schema1 WITH DEFAULT_SCHEMA=schema1;
Grant CREATE TABLE TO loaduser_schema1;
Grant ALTER ON SCHEMA::schema1 TO loaduser_schema1;
Grant ALTER ANY EXTERNAL DATA SOURCE TO loaduser_schema1;
Grant ALTER ANY EXTERNAL FILE FORMAT TO loaduser_schema1;

-- Log in as loaduser_schema1
CREATE EXTERNAL FILE FORMAT TextFile
WITH (
    FORMAT_TYPE = DelimitedText,
    FORMAT_OPTIONS (FIELD_TERMINATOR = ','),
       DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
);

CREATE EXTERNAL TABLE DimDate_external
(
    DateId INT NOT NULL,
    CalendarQuarter TINYINT NOT NULL,
    FiscalQuarter TINYINT NOT NULL
)
WITH (
    LOCATION='/DimDate.txt',
    DATA_SOURCE=AzureStorage,
    FILE_FORMAT=TextFile
);
 
CREATE TABLE DimDate
(
    DateId INT NOT NULL,
    CalendarQuarter TINYINT NOT NULL,
    FiscalQuarter TINYINT NOT NULL
)
 
INSERT INTO DimDate
SELECT * FROM DimDate_external

Loaduser_schema1 will be able to create external tables and external file formats and load into a target table in schema1. Loaduser_schema1 cannot SELECT any tables under any other schemas beside schema1.

[isr2020]

This article was very helpful for my customer. If you are reading this article there is a good chance you would benefit from quite a few learning I have documented around Data Load using Polybase including Azure Data Factory - https://medium.com/@isinghrana/practical-tips-on-data-load-to-synapse-using-polybase-aeba9866692f