MySQL: Grant **all** privileges on database

mysql mariadb mysql-error-1142
marioosh picture marioosh · Feb 16, 2011 · Viewed 1.8M times · Source

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'

Answer

diagonalbatman picture diagonalbatman · Feb 16, 2011 
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.

Related questions

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

I have created tables in MySQL Workbench as shown below : ORDRE table: CREATE TABLE Ordre ( OrdreID INT NOT NULL, OrdreDato DATE DEFAULT NULL, KundeID INT DEFAULT NULL, CONSTRAINT Ordre_pk PRIMARY KEY (OrdreID), CONSTRAINT Ordre_fk FOREIGN KEY (KundeID) REFERENCES …

Read More
Cast int to varchar

I have below query and need to cast id to varchar Schema create table t9 (id int, name varchar (55)); insert into t9( id, name)values(2, 'bob'); What I tried select CAST(id as VARCHAR(50)) as col1 from t9; select CONVERT(…

Read More
Access denied for user 'root'@'localhost' (using password: YES) after new installation on Ubuntu

Today I did a login as root into Ubuntu 14.04.1 LTS ll and then apt-get install mariadb-server (without sudo but as root). With mySQL -h localhost -u root --password=<PW> I got Access denied for user 'root'@'localhost' (using …

Read More