수안이의 컴퓨터 연구실

Gregory A. Larsen

만일 당신의 회사가 잦은 직원의 교체로 애를 먹고 있다면, DBA 또한 자주 새로운 계정 생성으로 고생을 할 것이다. 이런 경우 기존의 SQL 서버 로그인과 동일한 권한의 새로운 로그인을 생성하기 위해 당신이 하는 작업들을 무엇인가? 아마도 매번 엔터프라이즈 관리자를 열어서 직접 기존 로그인의 권한을 확인하고 새로운 SQL 서버 로그인을 생성한 후에 적절한 권한을 할당할 것이다. 이렇게 권한 확인과 계정 생성의 과정을 매번 직접 반복하는 것은 지루하고 자칫 과오를 범하기도 쉬워진다. 이러한 관리 업무를 자동화하여 간편하게 할 수 있는 방법을 고려해 본적이 있는가?

필자는 여러 번 이러한 권한 분석 및 로그인 생성 업무 때문에 애를 먹었었다. 반복되는 일련의 작업으로 고생하던 중, 이 과정의 자동화를 결심하게 되었다. 자동화로 바꾼 작업은 권한 분석 업무의 번거로움을 없애주었고, 새로운 로그인을 생성하는 시간을 단축시켜주었으며 수작업으로 인해 발생할 수 있는 문제를 줄여 주었다. 덕분에 나는 새로운 로그인에 실수로 잘못된 권한을 부여하지 않을 수 있게 되었다. 게다가, 이 과정의 자동화 이후로 필자의 고객(Washington State Department of Health?DOH의 내 동업자)은 더 이상 새로운 로그인에 부여할 특정 권한에 대해서 자세하게 알 필요가 없게 되었다. 대신, 기존의 계정이 갖고 있던 권한에 대해서만 알고 있으면 된다. 결국 이것은 관리자와 고객 모두를 편하게 해 준 셈이 된 것이다.

이 작업은 [웹 리스트 1]의 USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN라는 저장 프로시저로 구성하였다(완전한 웹 리스트는 http://www.adminmag.com 의 자료실에서 다운로드 받을 수 있다). 이 저장 프로시저는 기존의 로그인과 연관된 SQL 서버 권한을 검색하고 이와 동일한 권한을 갖는 새로운 로그인을 생성할 T-SQL 명령을 생성한다. 이 저장 프로시저는 SQL 서버 2000과 7.0에서 동작하며 SQL 서버와 Windows NT 인증 모두와 사용할 수 있다.

이 저장 프로시저는 sp_helpuser, sp_helpsrvrolemember, 그리고 sp_helprotect 시스템 저장 프로시저를 사용하여 SQL 서버 시스템 테이블에서 로그인 권한 정보를 수집한다. 이 저장 프로시저는 sp_helpuser를 사용하여 기존의 로그인이 액세스할 수 있는 데이터베이스와 데이터베이스 역할들을 확인하고, sp_helpsrvrolemember를 이용하여 기존의 로그인이 속해있던 서버 역할들을 알아내고, sp_helprotect를 사용하여 기존 로그인의 개체-수준의 권한들을 알아낸다.

쿼리 분석기에서 이 저장 프로시저를 실행하면 적절한 권한의 새로운 로그인을 추가하기 위한 T-SQL 명령을 볼 수 있다. 이 코드가 어떻게 시스템 저장 프로시저를 사용하여 새로운 로그인에 서버와 데이터베이스 권한을 부여하는지 알아보도록 하자.

저장 프로시저 세부사항

USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN에는 기존 로그인과 새로운 로그인을 위한 몇 가지 매개변수가 필요하다. 이 저장 프로시저에는 4가지 매개변수로 @OLDLOGIN, @NEWLOGIN, @NEWUSER, 그리고 @PASSWORD가 있다. 앞의 세가지 매개변수는 필수적이지만, @PASSWORD는 추가하려는 로그인의 타입에 따라 선택적이다. @OLDLOGIN 매개변수는 복사하려는 기존의 로그인을 나타내고 @NEWLOGIN은 생성할 로그인을 나타낸다. @NEWUSER 매개변수는 새로운 로그인에 할당할 데이터베이스 사용자를 나타낸다. 보통 이 사용자 계정은 새로운 로그인 계정과 동일한 이름을 가질 것이다. 새로운 로그인의 비밀번호에는 @PASSWORD 매개변수를 사용하는데 이는 SQL 서버 로그인 계정을 생성할 경우에만 필요하다.

[웹 리스트1]의 A 부분에서, 이 저장 프로시저는 필요한 로컬 변수들을 선언하고 임시 테이블 #TMP_LOGIN_RIGHTS를 생성하여 새로운 로그인을 생성할 모든 T-SQL 명령을 저장할 수 있도록 한다. 코드의 다음 부분에서 이 테이블을 사용한다. B부분의 코드는 입력 매개변수를 검사하고 서버에 새로운 로그인을 생성하는 명령을 구성한다. 첫 번째 매개변수 검사는 서버에 @OLDLOGIN 매개변수의 기존 계정이 존재하는지 확인한다. 만일 존재하지 않는다면 에러가 발생하고 프로시저는 종료한다. B부분의 @NEWLOGIN 매개변수 검사는 새로운 계정이 서버에 있는지 확인한다. 만일 이 계정이 이미 존재한다면 에러가 발생하고 저장 프로시저는 종료한다. 이 부분에서는 또한 @NEWLOGIN 매개변수의 백슬러시(\)의 유무를 통해서 새로운 로그인 계정이 도메인 계정인지, SQL 인증 로그인 계정인지도 알아낸다. 만일 @NEWLOGIN에 백슬러시가 포함됐다면 저장 프로시저는 도메인 계정을 추가하는 것이라 여기고 적절한 sp_grantlogin 구문을 생성하여 #TMP_LOGIN_RIGHTS 테이블에 추가한다. 만일 새로운 로그인 계정이 SQL 인증 계정이라면 B부분의 코드는 @PASSWORD 값이 매개변수로서 저장 프로시저에서 처리되었는지 확인한다. 만일 비밀번호 없이 저장 프로시저를 호출하면 저장 프로시저는 에러를 발생하고 종료한다. 저장 프로시저가 비밀번호 테스트를 성공적으로 수행하고 나면, 서버에 @NEWLOGIN SQL 인증 계정을 추가하는 sp_addlogin 구문을 #TMP_LOGIN_RIGHTS 테이블에 삽입한다.

다음으로 C부분에서는 @NEWLOGIN에게 @OLDLOGIN과 동일한 데이터베이스 액세스 권한을 허가한다. 이 작업을 위해 모든 데이터베이스의 목록을 포함하는 ALLDATABASES라는 커서를 선언한다. 또한 기존의 데이터베이스 사용자에 대한 정보를 저장할 #TMPUSERS라는 임시 테이블을 생성한다. 커서가 각각의 데이터베이스를 반환할 때마다 sp_helpuser 시스템 저장 프로시저는 현재 데이터베이스에 선언된 각 사용자 계정에 대한 정보를 #TMPUSERS에 저장한다. 만일 이전의 로그인이 현재 데이터베이스에 대해 액세스 권한이 있다면 새로운 사용자가 이미 데이터베이스의 사용자인지 아닌지를 확인한다. 만일 새로운 사용자가 이미 현재 데이터베이스에 선언되었다면 에러가 발생하고 저장 프로시저는 취소된다. 그런 경우, 다른 사용자 이름을 새로운 로그인 계정에 할당할 수 있다. 만일 새로운 사용자가 현재 데이터베이스에 존재하지 않는다면, 데이터베이스에 새로운 로그인이 액세스 가능하도록 sp_grantdbaccess 명령으로 #TMP_LOGIN_RIGHTS을 구성한다. 이 과정은 새로운 로그인에게 필요한 모든 데이터베이스에 액세스가 가능할 때까지 계속된다.

기존의 로그인은 여러 데이터베이스 역할의 구성원일 수도 또는 그렇지 않을 수도 있다. D부분의 코드는 C부분에서 선언한 커서 (ALLDATABASES)와 임시 테이블 (#TMPUSERS)를 사용하여 각 데이터베이스를 차례대로 처리한다. 각각의 데이터베이스마다, 저장 프로시저는 이전 로그인이 데이터베이스에 액세스 할 수 있었는지, 그리고 어떤 데이터베이스 역할의 멤버였는지를 확인한다. 만일 이전 로그인이 어떤 역할의 맴버였다면 저장 프로시저는 sp_addrolemember 명령을 이용하여 #TMP_LOGIN_RIGHTS 테이블을 구성한다. sp_addrolemember 명령은 이전 로그인이 속했던 각 데이터베이스 역할마다 새로운 로그인을 멤버로 구성한다.

데이터베이스 역할 외에도, 이전 로그인은 특정 서버 역할의 구성원이었을 수도 있다. E부분의 코드는 새로운 로그인이 어떤 서버 역할에 멤버로 구성되야 할지를 결정한다. 이 작업을 수행하기 위해서 E부분의 코드는 임시 테이블 #TMPSRVROLES를 생성하고, sp_helpsrvrolemember 시스템 저장 프로시저를 사용하여 각 서버 역할에 대한 멤버 목록을 저장한다. 만일 이전 로그인이 하나 또는 그 이상의 서버 역할의 멤버라면 코드에서는 sp_addsrvrolemember 시스템 저장 프로시저를 사용하여 새로운 로그인을 이전 로그인이 속했던 각 서버 역할의 멤버로 구성한다. 이렇게 생성된 명령들을 #TMP_LOGIN_RIGHTS 임시 테이블에 저장한다.

F부분의 코드는 이전 로그인이 가지고 있던 개체, 컬럼, 또는 구문 수준의 권한을 확인하고 이와 동일한 권한을 새로운 로그인에게 허용하는 명령을 생성한다. F 부분의 코드는 먼저, #TMPPROTECT라는 임시 테이블을 생성하고 주어진 데이터베이스 별로 로그인의 개체, 컬럼, 그리고 구문 수준의 권한을 저장한다. 코드는 한번에 한 데이터베이스씩 모든 데이터베이스를 처리하여 이전 로그인이 각 데이터베이스에 액세스할 수 있는지 확인하고, 액세스가 가능하다면, @OLDUSER 변수에 @OLDLOGIN과 관련있는 데이터베이스 사용자의 이름을 설정한다. 만일 코드에서 @OLDUSER를 설정하면, 코드는 sp_helprotect 시스템 저장 프로시저를 사용하여 현재 데이터베이스에 대한 모든 개체, 컬럼, 그리고 구문 수준의 권한을 얻어낸다. 코드는 그 권한들을 #TMPPROTECT 임시 테이블에 저장한다. 만일 @OLDLOGIN과 연관된 데이터베이스 사용자 @OLDUSER가 개체, 컬럼, 또는 구문 수준의 권한을 가진다면, 코드는 @NEWUSER에게 동일한 개체, 컬럼, 그리고 구문 수준의 GRANT 권한을 생성할 것이다. 또한 새로운 로그인과 연관된 데이터베이스 사용자를 생성할 것이다. F 부분의 코드는 @OLDUSER 계정의 개체, 컬럼, 또는 구문 수준의 권한에 따라 다른 타입의 GRANT 구문을 생성한다. GRANT 구문은 @OLDUSER가 특정 개체에 대해 WITH GRANT OPTION을 가지고 있는지에 따라 계정을 다룬다. 만일 그렇다면, 코드는 생성된 GRANT 명령에 WITH GRANT OPTION을 포함한다. 데이터베이스 개체 상의 WITH GRANT OPTION은 연관된 데이터베이스 사용자가 다른 데이터베이스 사용자에게 해당 개체에 대한 권한을 허용할 수 있게 해준다.

G 부분의 코드는 쿼리 분석기의 결과 창에서 이전의 여섯 개의 섹션에서 생성한 각 명령을 보여준다. 코드는 #TMP_LOGIN_RIGHTS 테이블의 각 레코드를 담고 있는 커서를 생성한다. 그런 후에 한번에 한 레코드씩 이 커서를 처리한다. 코드는 PRINT 구문을 사용하여 각 레코드를 출력한다. 그러고 나면, 출력된 이 T-SQL 구문을 사용하여 새로운 로그인을 선언하고 이전 로그인과 동일한 서버, 데이터베이스 권한을 허가할 수 있다.

저장 프로시저의 호출과 사용

여기까지, USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN 저장 프로시저가 어떻게 작동하는지 이해하였다. 이제는 이 저장 프로시저를 실행하고 T-SQL 구문을 사용하여 새로운 로그인을 생성하는 것을 보도록 하자. 이 예제에서는 NEWUSER라는 이름의 SQL 인증 로그인을 추가하기로 한다. 새로운 로그인은 다음의 보안 정책을 갖는 EXISTINGUSER라는 기존의 SQL 서버 로그인을 기준으로 생성할 것이다:* 로그인은 SQL 서버 인증을 사용한다.

* 로그인은 Pubs와 Northwind 데이터베이스에 액세스할 수 있으며, 기본 데이터베이스는 Northwind이다.
* 로그인은 Pubs 데이터베이스에서 db_owner 역할의 멤버이다.
* 로그인은 Northwind 데이터베이스에서 dbo.categories 개체와 dbo.order_details개체에 SELECT 권한을 가지고, dbo.customers 개체에 DELETE 권한을 가지며, dbo.[Sales by Year] 개체에 EXECUTE 권한을, 그리고 CREATE VIEW 구문 권한을 갖는 db_accessadmin 역할의 멤버이다.

위의 정보를 토대로, 쿼리 분석기에서 다음 구문을 사용하여 USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN을 호출한다.

EXEC DBO.USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN @OLDLOGIN='EXISTINGUSER', @NEWLOGIN='NEWUSER', @NEWUSER='Joe the NewUser', @PASSWORD='#NEWUSER964'

@OLDLOGIN 매개변수는 기존의 로그인을 새로운 로그인의 보안 정책에 대한 모델로 취급한다. @OLDLOGIN 매개변수가 백슬러시를 포함하지 않으므로, 이전 로그인은 SQL 서버 인증 로그인임을 알 수 있다. 만일 새로운 로그인을 NT 사용자의 기존 로그인의 권한에 바탕을 두길 원한다면, @OLDLOGIN 매개변수를 <도메인 이름>\<이전 로그인 이름> 구문을 사용하여 지정한다.
다음 매개변수인 @NEWLOGIN에는 생성하려 하는 새로운 로그인의 이름을 지정한다. @NEWUSER에는 어떤 도메인도 지정되지 않았으므로 이 로그인은 SQL 인증 로그인으로 구성될 것이다. @NEWUSER 매개변수는 새로운 로그인이 액세스할 각 데이터베이스에서 선언될 사용자 이름을 지정한다. 이 경우에는 데이터베이스 사용자 이름이 Joe the NewUser이다. SQL 서버 인증 사용자를 선언했으므로, 로그인에 사용할 비밀번호를 제공할 필요가 있다. 모든 SQL 서버 인증 로그인에는 비밀번호를 구성하는 것이 바람직하기에 USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN 저장 프로시저는 비밀번호 없이는 새로운 SQL 서버 인증 로그인을 생성하는 것을 허용하지 않는다. 저장 프로시저가 실행되고 나면, [그림 1]이 보여주는 T-SQL 결과를 볼 수 있다. 쿼리 분석기는 설정사항에 따라서 이 코드를 결과 창이나 메시지 창, 또는 파일로 보여준다.

새로운 로그인을 생성하기 위해, 쿼리 분석기의 결과 창에서 생성된 EXECUTE와 GRANT 구문을 선택하고 복사해서 빈 쿼리 분석기 창에 복사한다. 쿼리 분석기에 T-SQL을 복사한 다음, 이 코드를 실행하면 새로운 로그인을 추가할 수 있다. 이 T-SQL을 실행하기 전에 잘라내기, 붙여넣기, 실행하기 방법을 사용하여 코드를 확인해보고, 필요하다면 코드의 일부를 수정하거나 삭제할 수도 있다. 예를 들어, 새로운 로그인이 기존의 로그인 계정과 정확하게 동일한 권한을 가질 필요가 없다면 몇 가지 권한을 삭제할 수도 있을 것이다.
새로운 로그인을 추가하는 것 외에도 이 저장 프로시저는 기존의 로그인에 대한 권한을 감사하는 데에도 도움이 될 수 있다. USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN를 기존의 로그인과 함께 호출하되, 새로운 로그인은 의미 없는 가상의 이름으로 구성하자. 이 방법을 사용하면 기존의 로그인이 어떤 권한을 가졌는지 그리고 여러 데이터베이스와 개체에 어떤 작업을 왜 수행하거나 수행할 수 없었는지 알 수가 있다.

만일 당신이 기존의 로그인과 동일한 SQL 서버 권한을 갖는 새로운 로그인을 구성하기 위해 권한 분석과 계정 생성의 지루하고, 고통스러운 수작업을 반복하고 있었다면, 이 글에서 설명한 것과 같은 저장 프로시저의 구현을 고려해 보는 것도 좋다. DBA들은 너무나 바쁘기 때문에 기존 권한들을 조사하는데 귀중한 시간을 낭비하지 않는다. 따라서 로그인 관리를 합리적으로 하기 위한 어떠한 작업도 충분히 구현해볼 가치는 있는 것이다.

EXECUTE [MASTER].[DBO].[SP_ADDLOGIN] [NEWUSER],[#NEWUSER964]EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [NEWUSER],[master] EXECUTE [Northwind].[DBO].[SP_GRANTDBACCESS] [NEWUSER],[Joe the NewUser] EXECUTE [pubs].[DBO].[SP_GRANTDBACCESS] [NEWUSER],[Joe the NewUser] EXECUTE [Northwind].[DBO].[SP_ADDROLEMEMBER] [db_accessadmin],[Joe the NewUser] EXECUTE [pubs].[DBO].[SP_ADDROLEMEMBER] [db_owner],[Joe the NewUser] USE [Northwind] GRANT Select ON [Northwind].[dbo].[Categories] TO [Joe the NewUser] GRANT Select ON [Northwind].[dbo].[Order Details] TO [Joe the NewUser] GRANT Delete ON [Northwind].[dbo].[Customers] TO [Joe the NewUser] GRANT Execute ON [Northwind].[dbo].[Sales by Year] TO [Joe the NewUser] GRANT Create View TO [Joe the NewUser]

출처: WINDOWS & .NET[2004년 3월호]