데이터베이스 엔진 권한 시작

아티클
08/21/2023

적용 대상:SQL Server Azure SQL Database Azure SQL Managed InstanceAzure Synapse Analytics Analytics Platform System(PDW)

데이터베이스 엔진 권한은 로그인 및 서버 역할을 통해 서버 수준에서, 데이터베이스 사용자 및 데이터베이스 역할을 통해 데이터베이스 수준에서 관리됩니다. SQL Database 모델은 각 데이터베이스 내에서 동일한 시스템을 노출하지만 서버 수준 사용 권한은 사용할 수 없습니다. 이 문서에서는 몇 가지 기본 보안 개념을 검토한 다음 권한의 일반적인 구현에 대해 설명합니다.

참고 항목

Microsoft Entra ID 는 이전에 Azure AD(Azure Active Directory)라고 했습니다.

보안 주체

보안 주체는 SQL Server를 사용하는 ID의 공식 이름이며 작업을 수행할 수 있는 권한을 할당할 수 있습니다. 그들은 일반적으로 사람 또는 사람들의 그룹, 하지만 사람 척 다른 엔터티 수 있습니다. 나열된 Transact-SQL을 사용하거나 SQL Server Management Studio를 사용하여 보안 주체를 만들고 관리할 수 있습니다.

로그인은 SQL Server 데이터베이스 엔진 로그온하기 위한 개별 사용자 계정입니다. SQL Server 및 SQL Database는 SQL Server 인증을 기반으로 Windows 인증 및 로그인을 기반으로 로그인을 지원합니다. 두 유형의 로그인에 대한 자세한 내용은 Choose an Authentication Mode를 참조하세요.

고정 서버 역할

SQL Server에서 고정 서버 역할은 서버 수준 권한의 편리한 그룹을 제공하는 미리 구성된 역할 집합입니다. ALTER SERVER ROLE ... ADD MEMBER 문을 사용하여 역할에 로그인을 추가할 수 있습니다. 자세한 내용은 ALTER SERVER ROLE(Transact-SQL)을 참조 하세요. SQL Database는 고정 서버 역할을 지원하지 않지만 서버 역할처럼 작동하는 데이터베이스(dbmanager및loginmanager)에 master 두 개의 역할이 있습니다.

사용자 정의 서버 역할

SQL Server에서 사용자 고유의 서버 역할을 만들고 서버 수준 권한을 할당할 수 있습니다. 문을 사용하여 서버 역할에 로그인을 ALTER SERVER ROLE ... ADD MEMBER 추가할 수 있습니다. 자세한 내용은 ALTER SERVER ROLE(Transact-SQL)을 참조 하세요. SQL Database는 사용자 정의 서버 역할을 지원하지 않습니다.

데이터베이스 사용자

로그인은 데이터베이스에서 데이터베이스 사용자를 만들고 해당 데이터베이스 사용자가 로그인하도록 매핑하여 데이터베이스에 대한 액세스 권한을 부여합니다. 일반적으로 데이터베이스 사용자 이름은 로그인 이름과 동일하지만 동일할 필요는 없습니다. 각 데이터베이스 사용자는 단일 로그인에 매핑됩니다. 하나의 로그인을 데이터베이스의 한 사용자에만 매핑할 수 있지만 여러 데이터베이스에서 데이터베이스 사용자로 매핑할 수 있습니다.

해당 로그인이 없는 데이터베이스 사용자를 만들 수도 있습니다. 이러한 사용자를 포함된 데이터베이스 사용자라고 합니다. Microsoft는 데이터베이스를 다른 서버로 쉽게 이동할 수 있으므로 포함된 데이터베이스 사용자의 사용을 권장합니다. 로그인과 마찬가지로 포함된 데이터베이스 사용자는 Windows 인증 또는 SQL Server 인증을 사용할 수 있습니다. 자세한 내용은 포함된 데이터베이스 사용자 - 데이터베이스를 이식 가능하게 만들기를 참조하세요.

인증 방법 및 나타내는 사람이 약간 다른 12가지 유형의 사용자가 있습니다. 사용자 목록을 보려면 CREATE USER(Transact-SQL)를 참조하세요.

고정 데이터베이스 역할

고정 데이터베이스 역할은 데이터베이스 수준 권한의 편리한 그룹을 제공하는 미리 구성된 역할 집합입니다. 데이터베이스 사용자 및 사용자 정의 데이터베이스 역할은 문을 사용하여 ALTER ROLE ... ADD MEMBER 고정 데이터베이스 역할에 추가할 수 있습니다. 자세한 내용은 ALTER ROLE(Transact-SQL)을 참조 하세요.

사용자 정의 데이터베이스 역할

CREATE ROLE 권한이 있는 사용자는 일반적인 권한이 있는 사용자 그룹을 나타내는 새 사용자 정의 데이터베이스 역할을 만들 수 있습니다. 일반적으로 권한 관리 및 모니터링을 간소화하여 전체 역할에 대한 사용 권한이 부여되거나 거부됩니다. 데이터베이스 사용자는 문을 사용하여 ALTER ROLE ... ADD MEMBER 데이터베이스 역할에 추가할 수 있습니다. 자세한 내용은 ALTER ROLE(Transact-SQL)을 참조 하세요.

기타 보안 주체

여기에 설명되지 않은 추가 보안 주체에는 애플리케이션 역할, 인증서 또는 비대칭 키를 기반으로 하는 로그인 및 사용자가 포함됩니다.

Windows 사용자, Windows 그룹, 로그인 및 데이터베이스 사용자 간의 관계를 보여 주는 그래픽은 데이터베이스 사용자 만들기를 참조 하세요.

일반적인 시나리오

다음 예제에서는 사용 권한을 구성하는 일반적인 권장 방법을 나타냅니다.

Windows Active Directory 또는 Microsoft Entra ID

각 사용자에 대한 사용자를 만듭니다.
작업 단위 및 작업 기능을 나타내는 Windows 그룹을 만듭니다.
Windows 그룹에 Windows 사용자를 추가합니다.

연결하는 사용자가 여러 데이터베이스에 연결하는 경우

Windows 그룹에 대한 로그인을 만듭니다. (SQL Server 인증을 사용하는 경우 Active Directory 단계를 건너뛰고 여기에서 SQL Server 인증 로그인을 만듭니다.)
사용자 데이터베이스에서 Windows 그룹을 나타내는 로그인에 대한 데이터베이스 사용자를 만듭니다.
사용자 데이터베이스에서 각각 유사한 함수를 나타내는 하나 이상의 사용자 정의 데이터베이스 역할을 만듭니다. 예를 들어 재무 분석가 및 판매 분석가입니다.
하나 이상의 사용자 정의 데이터베이스 역할에 데이터베이스 사용자를 추가합니다.
사용자 정의 데이터베이스 역할에 권한을 부여합니다.

연결하는 사용자가 하나의 데이터베이스에만 연결하는 경우

사용자 데이터베이스에서 Windows 그룹에 대해 포함된 데이터베이스 사용자를 만듭니다. (SQL Server 인증을 사용하는 경우 Active Directory 단계를 건너뛰고 여기에 포함된 데이터베이스 사용자 SQL Server 인증을 만듭니다.
사용자 데이터베이스에서 각각 유사한 함수를 나타내는 하나 이상의 사용자 정의 데이터베이스 역할을 만듭니다. 예를 들어 재무 분석가 및 판매 분석가입니다.
하나 이상의 사용자 정의 데이터베이스 역할에 데이터베이스 사용자를 추가합니다.
사용자 정의 데이터베이스 역할에 권한을 부여합니다.

이 시점에서 일반적인 결과는 Windows 사용자가 Windows 그룹의 구성원이라는 것입니다. Windows 그룹에는 SQL Server 또는 SQL Database에 로그인이 있습니다. 로그인은 사용자 데이터베이스에서 사용자 ID에 매핑됩니다. 사용자가 데이터베이스 역할의 구성원입니다. 이제 역할에 권한을 추가해야 합니다.

권한 할당

대부분의 권한 문에는 다음과 같은 형식이 있습니다.

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

AUTHORIZATION 은 GRANT, REVOKE 또는 DENY여야 합니다.
PERMISSION 은 허용되거나 금지된 작업을 설정합니다. 정확한 사용 권한 수는 SQL Server와 SQL Database 간에 다릅니다. 사용 권한은 문서 사용 권한(데이터베이스 엔진)과 아래에 참조된 차트에 나열됩니다.
ON SECURABLE::NAME 는 보안 개체(서버, 서버 개체, 데이터베이스 또는 데이터베이스 개체) 및 해당 이름의 형식입니다. 일부 권한은 컨텍스트에서 모호하거나 부적절하기 때문에 필요하지 ON SECURABLE::NAME 않습니다. 예를 들어 권한에는 CREATE TABLE 절이 ON SECURABLE::NAME 필요하지 않습니다(GRANT CREATE TABLE TO Mary; Mary가 테이블을 만들 수 있도록 허용).
PRINCIPAL 는 사용 권한을 받거나 손실하는 보안 주체(로그인, 사용자 또는 역할)입니다. 가능한 경우 역할에 권한을 부여합니다.

다음 예제 grant 문은 스키마에 포함된 테이블 또는 뷰에 대한 Parts 사용 권한을 명명PartsTeam된 Production 역할에 부여 UPDATE 합니다.

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

다음 예제 grant 문은 스키마에 대한 Production 사용 권한을 부여하고 이 스키마에 포함된 테이블 또는 뷰의 확장에 따라 개별 개체 수준보다 사용 권한을 할당하는 보다 효과적이고 능동적인 접근 방식인 이름이 지정된 ProductionTeam역할에 대한 권한을 부여 UPDATE 합니다.

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

문을 사용하여 GRANT 보안 주체(로그인, 사용자 및 역할)에게 사용 권한이 부여됩니다. 사용 권한은 명령을 사용하여 DENY 명시적으로 거부됩니다. 이전에 부여되거나 거부된 사용 권한은 문을 사용하여 REVOKE 제거됩니다. 사용 권한은 누적되며 사용자는 사용자, 로그인 및 모든 그룹 멤버 자격에 부여된 모든 권한을 받습니다. 그러나 모든 권한 거부는 모든 권한을 재정의합니다.

팁

일반적인 실수는 대신를 사용하여 DENY 제거 GRANT 하려고 시도하는 것입니다REVOKE. 이렇게 하면 사용자가 여러 원본에서 권한을 받을 때 문제가 발생할 수 있습니다. 이는 매우 일반적입니다. 다음 예제에서는 보안 주체를 보여 줍니다.

Sales 그룹은 SELECT 문을 통해 OrderStatus 테이블에 대한 GRANT SELECT ON OBJECT::OrderStatus TO Sales;권한을 받습니다. 사용자 Jae는 영업 역할의 구성원입니다. 또한 Jae는 문을 GRANT SELECT ON OBJECT::OrderStatus TO Jae;통해 자신의 사용자 이름으로 OrderStatus 테이블에 대한 권한을 부여 SELECT 받았습니다. 관리자가 Sales 역할에 대한 제거 GRANT 를 원하는 것으로 가정합니다.

관리자가 올바르게 실행REVOKE SELECT ON OBJECT::OrderStatus TO Sales;되면 Jae는 개별 GRANT 문을 통해 OrderStatus 테이블에 대한 액세스를 유지 SELECT 합니다.
관리자가 잘못 실행 DENY SELECT ON OBJECT::OrderStatus TO Sales; 하면 Sales 역할의 멤버인 Jae가 해당 개인GRANT을 재정의하므로 사용 권한이 DENY 거부 SELECT 됩니다.

참고 항목

Management Studio를 사용하여 권한을 구성할 수 있습니다. 개체 탐색기 보안 개체를 찾아서 보안 개체를 마우스 오른쪽 단추로 클릭한 다음 속성을 선택합니다. 사용 권한 페이지를 선택합니다. 사용 권한 페이지 사용에 대한 도움말은 사용 권한 또는 보안 개체 페이지를 참조 하세요.

권한 계층 구조

사용 권한에는 부모/자식 계층이 있습니다. 즉, 데이터베이스에 대한 SELECT 권한을 부여하는 경우 이 권한에는 데이터베이스의 모든 자식 스키마에 대한 SELECT 권한이 포함됩니다. 스키마에 대한 사용 권한을 부여하는 SELECT 경우 스키마의 모든(자식) 테이블 및 뷰에 대한 권한이 포함됩니다 SELECT . 권한은 전이적입니다. 즉, 데이터베이스에 대한 SELECT 권한을 부여하는 경우 이 권한에는 모든 자식 스키마와 모든 손자 테이블 및 뷰에 대한 SELECT 권한이 포함됩니다.

사용 권한에는 포함 권한도 있습니다. 개체에 대한 권한은 CONTROL 일반적으로 개체에 대한 다른 모든 권한을 제공합니다.

부모/자식 계층 구조와 포함 계층 구조가 모두 동일한 권한에 따라 작동할 수 있으므로 권한 시스템이 복잡해질 수 있습니다. 예를 들어 테이블(지역),스키마(고객), 데이터베이스(SalesDB)를 살펴보겠습니다.

CONTROL테이블 지역에 대한 사용 권한에는 테이블 지역에 대한 다른 모든 권한(예: , SELECT, INSERTUPDATEDELETE및 기타 사용 권한 포함ALTER)이 포함됩니다.
SELECT 지역 테이블을 소유하는 Customers 스키마에 지역 테이블에 대한 사용 권한이 포함됩니다 SELECT .

따라서 SELECT Region 테이블에 대한 사용 권한은 다음 6개 문 중 어느 것을 통해 달성할 수 있습니다.

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

최소 권한 부여

위에 나열된 첫 번째 권한(GRANT SELECT ON OBJECT::Region TO Jae;)은 가장 세분화된 권한입니다. 즉, 해당 명령문은 권한을 부여 SELECT할 수 있는 최소 권한입니다. 하위 개체에 대한 권한은 함께 제공되지 않습니다. 항상 가능한 최소 권한을 부여하는 것이 좋은 원칙이지만(최소 권한의 원칙에 대해 자세히 읽을 수 있음) 동시에 부여 시스템을 단순화하기 위해 더 높은 수준에서 부여하려고 합니다. 따라서 Jae가 전체 스키마에 대한 권한이 필요한 경우 테이블 또는 뷰 수준에서 여러 번 부여하는 대신 스키마 수준에서 한 번 부여 SELECTSELECT 합니다. 데이터베이스 디자인은 이 전략의 성공에 큰 영향을 줄 수 있습니다. 이 전략은 동일한 권한이 필요한 개체가 단일 스키마에 포함되도록 데이터베이스가 디자인된 경우에 가장 효과적입니다.

팁

데이터베이스 및 해당 개체를 디자인할 때 처음부터 액세스 유형의 버킷에 따라 개체, 즉 테이블뿐만 아니라 뷰, 함수 및 저장 프로시저를 스키마의 뷰, 함수 및 저장 프로시저에 따라 개체에 액세스할 대상 또는 애플리케이션을 계획합니다.

사용 권한 다이어그램

다음 이미지는 사용 권한과 서로의 관계를 보여 줍니다. 일부 상위 수준 권한(예: CONTROL SERVER)은 여러 번 나열됩니다. 이 문서에서는 포스터가 너무 작아 읽기 어렵습니다. 전체 크기 데이터베이스 엔진 사용 권한 포스터를 PDF 형식으로 다운로드할 수 있습니다.

A screenshot from the Database Engine permissions PDF.

데이터베이스 엔진 보안 주체와 서버 및 데이터베이스 개체 간의 관계를 보여 주는 그래픽은 사용 권한 계층(데이터베이스 엔진)을 참조하세요.

사용 권한 및 고정 서버 및 고정 데이터베이스 역할 비교

고정 서버 역할 및 고정 데이터베이스 역할의 사용 권한은 비슷하지만 세분화된 권한과 정확히 동일하지는 않습니다. 예를 들어 고정 서버 역할의 sysadmin 멤버는 사용 권한이 있는 로그인과 마찬가지로 SQL Server 인스턴스에 대한 모든 권한을 갖습니다 CONTROL SERVER . 그러나 권한을 부여 CONTROL SERVER 해도 로그인이 sysadmin 고정 서버 역할의 멤버가 되지 않으며 고정 서버 역할에 로그인을 sysadmin 추가해도 로그인 CONTROL SERVER 에 사용 권한이 명시적으로 부여되지는 않습니다. 경우에 따라 저장 프로시저는 세분화된 권한을 확인하지 않고 고정 역할을 확인하여 권한을 확인합니다. 예를 들어 데이터베이스를 분리하려면 고정 데이터베이스 역할의 db_owner 멤버 자격이 필요합니다. 동등한 CONTROL DATABASE 권한으로는 충분하지 않습니다. 이 두 시스템은 병렬로 작동하지만 거의 상호 작용하지 않습니다. 가능한 경우 고정 역할 대신 최신 세분화된 권한 시스템을 사용하는 것이 좋습니다.

사용 권한 모니터링

다음 보기는 보안 정보를 반환합니다.

서버의 로그인 및 사용자 정의 서버 역할은 sys.server_principals 뷰를 사용하여 검사할 수 있습니다. 이 보기는 SQL Database에서 사용할 수 없습니다.
뷰를 사용하여 sys.database_principals 데이터베이스의 사용자 및 사용자 정의 역할을 검사할 수 있습니다.
뷰를 사용하여 sys.server_permissions 로그인 및 사용자 정의 고정 서버 역할에 부여된 권한을 검사할 수 있습니다. 이 보기는 SQL Database에서 사용할 수 없습니다.
사용자에게 부여된 권한 및 사용자 정의 고정 데이터베이스 역할은 보기를 사용하여 sys.database_permissions 검사할 수 있습니다.
보기를 사용하여 sys.database_role_members 데이터베이스 역할 멤버 자격을 검사할 수 있습니다.
서버 역할 멤버 자격은 sys.server_role_members 뷰를 사용하여 검사할 수 있습니다. 이 보기는 SQL Database에서 사용할 수 없습니다.
추가 보안 관련 보기는 보안 카탈로그 뷰(Transact-SQL)를 참조하세요.

예제

다음 문은 권한에 대한 유용한 정보를 반환합니다.

A. 각 사용자에 대한 데이터베이스 사용 권한 목록

데이터베이스에서 부여되거나 거부된 명시적 사용 권한(SQL Server 및 SQL Database)을 반환하려면 데이터베이스에서 다음 문을 실행합니다.

SELECT
    perms.state_desc AS State,
    permission_name AS [Permission],
    obj.name AS [on Object],
    dp.name AS [to User Name]
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
JOIN sys.objects AS obj
    ON perms.major_id = obj.object_id;

B. 서버-역할 멤버 나열

서버 역할의 멤버를 반환하려면(SQL Server에만 해당) 다음 문을 실행합니다.

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
LEFT JOIN sys.server_principals AS members
    ON server_role_members.member_principal_id = members.principal_id;

C. 데이터베이스 수준 역할의 멤버인 모든 데이터베이스 보안 주체 나열

데이터베이스 역할의 멤버(SQL Server 및 SQL Database)를 반환하려면 데이터베이스에서 다음 문을 실행합니다.

SELECT dRole.name AS [Database Role Name], dp.name AS [Members]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;

데이터베이스 엔진 권한 시작

보안 주체

로그인

고정 서버 역할

사용자 정의 서버 역할

데이터베이스 사용자

고정 데이터베이스 역할

사용자 정의 데이터베이스 역할

기타 보안 주체

일반적인 시나리오

Windows Active Directory 또는 Microsoft Entra ID

연결하는 사용자가 여러 데이터베이스에 연결하는 경우

연결하는 사용자가 하나의 데이터베이스에만 연결하는 경우

권한 할당

권한 계층 구조

최소 권한 부여

사용 권한 다이어그램

사용 권한 및 고정 서버 및 고정 데이터베이스 역할 비교

사용 권한 모니터링

예제

A. 각 사용자에 대한 데이터베이스 사용 권한 목록

B. 서버-역할 멤버 나열

C. 데이터베이스 수준 역할의 멤버인 모든 데이터베이스 보안 주체 나열

참고 항목

다음 단계

피드백

피드백

추가 리소스