Hey all,
I've been having some trouble creating a mysql user that can connect to the database from localhost. It's always been a straight forward thing to do in the past, so its time for a sanity check, if you guys don't mind.
Ok, so here's the actual command with actual simplified password that I'm using. It's on localhost so I don't think it's a security threat unless someone gets access to the box itself.
mysql> grant all privileges on ftp.* to 'proftpd'@'localhost' identified by 'testpattern'; Query OK, 0 rows affected (0.35 sec)
Here's what it looks like when you select it from the mysql database:
mysql> select User,Host,Password from user where User like 'proftpd'; +---------+-----------+-------------------------------------------+ | User | Host | Password | +---------+-----------+-------------------------------------------+ | proftpd | localhost | *2EE931CA39652F1ED359A3A36961511B387E74A9 | +---------+-----------+-------------------------------------------+ 1 row in set (0.00 sec)
And here's my attempt to connect with the password shown. Which is something I don't usually do, but am doing now to demonstrate what's going on:
[root@ops:~] #mysql -uproftpd -ptestpattern -h localhost ERROR 1045 (28000): Access denied for user 'proftpd'@'localhost' (using password: YES)
OK, so as I've said this should work!
The database I'm trying to give the user access to does also exist:
mysql> show databases like 'ftp'; +----------------+ | Database (ftp) | +----------------+ | ftp | +----------------+ 1 row in set (0.34 sec)
I checked the error log for mysql and didn't find any clues there:
[root@ops:~] #grep log /etc/my.cnf log-error=/var/log/mysqld.log
[root@ops:~] #tail /var/log/mysqld.log InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 150329 13:30:34 InnoDB: Waiting for the background threads to start 150329 13:30:35 InnoDB: 5.5.42 started; log sequence number 6071094973 150329 13:30:35 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 150329 13:30:35 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 150329 13:30:35 [Note] Server socket created on IP: '0.0.0.0'. 150329 13:30:35 [Note] Event Scheduler: Loaded 0 events 150329 13:30:35 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.42' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi
Does anybody out there have any idea why this isn't working?
Thanks Tim
mysql> FLUSH PRIVILEGES;
Yup! That was it. Thanks for the reminder! :)
Tim
On Mon, Mar 30, 2015 at 12:15 AM, Steven Tardy sjt5atra@gmail.com wrote:
mysql> grant all privileges on ftp.* to 'proftpd'@'localhost'
identified by
'testpattern'; Query OK, 0 rows affected (0.35 sec)
mysql> FLUSH PRIVILEGES; _______________________________________________ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
On 30/03/15 05:22, Tim Dunphy wrote:
mysql> FLUSH PRIVILEGES;
Yup! That was it. Thanks for the reminder! :)
Tim
From the mySQL man pages:
If you modify the grant tables indirectly using account-management statements such as GRANThttps://dev.mysql.com/doc/refman/5.5/en/grant.html, REVOKEhttps://dev.mysql.com/doc/refman/5.5/en/revoke.html, SET PASSWORDhttps://dev.mysql.com/doc/refman/5.5/en/set-password.html, or RENAME USERhttps://dev.mysql.com/doc/refman/5.5/en/rename-user.html, the server notices these changes and loads the grant tables into memory again immediately.
So I'm confused as to why FLUSH PRIVILEGES is necessary, because this suggests the GRANT command is doing a flush anyway.
I ask because I get this behaviour occasionally with adding mySQL users/permissions too, and I hate blithely running a command 'because it works' if it's not actually the correct thing to do (though admittedly it does work).
Because we're creating a user as well as assigning permissions maybe? So a GRANT to an existing user wouldn't require the FLUSH PRIVILEGES?
Appreciate this is a mySQL rather than CentOS question, but it bugs me on CentOS machines, so that's something!
Paul
--
[root@ops:~] #mysql --user=proftpd --password=testpattern -h localhost
On Sun, Mar 29, 2015 at 10:27 PM, Tim Dunphy bluethundr@gmail.com wrote:
Hey all,
I've been having some trouble creating a mysql user that can connect to the database from localhost. It's always been a straight forward thing to do in the past, so its time for a sanity check, if you guys don't mind.
Ok, so here's the actual command with actual simplified password that I'm using. It's on localhost so I don't think it's a security threat unless someone gets access to the box itself.
mysql> grant all privileges on ftp.* to 'proftpd'@'localhost' identified by 'testpattern'; Query OK, 0 rows affected (0.35 sec)
Here's what it looks like when you select it from the mysql database:
mysql> select User,Host,Password from user where User like 'proftpd'; +---------+-----------+-------------------------------------------+ | User | Host | Password | +---------+-----------+-------------------------------------------+ | proftpd | localhost | *2EE931CA39652F1ED359A3A36961511B387E74A9 | +---------+-----------+-------------------------------------------+ 1 row in set (0.00 sec)
And here's my attempt to connect with the password shown. Which is something I don't usually do, but am doing now to demonstrate what's going on:
[root@ops:~] #mysql -uproftpd -ptestpattern -h localhost ERROR 1045 (28000): Access denied for user 'proftpd'@'localhost' (using password: YES)
OK, so as I've said this should work!
The database I'm trying to give the user access to does also exist:
mysql> show databases like 'ftp'; +----------------+ | Database (ftp) | +----------------+ | ftp | +----------------+ 1 row in set (0.34 sec)
I checked the error log for mysql and didn't find any clues there:
[root@ops:~] #grep log /etc/my.cnf log-error=/var/log/mysqld.log
[root@ops:~] #tail /var/log/mysqld.log InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 150329 13:30:34 InnoDB: Waiting for the background threads to start 150329 13:30:35 InnoDB: 5.5.42 started; log sequence number 6071094973 150329 13:30:35 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 150329 13:30:35 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 150329 13:30:35 [Note] Server socket created on IP: '0.0.0.0'. 150329 13:30:35 [Note] Event Scheduler: Loaded 0 events 150329 13:30:35 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.42' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi
Does anybody out there have any idea why this isn't working?
Thanks Tim
-- GPG me!!
gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B _______________________________________________ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
On Mon, Mar 30, 2015 at 12:27 AM, Tim Dunphy bluethundr@gmail.com wrote:
I've been having some trouble creating a mysql user that can connect to the database from localhost. It's always been a straight forward thing to do in the past, so its time for a sanity check, if you guys don't mind.
Hi Tim,
You should keep in mind the security-related changes coming in MySQL 5..7, if you ever choose to upgrade. https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html http://mysqlserverteam.com/whats-new-in-mysql-5-7-so-far/
Repos https://dev.mysql.com/downloads/repo/yum/
Best, FC
On Mon, Mar 30, 2015 at 3:16 AM, Fernando Cassia fcassia@gmail.com wrote:
You should keep in mind the security-related changes coming in MySQL 5..7, if you ever choose to upgrade.
Sorry, I erased this link by mistake while composing my reply. Here it goes.
http://mysqlopt.blogspot.com/2015/02/mysql-575-m15-out-of-box-security.html
FC
-
Fernando Cassia -
Hal Wigoda -
Paul Jones -
Steven Tardy -
Tim Dunphy