MySQL Bugs: #38513: Using GRANT on skip-grant-tables causes "cannot execute this statement" error

Bug #38513	Using GRANT on skip-grant-tables causes "cannot execute this statement" error
Submitted:	1 Aug 2008 6:27	Modified:	5 Aug 2008 12:55
Reporter:	K Shirai	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	5.0.51b	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
"GRANT ALL PRIVILEGES" SQL is very convenient to create an user, but it cannot use on --skip-grant-tables environment through it is often used together.

Altenatively I can use INSERT INTO mysql.user syntax, but it is very complex.

How to repeat:
1) Launch mysqld with --skip-grant-tables.
2) Execute SQL like:

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'password';

This causes error:

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Suggested fix:
Even --skip-grant-tables is set, it should accept GRANT and update records on mysql.*.

you have to run 'flush privileges' before grant will work when running with skip-grant-tables

Many thanks for writing a feature request. I can see a security lack in your idea. Consider, --skip-grant-tables is just for setting a (forgotten) password. You never should let run a server long time with --skip-grant-tables.

The usual way is:

1) Start server with --skip-grant-tables
2) Change/add password to your super user account
3) Restart server without --skip-grant-tables
4) connect as your super user
5) Grant all on ....

Thanks for your reply.

> I can see a security lack in your idea.

Why?
Of course, I do not think to drive my server with it.
(But also I know some sites driving for getting more performance.) 

Which to use INSERT INTO or GRANT ...
it does not affect security level.
It's just difference of methods, 
the user can do same thing -- updating any privillage information.

So, if we can use easier syntax on this step:
> 2) Change/add password to your super user account
isn't it valuable feature?

As Shane already told you ... you just have to "FLUSH PRIVILEGES" after starting the daemon with --skip-grant-tables then you can use GRANT as usual.

Sorry ... I did not understand Shane's important message.
I tried it, then GRANT statement worked.

Shane, Susanne,
thank you very much.