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
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.
