Hi! On Friday, 2 September 2022 at 23:40:02 UTC+8 prrvchr wrote:
> > - java.sql.DatabaseMetaData.getUserName() should return the user who > is logged in (current user). > > This method is implemented in H2 itself properly. You can also use standard CURRENT_USER expression in SQL. > - In order to obtain all the users declared on the database the > INFORMATION_SCHEMA.SYSTEM_USERS table is used. This table normalized > by JDBC must list all the users whose current user... The query is: SELECT > USER_NAME FROM INFORMATION_SCHEMA.SYSTEM_USERS > > There is no such table or view in the SQL Standard. INFORMATION_SCHEMA is not related to JDBC in any way, it is covered only by the SQL Standard, Part 11: Information and Definition Schemas (SQL/Schemata). H2 has own non-standard table INFORMATION_SCHEMA.USERS, you need to use it instead. > > - In order to obtain all the roles declared on the database the > INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS table is used. This > table normalized by JDBC should list all roles, including default system > roles... The query is: SELECT ROLE_NAME FROM > INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS > > This view is a part of the SQL Standard. Database systems with optional features T331, “Basic roles” and F391, “Long identifiers” should also provide this view. We can add this view to H2. > > - in order to obtain the hierarchy of roles as well as the users > assigned to roles since there is no standardized table in JDBC, I need > a table in INFORMATION_SCHEMA allowing to establish these relations. With > HsqlDB, fredt provided me with the > INFORMATION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS table which lists all > the > GRANTEE and ROLE_NAME and allows me to establish the relationships... > > There is no such table or view is the SQL Standard. There is a DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS table, but according to the Standard, DEFINITION_SCHEMA is not accessible to applications even if it exists. Applications should use views in INFORMATION_SCHEMA based on this table, such as INFORMATION_SCHEMA.APPLICABLE_ROLES, INFORMATION_SCHEMA.ENABLED_ROLES and others, but they don't provide complete information. We can add these standard views to H2, but we shouldn't add ROLE_AUTHORIZATION_DESCRIPTORS. In the current H2 all available information is provided in INFORMATION_SCHEMA.USERS, INFORMATION_SCHEMA.ROLES, and INFORMATION_SCHEMA.GRANTS. These tables are non-standard and non-portable. https://h2database.com/html/systemtables.html#information_schema_users https://h2database.com/html/systemtables.html#information_schema_roles https://h2database.com/html/systemtables.html#information_schema_rights -- 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 h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/632e9586-ad71-4180-9601-1e26a3c0b286n%40googlegroups.com.
