SQL Server의 Public Role

데이터베이스 역할은 Windows 그룹과 비슷합니다. 각 사용자에게 별도로 액세스를 취소하거나 부여하는 대신, 관리자는 역할에서 권한을 부여하거나 취소하고 역할 멤버십을 변경하여 액세스를 관리합니다. 역할을 사용하면 데이터베이스 사용자에게 권한을 정확하게 부여하고 취소하기가 더 쉬워집니다. 또한 여러 사용자가 SQL 데이터베이스 역할의 멤버가 될 수 있으므로, 한 번에 전체 사용자 그룹에 대한 권한을 쉽게 관리할 수 있습니다.

이번 글에서는 Microsoft SQL Server의 public 역할과 관련된 몇 가지 모범 사례를 설명합니다. (여기서는 오라클 세계와 달리 SQL Server에서는 public 역할이 소문자로 표기되는 방식이므로, 의도적으로 소문자를 사용합니다.)

SQL Server의 역할 유형

Microsoft SQL Server는 미리 만들어진 여러 유형의 역할을 제공합니다:

• 고정 서버 역할 — 공용 역할을 제외하고는 수정하거나 삭제할 수 없기 때문에 “고정” 서버 역할이라고 합니다
• 고정 데이터베이스 역할
• 응용 프로그램 역할 — 애플리케이션이 자체 권한 집합으로 실행하는 데 사용할 수 있는 데이터베이스 보안 주체(기본값: 비활성화)
• 사용자 정의 역할 (SQL Server 2012부터) — 사용자 정의에 추가할 수 있는 것은 서버 수준 권한뿐입니다

Public 역할이 들어가는 위치

모든 데이터베이스 플랫폼에는 public이라는 미리 정의된 역할이 제공되지만, 이 역할의 구현 방식은 플랫폼에 따라 다릅니다. SQL Server에서는 public 역할이 고정 서버 역할의 일부이며, SQL Server public 역할 권한에 대해 권한을 부여하거나 거부하거나 취소(철회)할 수 있습니다.

SQL Server 로그인은 생성되면 public 역할이 로그인에 할당되며, 이를 취소할 수 없습니다. 서버 주체가 보안 개체에 대해 특정 권한을 부여받거나 거부받지 않은 경우, 로그인은 public 역할에 부여된 해당 개체의 권한을 자동으로 상속합니다.

Public 역할에 할당된 권한

SQL Server 보안을 유지하고 SQL Server security 또한 PCI DSS와 HIPAA를 포함한 여러 규정을 준수하려면, 각 사용자에게 할당된 모든 서버 및 데이터베이스 수준 역할을 알아야 합니다. Management Studio의 transact-SQL 쿼리를 사용하여 public 역할에 할당된 서버 수준 권한을 살펴봅시다:

      SELECT sp.state_desc as "Permission State", sp.permission_name as "Permission",
sl.name "Principal Name",sp.class_desc AS "Object Class", ep.name "End Point"
FROM sys.server_permissions AS sp
  JOIN sys.server_principals AS sl
    ON sp.grantee_principal_id = sl.principal_id
  LEFT JOIN sys.endpoints AS ep
    ON sp.major_id = ep.endpoint_id
WHERE sl.name = 'public';
      

위 표에서 보듯이 public 역할에는 서버 수준 권한이 단 다섯 가지로만 할당되어 있습니다. VIEW ANY DATABASE 권한은 사용자에게 어떤 데이터베이스 개체에도 접근 권한을 부여하지 않으며, SQL Server 인스턴스에 있는 모든 데이터베이스를 나열할 수 있도록 허용할 뿐입니다. 따라서 새 로그인은 만들되 다른 어떤 역할이나 권한도 할당하지 않으면, 사용자는 인스턴스에 로그인은 할 수 있지만 그 외에는 아무 작업도 수행할 수 없습니다.

SQL Server 로그인에 기본 데이터베이스가 할당된 경우의 권한

이제 해당 사용자에게 기본 데이터베이스를 할당하여 SQL 인증을 사용하는 SQL Server 로그인을 만들어 보겠습니다.

      USE [master]
GO
CREATE LOGIN [SQTest] WITH PASSWORD=N'nhggLboBn6SHolSWfipjzO/7GYw8M2RMbCt1LsCTK5M=', DEFAULT_DATABASE=[SBITS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
      

해당 사용자로 로그인한 다음 사용자의 기본 데이터베이스 SBITS에 대해 모든 데이터베이스 수준 권한을 나열해 보면, 그 사용자가 어떤 권한을 가지고 있는지 확인할 수 있습니다. 아래에 표시된 것처럼, 기본 데이터베이스가 SBITS이더라도 사용자 Stealth는 SBITS 데이터베이스에 대한 권한이 전혀 없습니다. 즉, 사용자 로그인에 기본 사용자 데이터베이스가 할당되었다고 해서 사용자가 데이터베이스 개체나 데이터에 접근해 볼 수 있다는 의미는 아닙니다.

이를 확인하려면 다음 스크립트를 사용할 수 있습니다:

      EXECUTE AS LOGIN= 'SQTest';
GO
USE SBITS
GO
SELECT dp.state_desc AS "Class Description", dp.permission_name AS "Permission Name",
SCHEMA_NAME(ao.schema_id) AS "Schema Name", ao.name AS "Object Name"
FROM sys.database_permissions dp
 LEFT JOIN sys.all_objects ao
  ON dp.major_id = ao.object_id
 JOIN sys.database_principals du
  ON dp.grantee_principal_id = du.principal_id
WHERE du.name = 'TestLoginPerms'
 AND ao.name IS NOT NULL
ORDER BY ao.name;
REVERT
      

마스터 데이터베이스에서 Public 역할이 상속받는 권한

사용자 로그인 Stealth는 기본적으로 public 역할의 일부이므로, public 역할이 master 데이터베이스에서 상속받는 권한을 살펴보겠습니다.

      USE master;
GO
 SELECT sp.state_desc AS "Permission State", sp.permission_name AS "Permission",
 SCHEMA_NAME(ao.schema_id) AS 'Schema', ao.name AS "Object Name"
 FROM sys.database_permissions sp
  LEFT JOIN sys.all_objects ao
     ON sp.major_id = ao.object_id
   JOIN sys.database_principals dp
     ON sp.grantee_principal_id = dp.principal_id
 WHERE dp.name = 'public'
 AND ao.name IS NOT NULL
 ORDER BY ao.name
      

SQL Server 2016에는 Public 역할에 부여된 master 데이터베이스의 권한이 총 2,089개 저장되어 있습니다. 부담스럽게 느껴질 수 있지만, 모두 SELECT 권한이며 사용자 Stealth가 master 데이터베이스에서 어떤 변경도 수행할 수 없도록 합니다. 그러나 조직의 보안 정책에 따라 일부 권한을 회수하는 것은 좋은 관행입니다. 다만 일부 권한은 특정 상황에서 사용자가 정상적인 작업을 수행하는 데 필요할 수 있으므로 회수할 때는 주의하십시오.

역할 검토 및 관리 간소화

한 번에 한 인스턴스씩 SQL Server의 public role과 관련된 문제를 파악하기 위해 복잡한 맞춤 스크립트를 사용하는 대신, Netwrix Access Analyzer를 사용하는 것을 고려해 보세요. 이 데이터 액세스 거버넌스 플랫폼은 SQL Server public role 및 SQL Server public database role을 포함해 모든 SQL Server 역할과 권한을 열거하고, 기본 제공만으로도 상세한 권한(엔터틀먼트) 리포트를 생성할 수 있습니다. 또한 기업 내 모든 SQL Server에 걸친 public role을 단일 화면에서 가시화해 주며, 버튼 한 번으로 문제를 신속하게 수정할 수 있도록 도와줍니다.

SQL Server의 public role에 대한 모범 사례

SQL Server의 public role과 관련해 다음과 같은 모범 사례를 권장합니다.

• 어떤 경우에도 기본 권한(default privileges) 외의 추가 권한을 public role에 부여하지 마세요. 필요한 경우 사용자 정의 롤(user-defined role)을 사용하세요.
• 공용 역할에 대한 서버 수준 권한을 수정하지 마세요. 그렇게 하면 사용자가 데이터베이스에 연결하지 못할 수 있습니다.
• SQL Server를 업그레이드할 때마다 public 역할에 Microsoft가 자주 변경을 적용하므로, 공용 권한을 검토하세요.

FAQ

공용 데이터베이스 역할(public database role)이란 무엇인가요?

public 데이터베이스 역할에 부여된 권한은 각 데이터베이스 사용자에게 상속됩니다.

SQL Server에서 public 역할에는 어떤 권한이 있나요?

모든 SQL Server 로그인은 public 서버 역할에 속합니다. 서버 보안 주체가 보안 가능 개체( securable object )에 대해 특정 권한을 허용 또는 거부받지 않은 경우, 사용자는 해당 개체에 대해 public 역할에 부여된 권한을 상속받습니다.