How to grant database creation privilege to a user in MySQL

Question

Following command provides all privilege in all databases for User 'admin'. But it doesn't allow 'admin' to create database.

GRANT ALL ON * . * TO 'admin'@'localhost'; 

How can I provide the access for 'admin' to create database?

As I am trying to Create database, I am getting the following error -

ERROR 1044 (42000): Access denied for user 'admin'@'localhost' to database 'newdb'

Answer 1

ALL privileges includes "ALL" of them except "wITH GRANT OPTION"... Thus above command will let admin user create the database too.

You might want to show the error that you're getting. Note that in above GRANT you have not specified the password for admin user too.

Update:

You should be able to do following:

login as root and create user:

$] mysql -uroot -p
mysql> grant all privileges on *.* to 'admin'@'localhost';

login as admin (without password):

$] mysql -uadmin -p
mysql> create database admin;

When you login, you can verify your privileges as follows:

mysql> show grants;

or

mysql> show grants for current_user();

mysql> select current_user() will show you, user you logged in as.

Answer 2

I know this is a bit old but all other answers are about creating another root-like admin user, so I wanted to add a few things (hopefully helpful).

First of all, as Mathew also stated, a new user has no privilege at-all. However, an admin or root user can grant it privileges so that it can create its own databases and has privileges on them.

There might be multiple ways to achieve this, but this is how I usually do it:

• create a user, named "foo" (can use password etc for auth)
• grant privileges on it with wildcard so that newly created database privileges need not be added manually afterwards

example grant command:

GRANT ALL PRIVILEGES ON `foo\_%`.* TO 'foo'@'localhost';

this command lets foo user at localhost to create "foo_bar" database and when it is created, the user has all privileges on it.

Answer 3

A new user has no permissions to do anything with the databases. In fact, if new user even tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, you need to provide the user with the access

GRANT ALL PRIVILEGES ON * . * TO 'admin'@'localhost'; 

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

Now, all the changes would be in effect.