Linux allows thesql login from root but not from another linux user account

apache-2.4linuxMySQLredhat

So, I have two user accounts on a BlueHost owned webserver. Let's call them "root" and "user1". When I login to my root linux account, I then try to login to mysql on the "root" mysql account. I then am able to login to mysql. Everything is good there.

The problem is, when I try to login to the mysql "root" user from the "user1" linux account using the exact same creds (none. There is no password on that mysql account), I get an error message that says:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Is there some user permissions in linux that I need to change on the "user1" user?

Best Answer

Recent mysql/mariadb servers have a plugin-based authentication system, exemple:

MariaDB [(none)]> select user,host,password,plugin from mysql.user limit 1;
+------+-----------+-------------------------------------------+-------------+
| user | host      | password                                  | plugin      |
+------+-----------+-------------------------------------------+-------------+
| root | localhost |                                           | unix_socket |
+------+-----------+-------------------------------------------+-------------+

In this configuration access for root@localhost is ONLY granted to unix user "root", your real user id is sent to the mysql server and login is granted only if it matches the requested mysql user.

For "user1" to be able to log into the mysql server you will need to either:

Change root authentication back to 'normal' login/password auth (carefull, it will break most of the automated script, logrotate, service start/stop and the like). ALTER USER root@localhost identified with 'mysql_native_password'; SET PASSWORD = password('foo');
Create a mysql user1 with unix_socket auth: CREATE USER user1 IDENTIFIED VIA unix_socket; and give him some root access right GRANT ALL WITH GRANT OPTION ON *.* TO user1@localhost;

Related Solutions

Mysql – cronjob executing thesql and get root access denied

You should not rely on MySQL client to pick up the appropriate MySQL configuration file because of the cascading nature of how the client is reading those files. Instead you should put both your my.cnf and test_mysql.sh inside /home/tmp and use --defaults-extra-file [link] parameter in your script.

Do take note, the --defaults-extra-file needs to be the first argument of your mysql command otherwise it won't work.

execute_query(){
    query="$1"
     res=`mysql --defaults-extra-file=/home/tmp/my.cnf --skip-column-names -e "$query"`
    echo "$res"
}

res=`execute_query "show databases"`
echo $res

/root is not a very good place to put stuff in (permissions etc.) and should be avoided.

Running scripts that interact with a MySQL server should also be performed with a non-privileged user.

Mysql – Cannot login to thesql with non root user from command line

GRANT ... TO 'rails'@'localhost' ... REQUIRE SSL

For starters requiring SSL for connections to/from localhost is completely senseless. SSL or TLS provide transport security which you don't need for communications that won't leave the actual system, that will not provide any additional security and only extra overhead.

In addition the official MySQL libraries on Linux/UNIX treat the keyword localhost different from what you would expect and will use the Unix socket, not TCP. from the manual:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. ... You can also specify the connection protocol explicitly, by using the --protocol=TCP option.

Using the socket gives you much more performance than using TCP/IP and AFAIK the socket does not support SSL (and would not need to either).

If you do force a TCP connection, you'll need to GRANT access over TCP as well, with GRANT ... TO 'rails'@'127.0.0.1' ... , because granting connections from localhost implies the Unix socket.

Particularly unhelpful is that for error messages MySQL follows the convention to do a reverse DNS lookup for ip-addresses which will convert the ip-address 127.0.0.1 to localhost.

Best Answer

Related Solutions

Mysql – cronjob executing thesql and get root access denied

Mysql – Cannot login to thesql with non root user from command line

Related Topic