Synapse Serverless SQL - Access tables and views without Storage Access

 1-- create new database as master
 2CREATE DATABASE demo10;
 3
 4-- switch to new database
 5USE demo10;
 6GO
 7
 8-- master key encryption to allow authentication by Managed Identity
 9CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'megastrongpassword--12345678910--';
10
11-- Create a credential using Managed Identity
12CREATE DATABASE SCOPED CREDENTIAL synapsemhwe
13WITH IDENTITY='Managed Identity'
14
15-- Create a data source to point to data lake
16CREATE EXTERNAL DATA SOURCE ExternalDataSourceDataLakeMI3
17    WITH (
18        LOCATION   = 'https://storagewemh.dfs.core.windows.net/datasets',
19        CREDENTIAL = synapsemhwe
20        );
21
22-- create file format for use with external table
23CREATE EXTERNAL FILE FORMAT SynapseDeltaFormat
24WITH ( 
25        FORMAT_TYPE = DELTA
26     );
27
28-- Create External Table
29CREATE EXTERNAL TABLE diabetes (
30    [PatientID] bigint,
31    [Pregnancies] bigint,
32    [PlasmaGlucose] bigint,
33    [DiastolicBloodPressure] bigint,
34    [TricepsThickness] bigint,
35    [SerumInsulin] bigint,
36    [BMI] float,
37    [DiabetesPedigree] float,
38    [Age] bigint,
39    [Diabetic] bigint
40    )
41    WITH (
42    LOCATION = 'delta/diabetes',
43    DATA_SOURCE = ExternalDataSourceDataLakeMI3,
44    FILE_FORMAT = [SynapseDeltaFormat]
45    )
46GO
47
48-- In MASTER
49USE MASTER;
50CREATE USER [financeusers] FROM EXTERNAL PROVIDER;
51
52-- In demo10
53USE demo10;
54-- CREATE VIEW
55CREATE VIEW vwDiabetes AS SELECT * FROM demo10.dbo.diabetes;
56
57-- GRANT
58--we need to grant references to the MI credential
59GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[synapsemhwe] TO [financeusers];
60
61-- grant select on the external table and view
62GRANT SELECT ON demo10.dbo.diabetes TO [financeusers];
63GRANT SELECT ON demo10.dbo.vwDiabetes TO [financeusers];
64
65-- Or add the user as role reader in db
66-- EXEC sp_addrolemember 'db_datareader', 'financeusers'
67
68-- deny use of the OPENROWSET BULK command
69-- The reason is that if the user knows the DATA_SOUCE name the user can query any data using the OPENROWSET e.g.
70-- OPENROWSET usage is enabled by default. We need to deny access to usage of the BULK command to the user group
71USE master;
72DENY ADMINISTER BULK OPERATIONS TO [financeusers];

 1--create new database
 2CREATE DATABASE demo2;
 3
 4--switch to new database
 5USE demo2;
 6GO
 7
 8-- create master key encryption to allow authentication by Managed Identity
 9CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'megastrongpassword--12345678910--';
10
11--Create a credential using Managed Identity
12CREATE DATABASE SCOPED CREDENTIAL {cusotom_name_of_credential} WITH IDENTITY='Managed Identity'
13
14--Create a data source to point to data lake
15CREATE EXTERNAL DATA SOURCE MyExternalDataSourceDataLake
16WITH (
17LOCATION = 'https://{storageaccount}.dfs.core.windows.net/{container}',
18CREDENTIAL = {cusotom_name_of_credential}
19);
20
21--create file format for use with external table
22CREATE EXTERNAL FILE FORMAT MyDeltaFormat
23WITH (
24FORMAT_TYPE = DELTA
25);
26
27--- NEW ---
28--Create External Table
29CREATE EXTERNAL TABLE dbo.diabetes3 (
30[PatientID] bigint,
31[Pregnancies] bigint,
32[PlasmaGlucose] bigint,
33[DiastolicBloodPressure] bigint,
34[TricepsThickness] bigint,
35[SerumInsulin] bigint,
36[BMI] float,
37[DiabetesPedigree] float,
38[Age] bigint,
39[Diabetic] bigint
40)
41
42WITH (
43LOCATION = 'folder1/folder2',
44DATA_SOURCE = MyExternalDataSourceDataLake,
45FILE_FORMAT = [MyDeltaFormat]
46)
47GO
48
49-- In master
50CREATE LOGIN [user@{yourtenant}.onmicrosoft.com] FROM EXTERNAL PROVIDER;
51
52-- In your database
53-- Add user to Demo2
54CREATE USER MyUserName1 FROM LOGIN [user@{yourtenant}.onmicrosoft.com];
55
56-- ALTER ROLE db_owner ADD member DevUser1;
57-- GRANT
58--we need to grant references on the MI credential
59GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[{cusotom_name_of_credential}] TO [MyUserName1];
60
61-- CREATE VIEW
62CREATE VIEW vwDiabetes3 AS SELECT * FROM demo2.dbo.diabetes3;
63
64--grant select on the external table and view
65GRANT SELECT ON demo2.dbo.diabetes3 TO [MyUserName1];
66GRANT SELECT ON demo2.dbo.vwDiabetes3 TO [MyUserName1];
67
68EXECUTE AS USER = 'MyUserName1';
69GO
70
71SELECT top 100 * from demo2.dbo.diabetes3
72
73REVERT
74SELECT CURRENT_USER
75GO