MariaDB CREATE USER and GRANT PERMISSION not working for me

Question

I am trying to add a user called admin in mariadb and grant them all permissions from any host.

I can see the users get added by examining the rows in mysql.user and after I create the user I see entries for 'admin'@'localost' and 'admin'@'%', but when I try to perform the grants, they don't get actioned;

Here's what I'm doing as the root user.

MariaDB [mysql]> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'mypswd';
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> select user, host, password from mysql.user;
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost |                                           |
| root  | 127.0.0.1 |                                           |
| root  | ::1       |                                           |
| admin | localhost | *81C702316842FA904B04F249E80134D93FEDB64C |
+-------+-----------+-------------------------------------------+
4 rows in set (0.000 sec)

MariaDB [mysql]> CREATE USER 'admin' IDENTIFIED BY 'mypswd';
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> select user, host, password from mysql.user;
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost |                                           |
| root  | 127.0.0.1 |                                           |
| root  | ::1       |                                           |
| admin | %         | *81C702316842FA904B04F249E80134D93FEDB64C |
| admin | localhost | *81C702316842FA904B04F249E80134D93FEDB64C |
+-------+-----------+-------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%'  IDENTIFIED BY 'mypswd';
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost'  IDENTIFIED BY 'mypswd';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*4B431B2B44AFED5F3EBCF1E6DFB60B3164A4B0D4' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Why are my GRANT's not working?

score 0 · Accepted Answer · answered Jan 17 '20 at 18:33

Sorry, my bad. I did search quite hard for an answer but I only just discovered that if you are logged in as root from localhost and just use SHOW GRANTS; it will show you just those grants for 'root'@'localhost'.

I needed to do this:

MariaDB [mysql]> SHOW GRANTS FOR 'admin'@'%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                            |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*81C702316842FA904B04F249E80134D93FEDB64C' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mysql]> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*81C702316842FA904B04F249E80134D93FEDB64C' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

I didn't find this intuitive.

MariaDB CREATE USER and GRANT PERMISSION not working for me

1 Answers1