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?
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.