Grant **all** privileges on database

manpreet Best Answer 2 years ago

I've created database, for example 'mydb'.

CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin;
CREATE USER 'myuser'@'%' IDENTIFIED BY PASSWORD '*HASH';
GRANT ALL ON mydb.* TO 'myuser'@'%';
GRANT ALL ON mydb TO 'myuser'@'%';
GRANT CREATE ON mydb TO 'myuser'@'%';
FLUSH PRIVILEGES;

Now i can login to database from everywhere, but can't create tables.

How to grant all privileges on that database and (in the future) tables. I can't create tables in 'mydb' database. I always get:

CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
ERROR 1142 (42000): CREATE command denied to user 'myuser'@'...' for table 't'

manpreet 2 years ago

This is old question but I don't think the accepted answer is safe. It's good for creating a super user but not good if you want to grant privileges on a single database.

grant all privileges on mydb.* to myuser@'%' identified by 'mypasswd';
grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd';

% seems to not cover socket communications, that the localhost is for. WITH GRANT OPTION is only good for the super user, otherwise it is usually a security risk.

Hope this helps.