Debezium Oracle grants

[Gergely Jahn]

Hi Community,

We're setting up Debezium with Oracle in an organisation where the following grants would violate security concerns:

GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL;
GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL;

GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL;

GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL;

It would be fine to grant FLASHBACK and SELECT on the table we would like to mine.

As far as I understand LOCK is only necessary for initial snapshot(schema snapshots) and if we can guarantee that no schema changes will occur, we don't need the LOCK. We just need to set "snapshot.locking.mode": "none".

In terms of  SELECT ANY TRANSACTION  the documentation says:
When FLASHBACK ANY TABLE is granted, this should also be granted. Does this mean if we only grant FLASHBACK to specific tables we don't need SELECT ANY TRANSACTION?
If we still need it what are the implications if we can't get it?

Thank you in advance,
Gergely Jahn

[Chris Cranford]

Hi -

While the documentation uses ANY TABLE for these, yes you can restrict these to the specific tables you intend to capture. It just becomes your responsibility to guarantee that the connector user has the proper grants for all tables that your include configuration specifies or you will most likely not see any changes for those tables, and that may result in data loss.

Now as for SELECT ANY TRANSACTION, this is the one caveat here that's critical. This grant allows the connected user to have access to the flashback area, various LogMiner performance views, and the ability to read any historical data in the database. If this grant is not provided, according to Oracle's documentation, you would be unable to use LogMiner to gather changes from the past.

Hope that helps.
-cc

--
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 visit https://groups.google.com/d/msgid/debezium/f9df5eaf-28c4-43e1-b2bc-70de2a13a501n%40googlegroups.com.

[Gergely Jahn]

Hi Chris,

Thank you for your response. In terms of SELECT ANY TRANSACTION I did some more research:

I tried to setup the connector without this privilege and it was able to take the initial snapshot and mine upcoming changes.
My connector config:
{
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "database.hostname": "oracle",
    "database.port": "1521",
    "database.user": "c##dbzuser",
    "database.password": "dbz",
    "database.dbname": "FREE",
    "decimal.handling.mode": "double",
    "topic.prefix": "CHECKSIGN",
    "topic.creation.default.partitions": "5",
    "topic.creation.default.replication.factor": "1",
    "key.converter": "org.apache.kafka.connect.storage.StringConverter",
    "value.converter": "io.confluent.connect.avro.AvroConverter",
    "value.converter.schema.registry.url": "http://schemaregistry:8081",
    "table.include.list": "BASEADM.PARTNER",
    "snapshot.max.threads": "1",
    "snapshot.locking.mode": "none",
    "tombstones.on.delete": "false",
    "schema.history.internal.kafka.bootstrap.servers": "broker:29092",
    "schema.history.internal.kafka.topic": "schema-changes.checksign",
    "snapshot.mode": "initial",
    "log.mining.strategy": "redo_log_catalog",
    "custom.metric.tags": "table=partner"
}

Debezium queries the actual SCN when it takes the initial snapshot and executes a flashback query on the table with this SCN.
Then it queries the redo log with the same SCN to process upcoming changes.

The classic LogMiner session queries V$LOGMNR_CONTENTS and based on this documentation this should require the above grant, but somehow it does not.
I've checked the latest version of the document where there is no explicit mention for SELECT ANY TRANSACTION, instead it says:
"You must have the EXECUTE_CATALOG_ROLE role and the LOGMINING privilege to query the V$LOGMNR_CONTENTS view and to use the LogMiner PL/SQL packages."

So it looks like SELECT ANY TRANSACTION is not necessary anymore with Oracle 12c+ onwards.

Does this make sense, or do I miss something?

Thanks in advance,
Greg

You received this message because you are subscribed to a topic in the Google Groups "debezium" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/debezium/o4zflYdAXAU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/9514c942-d720-444c-b7de-3713070e0694%40gmail.com.

[Gergely Jahn]

[Chris Cranford]

Hi Greg -

Nope, if that's the case that's great news. Perhaps we can condense the grant requirements in that case in the documentation.
Thanks for the confirmation!

Thanks,
-cc

To view this discussion visit https://groups.google.com/d/msgid/debezium/CANSmHXUa5QRVqJDQNY7D31woeOfBKkJFtz8PEEsF4HQ9LK7BEg%40mail.gmail.com.