SQL Server 로그인 및 사용자 권한 정책

오늘은 SQL Server 사용자 권한 정책에 대해 얘기 해 보겠습니다.

이하의 내용은 저희 회사에서 제가 사용하는 방법으로... 당연한 얘기지만 이 방법이 모든 경우에 적합하다고 볼 수는 없습니다.


첫번째 고민 : Windows 인증을 사용할까? SQL Server 인증을 사용할까?

일반적인 권고는 Windows 인증입니다.
아무래도 여러사람에 의해 공유되는 SQL Server 인증은 관리면이나 보안면에서 좋지 않을테니까요.
하지만 SQL Server 인증을 아예 사용하지 않는 것도... 현실적으론 어렵더군요.

- Windows 도메인 로그인을 가지고 있는 사람들... DBA, 개발자와 같은 이들에게는 Windows 인증을 사용합니다.
- 웹 서버, 미들웨어 서버 등의 어플리케이션 서버는 SQL Server 인증을 사용합니다.

두번째 고민 : 개발 DB에서 개발자의 권한은 어느 정도가 적당하지?

저는 데이터베이스에 접속해서 개발하는 사람을 세 부류로 나눴습니다.

- 개발 DBA : sysadmin입니다. 해당 데이터베이스에서 작업의 제약을 받지 않습니다.
- 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

[Developer] Role이 모든 SP에 대한 Execute 및 View Definition 권한을 갖도록 하기 위해... DDL 트리거를 만들고 아래 구문을 포함시킵니다. 즉, SP가 Create 될 때마다 아래 구문이 실행되도록 합니다.

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 권한을 가지도록 하고 있습니다.

[AppServer] Role이 모든 SP에 대한 Execute 권한을 갖도록 하기 위해... DDL 트리거를 만들고 아래 구문을 포함시킵니다.
 즉, SP가 Create 될 때마다 아래 구문이 실행되도록 합니다.

GRANT EXECUTE ON OBJECT::[SP이름] TO [AppServer];

네번째 고민 : SP를 Execute할 권한이 있는데도 SP 실행이 실패한다.

동적 쿼리를 사용하는 경우입니다.

SP에 대한 실행 권한이 있다해도 동적 쿼리에 포함된 개체에 대해 충분한 권한이 없다면 해당 SP는 실행이 안됩니다.

이런 경우 Execute AS 절을 사용하면 SP를 실행할 때만 충분한 권한이 있는 User로 가장 (impersonate) 시킬 수 있습니다.

SP를 실행할 때 사용할 User로 [SPExecutor]를 만들어 [db_owner] 권한을 가지도록 하고,
[AppServer], [Developer], [SQLDeveloper] Role이 [SPExecutor]를 가장할 수 있도록 GRANT문을 실행합니다.

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name]=N'SPExecutor' AND [type]='S')

    CREATE USER [SPExecutor] WITHOUT LOGIN 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 사용자 개체가 특정 로그인에 매핑되어 있어야 합니다.

위 "네번째 고민"의 예제에서 SPExecutor 사용자를 생성할 때 WITHOUT LOGIN 옵션을 사용했는데..
지금처럼 데이터베이스 간의 참조를 허용해야하는 경우라면  SPExecutor 로그인을 먼저 생성하고
SPExecutor 사용자를 생성할 때 FOR LOGIN [SPExecutor] 옵션을 주어야 합니다.

ALTER DATABASE [DB이름] SET TRUSTWORTHY ON;

Sequel Safe에서는 이상의 셋팅이 포함되어 있습니다.

2009/04/01 - [Sequel Safe] - [Sequel Safe] 개요
2009/04/02 - [Sequel Safe] - [Sequel Safe] 설치 및 개발 환경 셋업
2009/04/11 - [Sequel Safe] - [Sequel Safe] SQL 서버 Login 권한 정책 및 물리 모델 변경 제약