Privileges and LibreOffice Base

[z3dom]

(My LibreOffice Base connect to mysql Base via jdbc connector.)

I have a TABLE with COLUMN1, COLUMN2, COLUMN3.
A USER has full access to COLUMN1 and COLUMN2, but he shouldn’t have any access to COLUMN3.

So I create a “NEW FORM” in a LO Base with two fields (for COLUMN1 and COLUMN2). I thought that USER will be able to work with these columns, but USER can’t open NEW FORM cause of such error:
SQL Status: 42000
Error code: 1142

SELECT command denied to user ‘USER’ for table ‘TABLE’

So how can I resolve this problem: USER needs to work with first ans second column via NEW FORM, but he shouldn’t have any access to third form.

[Ratslinger]

Hello @z3dom. Are you implying that this user can never even see what may be in this third field? Even if they try to view the table contents?

[z3dom]

Yes! Information in third column is TOP SECRET, and user should never see it

[Ratslinger]

Hello,

First reaction to this is that you have a user working with a table containing data they are not supposed to see. Not logical. It would seem if that is the case why not move this to another table and create a link to it via the field not allowed. Then no privilege for that user to that linked table. Also that linked field (base table) only give SELECT rights with a default of NULL. Now whomever is placing this SECRET information in the DB can create the secondary table record & link it to the base table record.

As for not doing the above & using the procedure in your question, Base will not allow entering new records through the form if SELECT privilege is not present for all the fields, even with defaults involved. However, I have one method which can work (tested) and that involves using a macro. User only has INSERT privilege for this table. Form is not attached to any table & button on form executes the macro to connect to DB. Form fields used for INSERT statement & then execute statement. Your SECRET field is not involved (should have default value). Then whomever has proper right to this field can update it.

[z3dom]

Thanks, I decided to create second table (despite that this is very inconvinient)

[Ratslinger]

If it is that ‘inconvenient’, I did specify it could be done with a macro. Have tied it & it works.

[Spun69]

Partitioning the table is one option. Perhaps another is to use a view to include only the allowable columns and build the user form using this.

[Ratslinger]

@Spun69 Creating a View will not help here as the field in question can still be accessed by other means. Even a simple query will expose the field. Comment above states ‘…user should never see it’.