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 = ?
If there are any errors, please correct me...
This will be available in the next version of jdbcDriverOOo
<https://prrvchr.github.io/jdbcDriverOOo/#what-has-been-done-for-version-131>
.
Le dimanche 31 mars 2024 à 04:22:37 UTC+2, Evgenij Ryazanov a écrit :
> 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.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/d3b25cde-ebc9-4575-92e2-e2987e4366abn%40googlegroups.com.
