Grant privileges on MariaDB
I am trying to grant privileges to a user on MariaDB 10, but I have 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 tried everything I found on the internet, but I got the same error. I also tried to create a new user, but I still got the same error for every user I try to provide.
Does anyone help to solve this problem?
Thanks in advance.
source to share
So, first make sure users are created as username / hostname combinations. Thus, root@localhost
different from root@192.168.1.5
So, for remote connection you cannot use root@localhost
, since for connection fromlocalhost
So, create another user.
Secondly, if it root@localhost
already exists, then do not use identified by
, since you already have a password ...
source to share
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 127.0.0.1:3306
, connections are only allowed from the local host. Change the following lines in 50-server.cnf
and restart the database service ( service mariadb restart
).
bind-address = 0.0.0.0
- Allow listening on multiple network interfaces or a specific IP address with
bind-address
Your mysql.user tables show that the root user can only connect with localhost
and 127.0.0.1
.
If you need a remote user who can connect to the database from anywhere ( @'%'
), with superuser rights, you can create another superuser.
GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'%' IDENTIFIED BY 'use_a_secure_password';
The superuser now has the same privileges as the default root account, be careful!
As a final step, stay tuned for any updates to user privileges:
FLUSH PRIVILEGES;
Also I noticed that your mysql.user tables show a user ruser
who can connect over the network.
Useful Resources:
You can also check the following answer: fooobar.com/questions/66297 / ...
source to share