Bug – Permission Tables Escaping _

[zhuston]

The managing permissions system does not seem to recognize “_” as an escaped “_” in the Host and Db fields of the the mysql.host and mysql.db tables. The underscore by default is treated as any character but must be escaped to be treated as an underscore.

Overall this is an excellent program! I am just have a few issues when using it to manage permissions.

[peterlaursen]

Please tell the SQLyog version and the MySQL version!

Entering 'p_e_t_e_r' as a username from 'Add user' generates

Code:

insert into mysql.user (
host, user, password, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, reload_priv, shutdown_priv, process_priv, file_priv, grant_priv, references_priv, index_priv, alter_priv, show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv, execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv, create_routine_priv, alter_routine_priv )
values
( '%', 'p_e_t_e_r', PASSWORD(''), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );

Now

Code:

“select host, user from mysql.user where user like 'p%';”

returns

Code:

host user
——— ————-
% p_e_t_e_r

And you can log on as 'p_e_t_e_r'.

I understand you expected the user to be created as p_e_t_e_r. Right?

Now this

Code:

insert into mytab(id,name) values ('1','t_e_s_t');

select name from mytab where id = '1';

/*returns
name
———-
t_e_s_t
*/

but

Code:

insert into mytab(id,name) values ('1','t\e\s\t');

select name from mytab where id = '1';

/*returns
name
———-
test
*/

and

Code:

insert into mytab(id,name) values ('1','tnensnt');

select name from mytab where id = '1';

/*returns
name
———-
t
e
s
t
*/

This is MySQL behaviour (mySQL 5.0.33). I also think that this is not fully in accordance with the MySQL docs on how to use escaping with the “_” wildcard character. But this is inside a quoted string in an INSERT statement. So I am not perfectly sure. Also

Code:

insert into mytab(id,name) values ('1','t%e%s%t');

select name from mytab where id = '1';

/*returns
name
———-
t%e%s%t
*/

.. so it behaves identically with the two 'wildcards'. I guess I will have to study MySQL docs (and it may take a few days)! But it is no issue with SQLyog User Management. This is how INSERTing a quoted string with escaped wildcards works with (this) MySQL (version).

And further:

From the SQLyog GUI you are never supposed to do escaping yourself. SQLyog GUI handles this – that is one basic idea with a GUI tool. But not the case of course if you type into the SQL editor!

To create the user 'p_e_t_e_r', just enter 'p_e_t_e_r'.

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

[peterlaursen]

no no .. not picky at all.

We will go detailed through this last post of your tomorrow.

I do not understand everything right now!

[peterlaursen]

OK

First we started to understand we thought that your user management 'style' was undocumented and unsupported by MySQL.  

We also checked 'MySQL Administrator' and it does not support wildcards in user management either.

This http://dev.mysql.com/doc/refman/5.0/en/adding-users.html does not say much.

Do you have any additional docs on 'User management with wildcards' like this.

We have tested it briefly and it works as you describe it.

“when the Host field is left empty in the mysql.db table mysql references the mysql.host table.”  

Yes – it does!  Is it documented?  You know about that?

But still we do not know why you need to escape the wildcard.  

We do not need to do that to achieve the same (if we understand right).

Maybe you have some scripts/applications that we do not know about?

The short answer is that if we shall support it it must be officially supported by MySQL, and will of course take some time too.  

But it is very nice actually in situations where you have a complicated user configuration with your server.  

MySQL does not provide 'roles' and managing hundreds of users – if they each need specific privileges – can be tiresome!

We will study this more in detail and would appreciate links to any docs and guidelines (official or unoffical) that you may be able to provide.

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

[peterlaursen]

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:

Quote:

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

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.

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

[peterlaursen]

You are welcome!  MySQL user management can be demanding, so we will absolutely consider any 'smart' option that could be implemented in SQLyog.

Basically I am just trying to understand details of your explanation.  

I have never seen this before.

I think I and Manoj will have to sit on this next week.

We will probably create a test case and create a priority ticket with MySQL to get an authoritative answer what is supported and documented behaviour and what is n

t.

The concern is of course that we will not create an implementation that gets broken with the next MySQL server update (and especially not with a security related subject like User management).  And that could happen if we implement support for a (now) functional but unsupported and undocumented behaviour.  Also the MySQL developers use the MySQL documentation and they have no obligation to support undocumented features/behaviours for the future.

 

[Author]

Posts