mysqlmariadbmysql-error-1142

MySQL: Grant **all** privileges on database


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'

Solution

  • GRANT ALL PRIVILEGES
    ON mydb.*
    TO 'myuser'@'%'
    WITH GRANT OPTION;
    

    This is how I create my "Super User" privileges (although I would normally specify a host).

    IMPORTANT NOTE

    While this answer can solve the problem of access, WITH GRANT OPTION creates a MySQL user that can edit the permissions of other users.

    The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

    For security reasons, you should not use this type of user account for any process that the public will have access to (i.e. a website). It is recommended that you create a user with only database privileges for that kind of use.