이하의 내용은 저희 회사에서 제가 사용하는 방법으로... 당연한 얘기지만 이 방법이 모든 경우에 적합하다고 볼 수는 없습니다.
첫번째 고민 : Windows 인증을 사용할까? SQL Server 인증을 사용할까?
일반적인 권고는 Windows 인증입니다.
아무래도 여러사람에 의해 공유되는 SQL Server 인증은 관리면이나 보안면에서 좋지 않을테니까요.
하지만 SQL Server 인증을 아예 사용하지 않는 것도... 현실적으론 어렵더군요.
- 웹 서버, 미들웨어 서버 등의 어플리케이션 서버는 SQL Server 인증을 사용합니다.
두번째 고민 : 개발 DB에서 개발자의 권한은 어느 정도가 적당하지?
저는 데이터베이스에 접속해서 개발하는 사람을 세 부류로 나눴습니다.
- SQL 개발자 : db_owner이지만 SP, Function, Trigger를 제외한 다른 개체에대한 DDL문 사용 권한이 없습니다.
- 개발자 : data_reader & data_writer이고, 모든 SP에 대한 Execute와 View Definition 권한이 있습니다.
SQL 개발자와 개발자는 Database Role 개체로 생성한 후, 각 사람의 도메인 로그인을 해당 Role에 매핑하여 관리합니다.
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name]=N'Developer' AND [type]='R')
CREATE ROLE [Developer] AUTHORIZATION [dbo];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name]=N'SQLDeveloper' AND [type]='R')
CREATE ROLE [SQLDeveloper] AUTHORIZATION [dbo];
GO
EXEC sp_addrolemember N'db_datareader', N'Developer';
EXEC sp_addrolemember N'db_datawriter', N'Developer';
EXEC sp_addrolemember N'db_owner', N'SQLDeveloper';
GO
GRANT VIEW DEFINITION ON OBJECT::[SP이름] TO [Devolper];
GRANT EXECUTE ON OBJECT::[SP이름] TO [Devolper];
세번째 고민 : 어플리케이션 서버용 SQL Server 로그인의 권한은?
사실 권한 정책 자체는 심플합니다. 특정 계정에 필요 이상의 권한을 주지 않는다.
그렇다면 어플리케이션 서버가 가져야하는 권한은 어느 정도일까요?
SP를 Execute할 수 있는 권한 이것 하나만 주고 싶군요.
저는 어플리케이션 서버의 로그인을 [AppServer]라는 Database Role에 속하도록 설정하고, [AppServer] Role은 모든 SP에 대해 Execute 권한을 가지도록 하고 있습니다.
즉, SP가 Create 될 때마다 아래 구문이 실행되도록 합니다.
GRANT EXECUTE ON OBJECT::[SP이름] TO [AppServer];
네번째 고민 : SP를 Execute할 권한이 있는데도 SP 실행이 실패한다.
동적 쿼리를 사용하는 경우입니다.
SP에 대한 실행 권한이 있다해도 동적 쿼리에 포함된 개체에 대해 충분한 권한이 없다면 해당 SP는 실행이 안됩니다.
이런 경우 Execute AS 절을 사용하면 SP를 실행할 때만 충분한 권한이 있는 User로 가장 (impersonate) 시킬 수 있습니다.
이 때 생성한 로그인의 패스워드는 GUID를 생성하여 셋팅하는데...
이유는... 누구도 이 계정으로 로그인할 일이 없기 때문입니다.
오로지 SP 실행을 위한 계정일 뿐입니다.
DECLARE @vchPW varchar(50);
SET @vchPW = NEWID();
IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE [name] = N'SPExecutor')
EXEC ('CREATE LOGIN [SPExecutor] WITH PASSWORD=''' + @vchPW + ''', DEFAULT_DATABASE=master;');
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name]=N'SPExecutor' AND [type]='S')
CREATE USER [SPExecutor] FOR LOGIN SPExecutor WITH DEFAULT_SCHEMA=[dbo];
GO
EXEC sp_addrolemember 'db_owner', N'SPExecutor';
GO
GRANT IMPERSONATE ON USER::SPExecutor TO AppServer;
GRANT IMPERSONATE ON USER::SPExecutor TO Developer;
GRANT IMPERSONATE ON USER::SPExecutor TO SQLDeveloper;
GO
다섯번째 고민 : Execute AS 절을 사용했더니, 데이터베이스 간의 개체 참조가 되지 않는다.
모듈 안에서 Execute AS 절을 사용하는 경우, 다른 데이터베이스에 있는 개체를 참조할 수 없게 되는 문제가 생깁니다.
보안 상의 이슈로 데이터베이스간의 소유권 체인을 불허하는 것이 Default이기 때문이죠.
데이터베이스간 소유권 체인을 허용하는 것은... 상황에 따라 문제가 될 수도 있고 그렇지 않을 수도 있습니다.
문제가 되지 않는다면 TRUSTWORTHY 옵션을 설정하여 데이터베이스간의 소유권 체인을 허용할 수 있는데... 이렇게 하면 Execute AS 절을 사용하더라도 다른 데이터베이스의 개체를 참조할 수 있습니다.
(물론 다른 데이터베이스에도 SPExecutor가 User로 등록되어 있어야 합니다.)
ALTER DATABASE [DB이름] SET TRUSTWORTHY ON;
Sequel Safe에서는 이상의 셋팅이 포함되어 있습니다.