Unable to create new databases in mysql

Question

I am able to create new databases through the root account. but I need to create new databases using a new user that I have created.

Here is error I'm getting when trying to create a database using the new user

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

How can I grant permissions to "user" so it create any number of new databases?

Update:

This is also what I tried:

mysql> grant all privileges on db.* to 'user'@'%' with create;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create option' at line 1
mysql>

the below link should help clear your doubt http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database — UserX, Nov 05 '14 at 19:01

baao · Answer 1 · 2014-11-05T19:50:21.680

0

PHP example:

$con = new mysqli("...","...","..."); // CONNECT TO DB WITH ROOT USER

mysqli_query($con, "GRANT ALL PRIVILEGES ON newdb.* TO 'user'@'%' WITH GRANT OPTION");

Command line example:

login with root user, and use this:

GRANT ALL PRIVILEGES ON newdb.* TO 'user'@'%' WITH GRANT OPTION;

This will create a user how is permitted to create new databases (but will give him also all the permissions to do bad stuff, so handle it wise...).

This is for create only:

GRANT CREATE ON *.* TO 'newuser'@'%' IDENTIFIED BY 'password'

edited Nov 05 '14 at 19:50

answered Nov 05 '14 at 19:13

baao

71,625
17
143
203

Did you close it with ; ? Tried the statement 5 times – baao Nov 05 '14 at 20:02
Yea it cant work without ';'. Same error: mysql> create database newDatabase; ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'newDatabase' mysql> – EternallyCurious Nov 05 '14 at 20:06
ahhh, you need to do that with the root user – baao Nov 05 '14 at 20:07
huh? I want the right to "create new databases" with 'user'. How do I get that? – EternallyCurious Nov 05 '14 at 20:11
but to create the user / grant him the rights, you need to have the rights to do that. Later you can create dbs with the user you granted rights to – baao Nov 05 '14 at 20:12
Ohh! I'm making the grant statement from root which is working fine - only the create database statement from user, which is throwing the error shown above. – EternallyCurious Nov 05 '14 at 20:17
I don't get the error. You gave the user 'user' create rights, and you are getting an access denied error if you are creating a db with this user? – baao Nov 05 '14 at 20:25
Exactly! Isn't that weird? – EternallyCurious Nov 05 '14 at 20:33
In fact it is... :) you might want to try to change the % to localhost, but that's usually not the solution - well might be – baao Nov 05 '14 at 20:34
I need access from remote locations. The mysql server is running on a vm. – EternallyCurious Nov 05 '14 at 20:36
Right now, I'm trying from the same machine. But I have a java program on the host machine which will access the database. – EternallyCurious Nov 05 '14 at 20:38
sorry, there's nothing that comes to my mind to solve this. Except using grant all privileges... – baao Nov 05 '14 at 20:40
can you try this and look if the rights are correct for the user SELECT * FROM mysql.user; – baao Nov 05 '14 at 20:42
Ahh! Create now works from localhost. But I can't perform drop/se;ect operations. This is really weird. – EternallyCurious Nov 05 '14 at 20:44
This was what I expected with the other comment because of localhost. Those drop operations aren't included in create. The user now only can create databases. That's why I wrote the other line with all the privileges. – baao Nov 05 '14 at 20:46
Interstingly.. I executed both lines, but I got only create privileges; – EternallyCurious Nov 05 '14 at 20:50

Unable to create new databases in mysql

1 Answers1