Skip to main content

Official Support Articles

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
Feb 9th, Qlik Product Portfolio Strategy and Roadmap Webinar for Data Integration, last chance to REGISTER

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

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

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: