MySQL Bugs: #35415: Problem with browsing views under Openoffice

Bug #35415	Problem with browsing views under Openoffice
Submitted:	18 Mar 2008 19:46	Modified:	20 Mar 2008 17:57
Reporter:	Michal Pokrywka	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S4 (Feature request)
Version:	5.1.7	OS:	Linux (Ubuntu 7.04)
Assigned to:		CPU Architecture:	Any

Description:
When user has permission to select from view but no permission to underlying tables (implemented by view with sql security definer) such view cannot be browsed under Openoffice (v 2.3.1 deb from oo.org). This problem is related with OO engine trying to check if query is updateable. I checked mysql query log and found that there are issued sql queries to check indexes on table that is base of requested view. I don't know who's fault is this, but I managed to patch connector to ignore errors while chcecking indexes and now OO works as expected.
Btw. maybe someone should check why it takes over 30 queries to show one table/view with two columns in OO

How to repeat:
create database company;
use company;
create table people (id serial primary key, name varchar(32), salary double);
insert into people (name,salary) values ('John Smith',1000);
create sql security definer view people_list as select id, name from people;
create user janitor identified by '123';
grant select on company.people_list to janitor;

Under Openoffice with MySQL Connector/J login to db as "janitor" and try to open "people_list" view - SQL error is raised with info "SELECT command denied to user 'janitor'@'localhost' for table 'people'"

MySQL query log:
80318 20:12:28
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL COLUMNS FROM "people_list" FROM "company" LIKE '%'
 24 Query       SELECT * FROM "company"."people_list" WHERE 0 = 1
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL COLUMNS FROM "people_list" FROM "company"
 24 Query       SHOW FULL COLUMNS FROM "people_list" FROM "company" LIKE 'id'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL COLUMNS FROM "people_list" FROM "company"
 24 Query       SHOW FULL COLUMNS FROM "people_list" FROM "company" LIKE 'name'
 24 Query       SHOW KEYS FROM "people_list" FROM "company"
 24 Query       SELECT * FROM "company"."people_list" WHERE 0 = 1
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SELECT * FROM "company"."people_list" WHERE 0 = 1
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
080318 20:12:29
 24 Query       SELECT * FROM "company"."people_list"
 24 Query       SHOW KEYS FROM "people" FROM "company"
080318 20:13:06
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SELECT * FROM "company"."people_list" WHERE 0 = 1
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'
 24 Query       SHOW FULL TABLES FROM "company" LIKE 'people_list'

Suggested fix:
Patch src/com/mysql/jdbc/UpdatableResultSet.java method "checkUpdatability"

ignore sql errors while getting table keys

Attachment: fix-show-keys-perm-denied.diff (text/plain), 847 bytes.

Hi Michal and thanks for your report. Correct me if I'm wrong but it has nothing to do with OO, right? Probably even with c/J... Did you try doing same thing in cl client? Please try and inform me of outcome.

Of course mysql client works because when you write: "select * from people_list" it only invokes this query and nothing else. On the other hand, Openoffice Base tries to get column types and indexes, leading to sql permission exception when you only try to "select * from people_list".
As I said in first message I do not know exactly whose fault is this - I mean: who tries too hard to get all information about view and underlying tables, indexes etc.
Connector/J developer could answer this question:
Whether failing "checkUpdatability"(or similar - I am no Java/JDBC developer) method is invoked internally by connector/j(that would be connector/j bug)
OR
Is "checkUpdatability" invoked by connector user (OO in this case) who wants to know about updatability of tables that are source of view, but this user does not noticed that do not have permission to that tables.

I forgot to add that in case it is Openoffice bug, my workaround is still very useful because it works with OO available now and I think connector/j users do not check for updatability of tables that users have not permission to access

Michal, I do not believe c/J is a place to fix this. It would be much better if you file feature request to OO to add "checkUpdatability" checkbox... Anyway, I'll verify this as S4 (feature request) as see what c/J team leader has to say.

It seems OOo is opening the result set in CONCUR_UPDATABLE mode, which requires the JDBC driver to check if the result set really can be updated (a requirement of the JDBC specification).

The only way to do that currently with MySQL is for the driver to check whether only one table is referenced, and whether there are any primary keys on said table, and if all primary key components are referenced in the query.

Michal, while we're at it, can you point me on how to use c/J with OO please? It doesn't seem to honor CLASSPATH...

nm, found it. There is no option one can set to avoid this so I say this should be feature request for OO guys.

Heh, when trying JDBC connection with "strictUpdates=false" in the URL (and com.mysql.jdbc.Driver class) OO persists on discovering what's forbidden to it... There are like 5000 lines in my general query log like:
11 Query       SHOW FULL TABLES FROM `company` LIKE 'people_list'

Finally, when opening view, OO Database dies without any message...
Please file feature request with them that will allow better control on statement they open for this purpose.

From what Mark said (and from tutorial http://java.sun.com/developer/Books/JDBCTutorial/index.html) I think it is perfectly reasonable to open result set in CONCUR_UPDATABLE mode and after statement checking if result set is updatable by calling "getConcurrency()". Personally I find it strange that sql statement succeeds, but checking its updatability fails with access denied... I won't bother OO devs with this problem.

Michal, I can't do much more here but to notify Mark of your last comment.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44376

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50724