Issue Details (XML | Word | Printable)

Key: JDBC-226
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Mark Rotteveel
Reporter: Dr. Schorsch
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

Specifying a roleName makes all tables read only in OpenOffice / LibreOffice

Created: 08/Jan/12 10:29 PM   Updated: 22/Jul/18 11:07 AM
Component/s: None
Affects Version/s: Jaybird 2.2.4
Fix Version/s: None

File Attachments: 1. Zip Archive dbTestOOBase.zip (3 kB)

Environment: Mac OSX 10.6, oobase 3.3.0 and 3.3.1, Firebird 2.5

Sub-Tasks  All   Open   

 Description  « Hide
When specifying a user and a roleName in the connection string, all database tables become read only in ooBase.
Only when connecting as user SYSDBA or with role RDB$ADMIN, db is editable.
To verify my role models I made the same queries and roles using flamerobin and it works fine.

I have no clue whether this is an oo problem or a jaybird problem...

I am using the following JDBC-Url:

firebirdsql:oo:localhost/3050:MyDataBase?defaultHoldable&lc_ctype=UNICODE_FSS&autoquoted_identifier&sensitive&roleName=BUEROKRAT

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 14/Jan/12 09:05 AM
Could you create a reproduction case for this?

Dr. Schorsch added a comment - 16/Jan/12 08:53 PM
I have added a test scenario: A simple database with one table, one role called "TESTROLE". Testrole has one user as member with name "TESTUSER". TESTUSER is also member of "RDB$ADMIN".

After creating the database you have to create an alias for the database. The oo-document contains a jaybird jdbc-link to the database. Last element of the link is the role name.

Never mind what role you specify, the tables are locked. Only when connecting as sysdba the db becomes editable for oobase.

Mark Rotteveel added a comment - 06/May/12 07:52 AM
Using LibreOffice I have been able to reproduce this. I need to investigate the cause.

Mark Rotteveel added a comment - 06/May/12 12:02 PM
Base takes the result of DatabaseMetaData.getTablePrivileges() to decide if a user has the rights. It seems it looks to the column GRANTEE if it matches with the username.

I need to investigate how other databases handle this in general. Technically the GRANTEE is the role and nothing else. As a workaround I might modify the OODatabaseMetaData to include rights granted to the the current role of the current user with the GRANTEE set as the username.

Mark Rotteveel added a comment - 06/May/12 12:16 PM
Looking at the definition of the TABLE_PRIVILEGES view in SQL:2003 Schemata, section 5.60 and TABLE_PRIVILEGES table in 6.50 the way Base expects it is wrong.

Mark Rotteveel added a comment - 06/May/12 01:47 PM
Added subtask for workaround in 2.2. I need to do further investigation to see if this needs to be reported to LibreOffice / OpenOffice or if this change should also be incorporated in the normal DatabaseMetaData.

Dr. Schorsch added a comment - 25/Jan/14 10:26 PM
The problem is still persisting and even worse. My database is allways read only, not only when I specify a rolename. I am still using the connect string in the above example.

Any help is apreciated.

Kind regards,

Georg

Dr. Schorsch added a comment - 25/Jan/14 10:29 PM
Changed version to 2.2.4 because the problem still exists.

Mark Rotteveel added a comment - 28/Jan/14 06:48 PM - edited
Are you using OpenOffice or LibreOffice? What version? I am not able to reproduce what you describe in LibreOffice Base 4.1.4.2 and OpenOffice Base 4.0.1

LibreOffice Base 4.1.4.2 and OpenOffice Base 4.0.1 don't seem to check permissions at all; they will just try and display an error message if updating failed.

Mark Rotteveel added a comment - 28/Jan/14 07:07 PM
After some more digging, I see that OpenOffice and LibreOffice have an advanced option 'Ignore the privileges from the database driver' which is enabled by default. After disabling this property the rolenames work as expected: only SELECT rights: table is read-only, all privileges: table is editable (I haven't tested with SELECT + UPDATE or SELECT + INSERT or other combinations).

Make sure you don't have multiple versions of Jaybird on your classpath (otherwise you might be loading an older version of Jaybird).

Dr. Schorsch added a comment - 29/Jan/14 08:38 AM - edited
Dear Mark, thank you for your fast response!

I remember having seen the option 'Ignore the privileges from the database driver' and I think I left it on default value.
I will check your suggestion as soon as possible. I tried it on Mac OSX 10.6 with neooffice 4 and openoffice 4.0.

I my class path is only one version of jdbc. Since I keept all versions I could see that the "read only" problem occured with V 2.2.2
Regards, Georg