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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/632e9586-ad71-4180-9601-1e26a3c0b286n%40googlegroups.com.
