Hi Evgenij Ryazanov, I am aware of the requirements for this integration into Base. Thank you for answering it, it will be good for the LibreOffice / OpenOffice suites and will make them a little less moribund...
To get the list of users, I now use INFORMATION_SCHEMA.USERS table with the query: SELECT USER FROM INFORMATION_SCHEMA.USERS To get the list of roles, I can use INFORMATION_SCHEMA.ROLES table with the query: SELECT ROLE_NAME FROM INFORMATION_SCHEMA.ROLES or your can create a new view INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS, as it suits you. To get the hierarchy and users of roles, although I know nothing about it, I believe that there is no defined standard, but I need a view allowing me to establish the tree of roles as well as to know the roles of a user. I have a problem with java.sql.DatabaseMetaData.getUserName(), it seems to me that it returns a empty or blank user with a database created during the first connection. I also don't see a like DBA role allowing the administration of privileges and such, it would make things easier if there was one. Thank you for your contribution. Le samedi 3 septembre 2022 à 06:58:49 UTC+2, Evgenij Ryazanov a écrit : > 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/c65b51c7-0ff8-40c7-b331-328867759bdcn%40googlegroups.com.
