In this article and from CDC official document, I will outline the specific permissions I’ve tested and used in my environment, as admin privileges are not allowed in Oracle.
Windows User Roles
Computer Administrator: Oracle CDC Service
The computer administrator is a Windows user responsible for creating and maintaining the CDC Service on the computer. This user must belong to the group of local machine administrators.
Service Account: Oracle CDC Service
This Oracle CDC Service Windows Service Account is a Windows account used for running the Oracle CDC Service (the Service Account).
The only required privilege for the service account is the ability to use the
Oracle client
and theSQL Server native client ODBC
provider.This account does not need to access files unless required by specific providers. For example, if the Oracle client connection string references Oracle database instances in a
tnsnames.ora
file, then that file must be readable by the service account.
SQL Server User Roles
Oracle CDC Service Administrator
The CDC Service Administrator is a SQL Server user with full control over the Oracle CDC Service artifacts in the target SQL Server instance. The CDC Service Administrator uses the Oracle CDC Designer Console to design Oracle CDC Instances.
The CDC Service Administrator should be granted the SQL Server fixed server roles public
and dbcreator
.
The CDC Service Administrator is, at least initially, in the db_owner
fixed database role for the SQL Server CDC database associated with the Oracle CDC Instance. This gives the CDC Service Administrator access to the change data stored in the CDC database.
System Administrator
The SQL Server system administrator is a SQL Server user and should be granted the fixed server sysadmin
role on the SQL Server instance associated with the Oracle CDC Service(s).
There is only one Oracle CDC specific task that carried out with the SQL Server System Administrator and that is to enable the SQL Server database for an Oracle CDC Instance for SQL Server CDC.
Oracle CDC Service User
The SQL Server Oracle CDC Service user is a SQL Server login which is used by the Oracle CDC Service to perform its work against the MSXDBCDC
and all of the Oracle CDC Instances (CDC databases) handled by this service.
The SQL Server Oracle CDC Service user should be granted the following:
Member of the fixed database roles db_dlladmin, db_datareader, and db_datawriter for all CDC databases handled by the server.
Member of the fixed database roles db_datareader and db_datawriter for the
MSXDBCDC
database.
Because the Oracle CDC Service uses a single SQL Server login to work with all CDC databases and the MSXDBCDC
database, this login should be mapped in all of these databases.
Oracle CDC Change Consumer
The Oracle CDC Change Consumer is a SQL Server user that consumes changes stored in the CDC tables in the SQL Server Oracle CDC Instance database.
This user determines the user role that is required for accessing each of the CDC tables through the CDC functions generated by the SQL Server CDC infrastructure. If no user role is specified when a capture instance is specified, access to the changes is limited to the member of the db_owner fixed database role of the CDC database.
Oracle User Roles
Database Administrator (DBA)
The Oracle database administrator (DBA) is an Oracle database user. The tasks performed by the Oracle DBA include:
Setting the source Oracle database to work in ARCHIVELOG mode.
Setting up a log mining user with the required permissions.
Setting supplemental logging for captured tables.
Helping to restore archived transaction log files no longer available so they can be processed.
Log Mining User
The credentials for this user are stored in the SQL Server Oracle CDC Instance database using asymmetric key encryption. They are accessible only to the Oracle CDC Service but not to the SQL Server Oracle CDC Instance database owner.
Schema User
The Oracle Schema User is an Oracle user with read access to the schema of the Oracle tables to be captured. This user is necessary when working with the Oracle CDC Designer console to retrieve the list of Oracle schema, tables to be captured and their columns, indexes and keys.
From my experience, I have found some useful scripts to get user privileges in Oracle, along with additional permission requirements that are not in the official documentation.
Get user privileges
SELECT * FROM session_privs; SELECT on <any-captured-table> SELECT ANY TRANSACTION EXECUTE on DBMS_LOGMNR SELECT on V$LOGMNR_CONTENTS SELECT on V$ARCHIVED_LOG SELECT on V$LOG SELECT on V$LOGFILE SELECT on V$DATABASE SELECT on V$THREAD SELECT on ALL_INDEXES SELECT on ALL_OBJECTS SELECT on DBA_OBJECTS SELECT on ALL_TABLES
Additional Permissions
For CDC on Oracle
select * from V$LOGMNR_LOGS select * from V$PARAMETER select * from DBA_REGISTRY select * from V$INSTANCE SELECT ON V_$LOGMNR ???
For SSMA
CREATE ANY PROCEDURE EXECUTE ANY PROCEDURE SELECT ANY TABLE SELECT ANY SEQUENCE CREATE ANY TYPE CREATE ANY TRIGGER
For SQL Replication (the db_owner role should cover these requirements)
CREATE SESSION CREATE TABLE CREATE PUBLIC SYNONYM DROP PUBLIC SYNONYM CREATE VIEW CREATE SEQUENCE CREATE PROCEDURE CREATE ANY TRIGGER
Conclusion
In conclusion, understanding and implementing the correct permissions and roles for SQL Server CDC for Oracle
is crucial for ensuring a secure and efficient data capture process. By clearly defining the responsibilities and privileges of each user role, from Windows and SQL Server to Oracle, you can maintain a robust and compliant environment. The detailed breakdown of roles such as the Oracle CDC Service Administrator, System Administrator, and Log Mining User, among others, provides a comprehensive guide to setting up and managing CDC services effectively. Additionally, the inclusion of useful scripts and additional permissions highlights practical insights that go beyond official documentation, making this guide an invaluable resource for database administrators and developers working with SQL Server CDC for Oracle
.