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:
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,
-- 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 = );
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.