Debezium Oracle Source Connector privileges

[Katerina Chorianopoulou]

Hello everyone,

I am working with an Oracle database and want to use Debezium to capture change data. However, the database administrators have concerns about the privileges required after enabling minimal supplemental logging. They have asked whether the following grants are necessary if the log mining is isolated to specific schemas (not whole database) since they do not normally provide them to non-admin users:

GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL;
GRANT EXECUTE_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL;
GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL;

Would it be possible for someone from the Debezium community to provide guidance on whether these privileges are necessary?

[Chris Cranford]

Hi Katerina -

I've put together a quick blurb for each permission, hope this helps:

FLASHBACK ANY TABLE
This can safely be scoped to the tables you are capturing changes for. This is only used during the initial snapshot where flashback queries are used.

SELECT ANY TABLE
This can safely be scoped to the tables you're capturing as long as the following are true:
  * V$ tables described in the documentation are readable by the connector user.
  * All tables in the connector user's table space are readable and writable.

SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
This is required by Oracle LogMIner as well as when we use the DBMS_METDATA.GET_DDL method.

SELECT ANY TRANSACTION
This is required unless you explicitly configure "log.mining.transaction.snapshot.boundary.mode=skip".

LOCK ANY TABLE
By default, this is required. If you configure "schema.history.internal.store.only.captured.tables.ddl=true", this can safely be scoped to the table's that are being captured only.  You can also set "snapshot.locking.mode=none" if you want to skip applying any types of locks when gathering the schema; however, understand that no DDL changes should occur in the system while the snapshot is occurring when disabling locks.

Thanks,
Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/e3d2a38e-b295-4414-b854-e174beb34272n%40googlegroups.com.

[Chris Cranford]

Hi Katerina -

I can definitely see how that sentence may cause some confusion, so happy to clarify. 

It's quite common to have multiple users from an application stack responsible for changing various data in the database.  If the database is shared across multiple applications, each application or microservice may even have its own user account.  The Oracle connector will happily mine and capture changes performed by any of those users.  The only caveat with respect to user accounts is the SYS and SYSTEM accounts.  These two accounts are considered special because Oracle performs a lot of internal changes with these users, so we explicitly exclude these two user accounts from the capture.  So as long as the mutation of data occurs with non SYS and SYSTEM accounts, the changes will be picked up by the connector assuming that SUPPLEMENTAL LOGGING has been configured properly for the tables being captured.

If you'd like to send in a small documentation PR to adjust that sentence to be more clear based on the above, please feel free.

Thanks,
Chris

On 3/22/23 06:18, Katerina Chorianopoulou wrote:

Hello Chris, 

Thank you a lot for your reply. I have one more question which doesn't seem very clear.

Does the DB user that is used by Debezium and is granted all these permissions need to be the same as  the user that performs the DB changes that we want to capture?

For example, we have a couple of users that interact with our schema from our application stack and we need to capture all these changes. It would seem logical to be able to capture changes made by other users, however in the documentation here https://debezium.io/documentation/reference/stable/connectors/oracle.html#setting-up-oracle it is mentioned that "The connector captures database changes that are made by its own Oracle user account." 

Would you be able to clarify that?

Thank you in advance,

Kate

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/d0bf374c-7470-4381-a5f8-780eaad1852bn%40googlegroups.com.