Azure SQL의 데이터베이스 소유권 체인

Azure SQL의 데이터베이스 소유권 체인

 

·         Version : Azure SQL

 

Azure SQL Database Managed Instance에서는 SQL Server와 동일한 방식으로 데이터베이스간 쿼리를 실행할 수 있다. 또한 데이터베이스간 소유권 체인을 지원한다. 이번 포스트에서는 Managed Instance의 소유권 체인에 대해서 다루어 본다.

 

데이터베이스간의 소유권 체인을 사용하면 명시적 권한이 객체에 부여되지 않더라도SQL 인스턴스의 다른 데이터베이스에 있는 객체에 액세스 할 수 있다. DB_CHAINING옵션이 데이터베이스에서 설정되어 있는 경우 동일한 소유자로 다른 데이터베이스에 액세스할 수 있다.  

DB_CHAINING is disabled by default on new databases because you need to be aware what it exactly do and does it violates some security policy in your system before you explicitly enable it.   DB_CHAINING은 시스템의 일부 보안 정책을 위반하므로 기본적으로 비활성화 되어있다.

 

여러 데이터베이스의 여러 오브젝트에서 동일한 소유자가 있고 이 오브젝트에 액세스하는 스토어드 프로시저가 있는 경우 프로시저가 액세스해야하는 모든 오브젝트에 대한 액세스 권한을 GRANT할 필요가 없다. 프로시저와 오브젝트의 소유자가 동일한 경우 프로시저에 대한 GRANT 권한을 부여할 수 있으며 데이터베이스 엔진은 프로시저가 동일한 소유자를 공유하는 다른 모든 개체에 액세스 할 수 있게 한다.

아래 예제에서는 데이터에 액세스하는데 사용되는 동일한 소유자 로그인이 있는 두 개의 데이터베이스를 생성한다. PrimaryDatabase 는DataTable 이라는 테이블을 가지고 있으며,  SecondaryDatabase는 PrimaryDatabase의 DataTable 에서 데이터를 읽는 저장프로시저를 갖는다. 저장 프로시저를 실행하기 위해 로그인이 부여되었지만 테이블 데이터를 읽지는 못하였다.

-- Create two databases and a login that will call procedure in one database CREATE DATABASE PrimaryDatabase; GO CREATE DATABASE SecondaryDatabase; GO CREATE LOGIN TheLogin WITH PASSWORD = 'Very strong password!' GO   -- Create one database with some data table, -- and another database with a procedure that access the data table. USE PrimaryDatabase; GO CREATE PROC dbo.AccessDataTable AS BEGIN SELECT COUNT(*) FROM SecondaryDatabase.dbo.DataTable; END; GO CREATE USER TheUser FOR LOGIN TheLogin; GO GRANT EXECUTE ON dbo.AccessDataTable TO TheUser; GO   USE SecondaryDatabase; GO SELECT * INTO dbo.DataTable FROM sys.objects; GO CREATE USER TheUser FOR LOGIN TheLogin; GO

 

저장 프로시저를 사용하여 데이터를 읽으려고 하면 로그인에 테이블에 대한 GRANT 권한이 없기 때문에 오류가 발생한다.

EXECUTE('SELECT * FROM SecondaryDatabase.dbo.DataTable') AS LOGIN = 'TheLogin' ; GO -- Msg 229, Level 14, State 5, Line 34 -- The SELECT permission was denied on the object 'DataTable', database 'SecondaryDatabase', schema 'dbo'.

 

저장 프로시저를 사용하여 테이블에서 데이터를 읽으려고 하는 경우에도 동일한 문제가 발생한다.

EXECUTE('EXEC PrimaryDatabase.dbo.AccessDataTable') AS LOGIN = 'TheLogin' ; GO --Msg 229, Level 14, State 5, Procedure dbo.AccessDataTable, Line 5 [Batch Start Line 65] --The SELECT permission was denied on the object 'DataTable', database 'SecondaryDatabase', schema 'dbo'.

 

사용자에게 프로시저를 실행할 수 있는 권한은 있지만 데이터베이스 엔진은 해당 로그인에 대해 SecondaryDatabase의 테이블에서 읽을 수 있는 액세스 권한이 없으므로 쿼리를 차단한다. 아래 스크립트는 데이터베이스 소유권 체인을 활성화 한다.

ALTER DATABASE PrimaryDatabase SET DB_CHAINING ON; GO ALTER DATABASE SecondaryDatabase SET DB_CHAINING ON; GO

 

저장 프로시저를 통해 테이블에 다시 액세스하면 결과가 표시된다.

EXECUTE('EXEC PrimaryDatabase.dbo.AccessDataTable') AS LOGIN = 'TheLogin' ;

 

Managed Instance / Database Engine은 저장 프로시저와 테이블이 동일한 소유자를 가지며 DB_CHAINING이 켜져있기 때문에 테이블에 대한 액세스를 허용한다. 그러나 해당 로그인에 대해 직접적인 테이블에 액세스하는 권한은 없으므로 직접 테이블에 액세스 할 수는 없다.

EXECUTE('SELECT * FROM SecondaryDatabase.dbo.DataTable') AS LOGIN = 'TheLogin' ; GO --Msg 229, Level 14, State 5, Line 54 --The SELECT permission was denied on the object 'DataTable', database 'SecondaryDatabase', schema 'dbo'.

 

데이터베이스 소유권 체인이 유용할 뿐만 아니라 보안 관점에서 예기치 않은 동작이 발생할 수 있으므로 해당 옵션을 활성화 할때는 신중함이 필요하다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/08/17/database-ownership-chaining-in-azure-sql-managed-instance/

 

 

 

2018-09-06 / Sungwook Kang / http://sqlmvp.kr

 

Azure SQL,  Managed Instance, SQL, Azure SQL Database, SQL Server Security, DB_CHAINING