Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-4346

SQL Server - Fail connector when a user doesn't have the right permission (CDCReader)

    XMLWordPrintable

Details

    • False
    • False

    Description

      I'm using a user "testuser" which has "db_datareader" role (so he can read the table), but he's not part of the "CDCReader" role used when creating the capture:

      EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'customers', @role_name = 'CDCReader', @supports_net_changes = 0;
      We can verify that the user has no CDC permission by calling sys.sp_cdc_help_change_data_capture

      The connector starts successfully, do the snapshot successfully (because of the db_datareader role), but then it simply display in logs this WARN:

      [2021-07-16 09:50:10,649] WARN No table has enabled CDC or security constraints prevents getting the list of change tables (io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource)
      [2021-07-16 09:50:10,651] WARN No whitelisted table has enabled CDC, whitelisted table list does not contain any table with CDC enabled or no table match the white/blacklist filter(s) (io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource)

      Therefore all updates will be ignored silently by the connector. Some customers prefer to have the connector failing rather than displaying a WARN only. 

      Note that debezium code already check CDC permission using sys.sp_cdc_help_change_data_capture, this is what allows it to print those WARN

      Attachments

        Activity

          People

            Unassigned Unassigned
            nathan.nam Nathan Nam (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: