Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm new my MySQL admin from the command line and typically use Phpmyadmin or MySql workbench to admin machines. I'm getting a problem while trying to grant a user access to DBs and hoping I could get some help to figure it out.

    [root@server20039]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL ON *.* TO 'maintenance'@'localhost' IDENTIFIED BY 'qwerty' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Then I tried this and still got the same error

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'qwerty' WITH GRANT OPTION;

and this

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
    IDENTIFIED BY PASSWORD 'qwerty'  
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

I could really use a hand figuring this out!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Check user:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Check privileges:

mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*03465AB87C2B44D98228458669A91479C2BAAA6C' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

If you have recently updated MySQL run: mysql_upgrade and then restart MySQL daemon.

share|improve this answer
    
mysql> SHOW GRANTS; | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABL ES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON . TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*02C4C7B76AEA81A9120FFC80F2933A99F0AE3499' WITH GRANT OPTION –  bakesale Apr 27 '13 at 15:51
    
The mysql_upgrade command worked. Thank you! –  bakesale Apr 27 '13 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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