수안이의 컴퓨터 연구실
사용자 스키마 분리를 통한 보다 쉬운 관리
Kalen Delaney
최근에 필자는 SQL 서버 2000의 보안에 대해 다루어왔다. 지난3월호 기사, “선을 넘기: 소유권 체인” 에서 데이터베이스간 소유권 체인의 상속에 대한 추가적인 보안과 그 한계에 대해 이야기 한 바 있다. 또 5월호 기사 “개체 소유권과 보안” 에서는 SQL 서버 2000의 모델, 즉 사용자와 스키마의 개념을 분리하지 못한 모델을 둘러싼 한계와 혼란에 대해 다루었다. 이 달에는, SQL 서버 2005, 즉 코드명 유콘에서 이런 문제를 해결하는 향상된 보안에 대해 살펴보도록 하자.
마이크로소프트는 SQL 서버 2005의 주요 초점을 보안에 맞추었고, 이전 버전에서 취약했거나 불완전했던 보안의 측면들을 많이 향상시켰다. SQL 서버 2005에서 가장 중요한 보안의 변화는 박스에서 바로 꺼낸 상태에서도 안전하다는 것이다. 즉, SQL 서버를 디폴트 옵션 그대로 설치한다고 하더라도 말이다. 뿐만 아니라 지난 달 필자가 언급했던 스키마라는 용어가 새로 생겼는데 이에 대해서 지난달 보안 기사에서 인증과 허가(authentication and authorization) 의 개념에 대해 다루었다. 이런 새로운 개념들은 SQL 서버 2005의 보안 모델에 대한 기반을 제공 한다.
스키마라는 것은 개발자들이 데이터베이스 개체를 만들 때 담아두는 그릇으로 생각할 수 있다. SQL 서버 2005 에서 개체라는 것을 언급할 때는 개체를 담고 있는 스키마에 대해 언급하게 되지, 개체의 소유자에 대해 언급하는 것이 아니다. 인증(authentication)이란 SQL 서버 에 의해 주어진 정체(identity)가 접근하고자 하는 자원에 타당하거나 타당치 않다고 인정 받는 과정이다.
프로그래밍 가능한 모듈의 수행 문맥(context)
소유권 체인은 한 사용자(예를 들어 user1)가 다른 사용자(예: user2)의 개체에 대해 접근할 때 SQL 서버 가 자동으로 이를 인증해 주는 것이다. 예를 들어, user1이 user2가 소유한 프로그래밍 가능한 모듈, 즉 함수나 저장 프로시저에 대해 수행하고자 할 때 나타난다. 그러나, 소유권 체인의 메커니즘을 떠나서, SQL 서버 2000은 의존하는 개체에 액세스 하는 동안 사용자 인증을 제어할 수 있는 방법이 없었다. 사용자는 모듈을 항상 그 자신으로서만 수행하고, 의존하는 모듈에 대해 다음 두 조건 중 하나가 참일 때 만 접근할 수 있었다.
1. 의존 하는 모듈이 그 모듈의 소유자와 동일한 소유자를 가질 때
2. 그 모듈을 실행하는 사용자가 명백하게 접근하려는 모듈에 대해 권한을 가질 때
SQL 서버 2005 는 저장 프로시저와 사용자 정의 함수(인라인 테이블 함수를 제외한)를 실행하는 문맥(context)를 정의할 능력을 EXECUTE AS 절을 통해 제공해 주며, EXECUTE AS 절은 해당 모듈의 제일 머리에 두면 된다. 이 능력은 응용프로그램 개발자에게 보다 나은 제어를 할 수 있게 해주는데, 이를 통해 개발자들은 어떤 사용자가 다른 사용자의 인증으로 인증되었더라도 하나의 모듈에서 작업을 할 수 있게 해 줄 수 있다.
EXECUTE AS 는 모듈이 동적 SQL 을 사용할 때 유용할 수 있다. SQL 서버 2000은 항상 실행하는 모든 동적 문장에 대해 권한을 점검하는데, 앞서의 첫 번째 조건을 무시하게 된다.(즉 소유권 체인이 전혀 적용되지 않는다.) 만약 프로시저 호출자가 동적 SQL문장에서 참조하는 개체에 대해 권한을 가지고 있지 못하다면, 그 동적 SQL문은 실행되지 못한다. 그러나, SQL 서버 2005에서는, 사용자가 동적 SQL 문을 포함하는 프로시저를 만들었을 때도 실행된다. SQL 서버 2005베타2에서는 EXECUTE AS 절은 인증에 대해 세가지 가능한 옵션을 가진다: CALLER (디폴트), USER = user_name, SELF 가 그 것이다.
EXECUTE AS CALLER. EXECUTE AS CALLER는 디폴트 동작인데, SQL 서버 2000과 호환성을 보장한다. 모듈 안의 문장이 수행될 때는, 호출자의 문맥에서 수행된다. 그러므로, 해당 루틴을 수행하는 사용자는 반드시 해당 루틴(모듈)에 대한 권한 뿐만 아니라 참조하는 데이터베이스 개체에 대해 권한을 가지고 있어야 한다. 권한은 명백하게 해당 개체에 대해 주어져 있거나, 아니면 소유권 체인에 의해 내재될 수도 있다. 후자의 경우, SQL 서버 2005는 호출하는 개체와 참조되는 개체의 소유권 체인을 조사함으로써, 참조되는 개체의 권한을 평가하며 이것은 마치 이전의 SQL 서버 2000이 하는 방식과 같다. 그러나 SQL 서버 2005에서 개체는 소유자를 가지지 않는다는 것을 명심하자. 스키마가 소유자와 개체를 가지고 있다.
EXECUTE AS USER = user_name. EXECUTE AS USER = user_name을 지정하면 지정된 user_name의 문맥에서 루틴이 수행된다. SQL 서버 는 먼저 사용자가 수행하고자 하는 루틴에 대해 EXECUTE 권한이 있는지 검사하고 해당 user_name이 참조하는 모듈에 대해 권한이 있는지 검사한다. 하지만 모듈을 수행할 때 사용할 사람을 아무나 제멋대로 선택할 수는 없다. 특정 이름을 지정하기 위해서는 특별한 권한(예를 들어 IMPERSONATE)이 필요하거나 특별한 역할(sysadmin 또는 db_owner)의 구성원이 되어야 한다.
사용자 Susan이 자신이 소유한 자신의 기본 스키마에 프로시저를 만들었고 그것이 그녀가 소유하지 않은 다른 스키마(JoeSchema) 의 테이블을 참조한다고 해 보자. (Susan은 JoeSchema가 그녀가 소유하지 않은 스키마라면 JoeSchema에 대해 프로시저를 만들 권한이 있어야만 한다.) 하지만 JoeSchema의 테이블에 대해 Mary 가 SELECT 권한을 가질 수 있다고 하자. 이럴 때 Susan은 EXECUTE AS USER = Mary 라는 절을 CREATE PROCEDURE 문에 다음과 같이 추가할 수 있다:
이렇게 하면 Susan은 그 프로시저에 대한 EXECUTE 권한을 사용자 Scott에게 줄 수 있다. Scott이 이 프로시저를 실행할 때 SQL 서버 는 그가 프로시저를 실행 할 권한이 있는 지 검사하지만 참조하는 테이블에 대해서는 Mary의 권한을 검사한다. 이 시나리오에서는 Scott 이 테이블에 대한 SELECT 권한을 직접 가지지는 못했지만 프로시저를 통해 테이블에 접근할 수 있는데, 프로시저는 Mary의 문맥에서 수행되며 Mary 는 테이블에 대해 접근 가능하기 때문이다.
EXECUTE AS SELF. EXECUTE AS SELF 는 "이 모듈을 만들거나 변경하는 사용자로서 수행” 한다는 의미이다. EXECUTE AS SELF 는 지정된 사용자가 그 루틴을 만들거나 변경하는 사용자와 동일하다면 EXECUTE AS USER = user_name과 동일하다. 카탈로그에는 SELF 라는 값이 아니라 그 사용자의 user ID (UID)가 저장된다.
사용자 SELF 가 개체의 소유자일 필요는 없다는 것을 명심하자. SQL 서버 2005에서 개체는 실제로 소유자라는 것을 갖지 않지만 스키마 소유자가 그 스키마의 모든 개체의 소유자라고 생각할 수 있다. 사용자들은 그들이 소유하지 않은 스키마에서 개체를 생성할 권한을 가질 수 있다. 예를 들어 Susan이 Joe 가 소유한 스키마에서 ALTER SCHEMA 권한을 가질 수도 있다. 만약 Susan 이 Joe의 스키마에서 테이블 생성 권한을 가진다면 Joe 는 소유자이고 Susan 은 생성자가 된다. Susan 이 만든 어떤 프로시저든 EXECUTE AS SELF 는 Susan의 문맥에서 수행된다.
어떤 옵션을 선택할까?
이런 새로운 사양은 이해하는데 약간의 시간이 걸릴지 모르기 때문에 여기서는 어떤 EXECUTE AS 옵션을 어떤 상황에서 써야 하는지에 대한 약간의 가이드 라인을 제시하고자 한다. EXECUTE AS CALLER 는 다음과 같은 상황에서 사용하자:
* 루틴에서 수행하고자 하는 문장이 호출자의 문맥에서 수행되기를 원할 때
* SQL 서버 가 루틴의 해당 문장에 대한 권한 점검을 의존하는 개체의 권한 검사를 통과하는(하지 않는) 소유권 체인에 근거하여 수행되기 원할 때
* 같은 소유자가 가진 스키마에 있는 개체만 참조하기 때문에 의존하는 개체에 대한 접근을 소유권 체인에 근거하여 판단되기 원할 때
EXECUTE AS USER = user_name 은 다음과 같은 상황에서 사용하자.
* 특정한 사용자의 문맥에서 해당 문장이 수행되기 원할 때
* 기초가 되는 스키마를 숨기기 위해 소유권 체인을 사용할 수 없고(예를 들어 개체를 액세스하는 루틴이 다른 소유자의 것일 때), 참조하는 개체에 대해 권한 부여를 하고 싶지 않을 때
* 내가 임의로 권한 집합을 만들고 싶을 때
많은 경우에 줄(grant) 수 없는 권한(예를 들어 TRUNCATE TABLE)을 주어야 할 때가 있다. SQL 서버 2005에서 조차도 이런 권한은 줄 수 없다. 하지만 데이터베이스 소유자는 테이블을 truncate하는 프로시저를 만들 수 있다. 테이블의 소유자와 데이터베이스의 소유자가 동일 하다면 깨어지지 않은 소유권 체인을 가진 것이다. 소유권 체인의 장점은 소유권 체인이 DML(Data Manipulation Language)문장 (SELECT, INSERT, UPDATE, DELETE)에만 적용되고 따라서 프로시저를 수행하는 사용자는 테이블을 truncate하려는 시도에 대해 권한 위반을 만나게 된다. 그러나 EXECUTE AS 를 사용하여 프로시저를 만들면 프로시저를 수행하는 사용자는 수행하는 동안 다른 사용자의 정체성으로 프로시저를 수행하게 되며 따라서 TRUNCATE TABLE 문장은 수행될 수 있다.
EXECUTE AS SELF 는 응용프로그램이 호출한 사용자를 위해 루틴을 만들고 그 루틴이 생성자의 문맥에서 수행되기를 원할 때 사용하면 좋다. 예를 들어 디자인 단계에서 어떤 사용자가 그 응용프로그램을 호출할 지 모르는 경우다.
사용자 스키마 분리
SQL 서버 2005 에서 크게 눈에 띄는 보안상의 또 다른 변화는, 사용자와 개체의 관계를 단순화 시켰고, 이로 인해 사용자가 소유한 개체에 대해 염려하지 않고 사용자를 삭제 할 수 있다는 것이다. ANSI 호환성을 위해서, SQL 서버 2005 는 사용자와 스키마를 구분하고 있다.
ANSI SQL-92 표준은 스키마를 사용자가 소유한 데이터베이스 개체와 하나의 네임 스페이스를 구성하는(즉 중복된 이름을 가질 수 없는 개체의 집합) 데이터베이스 개체의 집합으로 정의한다. 예를 들어, 두 테이블은 오직 서로 다른 스키마에 있을 때에만 같은 이름을 지닐 수 있다. 두 테이블은 같은 스키마에 있다면 절대 같은 이름을 가질 수 없다. 따라서 스키마를 개체를 담고 있는 그릇으로 생각할 수 있다. (데이터베이스 도구에서는 스키마란 한 스키마 혹은 데이터베이스에 있는 개체를 서술하는 카탈로그 정보를 언급한다. 분석 서비스에서 스키마란, 큐브나 차원 같은 다차원 개체를 의미 한다.)
SQL 서버 2005 는 사용자의 스키마에 대한 연결고리를 끊었다. 기본 principal이나 보조 principal이 같은 스키마를 가질 수 있다. 여기서 principal 이라는 용어는 안전한(안전하게 할 수 있는) 개체를 액세스 할 수 있는 엔터티를 의미 한다. 기본 principal은 단일 사용자(예를 들어 SQL 서버 로그인이나 윈도우 로그인)를 의미한다. 보조 principal은 다수 사용자(예를 들어 역할이나 윈도우 그룹)을 의미 한다. SQL 서버 2005에서의 또 다른 변화는, 개체는 소유자를 갖지 않는 다는 것이다. 누가 스키마의 소유자가 되건, 그 스키마의 소유자가 개체를 “소유”한다. 스키마란 개체를 담고 있는 그릇이지 소유자가 아니라는 것을 다시 한번 기억하자. SQL 서버 2005에서 만들어지는 모든 새로운 데이터베이스는 몇 개의 스키마를 포함하고 있다. SQL 서버 2000의 dbo, INFORMATION_SCHEMA, guest 사용자에 상응하는 것으로 모든 SQL 서버 2005의 데이터베이스는 sys라는 스키마를 가지고 있는데 이것은 모든 시스템 테이블과 뷰에 대한 사용자들의 접근을 허락해 준다. 마지막으로 SQL 서버 2000의 시스템 역할(미리 정의된 역할)에 상응하기 위해 SQL 서버 2005는 동일한 이름의 스키마를 가지고 있다.
사용자를 만들 때 실제로 존재하지 않는 스키마를 사용자의 디폴트 스키마로 지정할 수도 있다. 사용자의 디폴트 스카마란 개체 생성과 삭제 시에 참조하게 된다. “개체 소유권과 보안”에서 언급한 것처럼 사용자를 위한 디폴트 스키마를 지정하지 않으면 사용자의 디폴트 스키마는 dbo 스키마가 된다. SQL 서버 2005는 개체 접근을 위해 그 사용자의 디폴트 스키마가 무엇이든 상관 없이 sys 스키마를 항상 제일 먼저 점검해 본다. 예를 들어 사용자 Sue가 쿼리 SELECT * FROM table1 을 수행하고 Sue 의 디폴트 스키마는 SueSchema 라고 한다면 이름 해결(name resolution)은 다음과 같은 단계를 따르게 된다.
1. sys.table1 을 찾는다.
2. SueSchema.table1 을 찾는다.
3. dbo.table1 을 찾는다.
이런 조회 메커니즘은 ALTER 와 DROP을 포함한 모든 문장에 적용된다. sysadmin 이 개체를 한 요소 이름(one-part name)으로 생성했을 때 스키마는 항상 dbo가 됨을 기억하자. 그러나 sysadmin 은 명시적으로 개체가 놓여질 스키마를 지정할 수도 있다.
하위 호환성
SQL 서버 2000 은 사용자와 스키마를 분리(구별)하지 않는다. 그래서 SQL 서버 2000은 스키마의 자리에 개체를 만든 소유자를 박아 넣고 있다. 하위 호환성 유지를 위해서 SQL 서버 2005에서 마이크로소프트는 sp_grantdbaccess 와 sp_adduser 프로시저를 재 작성 했으며 새로운 Data Definition Language (DDL) 문을 만들었다. 이에 대해서는 다음이 “새로운DDL" 부분에서 다룬다.
SQL 서버 2005에서 sp_grantdbaccess 는 사용자를 만들고 사용자 이름과 동일한 이름의 스키마를 만든다. 스키마는 사용자의 디폴트 스키마가 되며 사용자는 스키마의 소유자가 된다. 예를 들어 다음 프로시저를 수행한다고 해 보자.
SQL 서버 2005 는 이것을 내부적으로 새로운 DDL문장으로 해석하는데 [리스트 1]이 이를 보여준다. 뿐만 아니라 SQL 서버 2005는 내부적으로 sp_revokedbaccess sue 를 다음과 같이 다시 쓴다.
SQL 서버 2005 는 사용자와 이름과 ID가 같은 스키마만 삭제한다.
디폴트 스키마 없이 CREATE USER 를 sp_grantdbaccess 대신 수행하면 디폴트 스키마는 dbo가 된다. SQL 서버 2005 는 사용자 이름과 같은 이름의 새로운 스키마를 만들지 않는다.
이제 준비하자
SQL 서버 2005가 출시되었을 때 즉시 업그레이드 할 계획이 없다고 할 지라도 사용자와 스키마를 지금부터 분리해서 생각해줄 것을 권고한다. SQL 서버 2000에서는 스키마는 항상 사용자의 이름과 같지만 권한을 부여할 때 사용자에게 부여하는 것이라는 것을 정확하게는 알고 있어야 한다. 스키마는 개체를 담고 있는 (그리고 그것의 적합성을 판단하는) 그릇일 뿐이다.
사용자와 스키마의 분리는 몇 가지 방법으로 관리를 쉽게 해준다. 가장 큰 이점은 사용자를 관리 하는 것이다. 특히 사용자를 삭제할 때 매우 편해진다. 뿐만 아니라, 여러 사용자 집합을 한 스키마의 소유자로 지정할 수 있는데 이때는 보조 principal(예를 들어 그룹이나 역할)을 스키마의 소유자로 지정하면 된다. 또, 여러 사용자가 같은 디폴트 스키마를 가져서 같은 이름 해결(name resolution)을 할 수 있다.
특정 모듈의 실행자로서 사용자를 지정 할 수 있는 능력은 개체를 액세스 하는데 더 많은 제어 능력을 주며 SQL 서버 2000의 서비스 팩 3를 능가하는 데이터베이스 간 소유권 체인에 있어서 많은 발전이다. 데이터베이스간 소유권 체인은 “전부 아니면 아무 것도(all-or-nothing)”의 해결책이었다: 소유권 체인은 데이터베이스 간에 적용되거나, 아니면 아예 안되거나 둘 중 하나였다. SQL 서버 2005의 발전된 제어력은 하나의 모듈 개발자가 어떤 모듈이 사용자가 실행할 수 있으며 어떤 모듈이 사용자가 실행할 수 없는지를 결정할 수 있게 해 준다.
이런 향상된 점들은 유콘 보안 사양의 빙산의 일각일 뿐이다. 새로운 사양에 대해 귀를 열어 놓고 기다리자. SQL 서버 2005 출시에 가까워 질수록, 이런 사양들에 대해 나는 더 깊이 파고 들 것이다.
[리스트 1] SQL 서버 2005가 번역한 sp_grantdbaccess
새로운DDL
SQL 서버 2005 는 스키마를 다룰 수 있는 새로운 문장들을 제공한다. 여기에 완전한 긴 문법을 적어서 지면을 차지하기 보다는 간단히 요약하여 적을 것이고 더 자세한 것은 SQL 서버 2005를 설치한 후에 온라인 설명서를 참조하는 것이 좋겠다.
CREATE SCHEMA를 가지고 새로운 스키마를 만들 수 있으며 이와 함께 옵션으로 AUTHORIZATION 절을 사용하여 스키마의 소유자를 지정할 수 있다. AUTHORIZATION 절을 사용하지 않으면 CREATE SCHEMA를 수행하는 사용자가 스키마의 소유자가 된다. 소유자는 다른 스키마를 소유할 수 있으며 소유한 스키마 외에 다른 스키마를 디폴트 스키마로 가질 수도 있다.
ALTER SCHEMA 문은 스키마 소유자를 재설정 할 수 있게 해준다. (한 시점에 한 principal 만이 스키마의 소유자가 될 수 있지만 principal은 SQL 서버 사용자, 윈도우 사용자, 윈도우 그룹, SQL 서버 역할 중 어느 것이든 가능하다). 스키마에 TAKE OWNERSHIP 권한을 가진 사용자만이 스키마의 소유권을 가질 수 있다. db_owner 역할의 구성원은 스키마 소유권을 변경할 수 있다.
DROP SCHEMA 는 아무 개체도 담고 잇지 않은 스키마를 제거한다. 스키마에 단 하나의 개체라도 담겨 있으면, 스키마 삭제는 실패한다.
또한 SQL 서버 2005 는 사용자와 함께 동작하는 새로운 DDL(Data Definition Language) 문을 가지고 있다. 이전에 저장 프로시저로 하던 것들을 이제는 CREATE 과 ALTER 문장으로 대신할 수 있다. CREATE USER는 새로운 사용자를 만들며 선택적으로 로그인 명과 연결시킬 수 있다. 또한 디폴트 스키마를 지정할 수 있다. ALTER USER 는 사용자 이름을 바꾸거나 사용자의 디폴트 스키마를 변경할 수 있다.
출처: Windows .NET [2004년 6월호]
Kalen Delaney
최근에 필자는 SQL 서버 2000의 보안에 대해 다루어왔다. 지난3월호 기사, “선을 넘기: 소유권 체인” 에서 데이터베이스간 소유권 체인의 상속에 대한 추가적인 보안과 그 한계에 대해 이야기 한 바 있다. 또 5월호 기사 “개체 소유권과 보안” 에서는 SQL 서버 2000의 모델, 즉 사용자와 스키마의 개념을 분리하지 못한 모델을 둘러싼 한계와 혼란에 대해 다루었다. 이 달에는, SQL 서버 2005, 즉 코드명 유콘에서 이런 문제를 해결하는 향상된 보안에 대해 살펴보도록 하자.
마이크로소프트는 SQL 서버 2005의 주요 초점을 보안에 맞추었고, 이전 버전에서 취약했거나 불완전했던 보안의 측면들을 많이 향상시켰다. SQL 서버 2005에서 가장 중요한 보안의 변화는 박스에서 바로 꺼낸 상태에서도 안전하다는 것이다. 즉, SQL 서버를 디폴트 옵션 그대로 설치한다고 하더라도 말이다. 뿐만 아니라 지난 달 필자가 언급했던 스키마라는 용어가 새로 생겼는데 이에 대해서 지난달 보안 기사에서 인증과 허가(authentication and authorization) 의 개념에 대해 다루었다. 이런 새로운 개념들은 SQL 서버 2005의 보안 모델에 대한 기반을 제공 한다.
스키마라는 것은 개발자들이 데이터베이스 개체를 만들 때 담아두는 그릇으로 생각할 수 있다. SQL 서버 2005 에서 개체라는 것을 언급할 때는 개체를 담고 있는 스키마에 대해 언급하게 되지, 개체의 소유자에 대해 언급하는 것이 아니다. 인증(authentication)이란 SQL 서버 에 의해 주어진 정체(identity)가 접근하고자 하는 자원에 타당하거나 타당치 않다고 인정 받는 과정이다.
프로그래밍 가능한 모듈의 수행 문맥(context)
소유권 체인은 한 사용자(예를 들어 user1)가 다른 사용자(예: user2)의 개체에 대해 접근할 때 SQL 서버 가 자동으로 이를 인증해 주는 것이다. 예를 들어, user1이 user2가 소유한 프로그래밍 가능한 모듈, 즉 함수나 저장 프로시저에 대해 수행하고자 할 때 나타난다. 그러나, 소유권 체인의 메커니즘을 떠나서, SQL 서버 2000은 의존하는 개체에 액세스 하는 동안 사용자 인증을 제어할 수 있는 방법이 없었다. 사용자는 모듈을 항상 그 자신으로서만 수행하고, 의존하는 모듈에 대해 다음 두 조건 중 하나가 참일 때 만 접근할 수 있었다.
1. 의존 하는 모듈이 그 모듈의 소유자와 동일한 소유자를 가질 때
2. 그 모듈을 실행하는 사용자가 명백하게 접근하려는 모듈에 대해 권한을 가질 때
SQL 서버 2005 는 저장 프로시저와 사용자 정의 함수(인라인 테이블 함수를 제외한)를 실행하는 문맥(context)를 정의할 능력을 EXECUTE AS 절을 통해 제공해 주며, EXECUTE AS 절은 해당 모듈의 제일 머리에 두면 된다. 이 능력은 응용프로그램 개발자에게 보다 나은 제어를 할 수 있게 해주는데, 이를 통해 개발자들은 어떤 사용자가 다른 사용자의 인증으로 인증되었더라도 하나의 모듈에서 작업을 할 수 있게 해 줄 수 있다.
EXECUTE AS 는 모듈이 동적 SQL 을 사용할 때 유용할 수 있다. SQL 서버 2000은 항상 실행하는 모든 동적 문장에 대해 권한을 점검하는데, 앞서의 첫 번째 조건을 무시하게 된다.(즉 소유권 체인이 전혀 적용되지 않는다.) 만약 프로시저 호출자가 동적 SQL문장에서 참조하는 개체에 대해 권한을 가지고 있지 못하다면, 그 동적 SQL문은 실행되지 못한다. 그러나, SQL 서버 2005에서는, 사용자가 동적 SQL 문을 포함하는 프로시저를 만들었을 때도 실행된다. SQL 서버 2005베타2에서는 EXECUTE AS 절은 인증에 대해 세가지 가능한 옵션을 가진다: CALLER (디폴트), USER = user_name, SELF 가 그 것이다.
EXECUTE AS CALLER. EXECUTE AS CALLER는 디폴트 동작인데, SQL 서버 2000과 호환성을 보장한다. 모듈 안의 문장이 수행될 때는, 호출자의 문맥에서 수행된다. 그러므로, 해당 루틴을 수행하는 사용자는 반드시 해당 루틴(모듈)에 대한 권한 뿐만 아니라 참조하는 데이터베이스 개체에 대해 권한을 가지고 있어야 한다. 권한은 명백하게 해당 개체에 대해 주어져 있거나, 아니면 소유권 체인에 의해 내재될 수도 있다. 후자의 경우, SQL 서버 2005는 호출하는 개체와 참조되는 개체의 소유권 체인을 조사함으로써, 참조되는 개체의 권한을 평가하며 이것은 마치 이전의 SQL 서버 2000이 하는 방식과 같다. 그러나 SQL 서버 2005에서 개체는 소유자를 가지지 않는다는 것을 명심하자. 스키마가 소유자와 개체를 가지고 있다.
EXECUTE AS USER = user_name. EXECUTE AS USER = user_name을 지정하면 지정된 user_name의 문맥에서 루틴이 수행된다. SQL 서버 는 먼저 사용자가 수행하고자 하는 루틴에 대해 EXECUTE 권한이 있는지 검사하고 해당 user_name이 참조하는 모듈에 대해 권한이 있는지 검사한다. 하지만 모듈을 수행할 때 사용할 사람을 아무나 제멋대로 선택할 수는 없다. 특정 이름을 지정하기 위해서는 특별한 권한(예를 들어 IMPERSONATE)이 필요하거나 특별한 역할(sysadmin 또는 db_owner)의 구성원이 되어야 한다.
사용자 Susan이 자신이 소유한 자신의 기본 스키마에 프로시저를 만들었고 그것이 그녀가 소유하지 않은 다른 스키마(JoeSchema) 의 테이블을 참조한다고 해 보자. (Susan은 JoeSchema가 그녀가 소유하지 않은 스키마라면 JoeSchema에 대해 프로시저를 만들 권한이 있어야만 한다.) 하지만 JoeSchema의 테이블에 대해 Mary 가 SELECT 권한을 가질 수 있다고 하자. 이럴 때 Susan은 EXECUTE AS USER = Mary 라는 절을 CREATE PROCEDURE 문에 다음과 같이 추가할 수 있다:
이렇게 하면 Susan은 그 프로시저에 대한 EXECUTE 권한을 사용자 Scott에게 줄 수 있다. Scott이 이 프로시저를 실행할 때 SQL 서버 는 그가 프로시저를 실행 할 권한이 있는 지 검사하지만 참조하는 테이블에 대해서는 Mary의 권한을 검사한다. 이 시나리오에서는 Scott 이 테이블에 대한 SELECT 권한을 직접 가지지는 못했지만 프로시저를 통해 테이블에 접근할 수 있는데, 프로시저는 Mary의 문맥에서 수행되며 Mary 는 테이블에 대해 접근 가능하기 때문이다.
EXECUTE AS SELF. EXECUTE AS SELF 는 "이 모듈을 만들거나 변경하는 사용자로서 수행” 한다는 의미이다. EXECUTE AS SELF 는 지정된 사용자가 그 루틴을 만들거나 변경하는 사용자와 동일하다면 EXECUTE AS USER = user_name과 동일하다. 카탈로그에는 SELF 라는 값이 아니라 그 사용자의 user ID (UID)가 저장된다.
사용자 SELF 가 개체의 소유자일 필요는 없다는 것을 명심하자. SQL 서버 2005에서 개체는 실제로 소유자라는 것을 갖지 않지만 스키마 소유자가 그 스키마의 모든 개체의 소유자라고 생각할 수 있다. 사용자들은 그들이 소유하지 않은 스키마에서 개체를 생성할 권한을 가질 수 있다. 예를 들어 Susan이 Joe 가 소유한 스키마에서 ALTER SCHEMA 권한을 가질 수도 있다. 만약 Susan 이 Joe의 스키마에서 테이블 생성 권한을 가진다면 Joe 는 소유자이고 Susan 은 생성자가 된다. Susan 이 만든 어떤 프로시저든 EXECUTE AS SELF 는 Susan의 문맥에서 수행된다.
어떤 옵션을 선택할까?
이런 새로운 사양은 이해하는데 약간의 시간이 걸릴지 모르기 때문에 여기서는 어떤 EXECUTE AS 옵션을 어떤 상황에서 써야 하는지에 대한 약간의 가이드 라인을 제시하고자 한다. EXECUTE AS CALLER 는 다음과 같은 상황에서 사용하자:
* 루틴에서 수행하고자 하는 문장이 호출자의 문맥에서 수행되기를 원할 때
* SQL 서버 가 루틴의 해당 문장에 대한 권한 점검을 의존하는 개체의 권한 검사를 통과하는(하지 않는) 소유권 체인에 근거하여 수행되기 원할 때
* 같은 소유자가 가진 스키마에 있는 개체만 참조하기 때문에 의존하는 개체에 대한 접근을 소유권 체인에 근거하여 판단되기 원할 때
EXECUTE AS USER = user_name 은 다음과 같은 상황에서 사용하자.
* 특정한 사용자의 문맥에서 해당 문장이 수행되기 원할 때
* 기초가 되는 스키마를 숨기기 위해 소유권 체인을 사용할 수 없고(예를 들어 개체를 액세스하는 루틴이 다른 소유자의 것일 때), 참조하는 개체에 대해 권한 부여를 하고 싶지 않을 때
* 내가 임의로 권한 집합을 만들고 싶을 때
많은 경우에 줄(grant) 수 없는 권한(예를 들어 TRUNCATE TABLE)을 주어야 할 때가 있다. SQL 서버 2005에서 조차도 이런 권한은 줄 수 없다. 하지만 데이터베이스 소유자는 테이블을 truncate하는 프로시저를 만들 수 있다. 테이블의 소유자와 데이터베이스의 소유자가 동일 하다면 깨어지지 않은 소유권 체인을 가진 것이다. 소유권 체인의 장점은 소유권 체인이 DML(Data Manipulation Language)문장 (SELECT, INSERT, UPDATE, DELETE)에만 적용되고 따라서 프로시저를 수행하는 사용자는 테이블을 truncate하려는 시도에 대해 권한 위반을 만나게 된다. 그러나 EXECUTE AS 를 사용하여 프로시저를 만들면 프로시저를 수행하는 사용자는 수행하는 동안 다른 사용자의 정체성으로 프로시저를 수행하게 되며 따라서 TRUNCATE TABLE 문장은 수행될 수 있다.
EXECUTE AS SELF 는 응용프로그램이 호출한 사용자를 위해 루틴을 만들고 그 루틴이 생성자의 문맥에서 수행되기를 원할 때 사용하면 좋다. 예를 들어 디자인 단계에서 어떤 사용자가 그 응용프로그램을 호출할 지 모르는 경우다.
사용자 스키마 분리
SQL 서버 2005 에서 크게 눈에 띄는 보안상의 또 다른 변화는, 사용자와 개체의 관계를 단순화 시켰고, 이로 인해 사용자가 소유한 개체에 대해 염려하지 않고 사용자를 삭제 할 수 있다는 것이다. ANSI 호환성을 위해서, SQL 서버 2005 는 사용자와 스키마를 구분하고 있다.
ANSI SQL-92 표준은 스키마를 사용자가 소유한 데이터베이스 개체와 하나의 네임 스페이스를 구성하는(즉 중복된 이름을 가질 수 없는 개체의 집합) 데이터베이스 개체의 집합으로 정의한다. 예를 들어, 두 테이블은 오직 서로 다른 스키마에 있을 때에만 같은 이름을 지닐 수 있다. 두 테이블은 같은 스키마에 있다면 절대 같은 이름을 가질 수 없다. 따라서 스키마를 개체를 담고 있는 그릇으로 생각할 수 있다. (데이터베이스 도구에서는 스키마란 한 스키마 혹은 데이터베이스에 있는 개체를 서술하는 카탈로그 정보를 언급한다. 분석 서비스에서 스키마란, 큐브나 차원 같은 다차원 개체를 의미 한다.)
SQL 서버 2005 는 사용자의 스키마에 대한 연결고리를 끊었다. 기본 principal이나 보조 principal이 같은 스키마를 가질 수 있다. 여기서 principal 이라는 용어는 안전한(안전하게 할 수 있는) 개체를 액세스 할 수 있는 엔터티를 의미 한다. 기본 principal은 단일 사용자(예를 들어 SQL 서버 로그인이나 윈도우 로그인)를 의미한다. 보조 principal은 다수 사용자(예를 들어 역할이나 윈도우 그룹)을 의미 한다. SQL 서버 2005에서의 또 다른 변화는, 개체는 소유자를 갖지 않는 다는 것이다. 누가 스키마의 소유자가 되건, 그 스키마의 소유자가 개체를 “소유”한다. 스키마란 개체를 담고 있는 그릇이지 소유자가 아니라는 것을 다시 한번 기억하자. SQL 서버 2005에서 만들어지는 모든 새로운 데이터베이스는 몇 개의 스키마를 포함하고 있다. SQL 서버 2000의 dbo, INFORMATION_SCHEMA, guest 사용자에 상응하는 것으로 모든 SQL 서버 2005의 데이터베이스는 sys라는 스키마를 가지고 있는데 이것은 모든 시스템 테이블과 뷰에 대한 사용자들의 접근을 허락해 준다. 마지막으로 SQL 서버 2000의 시스템 역할(미리 정의된 역할)에 상응하기 위해 SQL 서버 2005는 동일한 이름의 스키마를 가지고 있다.
사용자를 만들 때 실제로 존재하지 않는 스키마를 사용자의 디폴트 스키마로 지정할 수도 있다. 사용자의 디폴트 스카마란 개체 생성과 삭제 시에 참조하게 된다. “개체 소유권과 보안”에서 언급한 것처럼 사용자를 위한 디폴트 스키마를 지정하지 않으면 사용자의 디폴트 스키마는 dbo 스키마가 된다. SQL 서버 2005는 개체 접근을 위해 그 사용자의 디폴트 스키마가 무엇이든 상관 없이 sys 스키마를 항상 제일 먼저 점검해 본다. 예를 들어 사용자 Sue가 쿼리 SELECT * FROM table1 을 수행하고 Sue 의 디폴트 스키마는 SueSchema 라고 한다면 이름 해결(name resolution)은 다음과 같은 단계를 따르게 된다.
1. sys.table1 을 찾는다.
2. SueSchema.table1 을 찾는다.
3. dbo.table1 을 찾는다.
이런 조회 메커니즘은 ALTER 와 DROP을 포함한 모든 문장에 적용된다. sysadmin 이 개체를 한 요소 이름(one-part name)으로 생성했을 때 스키마는 항상 dbo가 됨을 기억하자. 그러나 sysadmin 은 명시적으로 개체가 놓여질 스키마를 지정할 수도 있다.
하위 호환성
SQL 서버 2000 은 사용자와 스키마를 분리(구별)하지 않는다. 그래서 SQL 서버 2000은 스키마의 자리에 개체를 만든 소유자를 박아 넣고 있다. 하위 호환성 유지를 위해서 SQL 서버 2005에서 마이크로소프트는 sp_grantdbaccess 와 sp_adduser 프로시저를 재 작성 했으며 새로운 Data Definition Language (DDL) 문을 만들었다. 이에 대해서는 다음이 “새로운DDL" 부분에서 다룬다.
SQL 서버 2005에서 sp_grantdbaccess 는 사용자를 만들고 사용자 이름과 동일한 이름의 스키마를 만든다. 스키마는 사용자의 디폴트 스키마가 되며 사용자는 스키마의 소유자가 된다. 예를 들어 다음 프로시저를 수행한다고 해 보자.
SQL 서버 2005 는 이것을 내부적으로 새로운 DDL문장으로 해석하는데 [리스트 1]이 이를 보여준다. 뿐만 아니라 SQL 서버 2005는 내부적으로 sp_revokedbaccess sue 를 다음과 같이 다시 쓴다.
SQL 서버 2005 는 사용자와 이름과 ID가 같은 스키마만 삭제한다.
디폴트 스키마 없이 CREATE USER 를 sp_grantdbaccess 대신 수행하면 디폴트 스키마는 dbo가 된다. SQL 서버 2005 는 사용자 이름과 같은 이름의 새로운 스키마를 만들지 않는다.
이제 준비하자
SQL 서버 2005가 출시되었을 때 즉시 업그레이드 할 계획이 없다고 할 지라도 사용자와 스키마를 지금부터 분리해서 생각해줄 것을 권고한다. SQL 서버 2000에서는 스키마는 항상 사용자의 이름과 같지만 권한을 부여할 때 사용자에게 부여하는 것이라는 것을 정확하게는 알고 있어야 한다. 스키마는 개체를 담고 있는 (그리고 그것의 적합성을 판단하는) 그릇일 뿐이다.
사용자와 스키마의 분리는 몇 가지 방법으로 관리를 쉽게 해준다. 가장 큰 이점은 사용자를 관리 하는 것이다. 특히 사용자를 삭제할 때 매우 편해진다. 뿐만 아니라, 여러 사용자 집합을 한 스키마의 소유자로 지정할 수 있는데 이때는 보조 principal(예를 들어 그룹이나 역할)을 스키마의 소유자로 지정하면 된다. 또, 여러 사용자가 같은 디폴트 스키마를 가져서 같은 이름 해결(name resolution)을 할 수 있다.
특정 모듈의 실행자로서 사용자를 지정 할 수 있는 능력은 개체를 액세스 하는데 더 많은 제어 능력을 주며 SQL 서버 2000의 서비스 팩 3를 능가하는 데이터베이스 간 소유권 체인에 있어서 많은 발전이다. 데이터베이스간 소유권 체인은 “전부 아니면 아무 것도(all-or-nothing)”의 해결책이었다: 소유권 체인은 데이터베이스 간에 적용되거나, 아니면 아예 안되거나 둘 중 하나였다. SQL 서버 2005의 발전된 제어력은 하나의 모듈 개발자가 어떤 모듈이 사용자가 실행할 수 있으며 어떤 모듈이 사용자가 실행할 수 없는지를 결정할 수 있게 해 준다.
이런 향상된 점들은 유콘 보안 사양의 빙산의 일각일 뿐이다. 새로운 사양에 대해 귀를 열어 놓고 기다리자. SQL 서버 2005 출시에 가까워 질수록, 이런 사양들에 대해 나는 더 깊이 파고 들 것이다.
[리스트 1] SQL 서버 2005가 번역한 sp_grantdbaccess
새로운DDL
SQL 서버 2005 는 스키마를 다룰 수 있는 새로운 문장들을 제공한다. 여기에 완전한 긴 문법을 적어서 지면을 차지하기 보다는 간단히 요약하여 적을 것이고 더 자세한 것은 SQL 서버 2005를 설치한 후에 온라인 설명서를 참조하는 것이 좋겠다.
CREATE SCHEMA를 가지고 새로운 스키마를 만들 수 있으며 이와 함께 옵션으로 AUTHORIZATION 절을 사용하여 스키마의 소유자를 지정할 수 있다. AUTHORIZATION 절을 사용하지 않으면 CREATE SCHEMA를 수행하는 사용자가 스키마의 소유자가 된다. 소유자는 다른 스키마를 소유할 수 있으며 소유한 스키마 외에 다른 스키마를 디폴트 스키마로 가질 수도 있다.
ALTER SCHEMA 문은 스키마 소유자를 재설정 할 수 있게 해준다. (한 시점에 한 principal 만이 스키마의 소유자가 될 수 있지만 principal은 SQL 서버 사용자, 윈도우 사용자, 윈도우 그룹, SQL 서버 역할 중 어느 것이든 가능하다). 스키마에 TAKE OWNERSHIP 권한을 가진 사용자만이 스키마의 소유권을 가질 수 있다. db_owner 역할의 구성원은 스키마 소유권을 변경할 수 있다.
DROP SCHEMA 는 아무 개체도 담고 잇지 않은 스키마를 제거한다. 스키마에 단 하나의 개체라도 담겨 있으면, 스키마 삭제는 실패한다.
또한 SQL 서버 2005 는 사용자와 함께 동작하는 새로운 DDL(Data Definition Language) 문을 가지고 있다. 이전에 저장 프로시저로 하던 것들을 이제는 CREATE 과 ALTER 문장으로 대신할 수 있다. CREATE USER는 새로운 사용자를 만들며 선택적으로 로그인 명과 연결시킬 수 있다. 또한 디폴트 스키마를 지정할 수 있다. ALTER USER 는 사용자 이름을 바꾸거나 사용자의 디폴트 스키마를 변경할 수 있다.
출처: Windows .NET [2004년 6월호]
"MSSQL" 카테고리의 다른 글
- 지금 SQL 서버에서는 어떤 문제들이 벌어지고 있을까? (1)2007/05/21
- SQL Server 2000에서 varchar와 char 데이터 타입 (0)2007/05/21
- SQL 서버 2005 보안 (0)2007/05/21
- 새로운 SQL 서버 로그인 생성하기 (0)2007/05/21
- SQL Server 2000에서 update시 join의 활용 (0)2007/05/21
Leave your greetings.