cancel
Showing results for 
Search instead for 
Did you mean: 

Report of Data Insight Users

Phil_Rosier
Level 4

Hi,

Apology this question is related to internal tables rather than DQL, but hopefully someone on here may be able to help.

I need to create an automated report or script that captures a list of the Data Insight users and their respective roles to enable regular reviews of who has access to the data insight console. We currently have a manual process using the GUI, but need to move to an unattended solution that captures this information, merges it with other records and submits the final report to an access management system where access to the Data insight console is reviewed and if necessary revoked.

Data Insight config.db has a table called “matrixuser” which contains details of the user accounts such as thier login and domain names, and a table called “role” which contains details of all the roles that are available in Data Insight --- but I can’t find anything that shows which users have been assigned to which roles.

Does anyone know how or where to find this

Kind regards

Phil.

1 ACCEPTED SOLUTION

Accepted Solutions

Phil_Rosier
Level 4

Hi Rishi, thanks for your advice. FYI, I made a couple of changes to the query as shown below to handle the role names so that they the same as displayed in the GUI.

Regards

Phil

SELECT 
     lower(matrixuser.login) AS LOGIN_NAME
    ,lower(matrixuser.domain) AS DOMAIN_NAME

    ,CASE
        WHEN role.name = 'mx_super_admin' THEN 'Server Administrator'
        WHEN role.name = 'mx_sys_admin' THEN 'Product Administrator'
        WHEN role.name = 'mx_storage_user' THEN 'Storage User'
        WHEN role.name = 'mx_user' THEN 'User'
        ELSE role.name
     END AS ROLE

                
FROM    rba
    
    INNER JOIN matrixuser ON matrixuser_id = rba.principal_id
    INNER JOIN role ON role.role_id = rba.role_id

WHERE 
    matrixuser.login != "matrixuser"
    AND rba.object_type = 1
;

 

View solution in original post

5 REPLIES 5

Rishi_Thaper
Level 4
Employee

Hi Phil,

Please find the query below that may help:

select m.login,m.domain,ro.name from matrixuser m, rba r, role ro where m.matrixuser_id= r.principal_id and r.role_id=ro.role_id and r.object_type=1

Regards,

Rishi

Phil_Rosier
Level 4

Hi Rishi, thanks for your advice. FYI, I made a couple of changes to the query as shown below to handle the role names so that they the same as displayed in the GUI.

Regards

Phil

SELECT 
     lower(matrixuser.login) AS LOGIN_NAME
    ,lower(matrixuser.domain) AS DOMAIN_NAME

    ,CASE
        WHEN role.name = 'mx_super_admin' THEN 'Server Administrator'
        WHEN role.name = 'mx_sys_admin' THEN 'Product Administrator'
        WHEN role.name = 'mx_storage_user' THEN 'Storage User'
        WHEN role.name = 'mx_user' THEN 'User'
        ELSE role.name
     END AS ROLE

                
FROM    rba
    
    INNER JOIN matrixuser ON matrixuser_id = rba.principal_id
    INNER JOIN role ON role.role_id = rba.role_id

WHERE 
    matrixuser.login != "matrixuser"
    AND rba.object_type = 1
;

 

DLP_Solutions2
Level 3
Partner Accredited

Is this a DQL report or are you running an external command/query??

Thanks

 

Ronak

Phil_Rosier
Level 4

Hi Ronak

Yes this is an external query to capture details who has access the Data insight console and thier respective roles within Data Insight. The query has evolved quite a bit since this posting as I also needed to include their name and employee ID details from the active directory records held in user database (user.db) and thier last logon details from the event logs.

It seems to be an increasingly common requirement to provide this type of information for Access Recertification purposes, to enable regular reviews of who has access to the applications and data deployed in thier environment. Unfortunately neither the GUI or DQL provides this so it's a case of writing SQLite queries to grab it from the configuration tables along with a batch script to automate the process of running the queries and transfering the output into thier Access Recertification systems.

Kind regards

Phil.

DLP_Solutions2
Level 3
Partner Accredited

Phil,

 

Can you post what you have at this point? Or send me a PM..

Especially if this is in a batch file.. please attach as a text file.

Thanks

 

Ronak