SQL Server: 사용자 정의 역할
SQL Server 2012 출시되면서 사용자 정의 서버 역할을 만들 수 있는 기능이 추가되어 SQL 관리 기능에도 중대한 발전이 이루어졌습니다.
Denny Cherry
데이터베이스 수준에서 권한 부여 프로세스를 수월하게 하기 위해 사용자 정의 데이터베이스 역할을 만드는 것은 오래전부터 가능했지만, 인스턴스 수준에는 고정 서버 역할이 9개밖에 없었습니다(SQL Server 2000 이전 버전을 사용하는 경우 8개, bulkadmin 역할은 SQL Server 2005에 도입됨). SQL Server 2012에서는 드디어 사용자 정의 서버 역할을 만들 수 있게 되었습니다.
오랫동안 이것이 불가능했기 때문에 SQL 관리가 골치 아픈 경우가 많았습니다. 인스턴스 수준에서 여러 사용자 또는 그룹에 권한을 부여하고 이러한 권한을 동기화하려면 어떻게 해야 할까요? 예를 들어 개발 서버 내의 차단 정보를 볼 수 있도록 다수의 사용자에게 View System State 권한을 부여하려고 한다고 가정해 보겠습니다. 이 경우 이 권한이 필요한 각 사용자 또는 도메인 그룹에 각각 권한을 부여해야 합니다.
모든 로그인이 도메인 로그인 기반인 경우, 사용 가능한 유일한 해결 방법은 이 권한이 필요한 모든 사용자를 위한 도메인 그룹을 만드는 것입니다. (사용자가 모든 서버에 대한 권한을 얻지 못하는 경우 각 서버당 도메인 그룹 하나가 필요할 수 있습니다) 사용자를 이 그룹에 넣고, 도메인 그룹으로 매핑된 로그인을 만든 다음, 해당 그룹에 해당 서버에 대한 View Server State 권한을 부여하면 됩니다. 그러나 이 작업은 아주 주의해서 해야 합니다. 그렇지 않으면 로그인 권한이 없던 사용자에게 권한이 부여될 수 있습니다. 즉, 원치 않는 사용자에게 권한이 부여될 수도 있습니다.
T/SQL을 사용한 역할
사용자 정의 서버 역할을 만드는 데는 T/SQL, SQL Server Management Studio UI 및 Windows PowerShell을 포함한 여러 가지 방법을 사용할 수 있습니다. T/SQL을 사용하여 사용자 정의 서버 역할을 만드는 경우 세 가지 다른 명령을 사용합니다. 첫째, Create Server Role 문으로 사용자 정의 서버 역할을 만들고, 다음, Alter Server Role 문으로 사용자를 해당 서버 역할에 추가한 다음, 마지막으로, Grant 문으로 해당 역할에 필요한 권한을 부여합니다.
이러한 세 문이 다음 코드에 나와 있는데, 이 코드는 “ViewServerState”라는 사용자 정의 서버 역할을 만드는 과정을 간략하게 보여 줍니다. 이 코드는 사용자를 “SomeFakeLogin”이라는 역할에 추가하고 이 역할에 View Server State 권한을 부여합니다. 사용자에게 이 권한을 부여하려면 Alter Server Role 줄을 사용하여 이러한 사용자를 고정 서버 역할로 추가하면 됩니다.
USE [master] GO CREATE SERVER ROLE [ViewServerState] AUTHORIZATION [sa] GO ALTER SERVER ROLE [ViewServerState] ADD MEMBER [SomeFakeLogin] GO GRANT VIEW SERVER STATE TO [ViewServerState] GO
사용자를 사용자 정의 서버 역할에서 제거할 때는 Alter Server Role을 사용합니다. 이번에는 Add Member 구문 대신 Drop Member 구문을 사용하면 됩니다.
ALTER SERVER ROLE [ViewServerState] DROP MEMBER [SomeFakeLogin] GO
하나 이상의 사용자 정의 서버 역할을 SQL Server의 여러 인스턴스에 대해 배포할 때는 몇 가지 다른 옵션이 있습니다. 사용자 정의 서버 역할을 만들기 위해 각 서버를 차례로 연결하고 싶지는 않을 것입니다. 한 가지 옵션은 SQL Server Management Studio 기능을 사용하는 것입니다. 그러면 T/SQL 스크립트를 여러 인스턴스를 대상으로 실행할 수 있습니다.
SQL Server의 Windows PowerShell 구성 요소를 사용하여 조직 내 SQL Server의 모든 인스턴스를 대상으로 새 사용자 정의 서버 역할을 배포할 수도 있습니다. (Windows PowerShell을 사용하여 이러한 스크립트를 배포하는 방법은 너무 다양하기 때문에 이 기사에서 설명하지 않겠습니다.)
SQL Server Management Studio
SQL Server Management Studio UI를 사용해도 동일한 작업을 손쉽게 수행할 수 있습니다. 사용자 정의 서버 역할을 만들기 위해 개체 탐색기 내에서 인스턴스에 연결합니다. 개체 탐색기 내에서 InstanceName | 보안 | 서버 역할로 이동합니다. 서버 역할을 마우스 오른쪽 단추로 클릭하고 상황에 맞는 메뉴에서 새 서버 역할을 선택합니다. 새 서버 역할 창이 열리면 서버 역할 이름 및 소유자를 지정하고, 보안 개체를 선택한 다음, 역할 멤버가 가지기를 원하는 사용 권한을 선택합니다(그림 1 참조).
.jpg)
그림 1 특정 사용자가 가지기를 원하는 권한을 지정합니다.
일반 페이지의 입력란을 완료하고, 멤버 페이지를 선택한 다음(그림 2 참조), 이 사용자 정의 서버 역할의 멤버가 될 SQL Server 로그인을 지정합니다.
.jpg)
그림 2 멤버를 선택하면 이 서버 역할에 액세스할 수 있는 사용자가 지정됩니다.
역할 멤버를 선택한 다음에는 멤버 자격 페이지를 선택합니다. 이 페이지에서 이 사용자 정의 서버 역할의 어떤 서버 역할이 멤버가 될지 지정합니다. 이 화면에서 서버 역할을 지정하면 사용자 정의 서버 역할 내의 사용자도 해당 서버 역할에 따라 권한을 부여받습니다.
사용자 정의 역할을 만들고 이를 serveradmin 고정 서버 역할의 멤버로 지정하는 경우(그림 3 참조) 사용자 정의 서버 역할의 모든 멤버가 즉시 serveradmin 고정 서버 역할의 멤버가 되지는 않습니다. 도메인 그룹 또는 데이터베이스 역할의 중첩과 관련해서는, 역할을 중첩할 때 사용자에게 해당되지 않는 상위 그룹의 권한을 부여하지 않도록 주의해야 합니다.
.jpg)
그림 3 사용자 정의 역할을 다른 서버 역할에 적용할 수 있습니다.
T/SQL을 사용해서 서버 역할을 중첩하려는 경우에도 Add Member 구문과 함께 Alter Server Role 문을 사용해야 합니다. 예를 들어 ViewServerState 사용자 정의 서버 역할을 setupadmin 고정 서버 역할의 멤버로 만들려면 setupadmin 고정 서버 역할을 변경해야 합니다. 그런 다음 ViewServerState 사용자 정의 서버 역할을 멤버로 추가해야 합니다.
ALTER SERVER ROLE [setupadmin] ADD MEMBER [ViewServerState] GO
사용자 정의 서버 역할의 용도는 다양합니다. 사용자 정의 서버 역할에 부여해서 권리를 간단하게 관리할 수 있는 십여 가지의 인스턴스 수준 권리가 있습니다. 전체 시스템 관리자 권한이 아닌 일부 권한을 부여하고 초급 DBA 역할을 만들 수도 있습니다. 전체 시스템 관리자 권한 없이 AlwaysOn 데이터베이스 장애 조치(SQL Server 내에서만 수행 가능)를 수행하도록 권한을 허용하는 AlwaysOnAdmin 그룹을 만들 수도 있습니다.
사용자 정의 서버 역할의 용도는 다양합니다. 특히 관리 작업을 상당히 편리하게 만들 수 있습니다. 또한 이러한 서버 역할을 사용하면 실수로 부적절한 권한을 부여하는 경우를 줄일 수 있어 SQL Server 인스턴스의 보안을 높이는 데도 도움이 됩니다.
.jpg)
Denny Cherry 는 SQL Server 관리에 대한 10년 이상 축적된 경험을 가지고 있습니다. 그의 전문 분야는 시스템 아키텍처, 성능 튜닝, 복제 및 문제 해결입니다. Microsoft Certified Master for SQL Server 2008을 포함하여 SQL Server과 관련된 여러 인증 자격을 보유하고 있으며, 여러 해 동안 Microsoft SQL Server MVP였고, SQL Server 관리 및 다른 여러 기술과 SQL Server 통합에 대한 다양한 기술 기사와 저서를 집필했습니다.