grant all permissions to a user on mysql database

Posted on 2010-11-08
Last Modified: 2012-05-10
Hi, I want to grant all permissions to a user on mysql database. I have logged on as administrator to the computer (i.e not on a domain). Computer is windows xp pro.

I know the following works for granting on an IP address

GRANT ALL ON mydatabase.* TO 'root'@'';

I tried

GRANT ALL ON mydatabase.* TO 'root'@'administrator';

that didn't work though.

Question by:RupertA
  • 5
  • 2
LVL 28

Expert Comment

ID: 34086299
Have  you tried:
    ON mydatabase.*
    TO root@localhost
     IDENTIFIED BY 'newpassword';

Author Comment

ID: 34086364
hi sammy, why would I do the

IDENTIFIED BY 'newpassword';


LVL 28

Expert Comment

ID: 34086767
wouldn't you want the user to have password?
LVL 28

Expert Comment

ID: 34086777
so basically, if you wish to grant permission to a user called root, then you need to identify that user by his/her password.

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

LVL 28

Accepted Solution

sammySeltzer earned 250 total points
ID: 34086800
If you don't wish to identify the user by password, then I think this will work:
GRANT ALL PRIVILEGES ON mydatabase.* TO root@localhost

If it is not localhost, then enter servername there.
LVL 28

Expert Comment

ID: 34086821
One last point here.

Reason yours is not working when you substitute ip address with administrator is that you are confusing the system.

It should either be ip address or servername:

Assisted Solution

wolfgang_93 earned 250 total points
ID: 34086907
By default the MySQI id root already has full access to all objects in all databases.

Generally you do not want to change anything about root other than to give it a
password (be default root does not have a password and it is VERY important
that it has one).

To setup other users and give them access to a particular database (I am perhaps
including some stuff that you have already done toward setting things up):

Issue the following commands as the id "root" (which is allowed to create new
users and give them access):

Create a new user "administrator" with password "xyz" allowed to access the system
from any client:
   GRANT USAGE ON *.* TO administrator@'%' IDENTIFIED BY 'xyz'

Create a new database called "mydatabase" if you haven't done it already:
   CREATE DATABASE mydatabase

Give user "administrator" via any client machine full access to the new database:
   GRANT USAGE ON *.* TO administrator@'%' IDENTIFIED BY 'xyz'

Finally flush the cache and make the new privileges stick:


Author Comment

ID: 34092214
Sammy's did solve this but Wolfgang's info was very helpful to aid my understanding so I am going to do 50/50.

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now