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?
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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.

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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Result Set to XML 4 40
Query still returning duplicates 5 32
SYbase 4 32
INDEX does not make a difference, why? 10 51
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

831 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