Skip to main content
Announcements
Marching toward a simplified navigation! READ ON

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

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
dima_etkin
Support
Support

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

Last Update:

Nov 9, 2022 4:03:25 AM

Updated By:

Sonja_Bauernfeind

Created date:

Nov 8, 2022 2:18:23 PM

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.

 

Labels (1)
Version history
Last update:
‎2022-11-09 04:03 AM
Updated by: