Unable to Use "GRANT" command in our DB. Throws an error.

HI All,

Hope somebody can help. I need to grant remote root access to our MySQL database to a developer. I've done this in the past with out issue using the command line inputs below. However, this time it throws an error Error When Trying to Grant An IP Access to ROOT and I cannot figure out why or how to make this work. Any idea?

mysql> use mysql
mysql> GRANT ALL ON *.* to root@'IP Address' IDENTIFIED BY password';
mysql> FLUSH PRIVILEGES;
Steve HoodIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
You first have to create a user called 'root' with access from that IP address before you can grant anything.  Remote 'root' access is never allowed by default.
theGhost_k8Database ConsultantCommented:
Just to add into @Dave's comment above, you can verify the current user's capabilities by using following command:
show grants;

Open in new window



Permission required is "GRANT OPTIONS"
Ref: https://dev.mysql.com/doc/refman/5.6/en/grant.html
Steve HoodIT ManagerAuthor Commented:
Hi there, thanks for your suggestion. I did that and this is what came up.
mysql> show grants;
+-------------------------------------------------------------------------------
-------------------------------+
| Grants for root@%
                               |
+-------------------------------------------------------------------------------
-------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*67F60C26B9A
B9C99C32995C0B2F8A6382071A31A' |
+-------------------------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)
Steve HoodIT ManagerAuthor Commented:
Thanks everyone.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.