Show Exasol Roles and Users

[Moritz]

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 

[mwellbro]

you mean like this:

select role_name as roles_and_users from exa_all_roles
union all
select user_name from exa_dba_users;

?

[mwellbro]

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';

[Moritz]

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  

[mwellbro · Accepted solution]

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)

[Moritz]

Thank you very much!

Moritz

[kristof]

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