DDA | Query to extract primary and secondary roles – Support and administration guides

This query returns, for each Blackboard user, their primary system role and any secondary system roles assigned to them.

The primary role is obtained from the users table, while the secondary roles are retrieved from domain_admin. When a user has multiple secondary roles, they are grouped into a single column separated by ;.

SELECT
    u.user_id,
    sr.system_role AS "Primary System Role",
    STRING_AGG(da.system_role, ';') AS "Secondary System Roles"
FROM system_roles sr
INNER JOIN users u
    ON u.system_role = sr.system_role
INNER JOIN domain_admin da
    ON u.pk1 = da.user_pk1
-- WHERE
--    sr.system_role = 'ROLCODE'
--    OR da.system_role = 'ROLCODE'
GROUP BY
    u.user_id,
    sr.system_role
ORDER BY
    u.user_id;

The query returns three fields:

user_id: the user's identifier in Blackboard.
Primary System Role: the primary system role assigned to the user.
Secondary System Roles: the list of secondary roles associated with the user.

The WHERE block is left commented out so that the query can be reused generically. If you need to locate only users who have a specific role, either as a primary or secondary role, you can enable it and replace ROLCODE with the code for the corresponding role.

Note: because INNER JOIN is used with domain_admin, the query only returns users who have at least one secondary role recorded in that table. You can replace this INNER JOIN with a LEFT JOIN if you want to retrieve all users.

Queries are offered without any kind of guarantee. The code worked when we created this articule, but due to changes in a constantly evolving software, we cannot assure its accuracy at the moment in time when is tested in your system.

Search

#happiness exists

DDA | Query to extract primary and secondary roles

Comments