Manage Privileges Doesn´t Like Backslash

[larsen]

Hi,

Plesk is creating entries in the mysql.db table for the field with backslashes like in “example_com”. I don´t know if this is specified as valid but it works, i.e. the user has access to that db.

But when I open “manage privileges” in SQLyog, it doesn´t show any rights for the user to that db. Only after I change it to “example_com” does it work.

Can this be fixed in SQLyog or is it a Plesk bug?

Lars

[peterlaursen]

I wish I knew the answer but I am confused myself.  It seems that even MySQL people are. Please read: http://code.google.com/p/sqlyog/issues/detail?id=100

In strings “_” is an escape sequence for “_”.  Plain “_” (without “”) is a wildcard substituting a single character.  So what PLESK aims to do on your server is probably to create/insert “example_com”. 

The ultimate test would be to execute

SELECT HEX(`user`) FROM `mysql`.`user` WHERE `user` LIKE '%_%' 

(I think the LIKE clause must be specified like this)

.. and next inspect the HEX pattern and see if the “” character is stored or not.  It may depend on server version.  If the “” is not stored what we display is correct. But it looks like we forgot some escaping here ourselves when updating the user.

I have never observed such users created on our own PLESK managed servers.  Can you provide more details of this (when it does, why it does etc.)?

[peterlaursen]

With SQLyog I created the user 

SQLyog generated (note no escapes generated .. with escapes it would be 'my\_host', 'peter\_me')

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, create_user_priv, event_priv, trigger_priv ) VALUES ( 'my_host', 'peter_me', 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', 'N', 'N', 'N' );

FLUSH PRIVILEGES;

INSERT INTO mysql.db(HOST, db, USER, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, create_tmp_table_priv, lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Execute_priv, event_priv, trigger_priv) VALUES('my_host', 'information_schema', 'peter_me', 'Y','N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N');

FLUSH PRIVILEGES:

The user displays as 'peter_me'@'my_host' in both 'edit user' and 'manage privileges'.  This is not in accordance to MySQL documentation at http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html . I will post the test case as a question/bug report to MySQL.

Here server is 5.1.44

[peterlaursen]

I posted: http://bugs.mysql.com/bug.php?id=52326

.. let us see if they can clarify.

[larsen]

'peterlaursen' wrote on '24:

.. and next inspect the HEX pattern and see if the “” character is stored or not.

I did this and the backslash is not contained within the returned hex value.

Quote:

It may depend on server version.

MySQL server is 5.0.32-Debian_7etch12-LOG

Quote:

I have never observed such users created on our own PLESK managed servers.  Can you provide more details of this (when it does, why it does etc.)?

We create those users on our own and use the domain to which they belong as a username. Points are replaced with underscore by us. So, for a database on the domain “example.com” the username will be “example_com”.

[peterlaursen]

“So, for a database on the domain “example.com” the username will be “example_com”.”  bad practice as a mySQL username can only be 16 characters and a domain name can be much longer.

I tried to update a user named “my_user” from SQLyog manage privileges and it works fine on my 5.1.44.  I assume that the whole thing is caused by an early 5.0 server bug.  If you do not think so please explain .. and preferably verify your problem on a recent server.

[larsen]

yes, the username is limited to 16 chars so we have to abbreviate it, but this is sufficient to distinguish several domains.

I now tested with a 5.1.41 MySQL server on Windows and the “problem” still shows up. Umm, I just noticed that I forgot to mention the fieldname “Db” in my first post and that you were testing with the username. Could you please test it with an underscore in the db name and not in the username?

mysql.user:

Code:

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 Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
——— —— —————————————– ———– ———– ———– ———– ———– ——— ———– ————- ———— ——— ———- ————— ———- ———- ———— ———- ——————— —————- ———— ————— —————- —————- ————– ——————- —————— —————- ———- ———— ——– ———- ———– ———— ————- ———– ————— ——————–
localhost test *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 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 N N N 0 0 0 0

mysql.db:

Code:

Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv
——— ———- —— ———– ———– ———– ———– ———– ——— ———- ————— ———- ———- ——————— —————- —————- ————– ——————- —————— ———— ———- ————
localhost my_db test Y Y Y Y Y N N N N N N N N N N N N N N

[peterlaursen]

OK .. we will try this. I will try to get time tomorrow.

[larsen]

This too is not urgent.

[peterlaursen]

[attachment=1354:privs.jpg]

I have a `my_base` database, a `my_table` table and a `my_text` column.  I can assign privileges to all those for 'my_user'@'my_host' without problem.  I edited the privileges several times and did not encounter any issue.

I wonder if there is something that I do not understand?

Is there any problem for you if you use SQLyog alone to create users and objects/databases and do not use PLESK?

I do not remember much PHP any more, but there are some settings on how to handle escaping (and lots of bugs with specific PHP versions in that respect).  So maybe a PHP or PLESK/PHP interfacing issue with your versions.

[larsen]

Hmm, it could be that we are not talking about the same thing:

The db itself is without a backslash. It´s the entry in the table mysql.db that has a backslash.

[Author]

Posts