ALERT! The community will be read-only starting on April 19, 8am Pacific as the migration begins. Read more for important details.
ALERT! The community will be read-only starting on April 19, 8am Pacific as the migration begins.Read more for important details.
Commodore Commodore
Commodore
111 views

Retrieving Modules with Security Access Data

Jump to solution

Hello,

I'm trying to gather a list of our modules and what orgs/users have access to them.  Has anyone done this?  I found the DSH tables, but it doesn't seem to show security for the Modules.  

Thanks,

Jajcen 

0 Likes
1 Solution

Accepted Solutions
Micro Focus Expert
Micro Focus Expert

Hi Jajcen,

I think that the data you're looking for is hiding in tables DSH_AUTHORIZED_GROUPS, DSH_AUTHORIZED_USERS and  DSH_AUTHORIZED_ORG_UNITS.   

You can join them with table DSH_MODULES using MODULE_ID column. Here's a starter SQL query, but you're likely to need some changes to get exactly what you're looking for:

SELECT
    m.name             AS "MODULE_NAME",
    m.description      AS "MODULE_DESCRIPTION",
    m.module_id,
    'ORG UNIT' AS "ACCESS_TYPE",
    ou.org_unit_id     AS "ACCESS_ID",
    ou.org_unit_name   AS "ACCESS_NAME"
FROM
    dsh_modules                m,
    dsh_authorized_org_units   aou,
    krsc_org_units             ou
WHERE
    m.entity_id IS NULL
    AND m.entity_type IS NULL
    AND m.module_id = aou.module_id
    AND aou.org_unit_id = ou.org_unit_id
UNION
SELECT
    m.name                   AS "MODULE_NAME",
    m.description            AS "MODULE_DESCRIPTION",
    m.module_id,
    'SECURITY_GROUP' AS "ACCESS_TYPE",
    sg.security_group_id     AS "ACCESS_ID",
    sg.security_group_name   AS "ACCESS_NAME"
FROM
    dsh_modules             m,
    dsh_authorized_groups   ag,
    knta_security_groups    sg
WHERE
    m.entity_id IS NULL
    AND m.entity_type IS NULL
    AND m.module_id = ag.module_id
    AND ag.group_id = sg.security_group_id
UNION
SELECT
    m.name          AS "MODULE_NAME",
    m.description   AS "MODULE_DESCRIPTION",
    m.module_id,
    'USER' AS "ACCESS_TYPE",
    u.user_id       AS "ACCESS_ID",
    u.username      AS "ACCESS_NAME"
FROM
    dsh_modules            m,
    dsh_authorized_users   au,
    knta_users             u
WHERE
    m.entity_id IS NULL
    AND m.entity_type IS NULL
    AND m.module_id = au.module_id
    AND au.user_id = u.user_id

View solution in original post

2 Replies
Micro Focus Expert
Micro Focus Expert

Hi Jajcen,

I think that the data you're looking for is hiding in tables DSH_AUTHORIZED_GROUPS, DSH_AUTHORIZED_USERS and  DSH_AUTHORIZED_ORG_UNITS.   

You can join them with table DSH_MODULES using MODULE_ID column. Here's a starter SQL query, but you're likely to need some changes to get exactly what you're looking for:

SELECT
    m.name             AS "MODULE_NAME",
    m.description      AS "MODULE_DESCRIPTION",
    m.module_id,
    'ORG UNIT' AS "ACCESS_TYPE",
    ou.org_unit_id     AS "ACCESS_ID",
    ou.org_unit_name   AS "ACCESS_NAME"
FROM
    dsh_modules                m,
    dsh_authorized_org_units   aou,
    krsc_org_units             ou
WHERE
    m.entity_id IS NULL
    AND m.entity_type IS NULL
    AND m.module_id = aou.module_id
    AND aou.org_unit_id = ou.org_unit_id
UNION
SELECT
    m.name                   AS "MODULE_NAME",
    m.description            AS "MODULE_DESCRIPTION",
    m.module_id,
    'SECURITY_GROUP' AS "ACCESS_TYPE",
    sg.security_group_id     AS "ACCESS_ID",
    sg.security_group_name   AS "ACCESS_NAME"
FROM
    dsh_modules             m,
    dsh_authorized_groups   ag,
    knta_security_groups    sg
WHERE
    m.entity_id IS NULL
    AND m.entity_type IS NULL
    AND m.module_id = ag.module_id
    AND ag.group_id = sg.security_group_id
UNION
SELECT
    m.name          AS "MODULE_NAME",
    m.description   AS "MODULE_DESCRIPTION",
    m.module_id,
    'USER' AS "ACCESS_TYPE",
    u.user_id       AS "ACCESS_ID",
    u.username      AS "ACCESS_NAME"
FROM
    dsh_modules            m,
    dsh_authorized_users   au,
    knta_users             u
WHERE
    m.entity_id IS NULL
    AND m.entity_type IS NULL
    AND m.module_id = au.module_id
    AND au.user_id = u.user_id

View solution in original post

Commodore Commodore
Commodore

Excellent exactly what I was looking for! I saw these tables and couldn't see how to connect them.  The documentation in the data model isn't clear at all for this either...

 


Thanks so much!

 

Jajcen

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.