New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Specifying a roleName makes all tables read only in OpenOffice / LibreOffice [JDBC226] #275
Comments
Modified by: Dr. Schorsch (xorx)description: When specifying a user and a roleName in the connection string, all database tables become read only in ooBase. I have no clue whether this is an oo problem or a jaybird problem... => When specifying a user and a roleName in the connection string, all database tables become read only in ooBase. I have no clue whether this is an oo problem or a jaybird problem... I am using the following JDBCUrl: firebirdsql:oo:localhost/3050:MyDataBase?defaultHoldable&lc_ctype=UNICODE_FSS&autoquoted_identifier&sensitive&roleName=BUEROKRAT environment: Mac OSX 10.6, oobase 3.3.0, Firebird 2.5 => Mac OSX 10.6, oobase 3.3.0 and 3.3.1, Firebird 2.5 |
Commented by: @mrotteveel Could you create a reproduction case for this? |
Modified by: @mrotteveelpriority: Blocker [ 1 ] => Major [ 3 ] |
Commented by: Dr. Schorsch (xorx) 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. |
Modified by: Dr. Schorsch (xorx)Attachment: dbTestOOBase.zip [ 12086 ] |
Commented by: @mrotteveel Using LibreOffice I have been able to reproduce this. I need to investigate the cause. |
Modified by: @mrotteveelassignee: Roman Rokytskyy [ rrokytskyy ] => Mark Rotteveel [ avalanche1979 ] |
Commented by: @mrotteveel 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. |
Commented by: @mrotteveel 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. |
Commented by: @mrotteveel 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. |
Modified by: @mrotteveelsummary: Specifying a roleName makes all tables read only => Specifying a roleName makes all tables read only in OpenOffice / LibreOffice |
Modified by: @mrotteveelFix Version: Jaybird 2.3 [ 10440 ] |
Commented by: Dr. Schorsch (xorx) 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 |
Commented by: Dr. Schorsch (xorx) Changed version to 2.2.4 because the problem still exists. |
Modified by: Dr. Schorsch (xorx)Version: Jaybird 2.2.4 [ 10531 ] Version: Jaybird 2.2 [ 10053 ] => |
Commented by: @mrotteveel 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. |
Commented by: @mrotteveel 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). |
Commented by: Dr. Schorsch (xorx) 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 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 |
Submitted by: Dr. Schorsch (xorx)
Jira_subtask_outward JDBC252
Attachments:
dbTestOOBase.zip
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 JDBCUrl:
firebirdsql:oo:localhost/3050:MyDataBase?defaultHoldable&lc_ctype=UNICODE_FSS&autoquoted_identifier&sensitive&roleName=BUEROKRAT
Commits: 4bcad09 eb7c113
The text was updated successfully, but these errors were encountered: