Summary/Reported Issue
- User needs assistance extracting a list of roles and roles/users mapped to roles in Dremio.
Relevant Versions
Dremio 25.2.x
Steps to Resolve
The answer to this question will vary a little bit depending on what you need and what your environment looks like. For example, if you have external users/roles, you may not get a complete set of data from a query to these tables.
But assuming you have are using internal users/roles, I do this in a couple of ways.
If I only want to check the users and their assigned roles, I use the following query:
SELECT user_name, role_name
FROM sys.users
JOIN sys.membership
ON user_name = member_name
ORDER BY user_name;
But if you want to list all users even if they have not assigned a role, you can run this query:
select c.user_name, b.role_name
from sys.roles a
join sys.membership b
on a.role_name = b.role_nameright
join sys.users c on b.member_name = c.user_name;
If you also need to validate all the roles assigned to a role, that would just be the output of `sys.membership` filtered out by `member_type` being `ROLE`. So, you can just union the output of that query to the previous query where we filtered out users:
SELECT member_name, role_name, member_type
FROM sys.users
JOIN sys.membership
ON user_name = member_name
UNION
select member_name, role_name, member_type
from sys.membership
where member_type = 'ROLE';
Keep in mind, there are other ways of getting the relevant information, but it depends on what exactly you require the output to look like.