[MSSQL] CDC Role

 

 

 

Role

Enalbe/Disable for Database

We must enable database before enable CDC on individual table.

Product	Role
SQL Server	sysadmin
Azure SQL Server	db_owner
Azure SQL Managed Instance	sysadmin

 

 

Enable/Disable for Table

Only `db_owner` can enable or disable CDC for individual table

 

Access to a change table

CDC 활성화시 다음과 같은 구문을 사용하는데

@role_name 을 지정하도록 되어있다.

-- =========  
-- Enable a Table Without Using a Gating Role template   
-- =========  
USE MyDB  
GO  
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'MyTable',  
@role_name     = NULL,  -- ✅ role name 지정, 이 role 을 가진 user 에게 CDC 데이터 접근 권한이 주어짐.
@supports_net_changes = 1  
GO

 

그래서 CDC 테이블 접근 권한 role은 누구에게만 주어지나?

`sysadmin` `db_owner` 권한 유저는 항상 CDC 테이블에 접근 가능하고

그 외에는  `@role_name` 에서 지정한 role 을 가진 유저여야 접근 가능하다.

 

Users must have SELECT permission on all the captured columns of the source table. In addition, when a role is specified, users who are not members of either the sysadmin or db_owner role must also be members of the specified role.
- Microsoft document

 

위와 같이 @role_name  `NULL`로 설정한 경우, `sysadmin`, `db_owner` 만 접근 가능하다.

 

'role_name' Is the name of the database role used to gate access to change data. 
role_name is sysname and must be specified.
If explicitly set to NULL, no gating role is used to limit access to the change data.
- Microsoft document

 

---

기존에 존재하는 role_name 인 경우 해당 role 에 접근 권한이 할당되고

새로운 role_name 지정시 새로운 role 이 자동 생성된다.

이 때, 새로운 ROLE 에 Source 테이블에 대한 SELECT 권한이 부여 되야만 fn_ 함수를 통해 CDC 테이블에 접근 가능하다.

 

 

함수 없이 직접 CDC 데이터가 담긴 테이블에 접근 하는 방식은 다음과 같다.

SELECT __$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, created_at, updated_at, __$command_id
FROM cdc.<schema-nam>_<table-name>_CT
WHERE __$operation <> 3 -- 변경된 신규 데이터만 조회
ORDER BY __$start_lsn, updated_at DESC;

 

Q. 위 구문으로 부터 생성된 role 은 태초에 어떤 권한을 갖는가?

A. 생성된 함수를 조회할 수 있는 권한만 갖는다.

 

따라서 별도로 source table 또는 CDC table 에 SELECT 이상 권한을 부여해야한다.

 

---

 

CDC 활성화시 Role 별 권한

user 가 다음 role 을 가지면 어떤 권한이 부여되는지 요약해보자.

Role	Enable/Disable database	Enable/Disable Table	Access CDC table using function	Access CDC Table using _CT table
sysadmin	✅		✅	✅
db_owner		✅	✅	✅
specific_role (created)			✅ (After GRANT `SELECT` on source DB to this role)	✅ (After GRANT `SELECT` on source CDC Table DB to this role)
public	Not reommended grant CDC access on this role

 

 

 

 

 

To support net changes queries, the source table must have a primary key or unique index to uniquely indentify rows.

+

The columns defined in the PK or UK must be included in the list of source columns to be captured.

> CDC 활성화 이후에 PK || UK 추가한 것은 반영되지 않는다.

 

 

 

 

 

Reference 

Enable and Disable change data capture - SQL Server