MS-SQL Server

프로시저 내 동적쿼리와 권한...

알 수 없는 사용자 2008. 3. 13. 11:33

어제 밤, 프로시저와 동적쿼리에 관련하여 깊은 고찰을 하게 만든 일이 발생하였습니다.
상황을 시뮬레이션 하자면 다음과 같습니다.

계정 doddaria_reader 데이터베이스 DB_01 에 대한 권한은 db_datareader 입니다. (정적 쿼리에 한하여 오브젝트의 select 권한만 할당되어 있다는 얘기)

EXEC sp_addlogin 'doddaria_reader', 'bangga810720'
EXEC sp_adduser 'doddaria_reader','doddaria_reader','db_datareader'

 해당 계정에 대해 별도로 SP 실행 권한을 부여했습니다.

GRANT execute ON testSP_Update TO doddaria_reader

프로시저 testSP_Update 내부에는 정적쿼리로 된 테이블B,C의 Insert /Update문이 존재하고, 동적쿼리로 된 테이블A의 Update 문이 하나 존재합니다.
다음과 같이 프로시저를 실행해 보았습니다.

exec testSP_Update 'DODDARIA'

결과는 다음과 같습니다.

서버: 메시지 229, 수준 14, 상태 5, 줄 1
'dbo' 소유자, 'db_01' 데이터베이스, 'A' 개체에 대한 UPDATE 사용 권한이 거부되었습니다.

B,C 에 대한 DML문은 에러없이 수행되었지만, 마지막의 동적쿼리 A에 대한 DML 문은 에러가 발생하였습니다.
일차적인 문제는 프로시저 내에 동적쿼리를 사용하였다는 점이지만, 다른 테이블 B,C에 대한 DML문은 문제없이 처리하였으면서, 왜 이것만 에러를 뱉어 냈는지 궁금했습니다.
결론은 프로시저 실행 권한이 있더라도 SP 내부의 동적 쿼리에서는 해당 오브젝트에 대한 권한(db_owner)이 없으면 DML 문은 동작하지 않는다는 것입니다.

이와 관련된 몇가지 참고자료 정리하였습니다

왜 저장 프로시저를 사용하는가?

동적 SQL이 실제로 무엇인가를 살펴보기 전에, 왜 저장 프로시저를 사용해야 하는지를 먼저 알아볼 필요가 있다. 저장 프로시저를 사용하지 않고 클라이언트 (※ 역주: 여기서의 클라이언트는 SQL 서버에 대한 클라이언트를 의미합니다. 웹프로그램 개발의 경우 웹서버가 여기에 해당하며, 클라이언트 코드는 ASP와 같은 Server Side Script를 의미합니다.) 혹은 (COM+와 같은) 중간층(middle layer)에서 직접 SQL 문으로 이루어진 명령문을 내보내는 복잡한 응용프로그램을 작성할 수도 있다. 세련되어 보이기 때문에 저장 프로시저를 사용하는 것은 아니며, 이에는 분명 장점이 존재한다.

1. 권한 체계 (The Permission System)

저장 프로시저는 사용자들에게 데이타에 대한 제한적인 접근을 허용케하는 전통적인 수단이다. 쿼리 분석기와 같은 도구를 이용할 경우 원하는 어떠한 작업도 수행가능하므로, 사용자들은 SELECT, INSERT, UPDATE 혹은 DELETE 같은 문장을 직접 실행할 수 있는 권한을 가져서는 안된다. 예를 들어, 권한을 가진 사용자가 직원 데이타베이스에 접근하게 될 경우, 쉽게 봉급을 인상시킬 수 있다. 저장 프로시저를 사용하면, 해당 프로시저의 실행은 프로시저 소유자의 권한을 이용하게 되므로, 사용자들은 테이블에 대한 직접적인 권한을 필요로 하지 않게 된다.

요즘은 이 상황에 대해 몇가지 선택 가능한 사항이 더 존재한다. 사용자 계정에 직접 권한을 부여하기 보다는, 비밀번호가 필요한 고정 서버 역할(application role)에 접근권한을 부여할 수 있으며, 그런 비밀번호를 응용프로그램 안에 숨겨둘 수 있다. SQL 서버에서 고정 서버 역할은 7.0 버전부터 지원되었으며, COM+와 같은 중간층을 사용하면 사용자가 SQL 서버에 직접 접근할 필요가 없다는 점에서 더 안전하다.

하지만 고정 서버 역할이나 COM+ 등을 사용하지 않는다면, SQL 서버 보안 측면에서 저장 프로시저는 여전히 중요한 의미를 가진다.

2. 실행계획 캐쉬 (Caching Query Plans)

저장 프로시저를 사용하는 다른 중요한 이유는 성능을 향상시키기 위해서이다. 저장 프로시저가 최초로 실행되면, SQL 서버는 해당 프로시저에 대한 실행계획을 생성시키며, 이 실행계획이 캐쉬에 저장된다. 해당 저장프로시저가 재실행 요청을 받으면, SQL 서버는 저장된 실행계획을 재사용한다. 실행계획이 만료되거나 혹은 SQL 서버가 새로운 실행계획을 생성시켜야 할 이유가 생길 때까지 해당 실행계획은 캐쉬에 유지된다. (이 과정은 프로시저가 실행되는 동안에 일어나는데, 여기에 대한 논의는 이 기사의 범위를 범어난다.)
중략...

저장프로시저를 사용해야 하는 이유 중 이제껏 가장 중요하게 생각해왔던 것은 두번째 실행계획 캐쉬 되겠습니다.
하지만 어제 밤 발생한 문제는 1번과 관련이 깊어 보입니다.
즉, 계정 관리 시 오브젝트에 직접 DML 실행 권한을 부여함으로써 일어날 수도 있는 참담한 사태를 사전에 방지하기 위하여 프로시저 실행 권한을 주어 프로시저 내의 DML만 처리가 가능하도록 하고, 쿼리 분석기 등을 통한 직접적인 DML 은 막겠다는 의도로 보입니다.

관련 문서를 또 잠시 참조해보면 다음과 같습니다.

EXEC()와 sp_executesql

MS SQL 서버에서 동적 SQL을 실행시키는 2가지 방법은 EXEC()와 sp_executesql이다.

EXEC()
EXEC()는 다음 예제와 같이 그 사용법이 아주 간단하다.:

   SELECT @table = ''sales'' + @year + @month
   EXEC(''SELECT * FROM '' + @table)

비록 위의 예가 상당히 단순해 보이지만, 여기에는 놓쳐서는 안될 중요한 점이 존재한다. 첫번째 중요한 점은 비록 해당 문장이 저장 프로시저 내에 존재하더라도 현재 사용자의 권한으로 실행된다는 것이다. 두번째로 중요한 것은 EXEC()가 저장 프로시저의 실행을 위한 EXEC와 매우 유사하다는 점이다. 하지만 이 예제에서는 저장 프로시저를 호출하는 대신에, 단일 SQL 문을 일괄실행시켰다. 마치 저장 프로시저를 호출할 때처럼, 해당 일괄실행문은 호출하는 저장 프로시저와는 다른 실행범위(scope)를 가지게 된다. 여기에는 몇가지 중요한 의미가 내포되어 있다.
중략...

이래서 어젯 밤 프로시저 내 동적쿼리의 UPDATE가 실행되지 않았군요......
이 기초적인 것을 몰라서 세 시간이나 삽질을 하고 있었습니다...ㅠ_ㅠ
새로운 것을 알았습니다;

이 외에 다른 좋은 내용이 많아서, 관련 자료 링크합니다..
http://pointnet.tistory.com/tag/exec