Hi, After consulting the H2 documentation <https://h2database.com/html/tutorial.html#creating_new_databases>, java.sql.DatabaseMetaData.getUserName() works if you provide a username on the first connection. I have no way to force provide a username during the Base connection wizard (File -> New -> Database -> Connect to an existing database -> H2 Driver) but I can specify it in the jdbcDriverOOo <https://prrvchr.github.io/jdbcDriverOOo/#how-to-create-a-new-database> user documentation.
Le samedi 3 septembre 2022 à 11:38:47 UTC+2, prrvchr a écrit : > 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/14616fca-9196-4ead-9150-51c09a3c7152n%40googlegroups.com.
