Qlik Replicate: Extract all Oracle source endpoint permissions for a specific userId

dima_etkin — Wed, 09 Nov 2022 09:03:25 GMT

This article has been created to assist Qlik Replicate customers identify any permissions missing for the purpose of the replication. As it stands now, extracting all of the permissions for a specific user may become a somewhat tedious task. To help extract all of the permissions for a specific userID please execute the query below. Please substitute the <userId> placeholder with the actual User Id connecting to the database from the replicate server:

select a.* from (select 'ROLE' typ, grantee grantee, granted_role priv, admin_option ad, '--' tabnm, '--' colnm, '--' owner, r.password_required pwd from dba_role_privs rp join dba_roles r on rp.granted_role = r.role union select 'SYSTEM' typ, grantee grantee, privilege priv, admin_option ad, '--' tabnm, '--' colnm, '--' owner, '--' pwd from dba_sys_privs union select 'TABLE' typ, grantee grantee, privilege priv, grantable ad, table_name tabnm, '--' colnm, owner owner, '--' pwd from dba_tab_privs union select 'COLUMN' typ, grantee grantee, privilege priv, grantable ad, table_name tabnm, column_name colnm, owner owner, '--' pwd from dba_col_privs) a where a.grantee = '<UserId>' order by case when a.typ = 'ROLE' then 4 when a.typ = 'SYSTEM' then 1 when a.typ = 'TABLE' then 2 when a.typ = 'COLUMN' then 3 else 5 end, case when a.priv in ('EXECUTE') then 1 when a.priv in ('SELECT', 'UPDATE', 'INSERT', 'DELETE') then 3 else 2 end, a.tabnm, a.colnm, a.priv;

Environment

Oracle source Endpoint

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

article Qlik Replicate: Extract all Oracle source endpoint permissions for a specific userId in Official Support Articles

Qlik Replicate: Extract all Oracle source endpoint permissions for a specific userId

Environment