MSSQL 로그인 및 사용자(DBA, Developer, Application) 권한 정책

1. 로그인 및 사용자 권한 정책

(1) Windows 인증을 사용할까? SQL Server 인증을 사용할까? 

- Windows 도메인 로그인을 가지고 있는 사람들... DBA, 개발자와 같은 이들에게는 Windows 인증을 사용합니다.

- 웹 서버, 미들웨어 서버 등의 어플리케이션 서버는 SQL Server 인증을 사용합니다.

 

(2) 개발 DB에서 개발자의 권한은 어느 정도가 적당하지?

① 개발 DBA : sysadmin입니다. 해당 데이터베이스에서 작업의 제약을 받지 않습니다.

CREATE LOGIN [DBADeveloper] WITH PASSWORD=N'P@ssw0rd'; GO   EXEC sp_addsrvrolemember N'DBADeveloper', N'sysadmin'; GO

 

② SQL 개발자 : db_owner이지만 SP, Function, Trigger를 제외한 다른 개체에 대한 DDL문 사용 권한이 없습니다.

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_owner', N'SQLDeveloper'; GO

 

③ 개발자 : data_reader & data_writer이고, 모든 SP에 대한 Execute와 View Definition 권한이 있습니다.

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name]=N'Developer' AND [type]='R')     CREATE ROLE [Developer] AUTHORIZATION [dbo]; GO   EXEC sp_addrolemember N'db_datareader', N'Developer'; EXEC sp_addrolemember N'db_datawriter', N'Developer'; GO

 

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

GRANT VIEW DEFINITION ON OBJECT::[SP이름] TO [Developer]; GRANT EXECUTE ON OBJECT::[SP이름] TO [Developer];

 

(3) 어플리케이션 서버용 SQL Server 로그인의 권한은?

- 특정 계정에 필요 이상의 권한을 주지 않습니다.

- SP를 Execute할 수 있는 권한만 부여합니다.

 

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

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

 

(4) 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

 

(5) Execute AS 절을 사용했더니, 데이터베이스 간의 개체 참조가 되지 않는다.

보안 상의 이슈로 데이터베이스간의 소유권 체인을 불허하는 것이 Default이기 때문에 모듈 안에서 Execute AS 절을 사용하는 경우, 다른 데이터베이스에 있는 개체를 참조할 수 없게 되는 문제가 생깁니다.

데이터베이스간 소유권 체인을 허용하는 것은 상황에 따라 문제가 될 수도 있고 그렇지 않을 수도 있는데, 문제가 되지 않는다면 TRUSTWORTHY 옵션을 설정하여 데이터베이스간의 소유권 체인을 허용할 수 있는데 이렇게 하면 Execute AS 절을 사용하더라도 다른 데이터베이스의 개체를 참조할 수 있습니다.

ALTER DATABASE [DB이름] SET TRUSTWORTHY ON;

 

단, 이 경우 SPExecutor 사용자 개체가 특정 로그인에 매핑되어 있어야 합니다.

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

IF NOT EXISTS(SELECT name FROM master.sys.server_principals WHERE name = N'SPExecutor') BEGIN     CREATE LOGIN [SPExecutor] WITH PASSWORD=N'P@ssw0rd'; END GO USE [DB이름]; GO IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = N'SPExecutor') BEGIN     CREATE USER [SPExecutor] FOR LOGIN [SPExecutor] WITH DEFAULT_SCHEMA=[dbo]; END GO