Get user's roles and role's users

prrvchr

unread,

Mar 30, 2024, 11:41:03 AMMar 30

to H2 Database

Hi all,

I'm looking to get the users of a role as well as the roles of a user.

I can do this with HsqlDB with the following two queries respectivly:

SELECT GRANTEE FROM INFORMATION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS WHERE ROLE_NAME=?

SELECT ROLE_NAME FROM INFORMATION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS WHERE GRANTEE=? AND ROLE_NAME!=GRANTEE

But I couldn't find anything for H2, thanks for your help...

Message has been deleted

Evgenij Ryazanov

unread,

Mar 30, 2024, 10:22:37 PMMar 30

to H2 Database

Hi!

Take a look on GRANTEE and GRANTEDROLE columns in non-standard INFORMATION_SCHEMA.RIGHTS table where GRANTEETYPE = 'USER' and GRANTEDROLE IS NOT NULL.

Please note that a role can be granted to another role. These records have GRANTEETYPE = 'ROLE' and GRANTEDROLE IS NOT NULL. Users have transitive permissions from these roles.

prrvchr

unread,

Apr 9, 2024, 10:52:30 AMApr 9

to H2 Database

Hi,

Thank you for this information, it allowed me to integrate the management of roles and rights into Base for H2. I give you the queries that I use, this may possibly benefit other people.

Get a user's roles:

SELECT GRANTEDROLE FROM INFORMATION_SCHEMA.RIGHTS WHERE GRANTEETYPE = 'USER' AND RIGHTS IS NULL AND TABLE_SCHEMA IS NULL AND TABLE_NAME IS NULL AND GRANTEE = ?

Get the role's users:

SELECT GRANTEE FROM INFORMATION_SCHEMA.RIGHTS WHERE GRANTEETYPE = 'USER' AND RIGHTS IS NULL AND TABLE_SCHEMA IS NULL AND TABLE_NAME IS NULL AND GRANTEDROLE = ?

Get the roles of a role:

SELECT GRANTEE FROM INFORMATION_SCHEMA.RIGHTS WHERE GRANTEETYPE = 'ROLE' AND RIGHTS IS NULL AND TABLE_SCHEMA IS NULL AND TABLE_NAME IS NULL AND GRANTEDROLE = ?

Get rights for a table and a role

SELECT CASE WHEN LOCATE('SELECT', RIGHTS) = 0 THEN 0 ELSE 1 END, CASE WHEN LOCATE('INSERT', RIGHTS) = 0 THEN 0 ELSE 2 END, CASE WHEN LOCATE('UPDATE', RIGHTS) = 0 THEN 0 ELSE 4 END, CASE WHEN LOCATE('DELETE', RIGHTS) = 0 THEN 0 ELSE 8 END, CASE WHEN LOCATE('ALL', RIGHTS) = 0 THEN 0 ELSE 15 END FROM INFORMATION_SCHEMA.RIGHTS WHERE GRANTEE = ? AND TABLE_SCHEMA = ? AND TABLE_NAME = ?