권한 관리를 위한 Azure SQL Database 서버 역할

아티클
03/19/2024

이 문서에서는 Azure SQL 데이터베이스의 고정 서버 수준 역할에 대해 설명합니다.

참고 항목

이 문서의 고정 서버 수준 역할은 Azure SQL Database의 경우 공개 미리 보기로 제공됩니다. 이러한 서버 수준 역할은 SQL Server 2022 릴리스의 일부이기도 합니다.

개요

Azure SQL 데이터베이스에서 서버는 논리적 개념이며 서버 수준에서 권한을 부여할 수 없습니다. 권한 관리를 단순화하기 위해 Azure SQL Database는 논리 서버에 대한 권한을 관리할 수 있도록 하는 고정 서버 수준 역할 집합을 제공합니다. 역할은 로그인을 그룹화하는 보안 주체입니다.

참고 항목

이 문서의 역할 개념은 Windows 운영 체제의 그룹과 같습니다.

이러한 특수 고정 서버 수준 역할은 접두사 ##MS_ 및 접미사 ##을 사용하여 다른 일반 사용자 생성 보안 주체와 구별합니다.

SQL Server 온-프레미스와 마찬가지로 서버 권한은 계층적으로 구성됩니다. 이러한 서버 수준 역할이 보유한 권한은 데이터베이스 권한으로 전파될 수 있습니다. 권한이 데이터베이스 수준에서 실질적으로 유용하려면 로그인이 모든 데이터베이스에 CONNECT를 부여하는 서버 수준 역할 ##MS_DatabaseConnector##의 구성원이거나 개별 데이터베이스에 사용자 계정을 가지고 있어야 합니다. 이는 가상 master 데이터베이스에도 적용됩니다.

예를 들어 서버 수준 역할 ##MS_ServerStateReader##에는 VIEW SERVER STATE 사용 권한이 있습니다. 이 역할의 구성원인 로그인이 master 및 WideWorldImporters 데이터베이스에 사용자 계정을 가지고 있는 경우 이 사용자는 두 데이터베이스에서 VIEW DATABASE STATE 권한을 갖습니다.

참고 항목

모든 권한은 사용자 데이터베이스 내에서 거부될 수 있으며, 사실상 역할 멤버 자격을 통한 서버 전체 권한 부여를 무시합니다. 그러나 시스템 데이터베이스 master에서는 권한을 부여하거나 거부할 수 없습니다.

Azure SQL 데이터베이스는 현재 7가지 고정 서버 역할을 제공합니다. 고정 서버 역할에 부여된 권한은 변경할 수 없으며, 이러한 역할은 다른 고정 역할을 멤버로 가질 수 없습니다. 서버 수준 로그인을 서버 수준 역할의 멤버로 추가할 수 있습니다.

Important

고정 서버 역할의 각 구성원은 같은 역할에 다른 로그인을 추가할 수 있습니다.

Azure SQL Database 로그인 및 사용자에 대한 자세한 내용은 SQL Database, SQL Managed Instance 및 Azure Synapse Analytics에 대한 데이터베이스 액세스 권한 부여를 참조하세요.

고정 서버 수준 역할

다음 표에서는 고정 서버 수준 역할과 해당 기능을 보여 줍니다.

고정 서버 수준 역할	설명
`##MS_DatabaseConnector##`	`##MS_DatabaseConnector##` 고정 서버 역할의 구성원은 연결할 데이터베이스의 사용자 계정이 없어도 모든 데이터베이스에 연결할 수 있습니다. 특정 데이터베이스에 대한 `CONNECT` 권한을 거부하기 위해 사용자는 데이터베이스에서 이 로그인을 위한 일치하는 사용자 계정을 만든 다음 데이터베이스 사용자에 대한 `CONNECT` 권한을 `DENY`할 수 있습니다. 이 `DENY` 권한은 이 역할에서 비롯된 `GRANT CONNECT` 권한을 무효화합니다.
`##MS_DatabaseManager##`	`##MS_DatabaseManager##` 고정 서버 역할의 구성원은 데이터베이스를 만들고 삭제할 수 있습니다. 데이터베이스를 만드는 `##MS_DatabaseManager##` 역할의 구성원은 해당 데이터베이스 소유자가 되어 사용자가 `dbo` 사용자로 데이터베이스에 연결할 수 있도록 허용합니다. `dbo` 사용자는 데이터베이스의 모든 데이터베이스 권한을 갖습니다. `##MS_DatabaseManager##` 역할의 구성원은 소유하지 않은 데이터베이스에 액세스할 권한이 반드시 필요하지는 않습니다. `master`에 있는 dbmanager 데이터베이스 수준 역할보다 이 서버 역할을 사용해야 합니다.
`##MS_DefinitionReader##`	`##MS_DefinitionReader##` 고정 서버 역할의 구성원은 `VIEW ANY DEFINITION`가 적용되는 모든 카탈로그 뷰를 읽을 수 있으며, 각각 이 역할의 구성원이 사용자 계정을 가지고 있는 모든 데이터베이스에서 `VIEW DEFINITION` 권한을 갖습니다.
`##MS_LoginManager##`	`##MS_LoginManager##` 고정 서버 역할의 구성원은 로그인을 만들고 삭제할 수 있습니다. `master`에 있는 loginmanager 데이터베이스 수준 역할보다 이 서버 역할을 사용해야 합니다.
`##MS_SecurityDefinitionReader##`	`##MS_SecurityDefinitionReader##` 고정 서버 역할의 구성원은 `VIEW ANY SECURITY DEFINITION`가 적용되는 모든 카탈로그 뷰를 읽을 수 있으며, 각각 이 역할의 구성원이 사용자 계정을 가지고 있는 모든 데이터베이스에서 `VIEW SECURITY DEFINITION` 권한을 갖습니다. 이는 `##MS_DefinitionReader##` 서버 역할이 액세스할 수 있는 집합의 작은 하위 집합입니다.
`##MS_ServerStateManager##`	`##MS_ServerStateManager##` 고정 서버 역할의 구성원은 `##MS_ServerStateReader##` 역할과 동일한 권한을 갖습니다. 또한 `DBCC FREEPROCCACHE`, `DBCC FREESYSTEMCACHE ('ALL')`, `DBCC SQLPERF()` 등의 여러 관리 작업에 대한 액세스를 허용하는 `ALTER SERVER STATE` 권한을 보유합니다.
`##MS_ServerStateReader##`	`##MS_ServerStateReader##` 고정 서버 역할의 구성원은 `VIEW SERVER STATE`가 적용되는 모든 DMV(동적 관리 뷰) 및 함수를 읽을 수 있으며, 각각 이 역할의 구성원이 사용자 계정을 가지고 있는 모든 데이터베이스에서 `VIEW DATABASE STATE` 권한을 갖습니다.

고정 서버 역할에 대한 사용 권한

각 고정 서버 수준 역할에는 관련된 특정 사용 권한이 있습니다. 다음 표는 서버 수준 역할에 할당된 권한을 보여 줍니다. 또한 사용자가 개별 데이터베이스에 연결할 수 있는 경우 상속된 데이터베이스 수준 권한도 보여 줍니다.

고정 서버 수준 역할	서버 수준 사용 권한	데이터베이스 수준 권한(로그인과 일치하는 데이터베이스 사용자가 있는 경우)
`##MS_DatabaseConnector##`	`CONNECT ANY DATABASE`	`CONNECT`
`##MS_DatabaseManager##`	`CREATE ANY DATABASE`, `ALTER ANY DATABASE`	`ALTER`
`##MS_DefinitionReader##`	`VIEW ANY DATABASE`, `VIEW ANY DEFINITION`, `VIEW ANY SECURITY DEFINITION`	`VIEW DEFINITION`, `VIEW SECURITY DEFINITION`
`##MS_LoginManager##`	`CREATE LOGIN`, `ALTER ANY LOGIN`	해당 없음
`##MS_SecurityDefinitionReader##`	`VIEW ANY SECURITY DEFINITION`	`VIEW SECURITY DEFINITION`
`##MS_ServerStateManager##`	`ALTER SERVER STATE`, `VIEW SERVER STATE`, `VIEW SERVER PERFORMANCE STATE`, `VIEW SERVER SECURITY STATE`	`VIEW DATABASE STATE`, `VIEW DATABASE PERFORMANCE STATE`, `VIEW DATABASE SECURITY STATE`
`##MS_ServerStateReader##`	`VIEW SERVER STATE`, `VIEW SERVER PERFORMANCE STATE`, `VIEW SERVER SECURITY STATE`	`VIEW DATABASE STATE`, `VIEW DATABASE PERFORMANCE STATE`, `VIEW DATABASE SECURITY STATE`

사용 권한

서버 관리자 계정 또는 Microsoft Entra 관리자 계정(Microsoft Entra 그룹일 수 있는)만 서버 역할의 다른 로그인을 추가하거나 제거할 수 있습니다. 이는 Azure SQL 데이터베이스에만 해당됩니다.

참고 항목

Microsoft Entra ID는 이전에 Azure Active Directory(Azure AD)로 알려졌습니다.

서버 수준 역할 작업

다음 표에서는 Azure SQL Database에서 서버 수준 역할을 사용하는 데 사용할 수 있는 시스템 뷰 및 함수에 대해 설명합니다.

기능	Type	설명
IS_SRVROLEMEMBER	메타데이터	SQL 로그인이 지정된 서버 수준 역할의 멤버인지 여부를 나타냅니다.
sys.server_role_members	메타데이터	각 서버 수준 역할의 각 구성원에 대해 하나의 행을 반환합니다.
sys.sql_logins	메타데이터	각 SQL 로그인에 대해 하나의 행을 반환합니다.
ALTER SERVER ROLE	명령	서버 역할의 멤버 자격을 변경합니다.

예제

이 섹션의 예에서는 Azure SQL Database에서 서버 수준 역할을 사용하는 방법을 보여 줍니다.

다음 예제에서는 Jiao SQL 로그인을 ##MS_ServerStateReader## 서버 수준 역할에 추가합니다. 이 문은 가상 master 데이터베이스에서 실행해야 합니다.

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B. 서버 수준 역할의 구성원인 모든 보안 주체 주체(SQL 인증) 나열

다음 문은 sys.server_role_members 및 sys.sql_logins 카탈로그 뷰를 사용하여 고정된 서버 수준 역할의 모든 멤버를 반환합니다. 이 문은 가상 master 데이터베이스에서 실행해야 합니다.

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

파트 1: 역할 멤버 자격 및 사용자 계정 준비

가상 master 데이터베이스에서 이 명령을 실행하세요.

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

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

결과 집합은 다음과 같습니다.

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

사용자 데이터베이스에서 이 명령을 실행합니다.

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

2부: 역할 멤버 자격 테스트

로그인 Jiao로 로그인하고 예에서 사용된 사용자 데이터베이스에 연결합니다.

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D. Microsoft Entra 로그인에 대한 서버 수준 역할 확인

SQL Database 서버 수준 역할의 일부인 모든 Microsoft Entra 로그인을 보려면 가상 master 데이터베이스에서 이 명령을 실행합니다. Microsoft Entra 서버 로그인에 대한 자세한 내용은 Microsoft Entra 서버 보안 주체를 참조하세요.

SELECT member.principal_id AS MemberPrincipalID,
    member.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.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. 특정 로그인에 대한 가상 `master` 데이터베이스 역할 확인

가상 master 데이터베이스에서 이 명령을 실행하여 bob이 가진 역할을 확인하거나 보안 주체와 일치하도록 값을 변경하세요.

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

서버 수준 역할의 제한 사항

역할 할당이 적용되는 데 최대 5분이 소요될 수 있습니다. 또한 기존 세션의 경우 서버 역할 할당에 대한 변경 내용은 연결을 닫았다가 다시 열 때까지 적용되지 않습니다. 이는 master 데이터베이스와 동일한 논리 서버의 다른 데이터베이스 간 분산 아키텍처 때문입니다.
- 부분적 해결 방법: 대기 기간을 줄이고 서버 역할 할당이 데이터베이스에서 최신 상태인지 확인하기 위해 서버 관리자 또는 Microsoft Entra 관리자는 로그인이 액세스할 수 있는 사용자 데이터베이스에서 DBCC FLUSHAUTHCACHE를 실행할 수 있습니다. 현재 로그온한 사용자는 DBCC FLUSHAUTHCACHE를 실행한 후 다시 연결해야 멤버 자격 변경 내용이 적용됩니다.
IS_SRVROLEMEMBER()는 master 데이터베이스에서 지원되지 않습니다.

권한 관리를 위한 Azure SQL Database 서버 역할

개요

고정 서버 수준 역할

고정 서버 역할에 대한 사용 권한

사용 권한

서버 수준 역할 작업

예제

B. 서버 수준 역할의 구성원인 모든 보안 주체 주체(SQL 인증) 나열

파트 1: 역할 멤버 자격 및 사용자 계정 준비

2부: 역할 멤버 자격 테스트

D. Microsoft Entra 로그인에 대한 서버 수준 역할 확인

E. 특정 로그인에 대한 가상 `master` 데이터베이스 역할 확인

서버 수준 역할의 제한 사항

피드백

피드백

추가 리소스

권한 관리를 위한 Azure SQL Database 서버 역할

개요

고정 서버 수준 역할

고정 서버 역할에 대한 사용 권한

사용 권한

서버 수준 역할 작업

예제

A. 서버 수준 역할에 SQL 로그인 추가

B. 서버 수준 역할의 구성원인 모든 보안 주체 주체(SQL 인증) 나열

C. 완료 예제: 서버 수준 역할에 로그인 추가, 역할 멤버십 및 권한에 대한 메타데이터 재개, 테스트 쿼리 실행

파트 1: 역할 멤버 자격 및 사용자 계정 준비

2부: 역할 멤버 자격 테스트

D. Microsoft Entra 로그인에 대한 서버 수준 역할 확인

E. 특정 로그인에 대한 가상 master 데이터베이스 역할 확인

서버 수준 역할의 제한 사항

관련 콘텐츠

피드백

피드백

추가 리소스

E. 특정 로그인에 대한 가상 `master` 데이터베이스 역할 확인