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

적용 대상: Microsoft Fabric의 SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)SQL 데이터베이스

이 문서에서는 몇 가지 기본 보안 개념을 검토한 다음 일반적인 사용 권한 구현에 대해 설명합니다. 데이터베이스 엔진의 권한은 로그인 및 서버 역할을 통해 서버 수준에서 , 데이터베이스 사용자 및 데이터베이스 역할을 통해 데이터베이스 수준에서 관리됩니다.

Microsoft Fabric의 SQL Database 및 SQL 데이터베이스는 각 데이터베이스 내에서 동일한 옵션을 제공하지만 서버 수준 사용 권한은 사용할 수 없습니다.

• SQL Database에서 자습서: Azure SQL Database에서 데이터베이스 보안을 참조하세요. Microsoft Entra ID 인증을 사용하는 것이 좋습니다. 자세한 내용은 자습서: Microsoft Entra 애플리케이션을 사용하여 Microsoft Entra 사용자 만들기를 참조 하세요.

• Microsoft Fabric의 SQL 데이터베이스에서 데이터베이스 사용자에 대해 지원되는 유일한 인증 방법은 Microsoft Entra ID입니다. 서버 수준 역할 및 사용 권한은 사용할 수 없습니다. 자세한 내용은 Microsoft Fabric의 SQL 데이터베이스 권한 부여를 참조 하세요.

보안 원칙

보안 주체는 SQL Server에서 사용하는 ID로, 작업을 수행할 수 있는 권한을 할당할 수 있습니다. 보안 주체는 일반적으로 사람 또는 사용자 그룹이지만 사람인 것처럼 가장하는 다른 엔터티일 수 있습니다. 이 문서에 표시된 Transact-SQL 예제를 사용하거나 SQL Server Management Studio를 사용하여 보안 주체를 만들고 관리할 수 있습니다.

로그인

로그인 은 SQL Server 데이터베이스 엔진에 로그인하기 위한 개별 사용자 계정입니다. SQL Server 및 SQL Database는 Windows 인증에 따른 로그인과 SQL Server 인증을 기반으로 하는 로그인을 지원합니다. 두 가지 유형의 로그인에 대한 자세한 내용은 인증 모드 선택을 참조하세요.

고정 서버 역할

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

사용자 정의 서버 역할

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

데이터베이스 사용자

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

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

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

고정 데이터베이스 역할

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

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

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

기타 보안 주체

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

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

일반적인 시나리오

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

Windows Active Directory 또는 Microsoft Entra ID에서

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

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

1. Windows 그룹에 대한 로그인을 만듭니다. (SQL Server 인증을 사용하는 경우 Active Directory 단계를 건너뛰고 여기에 SQL Server 인증 로그인을 만듭니다.)

2. 사용자 데이터베이스에서 Windows 그룹을 나타내는 로그인에 대한 데이터베이스 사용자를 만듭니다.

3. 사용자 데이터베이스에서 각각 유사한 함수를 나타내는 하나 이상의 사용자 정의 데이터베이스 역할을 만듭니다. 예를 들어 재무 분석가 역할과 영업 분석가 역할이 있을 수 있습니다.

4. 하나 이상의 사용자 정의 데이터베이스 역할에 데이터베이스 사용자를 추가합니다.

5. 사용자 정의 데이터베이스 역할에 권한을 부여합니다.

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

1. 사용자 데이터베이스에서 Windows 그룹에 대한 포함된 데이터베이스 사용자를 만듭니다. (SQL Server 인증을 사용하는 경우 Active Directory 단계를 건너뛰고 여기에 포함된 데이터베이스 사용자 SQL Server 인증을 만듭니다.)

2. 사용자 데이터베이스에서 각각 유사한 함수를 나타내는 하나 이상의 사용자 정의 데이터베이스 역할을 만듭니다. 예를 들어 재무 분석가 역할과 영업 분석가 역할이 있을 수 있습니다.

3. 하나 이상의 사용자 정의 데이터베이스 역할에 데이터베이스 사용자를 추가합니다.

4. 사용자 정의 데이터베이스 역할에 권한을 부여합니다.

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

권한 할당

대부분의 사용 권한 문은 다음과 같은 형식을 갖습니다.

<authorization> <permission> ON <securable>::<name> TO <principal>;

• <authorization>은 GRANT, REVOKE, 또는 DENY이어야 합니다.

• <permission>는 허용하거나 금지할 작업을 설정합니다. 정확한 사용 권한 수는 SQL Server와 Azure SQL Database 간에 다릅니다. 사용 권한에 대한 자세한 내용은 사용 권한(데이터베이스 엔진)을 참조하고 이 문서의 뒷부분에 있는 차트를 참조하세요.

• ON <securable>::<name>은 보안 개체(서버, 서버 개체, 데이터베이스 또는 데이터베이스 개체)의 형식과 해당 이름입니다. 일부 권한은 명확하거나 적절하지 않기 때문에 <securable>::<name>이(가) 필요하지 않습니다. 예를 들어 CREATE TABLE 권한에는 <securable>::<name> 절이 필요하지 않습니다(GRANT CREATE TABLE TO Mary;은 Mary가 테이블을 만들 수 있도록 허용).

• <principal>는 사용 권한을 받거나 손실하는 보안 주체(로그인, 사용자 또는 역할)입니다. 가능한 경우 역할에 권한을 부여합니다.

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

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

다음 예제 문은 명명된 역할 ProductionTeam에 대해 스키마 Production 및 이 스키마에 포함된 모든 테이블 또는 뷰에 대한 UPDATE 권한을 부여합니다. 이는 개별 개체 수준에서 권한을 할당하는 것보다 더 효과적이고 확장 가능한 접근 방식입니다.

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

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

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

• 관리자가 REVOKE SELECT ON OBJECT::OrderStatus TO Sales;을 올바르게 실행하면 Jae는 개별 GRANT 문을 통해 OrderStatus 테이블에 대한 SELECT 액세스를 유지합니다.

• 관리자가 DENY SELECT ON OBJECT::OrderStatus TO Sales;을(를) 잘못 실행하면, Sales 역할의 멤버인 Jae는 DENY이(가) Sales보다 우선하여 적용되어 그 개인의 GRANT을(를) 재정의하므로 SELECT 권한을 거부당합니다.

사용 권한 계층 구조

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

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

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

• CONTROL테이블 Region에 대한 사용 권한에는 ALTER, SELECT, INSERT, UPDATE, DELETE 및 기타 권한을 포함하여 테이블 Region에 대한 다른 모든 사용 권한이 포함됩니다.

• SELECT 스키마에서 소유한 Region 테이블에 대한 SELECT 권한이 Region 테이블에 포함됩니다.

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

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 형식으로 다운로드할 수 있습니다.

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

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

고정 서버 역할 및 고정 데이터베이스 역할의 사용 권한은 세분화된 사용 권한과 유사하지만 정확히 동일하지는 않습니다. 예를 들어 sysadmin 고정 서버 역할의 멤버는 사용 권한이 있는 로그인과 마찬가지로 SQL Server 인스턴스에 대한 모든 권한을 갖습니다 CONTROL SERVER .

그러나 권한을 부여 CONTROL SERVER 해도 로그인이 sysadmin 고정 서버 역할의 멤버가 되지 않으며 sysadmin 고정 서버 역할에 로그인을 추가해도 로그인 CONTROL SERVER 에 사용 권한이 명시적으로 부여되지는 않습니다. 경우에 따라 저장 프로시저는 고정된 역할을 확인하고 세분화된 사용 권한을 확인하지 않음으로써 사용 권한을 확인합니다.

예를 들어 데이터베이스를 분리하려면 db_owner 고정 데이터베이스 역할의 멤버 자격이 필요합니다. 동등한 CONTROL DATABASE 권한으로는 충분하지 않습니다. 이 두 시스템은 병렬로 작동하지만 거의 상호 작용하지 않습니다. 가능한 경우 고정 역할 대신 최신 세분화된 권한 시스템을 사용하는 것이 좋습니다.

권한 모니터링

다음 뷰는 보안 정보를 반환합니다. 모든 보안 관련 보기는 보안 카탈로그 뷰(Transact-SQL)를 참조하세요.

¹ 이 보기는 SQL Database에서 사용할 수 없습니다.

예제

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

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

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

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
     INNER JOIN sys.database_principals AS dp
         ON perms.grantee_principal_id = dp.principal_id
     INNER 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 OUTER 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
     INNER JOIN sys.database_principals AS dp
         ON dRo.member_principal_id = dp.principal_id
     INNER JOIN sys.database_principals AS dRole
         ON dRo.role_principal_id = dRole.principal_id;

관련 콘텐츠

• SQL Server 데이터베이스 엔진 및 Azure SQL Database에 대한 보안
• 보안 함수(Transact-SQL)
• 보안 관련 동적 관리 뷰 및 함수(Transact-SQL)
• 보안 카탈로그 뷰(Transact-SQL)
• sys.fn_builtin_permissions(Transact-SQL)
• 유효한 데이터베이스 엔진 권한 확인
• 자습서: 데이터베이스 엔진 시작
• 1단원: 데이터베이스 개체 만들기 및 쿼리
• 자습서: SQL Server Management Studio
• 자습서: Transact-SQL 문 작성