23-06-2020 09:52 AM
Hi guys,
Could you name a query statement that lists all roles and users in the same table?
Any help would be much appreciated!
Best
Moritz
Solved! Go to Solution.
23-06-2020 11:37 AM
I think now I got you - there´s a table for that as well:
select * from exa_dba_role_privs;
GRANTEE is the USER , GRANTED_ROLE is the role - if you need the roles as a list you might go for a group_concat(GRANTED_ROLE)
23-06-2020 02:04 PM
You could also use DBeaver which provides exactly the function you're looking for (sorry, most of it is in German but I think it's self-explanatory; the tool itself also supports English):
To get this overview DBeaver fires this SQL to the database which you can of course execute manually and is quite similar to Malte's queries:
select r.*,p.ADMIN_OPTION,p.GRANTEE from EXA_DBA_ROLES r INNER JOIN EXA_DBA_ROLE_PRIVS p ON p.GRANTED_ROLE = r.ROLE_NAME ORDER BY P.GRANTEE,R.ROLE_NAME
23-06-2020 10:41 AM
btw. if you meant "for the same table" it´s more along the lines of: select * from exa_dba_obj_privs where object_name='your-table-here';
23-06-2020 10:12 AM
you mean like this:
select role_name as roles_and_users from exa_all_roles
union all
select user_name from exa_dba_users;
?
23-06-2020 11:32 AM
Hi mwellbro,
Thanks for your quick reply!
I'd like to see the user in the first column and the corresponding role in the second column.
For instance, Peter Berg - DBA or Daniel Kahnemann - Developer.
Basically, this would be a join of the tables from above. However, I wonder how to do it.
Best
Moritz
23-06-2020 11:37 AM
I think now I got you - there´s a table for that as well:
select * from exa_dba_role_privs;
GRANTEE is the USER , GRANTED_ROLE is the role - if you need the roles as a list you might go for a group_concat(GRANTED_ROLE)
23-06-2020 02:00 PM
Thank you very much!
Moritz
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In