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.

The root account should ALREADY have all privileges. It's the default account, after all. It should have all privileges, so it can create other lesser-privileged accounts. what does `show grants for root@localhost` show? — Marc B, Nov 25 '14 at 15:05
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, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '******************' WITH GRANT OPTION — Ivanoff, Nov 25 '14 at 15:34
can't connect how? permission denied? connection refused? per.den is an account problem, con.ref is a network problem (firewall, or mysql not listening on the default port, etc...). — Marc B, Nov 25 '14 at 15:36
I've got an ACCESS DENIED FOR USER 'root' (USING PASSWORD: YES) — Ivanoff, Nov 25 '14 at 15:44
root@localhost only allows for local connections. if you're trying to connect to the root account from some OTHER machine (bad bad security hole.. never use the root account except for sysadmin tasks), then you need to create `root@ip_of_other_machine` — Marc B, Nov 25 '14 at 15:45
When I create a user root@myip, after iI try GRANT ... I've got the same error :( — Ivanoff, Nov 25 '14 at 16:09
Run this: select user,host,plugin from user; I wonder if that will only grant grants through unix sockets and not tcp sockets... — Mike, May 08 '15 at 18:35

score 1 · Answer 1 · answered Jan 30 '19 at 13:49

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:

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

score 0 · Answer 2 · answered May 30 '16 at 05:17

Ok, so first, understand that users are created as username/hostname combinations. So root@localhost is different from root@192.168.1.5 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....

Grant privileges on MariaDB

2 Answers2

Linked