【備忘】Openrowsetを利用するviewにSELECT権限（その他の権限を確認しながら）付与するサンプル

権限の動作確認

事前に 常にManaged ID 認証でOpen RowsetするViewを作りたいときに使うサンプルを実施したところから開始します

sql

USE [cred_test]
GO
--包含データベースユーザーの作成
CREATE USER [<ユーザー、グループ名>] FROM EXTERNAL PROVIDER

-- 対象のviewの作成
CREATE VIEW dbo.v_acltest as
SELECT
    TOP 100 *
FROM OPENROWSET(
    BULK '/Refined/',
    DATA_SOURCE='msi_datalake_enrich', --> Root URL is in LOCATION of DATA SOURCE
    FORMAT = 'DELTA'
    ) AS [result]

この時点では対象のユーザーからはview表示されない（データベース名は画像用に別名になってます）

sql

-- viewでのselect権付与

GRANT SELECT ON dbo.[v_acltest] TO [<ユーザー、グループ名>]

この時点で、エクスプローラにも表示されクエリを実行できるが、viewがopenrowsetを利用している場合にはそのための権限が不足している旨が表示される

参考：https://learn.microsoft.com/ja-jp/azure/synapse-analytics/sql/develop-openrowset#security

sql

-- データべーススコープでADMINISTER BULK OPERATIONS権限を付与する

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<ユーザー、グループ名>]

今度はDATA SOURCE[msi_datalake_enrich]内のDATABASE SCOPED CREDENTIAL[cred_synapsemsi]への権限不足を指摘される

sql

-- DATABASE SCOPED CREDENTIALへの参照権限を付与する
-- https://learn.microsoft.com/ja-jp/sql/t-sql/statements/grant-database-scoped-credential-transact-sql?view=sql-server-ver16

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[cred_synapsemsi] TO [<ユーザー、グループ名>]

以上でクエリが可能となる。

まとめ

したがって、サーバレスSQL Pool上でオブジェクトレベルの権限制御のみで権限管理し、データレイクへの権限付与を不要とする場合には以下の流れが一例となる（アプリケーションロールを運用する場合にはこの限りではない。）

1. サーバーレスSQL Pool データベース作成
2. 資格情報を作成
3. 対象の包含データベースユーザーの作成
4. 資格情報へのREFERENCE権限とADMINISTER BULK OPERATIONS権限の付与

ここまででユーザーとデータベースのセットアップが完了
以下は権限付与対象となるViewを作成するたびに実施する。

1. データソースおよびViewを作成×N回
2. ViewへのSELECT 権限をユーザーに付与

一連の流れをまとめると以下の通り

sql

-- サーバーレスSQL Pool データベース作成
CREATE DATABASE [cred_test] 
    COLLATE Latin1_General_100_BIN2_UTF8; -- お好みで
    -- https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/always-use-utf-8-collations-to-read-utf-8-text-in-serverless-sql/ba-p/1883633
GO
USE [cred_test]
GO

-- 資格情報を作成 
CREATE MASTER KEY    -- CREATE DATABASE SCOPED CREDENTIALに必要
GO
CREATE DATABASE SCOPED CREDENTIAL [cred_synapsemsi]
WITH IDENTITY = 'Managed Identity'
GO

--対象の包含データベースユーザーの作成
CREATE USER [<ユーザー、グループ名>] FROM EXTERNAL PROVIDER
GO
GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<ユーザー、グループ名>]
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[cred_synapsemsi] TO [<ユーザー、グループ名>]
GO
--ここまででユーザーとデータベースのセットアップが完了

-- データソースおよびViewを作成

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'msi_datalake_enrich')
    CREATE EXTERNAL DATA SOURCE [msi_datalake_enrich]
    WITH(
        CREDENTIAL = [cred_synapsemsi],
        LOCATION = 'https://testaccount.dfs.core.windows.net/30-enrich'
    )

CREATE VIEW dbo.v_acltest as
SELECT
    TOP 100 *
FROM OPENROWSET(
    BULK '/Refined/',
    DATA_SOURCE='msi_datalake_enrich', --> Root URL is in LOCATION of DATA SOURCE
    FORMAT = 'DELTA'
    ) AS [result]
GO
-- ViewへのSELECT 権限をユーザーに付与
GRANT SELECT ON dbo.[v_acltest] TO [<ユーザー、グループ名>]