Forum Discussion

Phil_Rosier's avatar
Phil_Rosier
Level 4
9 years ago

Report of Data Insight Users

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.

Data Insight
Information Governance
Reporting
Windows Server (2003-2008)
  • Phil_Rosier's avatar
    Phil_Rosier
    9 years ago

    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
    ;

     

  • Rishi_Thaper's avatar
    Rishi_Thaper
    Level 4
    9 years ago

    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's avatar
    Phil_Rosier
    Level 4
    9 years ago

    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's avatar
    DLP_Solutions2
    Level 3
    9 years ago

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

    Thanks

     

    Ronak

  • Phil_Rosier's avatar
    Phil_Rosier
    Level 4
    9 years ago

    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's avatar
    DLP_Solutions2
    Level 3
    9 years ago

    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