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

·

5 min read

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.