HSQLDB MULTIUSER, GRANTING not working in HSQLDB 2.6.1

[F3K Total]

Hello all,
i have a problem using HSQLDB 2.6.1 with Windows 10, jre1.8.0_311:

Beside the Superuser SA, i defined a second user U1, wanting it not to have all priviliges on the database.
For that i created a role SUSER

Code: Select all

CREATE ROLE SUSER;
GRANT SELECT,UPDATE("FN") ON TABLE PUBLIC."Persons" TO SUSER
GRANT ALL ON TABLE PUBLIC."Persons_Data" TO SUSER

then granted that role to U1

Code: Select all

GRANT SUSER TO U1

It means, that U1 can only change (update) the column FN from table Persons, but can do everything (SELECT, INSERT, UPDATE, DELETE) on table Persons_Data

The problem is, this works excellent using HSQLDB 2.5.1 but no more using version 2.6.1, both in combination with AOO and LO.
U1 does using HSQLDB 2.6.1 not have any access to change anything.

Find attached both versions database-files.
I used

• hsqldb.jar and sqltool.jar for version 2.5.1
• hsqldb-jdk8.jar and sqltool-jdk8.jar for version 2.6.1

which have to be placed in folder "database" of the attached samples.
Then a macro will automatically connect ApacheOpenOffice to the database.

Can anyone comprehend this, help, or is this a Bug of HSQLDB 2.6.1?
R

[Villeroy]

Yes, it behaves exactly as you say. When I reconnect the correctly working 2.5 database to the 2.6 driver, U1 can not edit anything.
I observe the problem with 2.6.0 too. If I switch back to HSQL 2.5.1 everything works fine.

I could also reproduce the issue with another database of mine.
Switched to 2.5.1.
Created U1.
Applied your SQL to 2 tables and one column.
Tested with U1. Allright.
Switched to 2.6.1. U1 has no write access.

[F3K Total]

Villeroy,
thank you for the investigation!
R

[F3K Total]

Hello all,
in between, with the help of USER sliderule the investigation of this problem went further.
We found out, the the behaviour of the database version 2.6.1 is correct, using the menue Tools/SQL...
It's possible, logged in as U1, to modify e.g. the column FN in table Persons as desired, but not the column SN, also desired.
When we now run the following Starbasic Code:

Code: Select all

sub investigate_Table_Persons_Privileges
    oController = Thisdatabasedocument.currentController
    if not (oController.isconnected) Then oController.connect
    oConnection = oController.activeConnection
    print oConnection.tables.Persons.Privileges
end sub

we will get a different result:
Using HSQLDB 2.5.1 the result of the Privileges of table Persons is 511
Using HSQLDB 2.6.1 the result of the Privileges of table Persons is 0

There seems to be a problem transferring the Privileges correct since HSQLDB Version 2.6

Any ideas?

R

[Villeroy]

-- added system role SCRIPT_OPS for export / import of database structure and data

This is the only change in version 2.6.0 which is related to privileges.

[F3K Total]

Hello,
in between, USER silderule found out, that there is a difference in the .script files of my sample database GR.
HSQL 2.5.1

Code: Select all

SET DATABASE SQL TDC DELETE FALSE
SET DATABASE SQL TDC UPDATE FALSE

HSQL 2.6.1

Code: Select all

SET DATABASE SQL TDC DELETE TRUE
SET DATABASE SQL TDC UPDATE TRUE

i changed that in version 2.6.1 but it made no difference.
Still no access on the tables.
i give this up now.
If that specific granting is necessary in combination with AOO/LO, i'll use HSQLDB 2.5.1 version.

[Villeroy]

https://bugs.documentfoundation.org/sho ... ?id=146568

[F3K Total]

Hello Villeroy,
GREAT!!!
Thank you for that!
R