Menu

RLS grant ... filter where ...

Help
Ray Jahn
2020-05-11
2020-07-22
  • Ray Jahn

    Ray Jahn - 2020-05-11

    When the RLS feature of (grant ... filter where ...) is applied to a table, the following SQL statement would lose effect.

    Grant Select on Table tb_clnt_po to PUBLIC;

    [test results]

    SQL = select * from tb_clnt_po;

    login = 3 accounts

    SA (DBA) -> 3 entries shown, correct.
    WORK_MARY -> 0 entries shown, incorrect.
    VIEW_ALEX -> 0 entries shown, incorrect.

    Wonder if this symptom can be reproduced in other environments? Guidance to further narrow down the culprit is appreciated. The SQL scripts and the resultant DB file set are attached. Annotation is included in the SQL script file.

    Sincerely,
    Ray

    [environment]

    MS Windows XP, SP3, x32
    JRE = java.exe, 1.8.0, last version for XP
    JVM = hotspot
    hsqldb.jar = 2.5.1 candidate 6114
    sqltool.jar = 2.5.1 candidate 6114

     

    Last edit: Ray Jahn 2020-05-11
  • Ray Jahn

    Ray Jahn - 2020-05-19

    Excerpting the following from a related thread back into this thread to help viewers.

    20200518, 2.5.1 candidate 6114, SQL Select fails
    www.sf.net/p/hsqldb/discussion/73674/thread/924d6b9398

    This particular SQL Select script should produce identical result for any user account. But when the row level security feature of (conditional Grant) is applied, a strange interference occurred in access control. Only the DBA user would receive a correct result of SQL Select. Non-DBA users would obtain a wrong answer of SQL Select.

     

    Last edit: Ray Jahn 2020-05-19
  • Ray Jahn

    Ray Jahn - 2020-05-28

    2.5.1 candidate, SVN 6124

    Thanks for the SVN 6124 update. Unfortunately SVN 6124 still produces SQL error. Identical results of SQL Select are expected for all user accounts (displaying 3 entries), due to the following access control.

    [SQL Select results]

    3 types of users:
    1. DBA.
    2. workers, with permission to alter rows (row level security).
    3. viewers, no permission to alter rows.

    SA (DBA) → correct, 3 entries shown.
    WORK01 → SQL error, 0 entries shown.
    WORK02 → SQL error, 0 entries shown.
    VIEW01 → correct, 3 entries shown (SVN 6124 improvement).

    [accesss control]

    Using the HSQLDB feature of
    conditional Grant → Grant ... Where ...

    Grant rl_merchant to WORK01, WORK02;
    Grant Select on Table tb_clnt_po to PUBLIC;

    Grant Update, Insert on Table tb_clnt_po
    Filter Where (client, division, USER()) In (Select client, division, Upper(employee) From tb_clnt_edit)
    to rl_merchant;

    An updated SQL script file is attached. The passwords are removed from all user accounts, for easier test repetition.

    Sincerely,

     
  • Fred Toussi

    Fred Toussi - 2020-05-28

    Thanks, the latest snapshot did not contain any changes in this area. I will post here when the isssue has been addressed.

     
  • Fred Toussi

    Fred Toussi - 2020-06-23

    The latest snapshot (SVN version 6134) is now available with enhcemnts to row-level access control. It is now possible to have different (or no) filters for SELECT, DELTED, INSERT, and UPDATE. You example works.

     
  • Ray Jahn

    Ray Jahn - 2020-06-24

    Thanks for SVN 6134 regarding the row level security via conditional Grant. This is a great improvement. Sincerely, Ray

    [Suggestions]

    (1). An error dialog (manual) / Java Exception (scripting) is desired upon violation of the conditional Grant, irrespective of the user type (worker or viewer).

    For user accounts of worker type, 6134 quietly restores the original data upon violation of the conditional Grant. No error dialog is issued in 6134 for users whose roles are covered in the conditional Grant (permission table).

    Automated (batch) data alteration (create, update) via BeanShell (Java) scripts needs error feedback (Java Exception) from HSQLDB.

    (2). Perhaps the default JDBC driver in HSQLDB own client could be updated.
    1.8 → org.hsqldb.jdbcDriver
    2.5 → org.hsqldb.jdbc.JDBCDriver

    [Test results]

    3 user types.
    (1). DBA → OK.
    (2). worker → behavior OK, but missing error dialogs upon violation of the conditional Grant.
    (3). viewer → OK.

    worker = users whose roles are covered in the conditonal Grant (permission table).
    viewer = users whose roles are absent in the conditonal Grant (permission table).

    [Test setup]

    2 UI clients (manual operation), using the full version of HSQLDB.

    (1). common parameters
    MS Windows XP, SP3, x32
    JRE = 1.8.0.152, last version for XP
    JVM = hotspot
    JDBC = org.hsqldb.jdbc.JDBCDriver
    URL = jdbc:hsqldb:file:c:/data.cbg/hsql/xx.test;default_schema=true
    (default_schema=true when using LibreOffice)

    (2). HSQLDB own client
    %java.exec% -jar %hsql.jars%

    (3). LibreOffice
    tools, options, advanced, class path = b:\dbms\hsql\lib\hsqldb.jar
    (to override the default HSQLDB bundled with LibreOffice)

     
  • Fred Toussi

    Fred Toussi - 2020-06-24

    I don't understand what you mean in (1). We have filtered grant, which means only the rows that pass the filter are used for SELECT, UPDATE, or DELETE. No exception is thrown in SELECT and DELETE. An exception is thrown when an attempt is made to INSERT or UPDATE with values that do not pass the filter.

    The jdbcDriver acts as an alias for JDBCDriver in version 2.5.x. But it's a good idea to change the client preset to org.hsqldb.jdbc.JDBCDriver.

     
  • Ray Jahn

    Ray Jahn - 2020-06-29

    Apology for your confusion. The test procedure to reproduce the symptom in interactive mode follows. A LO *.odb file is attached.

    Sincerely,
    Ray

    [test]

    (1). interactive mode -

    LibreOffice
    1. 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".
    4. Alter any PO of adult divisions (Div.man, Div.lady).
    5. The conditional Grant is violated.
    6. The original record is quietly restored, when the cursor leaves the altered record (correct behavior).
    7. 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).

    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 move the cursor to other records. (correct behavior)

    Does HSQLDB engine control the activation of error dialogs in interactive mode, upon violation of conditional Grant? A unified behavior of error dialog similar to that of VIEW01 would be desirable, upon violation of conditional Grant.

    (2). script mode - Java exception is available upon violation of the conditional Grant (involving only INSERT and UPDATE in my particular test case). It works great and enables data alteration in batch. Apology for skipping the script mode test in the previous test of 6134 after encountering the lack of error dialogs in interactive mode.

    [setup]

    (1). HSQLDB = 2.5.1 candidate 6148, full version
    (2). LibreOffice 5.4.7.2, last version for MS Windows XP, SP3
    (3). create 16.test.odb with LO (for test in interactive mode)
    LO → menu → tool → options → advanced → class path → add archive → c:/dbms/hsql/lib/hsqldb.jar
    LO → menu → file → new → database → connect to existing DB → JDBC
    JDBC = org.hsqldb.jdbc.JDBCDriver
    URL = jdbc:hsqldb:file:c:/data.cbg/hsql/16.test;default_schema=true
    user = (empty), to test with multiple user accounts
    pass = checked, to enable the login dialog
    registration → unchecked
    initial edit → unchecked
    finish, to save 16.test.odb

     

    Last edit: Ray Jahn 2020-06-29
  • Fred Toussi

    Fred Toussi - 2020-06-29

    Thanks, I now understand.

    HSQLDB does not control the activation of error dialog in LibreOffice.

    LibreOffice can choose to show a dialog or simply restore the modified text after it gets a Java Exceptions thrown by HSQLDB.

     
  • Ray Jahn

    Ray Jahn - 2020-06-29

    Thanks for guidance. I shall submit an inquiry to the LO team.

    The row level security (RLS) feature by conditional Grant (Grant ... Where ...) in HSQLDB 2.5.1 works wonder in business. Salient features: terse, versatile, easy on code maintenance, and very importantly, easy on real business operations, when compared with alternative RLS designs.

    http://www.sf.net/p/hsqldb/discussion/73674/thread/fe3b588587/#851f

     

    Last edit: Ray Jahn 2020-06-29
  • Ray Jahn

    Ray Jahn - 2020-07-02

    It appears that, upon violation of conditional Grant, a Java Exception disappeared on its way from HSQLDB 2.5.1 to LO Base GUI.

    20200701 Base GUI, missing error dialogs, for row level security
    http://ask.libreoffice.org/en/question/253067

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

    (2). LO → select "Table"
    user = WORK01
    pass = (empty), no password, for easier test repetition
    OK.

    (3). LO → menu → tools → SQL

    This SQL script would violate the conditional Grant (WORK01 altering data of adult divisions).
    SQL => Update tb_clnt_po Set po_clnt = '2379567' Where client = 'Client.01' and po_clnt = '2379304';

    Execute.

    status = Command successfully executed. ← no Java Exception ???
    output = 0 rows updated. ← correct, due to violation of conditional Grant

     

    Last edit: Ray Jahn 2020-07-02
  • Fred Toussi

    Fred Toussi - 2020-07-02

    During selection of rows to update, the filter is applied in addition to the WHERE cluase, and does not throw an Exception. If no row passes the filter the update count is 0. If some row passes the filter, the updated row is checked and any violation of the filter will result in an exception.

     
  • Fred Toussi

    Fred Toussi - 2020-07-02

    The result of update attempts with the database named 16 that you attached here, using 'WORK_MARY' as USER.

    1. The user can update the row with '2379391' as it passes the FILTER. This statement fails, throwing "with check option violation" exception because after the update, the user would not have update rights on a row with 'Client.13'
      /e/update tb_clnt_po set client = 'Client.13' where po_clnt = '2379391'

    2. This statement succeeds because after the update, the user still has update rights on the modified row .
      /u1/update tb_clnt_po set po_clnt = '10' where po_clnt = '2379391'

    3. This statement updates zero rows because the user's ROLE filters out the row, as if the row did not exist.
      /u0/update tb_clnt_po set po_clnt = '11' where po_clnt = '1056754763'

    4. This statement updates one row because the row with '2379304' passess the FILTER and it still passes the FILTER after the update.
      /u1/Update tb_clnt_po Set po_clnt = '2379567' Where client = 'Client.01' and po_clnt = '2379304'

     
  • Ray Jahn

    Ray Jahn - 2020-07-03

    Thanks for the detailed clarification.

    Example (3) mentioned "... filters out the row, as if the row did not exist". Its status code was not stated, but assumed to be success, no error. I start to catch a sense of what might constitute error per current definition of conditional Grant in 2.5.1. From a perspective of SQL script, after row suppression by the filter in conditional Grant, there is no error if no matching rows remain. Java Exception would not be raised. Is my interpretation accurate on the design intention in 2.5.1?

    If the interpretation is correct, this feedback design combines three conditions into one in status feedback, in a perspective of SQL script in upstream DB engine. (1) True success in data alteration (Insert, Update). (2) No matching rows whatsoever, even before any filter. (3) Some matching rows before filter, none remaining after filter, per conditional Grant. Condition (3) equates to no permission. This condition aggregation will affect the feedback status passed to downstream software.

    Now concatenate a perspective of GUI display module in downstream software to the perspective of SQL script in upstream DB engine. There is no universal rule in record display control. The display rule varies with business logic.

    Please note the single record mode during data alteration in GUI module.

    Hospital or bank applications emphasize privacy or fraud prevention. Rows violating the conditional Grant, if existing, are intentionally suppressed in the GUI display. Condition (3) would be irrelevant for GUI module users since violating rows never appear in GUI display of hospital or bank applications.

    In contrast, businesses of collaborative work prefer open view in display control. All rows are visible to public on purpose (business need) in my test database. GUI module users trigger condition (3) when the attempted data alteration would violate the conditional Grant. The GUI module should activate error dialog regarding condition (3), alerting no permission.

    The 1st dilemma:

    In applications of collaborative nature, the GUI module in downstream has no way to identify the triggering of condition (3) in database unless the upstream DB engine issues a proper feedback status code (Java Exception). GUI module is at the mercy of DB engine regarding a proper hand holding for condition (3).

    The 2nd dilemma:

    There is a conflict in the type of feedback about condition (3) needed by downstream applications from the upstream DB engine. For hospital and bank applications which intend to quietly suppress the violating record rows in GUI display, 2.5.1 fits well. For collaborative applications using open view, a proper hand shaking about condition (3) is missing between the upstream DB engine and the downstream software.

    Not sure how to practically resolve the 2nd conflict. Any idea is appreciated.

    As an example for collaborative applications, the view only user VIEW01 in my test database is allowed to view all records (open view). The SQL role of user VIEW01 does not receive permission of data alteration in any Grant statement, ordinary or conditional. 2.5.1 raises Java Exception when VIEW01 attempts data alteration, apparently not due to condition (3) in conditional Grant. LO Base receives the Java Exception. The GUI module then activates the error dialog, alerting no permission. This is the desired behavior of error dialog upon no permission, regardless of condition (3) or else.

     
  • Fred Toussi

    Fred Toussi - 2020-07-03

    Your interpretation of example 3 is correct.

    I understand the use of GUI in collaborative apps where the user can see all the data but is allowed to update only some rows.

    If it is too difficult to let the users know that updates to the rows that do not belong to them would be silently ignored, and you must show them an error message, then you can use a different feature of HSQLDB for this purpose.

    Grant UPDATE without FILTER to those users. Create a TRIGGER on the table with the opposite condition to the FILTER condition to throw an exception in the body of the trigger. The trigger is activated only when the user updates any wrong row.

    CREATE TRIGGER trigone BEFORE UPDATE ON yourtable 
    REFERENCING NEW ROW AS newrow
    FOR EACH ROW
    BEGIN ATOMIC
      IF (newrow.X, newrow.Y, newrow.Z) NOT IN (SELECT ...) THEN
        SIGNAL SQLSTATE '44000' SET MESSAGE_TEXT = 'update not allowed';
    END;     
    

    Note, you need to refine the condition yourself to cover only the users that do not have any update rights on specific rows.

     

    Last edit: Fred Toussi 2020-07-03
  • Ray Jahn

    Ray Jahn - 2020-07-07

    Thanks for the tip of Before Trigger. A few candidate approaches to override the default behavior of DB exception are listed below. Your assessment and guidance is appreciated.

    (1). Before Trigger

    The RLS checking and exception raising is packaged in a Before Trigger, as demonstrated in your 2020-07-23 example.

    It may be desirable to start with (Referencing Old Row) in the Before Trigger. (Referencing New Row) introduces additional concerns, in both business logic and RLS constraint.

    I am ignorant of the trigger overhead (performance).

    (2). Declarative Constraint, by using user defined function (UDF).

    The RLS condition and exception raising may be packaged in a UDF RLS_Permit( … ). Then the conditional Grant would invoke the UDF RLS_Permit( … ) in the filter clause.

    Grant … on trading_table Filter Where RLS_Permit( … ) = true to role_worker;

    Stack overhead may occur as the call to UDF is repeated for each row.

    (3). Client session parameter

    rls_display=hide_rows (default).
    rls_display=show_rows.

    (hide_rows) suppresses the display of ineligible rows AFTER filter, for hospital or bank applications.
    (show_rows) displays rows eligible BEFORE filter, for collaborative applications.

    The client parameter would be invoked in database URL like
    URL = jdbc:hsqldb:file:c:/data.cbg/hsql/16.test;rls_display=show_rows;default_schema=true

    The overhead by client parameter is likely the least among these 3 candidates. Checking the flag rls_display is the overhead in decision to raise exception.


    I would appreciate your guidance on my failed attempt to test the 2nd candidate approach. The test scripts for conditional Grant and UDF are in the attached text file, EOL = MS convention.

    (1). conditional Grant

    HSQLDB 2.5.1 (and SVN 6148) does not recognize the conditional Grant any more. 2.5.1 complained

    unexpected token: FILTER required: TO ...

    Parser always balks at the keyword FILTER, no matter how I altered / mutilated the the conditional Grant statement. The syntax change for conditional Grant between (before 2.5.1) and 2.5.1 (web) does not seem to be the culprit.

    Before 2.5.1 → Grant … (multiple privileges) … on a_table Filter Where … to role_worker;
    After 2.5.1 → Grant … (one privilege) ... Filter Where … on a_table to role_worker;

    (2). UDF

    Could not figure out where the culprit is for SQLTOOL complaint. Tried END;, and many other mutilations of the UDF, to no avail. Same complaint even with examples copied from HSQLDB web page (column names adapted, etc.).

    Unterminated input: "CREATE FUNCTION … (the UDF body) … END"

    Thanks for help.

     
  • Fred Toussi

    Fred Toussi - 2020-07-07

    The idea of a function will not work. Functions do not accept table names and it is not possible to write statements with variable table names (this is called dynamic SQL when used in routines).

    Regarding adding a session property to change the behaviour, it is possible in a future release and is not currently planned.

    Regarding triggers, they are quite simple to write. You can reference both OLD ROW and NEW ROW in the same BEFORE trigger and throw exceptions. Check the OLD ROW if the user is not supposed to change the row. Check the NEW ROW if the user is attempting to change the row to something that they are not supposed to. In the check that you perform, first check the user is one of the users that have only limited rights, then if so, check the NOT IN condition

    Regarding performance. Your database server may be handling 1000 update requests a second. Still, you may see a sub-millisecond performance difference when you add a simple trigger.

    The original syntax has not been dropped and still works (although not documented). The FILTER applies to all the listed actions (UPDATE, DELETE, etc).

    The new syntax means a FILTER goes after each action and applies to that action only, allowing different or no FILTER for each action.

    Your example is missing the parentheses around the WHERE clause of the filter. The syntax error should ideally mention the missing paranthesis.

    FILTER <left paren> WHERE <search condition> <right paren>
    

    Finally thank you for your contribution.

     
  • Fred Toussi

    Fred Toussi - 2020-07-07

    This is a more detailed example of the TRIGGER

    Grant UPDATE without FILTER to the users. Create a TRIGGER on the table with the opposite condition to the FILTER condition to throw an exception in the body of the trigger. The trigger is activated only when the user updates any wrong row or changes a row to something they shouldn't. The NOT IN condition is the exact opposited the condition in the 'equivalent' FILTER clcause:

    CREATE TRIGGER trigone BEFORE UPDATE ON tb_clnt_po
       REFERENCING OLD ROW AS oldrow NEW ROW AS newrow
       FOR EACH ROW
       BEGIN ATOMIC
         IF (USER() IN (SELECT employee FROM tb_clnt_edit) ) THEN
    
          IF ((oldrow.client, oldrow.division, USER()) NOT IN (Select client, division, Upper(employee) From tb_clnt_edit)) THEN
            SIGNAL SQLSTATE '44000' SET MESSAGE_TEXT = 'update not allowed';
          END IF;
    
          IF ((newrow.client, newrow.division, USER()) NOT IN (Select client, division, Upper(employee) From tb_clnt_edit)) THEN
           SIGNAL SQLSTATE '44000' SET MESSAGE_TEXT = 'update not allowed';
          END IF;
    
        END IF;
      END;   
    

    You need a similar trigger BEFORE DELETE, using only OLD ROW. if you want unauthorized deletes to throw an exception

     
  • Fred Toussi

    Fred Toussi - 2020-07-21

    In the discussion, I forgot about a feature that can be used to detect the Example(3) situation when a client attempts to update a row for which they do not have update rights due to the FILTER clause. HyperSQL generates an SQL warning when an UPDATE statement does not actually update any row. The warning can be accessed with the getWarnings method of the JDBC Connection. http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jdbc/JDBCConnection.html

     

    Last edit: Fred Toussi 2020-07-21
  • Ray Jahn

    Ray Jahn - 2020-07-22

    Thanks for the tip of JDBC SQLWarning and getWarnings() for the row level security (RLS) feature.

    Another discussion in business need about the RLS display control is worthy of attention. It calls for a RLS display control at the SQL statement level, in addition to the client session level. This is not within the need of my current application (collaboration, open view, throughout client session). But it is a worthwhile feature in RLS road map for market competition.

    http://www.google.com/search?hl=en&as_q=dynamic+row+level+security

    HSQLDB 2.5.1 offers a distinct advantage of simplicity in dynamic RLS, due to its design of RLS by conditional Grant. In contrast, an extra module is currently required to accomplish dynamic RLS for RLS designs based on policy rules. MS SQL Server + MS Power BI is an example.

    A design by tiered switch chain, scalable, incurring negligible runtime overhead, might suffice to support the business logic.

    rls_display = hide_rows | show_rows.
    hide_rows → exclusion applications, e.g., hospital, finance, etc.
    show_rows → collaboration applications, open view.

    3 tiers of (rls_display) switch chain.
    Tier 1: server engine → default = hide_rows.
    Tier 2: client session → default = inherit tier 1, if absent. Parameter embedded in URL string.
    Tier 3: SQL statement → default = inherit tier 2, if absent. Parameter embedded in the Where clause.

    Hypothetical syntax examples:
    Tier 2: URL = jdbc:hsqldb:file:...;default_schema=true;rls_display=show_rows;
    Tier 3: Select … from … where … and rls_display = show_rows;

     

    Last edit: Ray Jahn 2020-07-22

Log in to post a comment.