7

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'

  • Did you use GRANT ALL ON *.* TO 'admin'@'localhost';? In your example you say that you ran: GRANT ALL ON * . * TO 'admin'@'hostname';, but hostname is a distinct host than localhost. – oNare Sep 12 '15 at 14:36
  • hostname is 'localhost'. – Abhishek Sep 12 '15 at 17:53
  • Were you connected as "root" when you ran that GRANT? – Rick James Sep 12 '15 at 23:11
  • Yes. I was connected as "root" when I ran that GRANT. – Abhishek Sep 13 '15 at 8:04
  • Did you try flush privileges; as root? – dwjv Sep 14 '15 at 14:20
4

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.

  • I have added the error in my question. – Abhishek Sep 12 '15 at 12:34
  • @abhishek update answer.... btw what command is causing above error? Check what grants your user is having? Are you sure you're connecting as admin user? – mysql_user Sep 12 '15 at 16:53
  • Yes I am 100% sure that I am connecting as admin user. – Abhishek Sep 12 '15 at 17:58
  • mysql> show grants; +-----------------------------------------------------------------------------+ | Grants for admin@localhost | +-----------------------------------------------------------------------------+ | GRANT USAGE ON . TO 'admin'@'localhost' IDENTIFIED BY PASSWORD <secret> | | GRANT ALL PRIVILEGES ON newdb.* TO 'admin'@'localhost' | +-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) – Abhishek Sep 12 '15 at 18:00
  • Note that you have all privs on mbdb only... so you won't be able to work with any other db! – mysql_user Sep 12 '15 at 18:02
0

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.

  • Flush privileges is not required for GRANT commands. – mysql_user Sep 15 '15 at 7:05
  • Yes..I agree.... But FLUSH PRIVILAGES is required when we modify grant on tables directly using statements such as INSERT, UPDATE, or DELETE... Thanks @mysql_user – Mathew Sep 15 '15 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.