Unlocking SQL Server CDC for Oracle: Essential Permissions and Roles Explained

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 the SQL 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.