Use of MySQL with OpenOffice.org

Hi *,


I and some other Guys at OpenOffice.org find it a great way to use
MySQL as a great Database within OpenOfice.org. But their are some
issues that this is not as easy as it should.

The easiest way (I think) to connect to MySQL is the JDBC-Brigde.
But their are some silly bugs/ideas.

first OpenOffice.org use getTablePrivileges to ask the database,
wether the user have the rights for this Database. Only if the
Database gives the rights back, OpenOffice use this. The JDBC-Driver
ask only in tables_priv, but not in user etc. So, if I connect as
root, getTablePrivileges gives back, I have no rights. next point
is, that a user is always a user@host combination. But I have to
connect from a machine. Wy can the driver looks only wether the host
is right?


This are some great things to use MySQL with OpenOffice.org and I
told this before on the java-list. But I think, Mark has no willing
to go a step away from his point of thinking. So my question ist,
could the JDBC-Driver do something similar as the ODBC-Driver? to
get the Table-Privileges and the users right?

How-To-Repeat:
a simple query for getTablePrivileges from a java-programm


I ask this, because we guys would plan to do a step to MySQL or we
have to write our own patched-jdbc-driver.

--

mfg
Hermann Kienlein
------------------------------------------------------------ -------
EDV-Systeme Kienlein GdbR FON: +49-9184-80040
Pfarrer-Zinckel-Str. 10 FAX: +49-9184-80041
D-92364 Deining http://www.kienlein.com



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12768@lists.mysql.com
To unsubscribe, e-mail

Re: Use of MySQL with OpenOffice.org

> Subject: Use of MySQL with OpenOffice.org
> Date: Thu, 17 Oct 2002 08:23:15 +0200
> From: Hermann Kienlein
> To: bugs@lists.mysql.com
>
> Hi *,
>
>
> I and some other Guys at OpenOffice.org find it a great way to use
> MySQL as a great Database within OpenOfice.org. But their are some
> issues that this is not as easy as it should.
>
> The easiest way (I think) to connect to MySQL is the JDBC-Brigde.
> But their are some silly bugs/ideas.

Let's not resort to name calling, please. I am not calling the way you
and your team approach things in OpenOffice as 'silly'. You're not going
to motivate anyone to change anything if you call them stupid first.
Considering that the driver tests as JDBC-compliant, and many thousands
of people use it without issues, I would re-phrase your statement as
'I/We disagree with the way the JDBC driver currently works', or
'Because the JDBC spec is unclear, we differ in our interpretation of
how getTablePrivlileges should work with MySQL, etc."

> first OpenOffice.org use getTablePrivileges to ask the database,
> wether the user have the rights for this Database. Only if the
> Database gives the rights back, OpenOffice use this. The JDBC-Driver
> ask only in tables_priv, but not in user etc.

The ODBC driver doesn't look in the user table, either. I am trying to
figure out how to get the queries to work out, because in the end they
will be very complex, so don't expect to see privileges that weren't
granted by 'GRANT ...' to show up for a few weeks.

> So, if I connect as
> root, getTablePrivileges gives back, I have no rights. next point
> is, that a user is always a user@host combination. But I have to
> connect from a machine. Wy can the driver looks only wether the host
> is right?

Because that is the _full_ specification for a user, e.g.

GRANT ALL PRIVILEGES ON test.* to 'hermann'@'192.168.1.%' identified by
'foo'

and

GRANT SELECT ON test.* to 'hermann'@'127.0.0.1' identified by 'foo'

Creates (in MySQL's point-of-view) _two_ different users, because you
get different privileges depending on whether or not you are connecting
from the 192.168.1.0 network, or from localhost. This is why the full
username includes the hostname, because it is a component of your
identity, as far as MySQL is concerned (when it is specified in your
grant statements).

> This are some great things to use MySQL with OpenOffice.org and I
> told this before on the java-list. But I think, Mark has no willing
> to go a step away from his point of thinking.

I'll budge at least part-way. I'll put in a property for MySQL
Connector/J 3.0.2 and later, called 'useHostsInPrivileges' which
defaults to true. If you want to use (what I feel) is the non-standard
behavior, you will need to add 'useHostsInPrivileges=false' to your URL
or your Properties that you pass to DriverMangager.getConnection() or
Connection.connect(). When this value is set to false, user names will
not contain the '@hostname' part.

> So my question ist,
> could the JDBC-Driver do something similar as the ODBC-Driver? to
> get the Table-Privileges and the users right?

The ODBC driver does the exact same queries as the JDBC driver, so
please come up with a _repeatable_ test case (preferably, outside of OO,
as I don't use it, nor have it installed anywhere) that demonstrates the
behavior you are seeing.

> How-To-Repeat:
> a simple query for getTablePrivileges from a java-programm
>
>
> I ask this, because we guys would plan to do a step to MySQL or we
> have to write our own patched-jdbc-driver.

You don't have to go that far, but please refrain from accusing me from
being 'bull-headed' and 'silly' in a public forum, because it's not the
correct way to go about facilitating change with anyone.

-Mark
--
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12779@lists.mysql.com
To unsubscribe, e-mail

Re: Use of MySQL with OpenOffice.org

Hi Mark, *,

Mark Matthews schrieb:
>> Subject: Use of MySQL with OpenOffice.org
>> Date: Thu, 17 Oct 2002 08:23:15 +0200
>> From: Hermann Kienlein
>> To: bugs@lists.mysql.com
>>
>> Hi *,
>>
>>
>> I and some other Guys at OpenOffice.org find it a great way to use
>> MySQL as a great Database within OpenOfice.org. But their are some
>> issues that this is not as easy as it should.
>>
>> The easiest way (I think) to connect to MySQL is the JDBC-Brigde.
>> But their are some silly bugs/ideas.
>
>
> Let's not resort to name calling, please. I am not calling the way you
> and your team approach things in OpenOffice as 'silly'. You're not going
> to motivate anyone to change anything if you call them stupid first.
> Considering that the driver tests as JDBC-compliant, and many thousands
> of people use it without issues, I would re-phrase your statement as
> 'I/We disagree with the way the JDBC driver currently works', or
> 'Because the JDBC spec is unclear, we differ in our interpretation of
> how getTablePrivlileges should work with MySQL, etc."

Excuse me for the phrase silly bugs, but I wrote this as in German
"blöder Fehler" is meant as a thing that is not really a bug than
either a missing feature or little thing, that is not worth to speak
about it. I never would you or anyone else name as silly or stupid.

>
>> first OpenOffice.org use getTablePrivileges to ask the database,
>> wether the user have the rights for this Database. Only if the
>> Database gives the rights back, OpenOffice use this. The JDBC-Driver
>> ask only in tables_priv, but not in user etc.
>
>
> The ODBC driver doesn't look in the user table, either. I am trying to
> figure out how to get the queries to work out, because in the end they
> will be very complex, so don't expect to see privileges that weren't
> granted by 'GRANT ...' to show up for a few weeks.
>

I do not really know, how the odbc-driver works internal. But some
other said, that the odbc-driver gives it right back.

>> So, if I connect as
>> root, getTablePrivileges gives back, I have no rights. next point
>> is, that a user is always a user@host combination. But I have to
>> connect from a machine. Wy can the driver looks only wether the host
>> is right?
>
>
> Because that is the _full_ specification for a user, e.g.
>
> GRANT ALL PRIVILEGES ON test.* to 'hermann'@'192.168.1.%' identified by
> 'foo'
>
> and
>
> GRANT SELECT ON test.* to 'hermann'@'127.0.0.1' identified by 'foo'
>
> Creates (in MySQL's point-of-view) _two_ different users, because you
> get different privileges depending on whether or not you are connecting
> from the 192.168.1.0 network, or from localhost. This is why the full
> username includes the hostname, because it is a component of your
> identity, as far as MySQL is concerned (when it is specified in your
> grant statements).
>
again, the odbc-driver does this not, and if build my own
application with MySQL, I can work with it, but with OpenOffice.org
we have the problem to speak to different databases in different
ways. I agree that for MySQL this are different user. But the
JDBC-Driver should IMHO be a connection to a database, so that I can
simple change the driver and database and I can work on with my
application. If I must do this things in my application, I have do
do this for every singe database/driver combination. And this could
not be the intention of using a jdbc and/or odbc driver.

>> This are some great things to use MySQL with OpenOffice.org and I
>> told this before on the java-list. But I think, Mark has no willing
>> to go a step away from his point of thinking.
>
>
> I'll budge at least part-way. I'll put in a property for MySQL
sorry, I don´t find budge in my dictionary.
> Connector/J 3.0.2 and later, called 'useHostsInPrivileges' which
> defaults to true. If you want to use (what I feel) is the non-standard
> behavior, you will need to add 'useHostsInPrivileges=false' to your URL
> or your Properties that you pass to DriverMangager.getConnection() or
> Connection.connect(). When this value is set to false, user names will
> not contain the '@hostname' part.

this could be a way to go on

>
>> So my question ist,
>> could the JDBC-Driver do something similar as the ODBC-Driver? to
>> get the Table-Privileges and the users right?
>
>
> The ODBC driver does the exact same queries as the JDBC driver, so
> please come up with a _repeatable_ test case (preferably, outside of OO,
> as I don't use it, nor have it installed anywhere) that demonstrates the
> behavior you are seeing.

if I would write a repeatable test-case outside of OOo, I have to
use the same code as inside of OOo. But I will go on that some other
guy can do a better speach than I

>
>> How-To-Repeat:
>> a simple query for getTablePrivileges from a java-programm
>>
>>
>> I ask this, because we guys would plan to do a step to MySQL or we
>> have to write our own patched-jdbc-driver.
>
>
> You don't have to go that far, but please refrain from accusing me from
> being 'bull-headed' and 'silly' in a public forum, because it's not the
> correct way to go about facilitating change with anyone.

sorry again, I never would say silly to you or anyone else, I used
this phrase in my german way of speaking about a ..(I dont know how
to say it without saying something stupid again. perhaps some other
on this list can say better in english what is meant)


--

mfg
Hermann Kienlein
------------------------------------------------------------ -------
EDV-Systeme Kienlein GdbR FON: +49-9184-80040
Pfarrer-Zinckel-Str. 10 FAX: +49-9184-80041
D-92364 Deining http://www.kienlein.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12780@lists.mysql.com
To unsubscribe, e-mail

Re: Use of MySQL with OpenOffice.org

Hi Mark,

I just want to mentioned what OpenOffice does to fetch the privileges
for a user in a short list. Perhaps these make things clear.
1. get user name from the metadata. md.getUserName()
2. getTablePrivileges()
3. res.next()
4. get the grantee and the privileges
5. compare if user name and grantee are the same person
6. if so, accept the privileges

Hope this helps a little bit to understand why the grantee should be the
user without the host attached.

Best regards,
Ocke Janssen

--
dba.openoffice.org
Example isn't another way to teach,
it is the only way to teach.
Albert Einstein




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12781@lists.mysql.com
To unsubscribe, e-mail

Re: Use of MySQL with OpenOffice.org

Hi Hermann, Hi Mark,

> Excuse me for the phrase silly bugs, but I wrote this as in German
> "blöder Fehler" is meant as a thing that is not really a bug than
> either a missing feature or little thing, that is not worth to speak
> about it.

"blöder Fehler"
=
"a very small little bug but which is quite annoying and nasty"

something like that?

> I agree that
> for MySQL this are different user. But the JDBC-Driver should IMHO be
> a connection to a database, so that I can simple change the driver
> and database and I can work on with my application. If I must do this
> things in my application, I have do do this for every singe
> database/driver combination. And this could not be the intention of
> using a jdbc and/or odbc driver.

I tend to agree to Hermann here. If I log on as "user" to a database,
then I do not expect that later on, I have to ask for "user@myhost" when
I talk to this database again.
This means that the layer which mediates this connection (here: the JDBC
driver) has different semantics for "user name" in different places.

So if MySQL has these different semantics, then I would expect the JDBC
driver to adjust this, and present it's own clients a _defined_
semantics of "user name".

The alternative would be that the clients of the JDBC driver know about
this difference, and adjust themself, which I consider the worse
alternative.

>> I'll budge at least part-way. I'll put in a property for MySQL
>
> sorry, I don´t find budge in my dictionary.

http://dict.leo.org/?search=budge
:)

Ciao
Frank


--
Frank Schönheit mailto:fs@openoffice.org
Software Engineer http://www.openoffice.org
OpenOffice.org database access http://dba.openoffice.org
OpenOffice.org common user interface http://ui.openoffice.org


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12782@lists.mysql.com
To unsubscribe, e-mail

Re: Use of MySQL with OpenOffice.org

Hi!

>>>>> "Mark" == Mark Matthews writes:
>
>> I and some other Guys at OpenOffice.org find it a great way to use
>> MySQL as a great Database within OpenOfice.org. But their are some
>> issues that this is not as easy as it should.

We would really like to see MySQL used with OpenOffice and will try to
give you all the help we can give you to make this possible.

>> The easiest way (I think) to connect to MySQL is the JDBC-Brigde.
>> But their are some silly bugs/ideas.

Not probably the best choice of words as Mark misunderstood these :(
Hermann, don't however let this discourage you; Both Mark and me
wants to help you find a good solution for this problem. Feel free to
keep sending emails to this forum!

What we must first do is to completely understand the problem on both
sides (what openoffice needs and why it needs this and how MySQL
works). After this it shouldn't be hard to find a solution for this.

>> first OpenOffice.org use getTablePrivileges to ask the database,
>> wether the user have the rights for this Database. Only if the
>> Database gives the rights back, OpenOffice use this. The JDBC-Driver
>> ask only in tables_priv, but not in user etc.

Mark> The ODBC driver doesn't look in the user table, either. I am trying to
Mark> figure out how to get the queries to work out, because in the end they
Mark> will be very complex, so don't expect to see privileges that weren't
Mark> granted by 'GRANT ...' to show up for a few weeks.

What information does openoffice have at this time?

- user name
- password
- connect options to MySQL (socket, TCP/IP etc..)

By the way, I assume you know that if you connect as a user and do
'show databases', MySQL will only show databases for which the user
have some kind of privileges.

>> So, if I connect as
>> root, getTablePrivileges gives back, I have no rights. next point
>> is, that a user is always a user@host combination. But I have to
>> connect from a machine. Wy can the driver looks only wether the host
>> is right?



Mark> Creates (in MySQL's point-of-view) _two_ different users, because you
Mark> get different privileges depending on whether or not you are connecting
Mark> from the 192.168.1.0 network, or from localhost. This is why the full
Mark> username includes the hostname, because it is a component of your
Mark> identity, as far as MySQL is concerned (when it is specified in your
Mark> grant statements).

Herman, can you please in detail describe what you would like MySQL /
MySQLConnector-J to do and Mark and I will try to figure out a way how
to do this.

I just talked with Mark and we will on our end do a list of all
possible ways we can think of to solve this problem.

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12791@lists.mysql.com
To unsubscribe, e-mail

Re: Use of MySQL with OpenOffice.org

Ocke Janssen wrote:
> Hi Mark,
>
> I just want to mentioned what OpenOffice does to fetch the privileges
> for a user in a short list. Perhaps these make things clear.
> 1. get user name from the metadata. md.getUserName()
> 2. getTablePrivileges()
> 3. res.next()
> 4. get the grantee and the privileges
> 5. compare if user name and grantee are the same person
> 6. if so, accept the privileges
>
> Hope this helps a little bit to understand why the grantee should be the
> user without the host attached.

I'd like to apologize publicly for my tone of voice in my previous
e-mail. Putting a day of calm between then and now, I can see that it
might be interpreted as a bit of ranting, and that's not how I intended
it, but I shouldn't have used a tone of voice that could be interpreted
as being non-professional.

Actually, after your clarification of what you need, and looking further
into what both MySQL and Connector/J are doing, there's a couple of ways
to fix the grants metadata problem.

First, let me tell you that MySQL does (by default) consider the
hostname to be part of the username, due to the granularity of the
grants system. Given this, the options are:

1. Fix DatabaseMetaData.getUser() to return 'user@hostname' (already
done for Connector/J 3.0.2)
2. Put a config property in Connector/J to drop '@hostname' from _all_
user metadata information (already done for Connector/J 3.0.2)
3. Add an option to mysqld itself to not use hostnames as qualifiers for
user names.

Also, there is some talk to re-work the JDBC and ODBC drivers to use
'SHOW GRANTS for ...' instead of the SELECT that it does now. There are
issues with this too, as if no GRANTS have been made for the current
user, there will be nothing useful returned.

Could you please explain what exactly you're looking for from the 'user'
table, other than GRANTS that have been granted via the GRANT statement,
so that I can figure out how to make this work for OpenOffice?

We at MySQL genuinely want OpenOffice to work seemlessly with our
product. Please let me know of any further assistance you might require.

-Mark

--
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12792@lists.mysql.com
To unsubscribe, e-mail

Re: Use of MySQL with OpenOffice.org

Frank Schönheit wrote:

> Hi Hermann, Hi Mark,
>
>
> >Excuse me for the phrase silly bugs, but I wrote this as in German
> >"blöder Fehler" is meant as a thing that is not really a bug than
> >either a missing feature or little thing, that is not worth to speak
> >about it.
>
>
> "blöder Fehler"
> =
> "a very small little bug but which is quite annoying and nasty"
>
> something like that?
>
>
> >I agree that
> >for MySQL this are different user. But the JDBC-Driver should IMHO be
> >a connection to a database, so that I can simple change the driver
> >and database and I can work on with my application. If I must do this
> >things in my application, I have do do this for every singe
> >database/driver combination. And this could not be the intention of
> >using a jdbc and/or odbc driver.
>
>
> I tend to agree to Hermann here. If I log on as "user" to a database,
> then I do not expect that later on, I have to ask for "user@myhost" when
> I talk to this database again.
> This means that the layer which mediates this connection (here: the JDBC
> driver) has different semantics for "user name" in different places.
>
> So if MySQL has these different semantics, then I would expect the JDBC
> driver to adjust this, and present it's own clients a _defined_
> semantics of "user name".
>
> The alternative would be that the clients of the JDBC driver know about
> this difference, and adjust themself, which I consider the worse
> alternative.

Have you tried Connector/J 3.0.2? It fixes the 'sematics' problem you
describe, and also allows you to turn off hostnames across all
privileges in DatabaseMetaData (see the README)

-Mark


--
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13038@lists.mysql.com
To unsubscribe, e-mail