Query on "SELECT ANY TRANSACTION" privilege for Oracle data source

[P1341017]

Hi,

Our client has concerns in granting the "SELECT ANY TRANSACTION" privilege as requested here for Oracle data source.

Reference:
https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedReplicateHDD/Oracl...

Can you advise us the purpose why Qlik Replicate needs this privilege, and what's the impact if these queries are not granted?
Any alternative privilege that we can ask instead, as the concerns is that Qlik Replicate will be able to see the transactions that occur in all tables, while we need access and replicate for 5 tables only.

Appreciate your assistance.

[OritA]

Hi, 

In general Replicate is performing several queries to system table in order to get information it needs for reading the redo logs and performing its function. For getting more specitid details  please open a case for your specific question so we can check it internaly and get back to you with the requested information.

Thanks & regards,

Orit 

[Heinvandenheuvel]

It's the RULE. It's documented. Just do it. 

[SushilKumar]

Hello Team,

Request you to follow below link which permission is required to configure oracle as Source.

if you Do not put those Privileges in Source Oracle Database Qlik replicate will not work as designed.

https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedReplicateHDD/Oracl...

"SELECT ANY TRANSACTION" privilege as requested here for Oracle data source.

To get information about each version of the row in a transaction level, these privileges Allow Qlik user to query flashback_transaction_query view, hence Qlik user It is possible to get very detailed information in each transaction from the above query about the transaction being performed on the participating table.

Name of Column Description
XID Identifies the transaction. It is mainly used in flashback version query by joining its pseudo column VERSIONS_XID.
START_SCN System change number (scn) when transaction started
START_TIMESTAMP Timestamp when transaction started
COMMIT_SCN System change number (scn) when transaction committed
COMMIT_TIMESTAMP Timestamp when transaction committed
UNDO_CHANGE# Undo system change number
OPERATION DML operation that performed on the row
ROW_ID ROWID of the row that was modified by the DML
UNDO_SQL SQL command that reverts back the specified transaction that was performed with DML command indicated by the operation column

Q:2 while we need access and replicate for 5 tables only.

When you start the task, it creates the Table lists which are participating at the Task level. There is no mechanism to skip the Scanning for the tables (by any replication tool be it Oracle Golden gate or DMS) in REDO/archive Log. However, it keeps track of Changes and capture and process for the tables which are part of the replication setup.

When any information regarding any changes put into redo/archive Oracle Server process put it by OBJECT_ID not by the Table name and Qlik Replicate Got it that info from other privileges inside the database.

Hope this answers your Question.

Regards,
Sushil Kumar

[lyka · Accepted solution]

Hello,

GRANT SELECT ANY TRANSACTION TO <USER OR ROLE> is required to perform CDC. This will allow to query the contents of the FLASHBACK_TRANSACTION_QUERY view. Replicate needs this to select data from V_$LOGMNR_LOGS and V_$LOGMNR_CONTENTS

Thanks

Lyka

[Sreekanth_333]

Hello,

Do we need the SELECT ANY TRANSACTION permission if we are using Replicate Log Reader ? I think its needed only if we are using Oracle Log Miner. Correct if I'm wrong on this

Thanks

Sreekanth

[john_wang]

Hello @Sreekanth_333 ,

Qlik Replicate needs access to information about open transactions and transaction start events for the tables that are part of the replication setup. Without this access, Replicate cannot properly capture or track transaction status or the changes made to the participating tables. This permission is required for both the Replicate LogReader and Oracle LogMiner endpoints.

Hope this helps.

John.

[Sreekanth_333]

Hello, 

Can we provide SELECT on V$TRANSACTION instead of SELECT ANY TRANSACTION ?

[DesmondWOO]

Hi @Sreekanth_333 ,

I conducted a test and found that the CDC process appeared to work correctly even without the SELECT ANY TRANSACTION privilege. However, Qlik Replicate may still require this privilege to access additional transaction information from views such as FLASHBACK_TRANSACTION_QUERY in certain scenarios.

After discussing with John, we recommend opening a new ticket so that the R&D team can verify this behavior.

Regards,
Desmond

[Sreekanth_333]

Hello,

I have raised a case 00427401 on this. Thanks a lot for your support