CentOS - Mar 2015 - mysql can't connect from localhost -strange behavior

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 at 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 at ops:~] #grep log /etc/my.cnf
log-error=/var/log/mysqld.log

[root at 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

> mysql> grant all privileges on ftp.* to
'proftpd'@'localhost' identified by
> 'testpattern';
> Query OK, 0 rows affected (0.35 sec)
mysql> FLUSH PRIVILEGES;

>
> mysql> FLUSH PRIVILEGES;

Yup! That was it. Thanks for the reminder! :)

Tim

On Mon, Mar 30, 2015 at 12:15 AM, Steven Tardy <sjt5atra at 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 at centos.org
> http://lists.centos.org/mailman/listinfo/centos
>


-- 
GPG me!!

gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B

[root at ops:~] #mysql --user=proftpd --password=testpattern  -h localhost


On Sun, Mar 29, 2015 at 10:27 PM, Tim Dunphy <bluethundr at 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 at 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 at ops:~] #grep log /etc/my.cnf
> log-error=/var/log/mysqld.log
>
> [root at 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 at centos.org
> http://lists.centos.org/mailman/listinfo/centos
>


-- 
-----------------
Hal Wigoda
Chicago

On Mon, Mar 30, 2015 at 12:27 AM, Tim Dunphy <bluethundr at 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 at 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
-- 
During times of Universal Deceit, telling the truth becomes a revolutionary
act
 George Orwell