Grant privileges on MariaDB

Question

I'm trying to grant privileges for user on MariaDB 10, but I've got an error 1045

[root@lw343 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.0.11-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [mysql]> select user,host from mysql.user;                              
+--------+-----------+
| user   | host      |
+--------+-----------+
| ruser  | %         |
| root   | 127.0.0.1 |
| bill   | localhost |
| nagios | localhost |
| root   | localhost |
+--------+-----------+
5 rows in set (0.00 sec)

MariaDB [mysql]> select user(),current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

MariaDB [mysql]> show variables like 'skip_networking';                         
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO root@"localhost" IDENTIFIED BY '**********' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
MariaDB [mysql]>

I have tried all what I found on the internet, but I've got the same error. I also tried creating new user, but I still got same error on every user I try to grant on.

Does anybody could help me to resolve this problem?

Thanks in advance.

Answer 1

First of all i would check if the database server is listening on the network.

netstat -tlpn | grep mysql

i expect something like this:

tcp        0      127.0.0.1:3306              0.0.0.0:*                   LISTEN 

If the database server is listening on 127.0.0.1:3306, connection are allowed only from localhost. Change the following lines in 50-server.cnf and restart the database service (service mariadb restart).

bind-address = 0.0.0.0

• Permit listening on multiple network interfaces or a specific IP address with bind-address

Your mysql.user tables shows that user root can connect only from localhost and 127.0.0.1.

If you need a remote user, that can connect to database from everywhere (@'%'), with root privileges, you can create another superuser.

GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'%' IDENTIFIED BY 'use_a_secure_password';

Now superuser has the same privileges as the default root account, beware!

As a final step following any updates to the user privileges:

FLUSH PRIVILEGES;

Also i notice that your mysql.user tables shows a user ruser that can connect over the network.

Usefull ressources:

• Adding User Accounts
• Server Command Options - bind-address

You may also check following answer: https://stackoverflow.com/a/16288118/3095702

Answer 2

Ok, so first, understand that users are created as username/hostname combinations. So root@localhost is different from [email protected] So for a remote connection you cannot use root@localhost since that is for connecting from localhost

So, create a different user.

Secondly, if root@localhost already exists then don't use identified by since you already have a password....