Oracle 19C as source

[Pranita123]

Hello Community,

I'm currently working with Oracle 19c as a data source in Qlik. In the documentation, it suggests assigning Sysasm rights to the user used in Replicate. Could you please explain the purpose of this and provide insight into how it works at the backend?  Could you help clarify the necessity and security implications of granting Sysasm rights as dba is not agree to give permission

Thank you.

[SachinB]

Hello @Pranita123 ,

Thank you for reaching out to the Qlik Community !

Replicate requires ASM access permissions in order to read the online redo logs from ASM (SYSASM or SYSADM privilege). This is because reading the online redo logs from ASM is performed using a function from the Oracle DBMS_DISKGROUP package, which requires the SYSASM or SYSADM privileges.

From Oracle 11g Release 2 (11.2.0.2), Qlik Replicate must be granted the SYSASM privilege in order to access the ASM account. For older supported versions, granting Qlik Replicate the SYSDBA privilege should be sufficient.

Also validate ASM account access by opening a command prompt and issuing the
following statements:

sqlplus asmuser/asmpassword@+asmserver as sysdba

OR

sqlplus asmuser/asmpassword@+asmserver as sysasm


Regards,

Sachin B

[Pranita123]

Hi @SachinB ,

Thank you for the response.

We are aware about mentioned thing, but dba want more detail for the same.

If there is any alternative way then please suggest.

Regards,

Pranita

[SachinB]

Hello @Pranita123 ,

There is no other way.  These are privilege required in order to read the online redo logs from ASM .This is because reading the online redo logs from ASM is performed using a function from the Oracle DBMS_DISKGROUP package, which requires the SYSASM or SYSADM privileges.

UG Link : https://help.qlik.com/en-US/replicate/November2022/Content/Global_Common/Content/SharedReplicateHDD/...

Regards,

Sachin B

[SushilKumar]

Hello Team,

Add more to Tse comments. As Your DBA may aware as ASM disk are RAW disk and Read and Write Operations Performed By ASM process. Without those privileges to Process Access which specific to ASM require which requires the SYSASM or SYSADM privileges.  Mainly used to perform read and Access require related to task initialization to read Redo and archive logs stored in +ASM Disk groups. 

Hope it helps.

Regards,

Sushil Kumar

[Heinvandenheuvel]

>> as dba is not agree to give permission

With he information above you should have a better formulated request for the the DBA.

Or maybe you were not the right person to ask.

Many DBA's forget they are 'just' servants. With the best intentions they get overly protective about 'their' database. It's not their database.  The business owns the database. If the business decides to use Replicate t5hen they should facilitate. Period.  Replicate has requirements. It becomes the DBA's duty to fulfill those requirement. The DBA does NOT get to decide. The DBA can 'advice' against it, they can try to offer alternatives (for Replicate?), that all fine. But if they refuse to give SYSASM access when asked by the business then they should be fired.

And yes I've been there. Fighting with dozen's of well intended DBA's. We had to have a letter from "the King" telling them "hand over the corporate jewels to the Replication team  - they are on a corporate approved mission'. And yes we explained why, and show we would mitigate any perceived security risk.

Cheers, Hein.

[john_wang]

Hello @Pranita123 ,

I totally agree with @Heinvandenheuvel . 

Qlik Replicate reads files from ASM (API) by calling dbms_diskgroup packages, and this requires at least SYSASM (or SYSDBA). These calls cannot be made without SYSASM/SYSDBA privilege. This is an Oracle requirement rather than Qlik Replicate restriction. Personally I do not think this is negotiable however there are alternative ways which maybe help:

1- Add one more Destination ID in Oracle database and direct the redo log files to OS level file system (rather than ASM); or

2- Copy redo log files from ASM instance and write to OS file system archived redo log files (out of Replicate) then Replicate reads the archived redo log files via the Replicate Log Reader access mode;

3- Using  Oracle LogMiner access mode. However this option may be is not supported, depends on your Oracle installation and setup. You know in Oracle 19c there is a chance to install non-container mode, within this mode the LogMiner is available for Qlik Replicate still.  If it's a Multitenant environment then LogMiner is not supported. (from Oracle 21c it's impossible anymore, all installations are container mode, no way to use LogMiner in Qlik Replicate).

Again, maybe these are not the best answer for you, as it's some intrusive to the original database, or performance is not good, grants the proper privileges to Qlik Replicate is highly recommended.

Regards,

John.

[Pranita123]

Hello @john_wang  thank you for response,

Sysdba permission is approved by them so is that okay to proceed with only this permission 

Regards ,

Pranita.

[john_wang]

Hello @Pranita123 ,

Thanks for the feedback.

From Qlik Replicate User Guide, Replicate must be granted the SYSASM privilege in order to access the ASM account. With SYSDBA maybe works but it's not official certified/QAed scenario.

Regards,

John.

[deepaksahirwar]

Hi @Pranita123 ,

The SYSASM privilege is required for Qlik Replicate to access the Automatic Storage Management (ASM) account in Oracle1. This is necessary because reading the online redo logs from ASM is performed using a function from the Oracle DBMS_DISKGROUP package, which requires the SYSASM or SYSADM privileges2.

The SYSASM privilege is needed from Oracle 11g Release 2 (11.2.0.2) onwards1. For older supported versions, granting Qlik Replicate the SYSDBA privilege should be sufficient1.

As for the security implications, granting SYSASM rights provides Qlik Replicate with the necessary permissions to read the online redo logs from ASM. However, it’s important to note that these privileges should be granted judiciously, as they provide significant control over the database. It’s always a good practice to follow the principle of least privilege, i.e., grant only the minimum permissions necessary for Qlik Replicate to function properly.

If your DBA has concerns about granting these permissions, it might be helpful to have a discussion about the specific functionalities that Qlik Replicate requires these permissions for, and any potential risks involved. It’s also worth noting that Qlik provides detailed documentation on the required permissions for working with an Oracle source1,

Thanks,

Deepak