LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 03-15-2019, 09:46 AM   #1
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 658

Rep: Reputation: 34
MySQL-server : Access denied for user 'root'@'localhost'


Hello
I log in to Mysql server :
Code:
mysql -u root -p
I want to grant permissions to a user :
Code:
mysql> GRANT ALL PRIVILEGES ON `MyDatabase` . * TO 'admin'@'10.10.1.%' WITH GRANT OPTION ;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'MyDatabase'
As you can see this fails. Why ???

I check who I am :
Code:
mysql> SELECT USER(),CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)
I check my permissions :
Code:
mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*467DE5F1F5EFF302257F276E...' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
I check privileges :
Code:
mysql> SELECT `User`, `Grant_priv` FROM `mysql`.`user` WHERE `User` = 'root';
+------+------------+
| User | Grant_priv |
+------+------------+
| root | Y          |
| root | Y          |
| root | Y          |
+------+------------+
3 rows in set (0.00 sec)
I try to set privileges again :
Code:
mysql> grant all privileges on *.* to 'root'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
 
Old 03-15-2019, 10:18 AM   #2
sevendogsbsd
Member
 
Registered: Sep 2017
Distribution: None, just FreeBSD
Posts: 735

Rep: Reputation: Disabled
If I had to guess, it's because using root to run anything is a bad idea. Never run mysql so someone else may chime in about this. Typically, running a process as root is bad because if an attacker gets the process, they have root, which is the holy grail.
 
Old 03-15-2019, 11:00 AM   #3
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 658

Original Poster
Rep: Reputation: 34
On another server I have no issue :
Code:
mysql -u root -p
Code:
mysql> GRANT ALL PRIVILEGES ON `MyDatabase` . * TO 'admin'@'10.10.0.%';
Query OK, 0 rows affected (0.00 sec)
 
Old 03-15-2019, 11:18 AM   #4
sevendogsbsd
Member
 
Registered: Sep 2017
Distribution: None, just FreeBSD
Posts: 735

Rep: Reputation: Disabled
Like I said, it was a guess. I still wouldn't run a db server (or any server for that matter) as root. Your system though..
 
Old 03-15-2019, 01:14 PM   #5
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 658

Original Poster
Rep: Reputation: 34
Even if I want to make another user with "root-privileges", I can't because of "Access denied for user 'root'@'localhost' (using password: YES)"
 
Old 03-15-2019, 01:33 PM   #6
yancek
LQ Guru
 
Registered: Apr 2008
Distribution: PCLinux, Slackware
Posts: 8,406

Rep: Reputation: 1706Reputation: 1706Reputation: 1706Reputation: 1706Reputation: 1706Reputation: 1706Reputation: 1706Reputation: 1706Reputation: 1706Reputation: 1706Reputation: 1706
Quote:
Like I said, it was a guess. I still wouldn't run a db server (or any server for that matter) as root. Your system though..
The OP is trying to do administrative tasks in mysql as the mysql root user which is not the same as the OS system root user which doesn't necessarily mean s/he is running mysql as root.

The command you posted that 'worked' on the other system is not the same as the one that fails as the working system does not include the GRANT option. You might try comparing the Grant options on the failed instance with the Grant options on the working instance of mysql. Also, are they running the same version of mysql as changes may have been made between versions that might have an affect.
 
2 members found this post helpful.
Old 03-15-2019, 01:40 PM   #7
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 658

Original Poster
Rep: Reputation: 34
Quote:
Originally Posted by yancek View Post
The command you posted that 'worked' on the other system is not the same as the one that fails as the working system does not include the GRANT option. You might try comparing the Grant options on the failed instance with the Grant options on the working instance of mysql. Also, are they running the same version of mysql as changes may have been made between versions that might have an affect.
Adding "WITH GRANT OPTION" does not make any difference. The outcome is always "ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'MyDatabase'"
Quote:
Originally Posted by yancek View Post
Also, are they running the same version of mysql as changes may have been made between versions that might have an affect.
Same version on both systems :
Code:
mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
 
Old 03-15-2019, 01:41 PM   #8
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.5
Posts: 2,577

Rep: Reputation: 877Reputation: 877Reputation: 877Reputation: 877Reputation: 877Reputation: 877Reputation: 877
Hmmm. From my mysql (scasey is my "superuser" in mysql)
Code:
mysql> SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for scasey@localhost                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'scasey'@'localhost' WITH GRANT OPTION                                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `mailstats`.* TO 'scasey'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Your root user doesn't have that ALL PRIVILEGES grant.
Does that exist on the other, working, server?

Last edited by scasey; 03-15-2019 at 01:42 PM.
 
Old 03-15-2019, 01:43 PM   #9
sevendogsbsd
Member
 
Registered: Sep 2017
Distribution: None, just FreeBSD
Posts: 735

Rep: Reputation: Disabled
Quote:
Originally Posted by yancek View Post
The OP is trying to do administrative tasks in mysql as the mysql root user which is not the same as the OS system root user which doesn't necessarily mean s/he is running mysql as root.

The command you posted that 'worked' on the other system is not the same as the one that fails as the working system does not include the GRANT option. You might try comparing the Grant options on the failed instance with the Grant options on the working instance of mysql. Also, are they running the same version of mysql as changes may have been made between versions that might have an affect.
Thanks for the clarification, appreciate it.
 
Old 03-15-2019, 01:43 PM   #10
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 658

Original Poster
Rep: Reputation: 34
Quote:
Originally Posted by scasey View Post
Your root user doesn't have that ALL PRIVILEGES grant.
Does that exist on the other, working, server?
On working system :
Code:
mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '701d569d40123456' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
On faulty system :
Code:
mysql> SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '701d569d40123123' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
Old 03-15-2019, 02:08 PM   #11
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 658

Original Poster
Rep: Reputation: 34
I found the following solution to "re-gain" root privileges :
Code:
/sbin/service mysqld stop && mysqld_safe --skip-grant-table
and then :
Code:
mysql> grant all privileges on *.* to 'root'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
MYSQL: Access denied for user 'root'@'localhost' (using password: NO) jun_tuko Linux - Software 3 03-05-2013 06:37 AM
[SOLVED] mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: 124vikas.dange@gmail.com Linux - Server 4 11-19-2010 01:36 AM
Mysql error 1045: Access denied for user 'root'@'localhost' (using password: YES) visitashu Linux - Server 15 11-10-2010 11:05 PM
mysql server ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using pas tarak420 Linux - Server 2 09-18-2008 09:52 PM
Sarge mysql-server Access denied for user: root@localhost infinity432 Linux - Software 2 05-18-2005 11:42 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 05:06 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration