[zhuston]

peterlaursen wrote on Feb 8 2007, 10:20 AM:

I certainly know how to use wildcards in host names.

I also know about the db, table, columns, routines .. specific privileges

But the last documents that you refer to also says:

However you write:

“In that table the “Db” field is given the value “main_books”

And that is what confuses me! “main_books” contains a wildcard!

I'll re-read the material and discuss internally with developers here.

The statement below that you referenced only applies to the tables_priv columns_priv and procs_priv tables:

“The Db, Table_name, and Column_name columns cannot contain wildcards or be blank”

I have not made use of the tables_priv columns_priv and procs_priv tables. I have only used the mysql.db and mysql.host tables which allow “wildcard characters ‘%’ and ‘_’ in the Host and Db columns of either table”.

I do not want to sound demanding. I just wanted to point these things out so that your product could be improved.

[zhuston]

There are 2 main pages in the mysql documentation that describe the Access Control system.

1. http://dev.mysql.com/doc/refman/5.0/en/con…ion-access.html

2. http://dev.mysql.com/doc/refman/5.0/en/request-access.html

1. The first describes how the initial connection is made using the mysql.user table.

2. The second page most relates to the problems I was having. It describes how the mysql.db table works with the mysql.host table to provide the second level of permissions. I will list a few important quotes from the page.

“The db and host tables grant database-specific privileges”

“The wildcard characters ‘%’ and ‘_’ can be used in the Host and Db columns of either table.”

“A blank Host value in the db table means consult the host table for further information”

I previously did not know how the wild cards worked but I used phpMyAdmin which escapes the “_” when it is iserted into the Host and Db columns. I experienced the problems when I began trying to use SQLyog for all of my user management which I previously did in phpMyAdmin. I guess the main thing I would point out is that I do not see a way in SQLyog to manage the mysql.host table which is used in conjunction with the mysql.db table. In our case we use the mysql.host table because our users connect from a couple of different IP addresses that can not be matched with wild cards. Our only solutions are to make use of the mysql.host table, create multiple users for each host, or allow access from any host. We found the easiest to maintain system was to make use of the mysql.host table.

[zhuston]

I am sorry for my short description of the problem I was having.

SQLyog v5.21

Mysql 5.0.18

My specific problem invovled my work with the “Manage Permissions” section of the User manager.

1. I have a database called “main_books”. I try to give user “abc” access to the database through the manage permissions interface. When I select the “main_books” database and add permissions it creates a row in the “mysql.db” table. In that table the “Db” field is given the value “main_books” instead of “main_books” as it would be given if the “_” was automatically escaped.

Now this part is not so much of a problem for me since it still works. The “_” is just treated as a wild card character so it would match any character including the underscore.

2. My real problem comes with this. Say I have an existing entry in my “mysql.db” table with the following values:

Db = main_books

User = bob

Notice: The underscore has been escaped in the database as is necessary since a LIKE match is done when determining permissions.

Now when I go to the “Manage Permissions” window and select user “bob” it does not show that he has access to the “main_books” database. My guess is that SQLyog is doing an exact match with the value instead of the LIKE match.

3. My third problem is that when the Host field is left empty in the mysql.db table mysql references the mysql.host table. SQLyog does not seem to have an interface for working with the mysql.host table so when it is left empty in the mysql.db table the row is ignored in the “Manage Permissions” window.

I know I may be sounding picky. I have never reported a bug before and use SQLyog on a daily basis so I thought I would just take the time to report some of the problems I was having with it. I have evaluated other MySQL GUI editors and so far SQLyog is still my favorite.

[Author]

Posts