데이터베이스 엔진 권한 시작
적용 대상: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)
데이터터베이스의 권한은 로그인 및 서버 역할을 통해 서버 수준에서 관리되고 데이터베이스 사용자 및 데이터베이스 역할을 통해 데이터베이스 수준에서 관리됩니다. SQL 데이터베이스의 모델은 각 데이터베이스 내에서 동일한 시스템을 노출하지만 서버 수준 권한은 사용할 수 없습니다. 이 문서에서는 몇 가지 기본 보안 개념을 검토한 다음 권한의 일반적인 구현에 대해 설명합니다.
참고 항목
Microsoft Entra ID는 이전의 Azure AD(Azure Active Directory)입니다.
보안 주체
보안 주체는 SQL Server를 사용하는 ID의 공식 이름이며 작업을 수행할 수 있는 권한을 할당할 수 있습니다. 일반적으로 사람 또는 사람 그룹이지만 사람인 척하는 다른 개체일 수도 있습니다. 나열된 Transact-SQL 을 사용하거나 SQL Server Management Studio를 사용하여 보안 주체를 만들고 관리할 수 있습니다.
로그인
로그인은 SQL Server Database Engine에 로그온하기 위한 개별 사용자 계정입니다. 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는 고정 서버 역할을 지원하지 않지만 master 데이터베이스에 서버 역할처럼 작동하는 두 가지 역할(dbmanager 및 loginmanager)이 있습니다.
사용자 정의 서버 역할
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 문은 Production 스키마에 포함된 Parts 테이블 또는 뷰에 대한 UPDATE 사용 권한을 PartsTeam로 명명된 역할에 부여합니다.
GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;
다음 예제 grant 문은 Production 스키마에 포함된 ProductionTeam 테이블 또는 뷰에 대한 UPDATE 권한을 역할에 부여합니다. 이는 개별 개체 수준보다 더 효율적이고 합리적으로 권한을 할당하는 방법입니다.
GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;
GRANT 문을 사용하여 보안 주체(로그인, 사용자 및 역할)에게 사용 권한이 부여됩니다. 사용 권한은 DENY 명령을 사용하여 명시적으로 거부됩니다. 이전에 부여되거나 거부된 사용 권한은 REVOKE 문을 사용하여 제거됩니다. 사용 권한은 누적되며 사용자는 사용자, 로그인 및 모든 그룹 멤버 자격에 부여된 모든 권한을 받습니다. 그러나 모든 권한 거부는 모든 권한을 재정의합니다.
팁
일반적인 실수는 REVOKE 대신 DENY를 사용하여 GRANT를 제거하려고 시도하는 것입니다. 이렇게 하면 사용자가 여러 원본에서 권한을 받을 때 문제가 발생할 수 있습니다. 이는 매우 일반적입니다. 다음 예제는 그 원리를 설명합니다.
Sales 그룹은 SELECT 문을 통해 OrderStatus 테이블에 대한 GRANT SELECT ON OBJECT::OrderStatus TO Sales;권한을 받습니다. 사용자 Jae는 Sales 역할의 멤버입니다. 또한 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는 Sales에 대한DENY가 개인GRANT를 재정의하므로SELECT권한이 거부됩니다.
참고 항목
Management Studio를 사용하여 권한을 구성할 수 있습니다. 개체 탐색기에서 보안 파일을 찾아 마우스 오른쪽 버튼으로 클릭한 다음 속성을 선택합니다. 권한 페이지를 선택합니다. 사용 권한 페이지 사용에 대한 도움말은 사용 권한 또는 보안 개체 페이지를 참조하세요.
사용 권한 계층 구조
사용 권한에는 부모/자식 계층이 있습니다. 즉, 데이터베이스에 대한 SELECT 권한을 부여하는 경우 이 권한에는 데이터베이스의 모든 자식 스키마에 대한 SELECT 권한이 포함됩니다. 스키마에 SELECT 권한을 부여하면 스키마의 모든 (하위) 테이블 및 뷰에 대한 SELECT 권한이 포함됩니다. 권한은 전이적입니다. 즉, 데이터베이스에 대한 SELECT 권한을 부여하는 경우 이 권한에는 모든 자식 스키마와 모든 손자 테이블 및 뷰에 대한 SELECT 권한이 포함됩니다.
사용 권한에는 포함 권한도 있습니다. 개체에 대한 CONTROL 권한은 일반적으로 개체에 대한 다른 모든 권한을 제공합니다.
부모/자식 계층 구조와 포함 계층 구조가 모두 동일한 권한에 따라 작동할 수 있으므로 권한 시스템이 복잡해질 수 있습니다. 예를 들어 테이블(지역),스키마(고객), 데이터베이스(SalesDB)를 살펴보겠습니다.
CONTROL권한에는ALTER,SELECT,INSERT,UPDATE,DELETE및 기타 권한을 포함하여 Region 테이블에 대한 다른 모든 권한이 포함됩니다.Region 테이블을 소유하는 Customers 스키마의
SELECT에는 Region 테이블에 대한SELECT권한이 포함되어 있습니다.
따라서 Region 테이블에 대한 SELECT 사용 권한은 다음 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가 전체 스키마에 대한 권한이 필요한 경우 테이블 또는 뷰 수준에서 SELECT를 여러 번 부여하는 대신 스키마 수준에서 SELECT를 한 번 부여합니다. 데이터베이스 디자인은 이 전략의 성공에 큰 영향을 줄 수 있습니다. 이 전략은 동일한 권한이 필요한 개체가 단일 스키마에 포함되도록 데이터베이스가 디자인된 경우에 가장 효과적입니다.
팁
데이터베이스 및 해당 개체를 디자인할 때 처음부터 액세스 유형의 버킷에 따라 개체, 즉 테이블뿐만 아니라 뷰, 함수 및 저장 프로시저를 스키마의 뷰, 함수 및 저장 프로시저에 따라 개체에 액세스할 대상 또는 애플리케이션을 계획합니다.
사용 권한 다이어그램
다음 이미지는 사용 권한과 서로의 관계를 보여 줍니다. 일부 상위 수준 권한(예: CONTROL SERVER)은 여러 번 나열됩니다. 이 문서에서는 포스터가 너무 작아 읽기 어렵습니다. 전체 크기의 데이터베이스 엔진 사용 권한 포스터를 PDF 형식으로 다운로드할 수 있습니다.
Database Engine 보안 주체와 서버 및 데이터베이스 개체 간의 관계를 보여 주는 그래픽은 사용 권한 계층 구조(데이터베이스 엔진)를 참조하세요.
사용 권한과 고정 서버 및 고정 데이터베이스 역할 비교
고정 서버 역할 및 고정 데이터베이스 역할의 사용 권한은 비슷하지만 세분화된 권한과 정확히 동일하지는 않습니다. 예를 들어 sysadmin 고정 서버 역할의 멤버는 CONTROL SERVER 권한으로 로그인할 경우 SQL 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;
참고 항목
- SQL Server 데이터베이스 엔진 및 Azure SQL Database용 Security Center
- 보안 함수(Transact-SQL)
- 보안 관련 동적 관리 뷰 및 함수(Transact-SQL)
- 보안 카탈로그 뷰(Transact-SQL)
- sys.fn_builtin_permissions(Transact-SQL)
- 유효 데이터베이스 엔진 권한 확인