MySQL Bugs: #80379: GRANT fails when table does not exist

Bug #80379	GRANT fails when table does not exist
Submitted:	15 Feb 2016 22:22	Modified:	7 Mar 2018 13:40
Reporter:	monty solomon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.6.25, 5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Even when a user already has privileges to create a table the GRANT to provide other privileges fails when the table does not exist. The requirement that the CREATE privilege be in the same GRANT statement seems excessive.

When I want to grant specific privileges to a user on a table that will exist later I have to grant that user CREATE privilege even when I don't want the user to have that privilege or I need to use a wildcard for the table and provide unwanted grants for other tables.

To accomplish the goal I need to execute statements like

GRANT CREATE, DELETE, INSERT, SELECT, UPDATE on test.futuretable to newuser;
REVOKE CREATE on test.futuretable from newuser;

The requirement that the CREATE privilege be specified is ignored when a wildcard is used for the table name.

GRANT DELETE, INSERT, SELECT, UPDATE on test.* to newuser;

How to repeat:
mysql> GRANT CREATE on test.futuretable to newuser IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT DELETE, INSERT, SELECT, UPDATE on test.futuretable to newuser;
ERROR 1146 (42S02): Table 'test.futuretable' doesn't exist

mysql> GRANT DELETE, INSERT, SELECT, UPDATE on test.* to newuser;
Query OK, 0 rows affected (0.00 sec)

mysql> drop user newuser;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL on test.* to newuser IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT DELETE, INSERT, SELECT, UPDATE on test.futuretable to newuser;
ERROR 1146 (42S02): Table 'test.futuretable' doesn't exist

mysql> GRANT DELETE, INSERT, SELECT, UPDATE on test.* to newuser;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
Permit MySQL to grant privileges on any non-existent database or table without requiring the CREATE privilege. The database administrator needs to be able to prepare user accounts and privileges for databases or tables that will be created at a later time even when those user accounts will not have or need the CREATE privilege.

Hello Monty,

Thank you for the report.
IMHO this is by design, and documented behavior as "MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the privileges to be granted must include the CREATE privilege" in https://dev.mysql.com/doc/refman/5.6/en/grant.html

Also see - Bug #303, some more related info in here Bug #10406

Thanks,
Umesh

The first example I provided shows that the CREATE privilege was already granted for the table

mysql> GRANT CREATE on test.futuretable to newuser IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS for newuser\G
*************************** 1. row ***************************
Grants for newuser@%: GRANT USAGE ON *.* TO 'newuser'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
*************************** 2. row ***************************
Grants for newuser@%: GRANT CREATE ON `test`.`futuretable` TO 'newuser'@'%'
2 rows in set (0.00 sec)

mysql> GRANT DELETE, INSERT, SELECT, UPDATE on test.futuretable to newuser;
ERROR 1146 (42S02): Table 'test.futuretable' doesn't exist

The bugs you referenced are very old (from 2003 and 2005). Maybe it is time to re-evaluate this issue and the design decision.

Hi Monty,

As my learned colleague has already indicated, in order to create a table, you must have grants to the access to the database. Then you can get a GRANT to create tables there. After you have a grant and you have a created a table, you can add other grants to that table.

This: "in order to create a table, you must have grants to the access to the database" is irrelevant.

When provisioning a user account it may, for example, be necessary that the user have SELECT access on a table that yet to be created.  There may never be a requirement that this user can CREATE that table.  Another user may create the table.  As far as I can tell, there is no reason to prevent GRANTS like this, except that it was just never allowed before.

The fact that you can get around the limitation on GRANTS by using wildcards, or by adding, then removing other privileges seems proof enough that there's noting wrong with the idea.   

In fact, if it is to be considered improper to allow certain privileges to exist on non-existent tables, and it is possible to create that situation using valid commands... well there's a bug in there somewhere AND the documentation has some problems, too.   The most consistent and simple solution is the "recommended solution" noted previously.

The restriction on the GRANT statement seems unreasonable and it is inconsistent since the server rejects

mysql> DROP USER IF EXISTS newuser;
mysql> GRANT CREATE on test.futuretable to newuser IDENTIFIED BY 'password';
mysql> GRANT DELETE, INSERT, SELECT, UPDATE on test.futuretable to newuser;

but allows

mysql> DROP USER IF EXISTS newuser;
mysql> GRANT CREATE, DELETE, INSERT, SELECT, UPDATE on test.futuretable to newuser IDENTIFIED BY 'password';
mysql> REVOKE CREATE on test.futuretable FROM newuser;

and allows

mysql> DROP USER IF EXISTS newuser;
mysql> CREATE TABLE test.futuretable (a int);
mysql> GRANT DELETE, INSERT, SELECT, UPDATE on test.futuretable to newuser IDENTIFIED BY 'password';
mysql> DROP TABLE test.futuretable;

The text "the privileges to be granted must include the CREATE privilege" in the documentation is ambiguous. Does that mean the overall privileges granted or just the ones specified in the GRANT statement?

14.7.1.4 GRANT Syntax
http://dev.mysql.com/doc/refman/5.7/en/grant.html

MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the privileges to be granted must include the CREATE privilege. This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for databases or tables that are to be created at a later time.

In the example provided by Joro in the comment to bug #85302 the user has all the privileges for the table after the table is dropped and that is viewed as a positive outcome. An attempt to execute a GRANT statement without CREATE privileges for that user will fail even though those privileges already exist.

mysql> GRANT DELETE, INSERT, SELECT, UPDATE on haha.t1 to hoho@localhost;
ERROR 1146 (42S02): Table 'haha.t1' doesn't exist

mysql> select * from tables_priv where User='hoho'\G
*************************** 1. row ***************************
       Host: localhost
         Db: haha
       User: hoho
 Table_name: t1
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger
Column_priv:

Hi!

Our documentation is quite correct in this case. That is the intended behaviour, not just for the reasons that are mentioned in our documentation. There are others that fully justify that CREATE must be among the the GRANT privileges. One of them is that sometimes, administrators backup entire schema, then drop it and then restore it from the backup. This helps in the defragmentation of the data ..... We see no reason for citing all possible reasons of why the behaviour is as it is. It is. enough that it is documented properly.

Regarding privileges being limited to those in the GRANT statement only, that follows from the title of the paragraph.