Skip to content
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

Closed
firebird-automations opened this issue Jan 9, 2012 · 21 comments

Comments

@firebird-automations
Copy link

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

@firebird-automations
Copy link
Author

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.
Only when connecting as user SYSDBA, db is editable.
To verify my role models I made the same queries using flamerobin and it works fine.

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.
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

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

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Could you create a reproduction case for this?

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

priority: Blocker [ 1 ] => Major [ 3 ]

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

Modified by: Dr. Schorsch (xorx)

Attachment: dbTestOOBase.zip [ 12086 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Using LibreOffice I have been able to reproduce this. I need to investigate the cause.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

assignee: Roman Rokytskyy [ rrokytskyy ] => Mark Rotteveel [ avalanche1979 ]

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

summary: Specifying a roleName makes all tables read only => Specifying a roleName makes all tables read only in OpenOffice / LibreOffice

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 2.3 [ 10440 ]

@firebird-automations
Copy link
Author

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

@firebird-automations
Copy link
Author

Commented by: Dr. Schorsch (xorx)

Changed version to 2.2.4 because the problem still exists.

@firebird-automations
Copy link
Author

Modified by: Dr. Schorsch (xorx)

Version: Jaybird 2.2.4 [ 10531 ]

Version: Jaybird 2.2 [ 10053 ] =>

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

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).

@firebird-automations
Copy link
Author

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 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

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 3.1 [ 10441 ]

Fix Version: Jaybird 3.0 [ 10440 ] =>

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

priority: Major [ 3 ] => Minor [ 4 ]

Fix Version: Jaybird 4 [ 10441 ] =>

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Closed [ 6 ]

resolution: Fixed [ 1 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants