MySQL Bugs: #18660: Can't grant any privileges on single table in database with underscore char

Bug #18660	Can't grant any privileges on single table in database with underscore char
Submitted:	30 Mar 2006 17:41	Modified:	15 Jun 2007 9:21
Reporter:	Przemyslaw Madzik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.24, 4.1.15, 5.0.18, 5.1.15-BK	OS:	Linux (linux, debian)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	bfsm_2007_05_31

Description:
I can't grant any privileges on single table in database with underscore character in name. I can do this for all tables in such database using `database\_with\_underscore`.* syntax, but not for single table.

Example:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.18-Debian_9-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on `pmadzik\_%`.* to pmadzik@localhost with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant usage on *.* to www@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
$ mysql -u pmadzik
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.18-Debian_9-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database pmadzik_test;
Query OK, 1 row affected (0.00 sec)

mysql> \u pmadzik_test
Database changed
mysql> create table testtable (col1 int, col2 varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into testtable values (1, 'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into testtable values (2, 'bbb');
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtable;
+------+------+
| col1 | col2 |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
+------+------+
2 rows in set (0.00 sec)

mysql> grant select on testtable to www@localhost;
ERROR 1142 (42000): SELECT,GRANT command denied to user 'pmadzik'@'localhost' for table 'testtable'
mysql> grant select on `pmadzik\_test`.testtable to www@localhost;
ERROR 1103 (42000): Incorrect table name 'testtable'
mysql> grant select on `pmadzik\_test`.`testtable` to www@localhost;
ERROR 1103 (42000): Incorrect table name 'testtable'
mysql> grant select on `pmadzik\_test`.* to www@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>

How to repeat:
$ mysql -u root
mysql> grant all on `pmadzik\_%`.* to pmadzik@localhost with grant option;
mysql> grant usage on *.* to www@localhost;
mysql> \q
$ mysql -u pmadzik
mysql> create database pmadzik_test;
mysql> \u pmadzik_test
mysql> grant select on `pmadzik\_test`.testtable to www@localhost;
mysql> grant select on `pmadzik\_test`.`testtable` to www@localhost;

I can confirm the behaviour you described even on the latest 5.0.21-BK. 

The following statement:

grant all on `pmadzik_test`.* to pmadzik@localhost with grant option;

will allow you to do what you want, but it will give you wider privileges that needed. According to the manual (http://dev.mysql.com/doc/refman/5.0/en/request-access.html):

"The wildcard characters ‘%’ and ‘_’ can be used in the Host and Db columns of either table. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character (‘_’) as part of a database name, specify it as ‘\_’ in the GRANT statement."

is is not a proper way to solve the problem. So, it looks like a bug for me.

Hi. Thanks for your problem report. Sorry, I was unable to repeat it. Here's output from my RH Linux machine:

[root@Munja mysql]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 242 to server version: 4.1.3-beta-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

mysql> drop database if exists _test;
Query OK, 1 row affected (0.01 sec)

mysql> create database _test;
Query OK, 1 row affected (0.00 sec)

mysql> use _test;
Database changed
mysql> create table testtable (Col1 INT, Col2 VARCHAR(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into testtable values (1,'aaa'),(2,'bbb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from testtable;
+------+------+
| Col1 | Col2 |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
+------+------+
2 rows in set (0.00 sec)

mysql> grant select on _test.testtable to majahuljic15527@localhost; 
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

mysql> quit
Bye
[root@Munja mysql]# mysql -u majahuljic15527
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 243 to server version: 4.1.3-beta-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use _test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from testtable;
+------+------+
| Col1 | Col2 |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
+------+------+
2 rows in set (0.00 sec)

mysql>

and the tables_priv table:
Host	Db	User	Table_name	Grantor	Timestamp	Table_priv	Column_priv
localhost	_test	majahuljic15527	testtable	root@localhost	30.03.2006 22:26:59	Select	

From the manual:
http://dev.mysql.com/doc/refman/4.1/en/error-messages-server.html
Error: 1142 SQLSTATE: 42000 (ER_TABLEACCESS_DENIED_ERROR)
Message: %s command denied to user '%s'@'%s' for table '%s'
It looks like you do not have privilege to grant this select or this could be Debian specific problem.

Can you test on another linux platform and post the result? Are you
using the debian mysql packages or the binaries provided by us on
dev.mysql.com?

1. My problem in brief:

WORKING:
  mysql> grant select on `pmadzik\_test`.* to www@localhost;

NOT WORKING:
  mysql> grant select on `pmadzik\_test`.testtable to www@localhost;
  mysql> grant select on `pmadzik\_test`.`testtable` to www@localhost;
WITH ERROR:
  ERROR 1103 (42000): Incorrect table name 'testtable'

2. Maybe you were unable repeat my problem, because you have executing grant
command (grant select on _test.testtable to majahuljic15527@localhost;) as root.

Root account is not the problem. If your account has enough privileges then it should work as you expected. Anyway, Valeriy was able to reproduce this behavior. I ran into unexpected problems today and will check this problem again tomorrow.

Verified as described by user. However strange, it seems to work when DB name starts with '_'. It is not platform-related or version-related.

create database pmadzik_test;
use pmadzik_test;
create table testtable (col1 int, col2 varchar(10));
insert into testtable values (1, 'aaa'),(2, 'bbb');
grant select on `pmadzik\_test`.testtable to www@localhost;
ERROR 1103 (42000): Incorrect table name 'testtable'

Using mysql  Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0
I have tables that contain '_' and trying to grant privileges on them just doesn't work. Eg:

GRANT USAGE ON `dbname`.`test_table` TO 'username'@'%';
#2013 - Lost connection to MySQL server during query 

GRANT USAGE ON `dbname`.`test\_table` TO 'username'@'%';
#1103 - Incorrect table name 'test\_table'

Looks like 5.1 is also affected somehow:

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.1.15-beta |
+-------------+
1 row in set (0.00 sec)

mysql> create database pmadzik_test;
Query OK, 1 row affected (0.01 sec)

mysql> use pmadzik_test;
crDatabase changed
mysql> create table testtable (col1 int, col2 varchar(10));
iQuery OK, 0 rows affected (0.01 sec)

mysql> insert into testtable values (1, 'aaa'),(2, 'bbb');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> grant select on `pmadzik\_test`.testtable to root@192.168.0.1;
ERROR 1146 (42S02): Table 'pmadzik\_test.testtable' doesn't exist
mysql> grant select on `pmadzik_test`.testtable to root@192.168.0.1;
Query OK, 0 rows affected (0.00 sec)

But the last grant will apply to several databases, it seems.

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/27124

ChangeSet@1.2491, 2007-05-22 18:33:11+05:00, gluh@mysql.com +3 -0
  Bug#18660 Can't grant any privileges on single table in database with underscore char
  GRANT_ACL for table level privilege check is removed,
  Database name cannot be pattern in case of table level grant and
  db_is_pattern(see check_access function)
  which affected wild_compare() function should be set into 0 in this case.

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/28273

ChangeSet@1.2491, 2007-06-07 14:26:47+05:00, gluh@mysql.com +3 -0
  Bug#18660 Can't grant any privileges on single table in database with underscore char
  In case of database level grant the database name may be a pattern,
  in case of table|column level grant the database name can not be a pattern.
  We use 'dont_check_global_grants' as a flag to determine
  if it's database level grant command 
  (see SQLCOM_GRANT case, mysql_execute_command() function) and
  set db_is_pattern according to 'dont_check_global_grants' value.

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/28387

ChangeSet@1.2522, 2007-06-08 14:42:08+05:00, gluh@mysql.com +3 -0
  Bug#18660 Can't grant any privileges on single table in database with underscore char
  In case of database level grant the database name may be a pattern,
  in case of table|column level grant the database name can not be a pattern.
  We use 'dont_check_global_grants' as a flag to determine
  if it's database level grant command 
  (see SQLCOM_GRANT case, mysql_execute_command() function) and
  set db_is_pattern according to 'dont_check_global_grants' value.

Pushed into 5.1.20-beta

Pushed into 5.0.44

A note has been added to the 5.1.20 and 5.0.44 changelogs.

It’s reproducible now with MySQL 5.6.13.

mysql> GRANT SELECT, INSERT, DELETE ON `test\_db`.`test_table` TO 'test_user'@'%';
ERROR 1146 (42S02): Table 'test\_db.test_table' doesn't exist

Well, an experiment shows that it’s perfectly safe to use `test_db`.`test_table` (without escaping the underscore in the DB name): the underscore character is not used as a wildcard in this case, so the user only gets access to one particular table in one particular database. However, I still think it is not documented well enough (at least not in the documentation for the GRANT command: http://dev.mysql.com/doc/refman/5.1/en/grant.html).

It is incredible to see such inconsistent behavior not fixed yet (even in MySQL 5.7 ...

To resume:

grant on table GRANT select ON `imscp_system`.`ftp_users` TO ...: we don't need (and we must not) to escape wildcard characters, else, we got a 'unknown table error'

grant on database GRANT select ON `imscp\_system`.* TO ...: we need to escape wildcard characters.