[closed] Base GUI, missing error dialogs, for row level security

[cbg.jahn]

Dear Community:

Problem → Error dialogs are missing during interactive operation in client - server mode. The procedure for symptom reproduction is provided below.

HSQLDB 2.5.1, 2020-06-29, is selected as the DB engine due to its row level security (RLS) feature based on conditional Grant (Grant … Where …). Firebird 3.0.6, 2020-06-28, is not capable of RLS.

At present LO Base is not capable to inspect the RLS constraint relationship defined by the conditional Grant.

Inquiry → Is it possible to configure LO so that Base GUI would always activate error dialogs upon Java Exception from DB engines?

Deployment → LO installation on 20~30 PCs is pending on a solution to the missing error dialogs.

Thanks in advance for advice.

Sincerely,

Ray Jahn

[symptom reproduction]

(1). LO → menu → file → open → 16.test.odb

(2). Select “Tables”.

user = WORK01

pass = (empty), no password, for easier test repetition

OK.

(3). Select table “TB_CLNT_PO”.

Alter any PO of adult divisions (Div.man, Div.lady).

The conditional Grant is violated (controlled by RLS of DB engine).

(4). Move the cursor away from the altered record.

The original record is quietly restored.

Missing the error dialog in interactive mode, upon violation of the conditional Grant.

The error dialog is also missing for user = WORK02, when altering any PO of children divisions (Div.boy, Div.girl) and thereby violating the conditional Grant.

Interestingly an error dialog appears for user = VIEW01, when the cursor leaves the altered record, if any PO of any division is altered (correct behavior). In the case of VIEW01, one needs to press (escape) to restore the original record and then move the cursor to other records. (correct behavior)

A unified behavior of error dialogs similar to that of VIEW01 would be desirable, upon violation of conditional Grant.

[setup]

(1). DB server hsqldb.jar, 2.5.1 → to c:/dbms/hsql/lib/hsqldb.jar

(2). Unzip database file set 16.test.zip → to c:/data.cbg/hsql

(3). The client file 16.test.odb can reside anywhere on the same PC.

(4). Configure LO.

This step overrides the default HSQLDB 1.8 in the LO installation.

LO → menu → tool → options → advanced → class path → add archive → c:/dbms/hsql/lib/hsqldb.jar

Create the client file 16.test.odb with LO, for test in interactive mode.

(1). LO → menu → tool → options → advanced → class path → add archive → c:/dbms/hsql/lib/hsqldb.jar

(2). LO → menu → file → new → database → connect to existing DB → JDBC

(3). database parameters.

JDBC = org.hsqldb.jdbc.JDBCDriver

URL = jdbc:hsqldb:file:c:/data.cbg/hsql/16.test;default_schema=true

(4). user credential

user = (empty), to test with multiple user accounts

pass = checked, to enable the login dialog

(5). initiation options

registration → unchecked

initial edit → unchecked

finish, to save 16.test.odb

MS Windows XP, SP3, x32

LibreOffice 5.4.7.2, x32, last version for XP

HSQLDB 2.5.1

JRE = Oracle JRE 1.8.0.152, last version for XP

JVM = Hotspot, not OpenJ9

MS Windows 10, x64

LibreOffice 6.3.3.2, x64

HSQLDB 2.5.1

JRE = AdoptOpenJDK JRE 1.8.0.212.

JVM = Hotspot, not OpenJ9

[attachments]

(1). 16.test.odb → the client file.

16.test.odb

(2). 16.test.zip → the database file set, controlled by DB server.

16.test.zip is disguised as 16.test.odt, due to restriction on file name extension for upload.

16.test.odt

(3). hsqldb.jar, 2.5.1 → to c:/dbms/hsql/lib/hsqldb.jar

hsqldb.jar, 2.5.1, 1.5 MB, exceeding the 1 MB limit of attachment file size.

Please download hsqldb.jar directly from hsqldb.org.

---

The following attachments provide business case and SQL scripts for RLS and conditional Grant. No need to read them unless necessary, e.g., inspecting the RLS constraint relationship, etc. EOL convention of MS (CR+LF) is used in these text attachments.

(4). sql.test.16.txt → the SQL scripts to create the file sets of database 16.test in HSQLDB 2.5.1.

sql.test.16.txt is disguised as sql.test.16.odt, due to the restriction on file name extension for upload.

sql.test.16.odt

(5). 16.case.txt → business case

16.case.txt is disguised as 16.case.odt, due to the restriction on file name extension for upload.

16.case.odt

[Ratslinger]

Hello,

All .png files are presenting problems of one type or another - all just try to display image and get error. No downloading seems possible. Possibly try using .odt extension instead.

[Ratslinger]

Have taken another step. Ubuntu Mate 20.04 with LO v6.4.4.2 and using PostgreSQL (v12.3) with native connector, have set up a table with row-level security. Have done minimal testing but see no problems.

[cbg.jahn]

Replaced *.png with *.odt. Thanks for the tip on file name extension of attachments.

The RLS design in HSQLDB 2.5.1 is distinct from those in alternative DB engines, inc. PostGreSQL. Conditional Grant (Grant … Where …) is used. HSQLDB raises Java Exception upon violation of conditional Grant.

The attachments of SQL scripts and business case might help.

[Ratslinger]

Initial reaction is there is a problem with HSQLDB v2.5.1 as running an SQL Update statement from Tools->SQL (which is a pass through) the result states Command successfully executed but there is no actual change to the record and obviously no error is shown.

Same statement for an authorized user changes the record and shows the same results. Seems testing outside of Base is the next step.

[Ratslinger]

@cbg.jahn,

Have now tried update from SQL Workbench/J and got the same result when trying to update with user WORK01. Stated was successful. Commit OK but no actual update. No error generated. Changed to authorized user & update works. Problem is in database or JDBC connector - not Base

[cbg.jahn]

The root cause turns out to be two conflicting types of business logic. Two distinct types of GUI behavior ensue upon the violation of row level security (RLS) by attempts of data alteration.

20200703 business logic, exclusion vs. collaboration applications

www.sf.net/p/hsqldb/discussion/73674/thread/bdaa1af91b/#ff95

exclusion → hide ineligible rows AFTER RLS filter, for hospitals or banks.

collaboration → open view, show rows eligible BEFORE RLS filter.

Three approaches were discussed. The client session parameter has the least overhead in runtime.

20200707 3 approaches, Before Trigger, Declarative Constraint, Client session parameter

www.sf.net/p/hsqldb/discussion/73674/thread/bdaa1af91b/#7109

[cbg.jahn]

Thanks for the help and advice. This inquiry is closed.

Perhaps LO Base could obviate this problem when Firebird implements the row level security (RLS) feature. Please note that RLS inherently implies client-server configuration due to simultaneous access by multiple users. The deployment of LO Base for client-server differs from that for personal databases.