[MDEV-10444] parser error in grant statement for roles Created: 2016-07-26  Updated: 2016-08-08  Resolved: 2016-08-08

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: 10.1.16
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Peter Mclarty Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None
Environment:

Redhat Linux 6.7



 Description   

There is a simple parser bug in the grant function when granting database privileges to a role
In this grant statement for a role this code works, however a simple change renders it broken

GRANT ALL PRIVILEGES ON `logging`.* TO 'developer'

to 

GRANT ALL PRIVILEGES ON `logging`.`*` TO 'developer'

This breaks the grant
example

MariaDB [(none)]> show grants;
 
+-----------------------------------------------------------------------------------------------------------------+
 
| Grants for pmclarty@192.168.%                                                                                   |
 
+-----------------------------------------------------------------------------------------------------------------+
 
| GRANT developer TO 'pmclarty'@'192.168.%'                                                                       |
 
| GRANT USAGE ON *.* TO 'pmclarty'@'192.168.%' IDENTIFIED BY PASSWORD '*7C5EB1AB3838C67932450CCDA5FFC43F00237D6A' |
 
| GRANT USAGE ON *.* TO 'developer'                                                                               |
 
| GRANT ALL PRIVILEGES ON `logging`.* TO 'developer'                                                              |
 
+-----------------------------------------------------------------------------------------------------------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> revoke ALL PRIVILEGES on `logging`.* from developer;
 
ERROR 1044 (42000): Access denied for user 'pmclarty'@'192.168.%' to database 'logging'
 
MariaDB [(none)]> grant ALL PRIVILEGES on `logging`.`*` to developer;
 
ERROR 1142 (42000): SELECT, INSERT, UPDATE, DELETE,  command denied to user 'pmclarty'@'192.168.71.175' for table '*'
 
MariaDB [(none)]> exit
 
Bye
 
PS H:\> mysql -h host1_dbt -upmclarty -p
 
Enter password: ********
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 6
 
Server version: 10.1.16-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> show grants;
 
+-----------------------------------------------------------------------------------------------------------------+
 
| Grants for pmclarty@192.168.%                                                                                   |
 
+-----------------------------------------------------------------------------------------------------------------+
 
| GRANT developer TO 'pmclarty'@'192.168.%'                                                                       |
 
| GRANT USAGE ON *.* TO 'pmclarty'@'192.168.%' IDENTIFIED BY PASSWORD '*7C5EB1AB3838C67932450CCDA5FFC43F00237D6A' |
 
| GRANT USAGE ON *.* TO 'developer'                                                                               |
 
| GRANT ALL PRIVILEGES ON `logging`.`*` TO 'developer'                                                            |
 
+-----------------------------------------------------------------------------------------------------------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> use logging;
 
Database changed
 
MariaDB [logging]> show tables;
 
Empty set (0.00 sec)
 
 
 
MariaDB [logging]>



 Comments   
Comment by Sergei Golubchik [ 2016-07-26 ]

What exactly is broken? What do you think the behavior should be?

As you can see from error messages and from the show grants output, you've granted access to the table *. This is a valid table name:

MariaDB [test]> create table `*` (a int);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> insert `*` values (1);
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [test]> show tables;
 
+----------------+
 
| Tables_in_test |
 
+----------------+
 
| *              |
 
+----------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select * from `*`;
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> drop table `*`;

An asterisk without backtick-quotes is not a valid table name, and in the GRANT statement it means a wildcard all tables.

Comment by Peter Mclarty [ 2016-07-26 ]

Ok then perhaps the fact that a table name of * needs to be pointed out in the documentation, I would not have considered * as a valid table name, however on rereading and reading the linked pages I see I missed valuable information.
For anyone who stumbles upon this when they encounter the same issue I did I am placing this crazy example

CREATE TABLE `#$%^&*()` (
 
  `%~` VARCHAR(255) DEFAULT NULL,
 
  `!@` VARCHAR(255) DEFAULT NULL
 
)
 
ENGINE = INNODB
 
COMMENT = 'Crazy tablename test';


Database changed
 
MariaDB [logging]> show tables;
 
+--------------------------------+
 
| Tables_in_logging              |
 
+--------------------------------+
 
| #$%^&*()                       |
 
| audit_hlt_specialoffer_master  |
 
| audit_opening_hours            |
 
| audit_provider_warning_message |
 
| sp_error_log                   |
 
+--------------------------------+
 
5 rows in set (0.00 sec)
 
 
 
MariaDB [logging]> select * from #$%^&*()
 
    -> \c
 
MariaDB [logging]> select * from  #$%^&*();
 
    -> \c
 
MariaDB [logging]> select * from `#$%^&*()`;
 
Empty set (0.00 sec)
 
 
 
MariaDB [logging]> show create table `#$%^&*()`;
 
ERROR 2006 (HY000): MySQL server has gone away
 
No connection. Trying to reconnect...
 
Connection id:    15
 
Current database: logging
 
 
 
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table    | Create Table                                                                                                                                                       |
 
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| #$%^&*() | CREATE TABLE `#$%^&*()` (
 
  `%~` varchar(255) DEFAULT NULL,
 
  `!@` varchar(255) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Crazy tablename test' |
 
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.01 sec)

The connection going away, in this case, isn't a database issue just a connectivity issue with this database.

Happy to close this as I now understand what I missed in the documentation, it will likely be missed by others where quoted object names are so heavily frowned upon in other databases that historically such cases show up so little. This jira might help others

Comment by Sergei Golubchik [ 2016-07-27 ]

greenman, see above. It seems to be a documentation issue.

Comment by Ian Gilfillan [ 2016-08-08 ]

https://mariadb.com/kb/en/mariadb/identifier-names/ gives the permitted range of unicode characters for identifiers. There are lots of seemingly-strange characters that are permitted when quoting - I've added an example with`*` at the bottom.

Generated at Thu Feb 08 07:42:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.