Brad Chen's Developer Blog: Azure Synapse Analytics

Azure Synapse Analytics

Synapse Permission

1.Permission for Synapse workspace

IAM>Owner

contributor

reader role

2.Permission for Synapse

Synapse Studio>Manage>Access Control>

Assign Role

Synapse Administrator

Synapse Apache Spark Administrator

Synapse SQL Administrator

Synapse Contributor

Synapse Artifact Publisher

Synapse Artifact User

Synapse Compute Operator

Synapse Credential User

Synapse Linked Data Manager

Synapse User

3.Permission for Synapse SQL Pool

CREATE LOGIN [alias@domain.com] FROM EXTERNAL PROVIDER

-- (option)sysadmin

ALTER SERVER ROLE sysadmin ADD MEMBER [alias@domain.com];

CREATE USER [alias@domain.com] FOR LOGIN [alias@domain.com]

EXEC sp_addrolemember 'db_owner', 'alias@domain.com';

EXEC sp_addrolemember 'db_datawriter', 'alias@domain.com';

EXEC sp_addrolemember 'db_datareader’, 'alias@domain.com';

4.Permission for Storage Account(ADLS)

(1) Permission for Storage Account Management

IAM>Owner

contributor

reader role

(2) Permission for Blob Data Access Permission

IAM>

Storage Blob Data Contributor

Storage Blob Data Owner

Storage Blob Data Reader

Storage Blob Delegator

SQL Pool

STEP 7: Grant access to SQL pools

By default, all users assigned the Synapse Administrator role are also assigned the SQL db_owner role on the serverless SQL pools in the workspace.

SQL Pool - master

List of server-role members

SELECT  roles.principal_id  AS RolePrincipalID
    ,   roles.name AS RolePrincipalName
    ,   server_role_members.member_principal_id AS MemberPrincipalID
    ,   members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS members 
    ON server_role_members.member_principal_id = members.principal_id ;

Listing all principals (SQL authentication) which are members of a server-level role

SELECT
        sql_logins.principal_id         AS MemberPrincipalID
    ,   sql_logins.name                 AS MemberPrincipalName
    ,   roles.principal_id              AS RolePrincipalID
    ,   roles.name                      AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins 
    ON server_role_members.member_principal_id = sql_logins.principal_id
;  
GO

SQL Pool - Use Database: YourDatabase

-- User

select * from sys.database_principals where [type] <> 'R';

-- Database Role and member

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;

Master or User-DB

Build-In

(Serverless SQL pool)

no local storage, only metadata objects are stored in databases. following T-SQL isn't supported:

l Tables

l Triggers

l Materialized views

l DDL statements other than ones related to views and security

l DML statements

You can use CETAS - CREATE EXTERNAL TABLE AS SELECT

YourDedicatedSQLPool

(Dedicated SQL pool)

All support

三種情境

The user can be an Azure Active Directory authentication contained database user (if you've configured your environment for Azure AD authentication) 就是用Azure AD的Contained database user帳號
a SQL Server authentication contained database user 也就是SQL驗證的Contained database user帳號 (此情境在Synapse不支援)
a SQL Server authentication user based on a SQL Server authentication login (created in the previous step.) 是SQL驗證的SQL驗證Login登入帳號

1.Azure AD的Contained database user帳號

CREATE USER [devuser1@yourdomain.onmicrosoft.com] from External PROVIDER;

EXEC sp_addrolemember 'db_owner', 'devuser1@yourdomain.onmicrosoft.com';

如果想建立SQL驗證的Contained database user帳號，則會出現語法錯誤，而無法建立

Contained Database Users - Making Your Database Portable

Azure: SQL Database and Azure Synapse Analytics support Azure Active Directory identities as contained database users. SQL Database supports contained database users using SQL Server authentication, but Azure Synapse Analytics does not. For more information, see Connecting to SQL Database By Using Azure Active Directory Authentication. When using Azure Active Directory authentication, connections from SSMS can be made using Active Directory Universal Authentication. Administrators can configure Universal Authentication to require Multi-Factor Authentication, which verifies identity by using a phone call, text message, smart card with pin, or mobile app notification. For more information, see SSMS support for Azure AD MFA with SQL Database and Azure Synapse Analytics.

所以以下這篇文件的描述有誤，因為Synapse不支援

SQL Authentication in Azure Synapse Analytics

2.SQL驗證的Contained database user帳號 (此情境在Synapse不支援)

3.SQL驗證的SQL驗證Login登入帳號

CREATE LOGIN [ApUser1] WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';

CREATE USER [ApUser1] FROM LOGIN [ApUser1];

EXEC sp_addrolemember 'db_owner', 'ApUser1';

	master	UserDB
Build-In (Serverless SQL pool)	CREATE LOGIN [alias@domain.com] FROM EXTERNAL PROVIDER 建立網域帳號的登入時，管理員執行CREATE LOGIN語法的帳號必須是AD帳號，不然會出現錯誤(找不到使用者alias@domain.com) ALTER SERVER ROLE sysadmin ADD MEMBER [alias@domain.com]; CREATE LOGIN [sqladmin1] WITH PASSWORD = '340$Uuxwp7Mcxo7Khy' ALTER SERVER ROLE sysadmin ADD MEMBER [sqladmin1];
YourDedicatedSQLPool (Dedicated SQL pool)	CREATE LOGIN [alias@domain.com] FROM EXTERNAL PROVIDER CREATE LOGIN User1 WITH PASSWORD = '340$Uuxwp7Mcxo7Khy' In Azure SQL Database or synapse serverless, use the ALTER ROLE statement. ALTER SERVER ROLE sysadmin ADD MEMBER [alias@domain.com]; ALTER SERVER ROLE sysadmin ADD MEMBER [sqladmin1];	CREATE USER [alias@domain.com] FOR LOGIN [alias@domain.com] CREATE USER [User1] FOR LOGIN [User1] EXEC sp_addrolemember 'db_owner', 'Mary'; EXEC sp_addrolemember 'db_datawriter', 'Mary'; EXEC sp_addrolemember 'db_datareader’, 'Mary';

To grant full access to all serverless SQL pools in the workspace, in the master database

Azure Active Directory authorization: (可透過Synapse Studio web或SSMS登入)

CREATE LOGIN [alias@domain.com] FROM EXTERNAL PROVIDER;

ALTER SERVER ROLE sysadmin ADD MEMBER [alias@domain.com];

SQL authorization: (通常透過SSMS登入)

CREATE LOGIN Mary WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';

ALTER SERVER ROLE sysadmin ADD MEMBER [Mary];

Synapse Studio

https://web.azuresynapse.net/

需先給予Synapse workspace權限，例如Contributor，在 https://web.azuresynapse.net/ 才能選到Azure Subscription與synapse workspace

The Serverless SQL pool has no local storage, only metadata objects are stored in databases. Therefore, T-SQL related to the following concepts isn't supported:

Tables
Triggers
Materialized views
DDL statements other than ones related to views and security
DML statements

You can use CETAS - CREATE EXTERNAL TABLE AS SELECT or instead you can create a provisioned SQL Pool.

Azure Active Directory authorization:

SQL authorization:

Azure Synapse RBAC roles - Azure Synapse Analytics | Microsoft Docs

Role	Permissions	Scopes
Synapse Administrator	Full Synapse access to SQL pools, Data Explorer pools, Apache Spark pools, and Integration runtimes. Includes create, read, update, and delete access to all published code artifacts. Includes Compute Operator, Linked Data Manager, and Credential User permissions on the workspace system identity credential. Includes assigning Synapse RBAC roles. In addition to Synapse Administrator, Azure Owners can also assign Synapse RBAC roles. Azure permissions are required to create, delete, and manage compute resources. Can read and write artifacts Can do all actions on Spark activities. Can view Spark pool logs Can view saved notebook and pipeline output Can use the secrets stored by linked services or credentials Can assign and revoke Synapse RBAC roles at current scope	Workspace Spark pool Integration runtime Linked service Credential
Synapse Apache Spark Administrator	Full Synapse access to Apache Spark Pools. Create, read, update, and delete access to published Spark job definitions, notebooks and their outputs, and to libraries, linked services, and credentials. Includes read access to all other published code artifacts. Doesn't include permission to use credentials and run pipelines. Doesn't include granting access. Can do all actions on Spark artifacts Can do all actions on Spark activities	Workspace Spark pool
Synapse SQL Administrator	Full Synapse access to serverless SQL pools. Create, read, update, and delete access to published SQL scripts, credentials, and linked services. Includes read access to all other published code artifacts. Doesn't include permission to use credentials and run pipelines. Doesn't include granting access. Can do all actions on SQL scripts Can connect to SQL serverless endpoints with SQL `db_datareader`, `db_datawriter`, `connect`, and `grant` permissions	Workspace
Synapse Contributor	Full Synapse access to Apache Spark pools and Integration runtimes. Includes create, read, update, and delete access to all published code artifacts and their outputs, including credentials and linked services. Includes compute operator permissions. Doesn't include permission to use credentials and run pipelines. Doesn't include granting access. Can read and write artifacts Can view saved notebook and pipeline output Can do all actions on Spark activities Can view Spark pool logs	Workspace Spark pool Integration runtime
Synapse Artifact Publisher	Create, read, update, and delete access to published code artifacts and their outputs. Doesn't include permission to run code or pipelines, or to grant access. Can read published artifacts and publish artifacts Can view saved notebook, Spark job, and pipeline output	Workspace
Synapse Artifact User	Read access to published code artifacts and their outputs. Can create new artifacts but can't publish changes or run code without additional permissions.	Workspace
Synapse Compute Operator	Submit Spark jobs and notebooks and view logs. Includes canceling Spark jobs submitted by any user. Requires additional use credential permissions on the workspace system identity to run pipelines, view pipeline runs and outputs. Can submit and cancel jobs, including jobs submitted by others Can view Spark pool logs	Workspace Spark pool Integration runtime
Synapse Credential User	Runtime and configuration-time use of secrets within credentials and linked services in activities like pipeline runs. To run pipelines, this role is required, scoped to the workspace system identity. Scoped to a credential, permits access to data via a linked service that is protected by the credential (also requires compute use permission) Allows execution of pipelines protected by the workspace system identity credential(with additional compute use permission)	Workspace Linked Service Credential
Synapse Linked Data Manager	Creation and management of managed private endpoints, linked services, and credentials. Can create managed private endpoints that use linked services protected by credentials	Workspace
Synapse User	List and view details of SQL pools, Apache Spark pools, Integration runtimes, and published linked services and credentials. Doesn't include other published code artifacts. Can create new artifacts but can't run or publish without additional permissions. Can list and read Spark pools, Integration runtimes.	Workspace, Spark pool Linked service Cred

Storage Account Permission

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand#query-fails-because-file-cannot-be-opened

Azure built-in roles

https://docs.microsoft.com/en-us/azure/role-based-access-control/built-in-roles

Storage Blob Data Contributor

Storage Blob Data Owner

Storage Blob Data Reader

Storage Blob Delegator

Storage Blob Data Contributor	Read, write, and delete Azure Storage containers and blobs. To learn which actions are required for a given data operation, see Permissions for calling blob and queue data operations.	ba92f5b4-2d11-453d-a403-e96b0029c9fe
Storage Blob Data Owner	Provides full access to Azure Storage blob containers and data, including assigning POSIX access control. To learn which actions are required for a given data operation, see Permissions for calling blob and queue data operations.	b7e6dc6d-f1e8-4753-8033-0f276bb0955b
Storage Blob Data Reader	Read and list Azure Storage containers and blobs. To learn which actions are required for a given data operation, see Permissions for calling blob and queue data operations.	2a2b9908-6ea1-4ae2-8e65-a410df84e7d1
Storage Blob Delegator	Get a user delegation key, which can then be used to create a shared access signature for a container or blob that is signed with Azure AD credentials. For more information, see Create a user delegation SAS.	db58b8e5-c6ad-4a2a-8342-4190687cbf4a

How to set up access control for your Azure Synapse workspace

https://docs.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control

SQL Authentication in Azure Synapse Analytics
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/sql-authentication?tabs=serverless

Brad Chen's Developer Blog

2022年3月28日星期一

Azure Synapse Analytics

沒有留言:

SQL Server Planning, Pricing and License

追蹤者

2022年3月28日 星期一

Azure Synapse Analytics

沒有留言:

SQL Server Planning, Pricing and License

2022年3月28日星期一