Management of users, roles and privileges in LibreOffice Base

[prrvchr]

Hi all,

I just finished the admin module allowing the management of users, roles and privileges in LibreOffice Base.

This integration could only be done with the help of fredt from hsqldb.org who provided me with the resources necessary for the successful completion of the module.

I am ready to make the effort to try to achieve the same level of integration of H2 in LibreOffice Base if you are willing to provide these necessary resources.

It only depends on you.

Thanks

Pierre

[Noel Grandin]

On 2022/08/24 3:10 pm, prrvchr wrote:
> I just finished the admin module <https://prrvchr.github.io/jdbcDriverOOo/#libreofficeopenoffice-base-improvement>

> allowing the management of users, roles and privileges in LibreOffice Base.

You are welcome to ask whatever questions you need to, and we will try and answer them, but nobody can make an
open-ended commitment to helping you.

H2 has

CREATE USER
https://h2database.com/html/commands.html#create_user

ALTER USER ADMIN
https://h2database.com/html/commands.html#alter_user_admin

ALTER USER RENAME
https://h2database.com/html/commands.html#alter_user_rename

ALTER USER SET PASSWORD
https://h2database.com/html/commands.html#alter_user_set_password

CREATE ROLE
https://h2database.com/html/commands.html#create_role

DROP ROLE
https://h2database.com/html/commands.html#drop_role

GRANT RIGHT
https://h2database.com/html/commands.html#grant_right

GRANT ALTER ANY SCHEMA
https://h2database.com/html/commands.html#grant_alter_any_schema

GRANT ROLE
https://h2database.com/html/commands.html#grant_role

REVOKE ROLE
https://h2database.com/html/commands.html#revoke_role

SET PASSWORD
https://h2database.com/html/commands.html#set_password

[prrvchr]

Hi Noel,

I am ready to make the necessary effort...

In addition to the commands you linked, here is what it would take to successfully implement user and role management in LibreOffice/OpenOffice Base:

• java.sql.DatabaseMetaData.getUserName() should return the user who is logged in (current user).
• 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
• 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
• 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...

Thanks for any help...

[Evgenij Ryazanov]

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

[prrvchr]

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.

[prrvchr]

Hi,

After consulting the H2 documentation, 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 user documentation.

[prrvchr]

Latest jdbcDriverOOo commit supports forcing uppercase names of users and roles when creating them.

The fact that the INFORMATION_SCHEMA.TABLE_PRIVILEGES table is empty after creating a table seems normal because there is no default DBA system role.

And this imposes in Base after the creation of a table, to use menu Tools -> SQL to manually execute an SQL command: GRANT ALL ON "NewTable" TO SA to allow privilege assignment by SA in the user administration GUI

In fact it seems to me that it will be difficult to do without a default DBA system role...

Le dimanche 4 septembre 2022 à 20:24:36 UTC+2, prrvchr a écrit :

By doing a little more testing, I realize that the table INFORMATION_SCHEMA.TABLE_PRIVILEGES has no content...

It seems to me that I am the administrator (the user who created the database), can someone confirm that this table is indeed empty?

Thanks.

[Evgenij Ryazanov]

Database administration is out of scope of the SQL Standard and there are no predefined roles is the Standard. In the Standard, every schema has an owner and this owner may perform DDL commands within this schema. Anything else is database-specific.

In H2, the most of DDL commands require schema owner privileges. Schema owner can be specified with standard command:

https://h2database.com/html/commands.html#create_schema

And it can be read from standard INFORMATION_SCHEMA.SCHEMATA.SCHEMA_OWNER.

Few commands in H2 need access to JVM, they require non-standard ADMIN privileges due to security reasons.

Users with these privileges can be created with non-standard CREATE USER command with ADMIN clause:

https://h2database.com/html/commands.html#create_user

You can check whether some user is ADMIN or not in non-standard INFORMATION_SCHEMA.USERS.IS_ADMIN column.

When database is created, user gets ADMIN privileges automatically. If they aren't required for normal operations, it is recommended to create and use a user with lower privileges.

H2 also has special non-standard ALTER ANY SCHEMA privileges, they give access to commands with required schema owner privileges in any schema.

They can be granted or revoked with non-standard commands:

https://h2database.com/html/commands.html#grant_alter_any_schema

https://h2database.com/html/commands.html#revoke_alter_any_schema

You can check non-standard INFORMATION_SCHEMA.RIGHTS for presence of these privileges.

If 'RIGHTS' = 'ALTER ANY SCHEMA', GRANTEE has them.

These privileges don't provide access to JVM, unlike ADMIN privileges.

[prrvchr]

Hi Evgenij Ryazanov,

Le lundi 5 septembre 2022 à 02:34:42 UTC+2, Evgenij Ryazanov a écrit :

Database administration is out of scope of the SQL Standard and there are no predefined roles is the Standard. In the Standard, every schema has an owner and this owner may perform DDL commands within this schema. Anything else is database-specific.

 I know the administration of a database  is out of scope of the SQL Standard. but nothing prevents us from defining one.

This is the meaning of my first message, which asked you if you were ready to create these new resources.

After many tests and commits, I can now establish the list of these resources / modifications necessary for the integration of H2 administration in Base:

• During the first connection, if a new database is to be created and no user is given during this connection, a default username (SA) must be created instead of a empty username. this modification is not obligatory but highly recommended.
• In order to be able to manage the inheritance of privileges, the tree structure and the users of the roles, it is necessary to create a new view in the schema INFORMATION_SCHEMA making it possible to establish these relations. The creation of this new view is mandatory.
• In order to be able to manage the privileges of a table that has just been created, a default system role (DBA) must exist and the user created when creating the database must be a member of this role. When creating a new table, all privileges must be assigned to the DBA role (GRANT ALL ON NEWTABLE TO DBA). The creation of this new role is highly recommended.

Voila, I think I did my part, the ball is in your court, thank you for your contribution..

[prrvchr]

A question I forgot to ask:

Can you confirm that the names of users and roles only support uppercase characters without double quotes in all the commands where they appear.

Thanks

[prrvchr]

For further clarification, I would like to confirm that for administration in Base of users and roles as well as associated privileges, I am using UNO API of LibreOffice / OpenOffice. This API was written, I think by SUN a long time ago for StarOffice.

Although this API is above SQL and JDBC which actually do not provide an administration standard, SUN provides with the UNO API everything that is necessary for the administration of a database. It can be considered as a standard.

[Evgenij Ryazanov]

> Can you confirm that the names of users and roles only support uppercase characters without double quotes in all the commands where they appear.

It is possible to use unquoted (NAME), quoted ("NAME"), or Unicode (U&"NAME") identifiers for names of users and roles in H2, but these names are always converted to the upper case, they are case insensitive, unlike other names.

If DATABASE_TO_LOWER=TRUE setting was used (usually together with PostgreSQL, MySQL, or MariaDB compatibility mode), they are reported in the INFORMATION_SCHEMA in the lower case, by default they are in the upper case.