Create a user
CREATE USER 'app_user'@'%' IDENTIFIED BY 'somepassword';
-- The `%` symbol in SQL user specifications acts as a wildcard, allowing connections from any host; other possible values include specific hostnames or IP addresses to restrict user access to particular sources.
mysql> CREATE USER 'app_user'@'%' IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.07 sec)
show grants for 'app_user'@'%';
mysql> show grants for 'app_user'@'%';
+--------------------------------------------------------------------------------------------------+
| Grants for app_user@% |
+--------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%` |
| GRANT ALL PRIVILEGES ON `someusername_db_dev`.* TO `app_user`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------+
mysql> show grants;
+--------------------------------------------------------------------------------------------------+
| Grants for app_user@% . |
+--------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%` |
| GRANT ALL PRIVILEGES ON `someusername_db_dev`.* TO `app_user`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------+
2 rows in set (0.05 sec)
select user,host from mysql.user;
mysql> select user,host from mysql.user;
+-------------------+-----------+
| user | host |
+-------------------+-----------+
| admin | % |
| app_user | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+-------------------+-----------+
5 rows in set (0.06 sec)
GRANT SELECT, SHOW VIEW ON *.* TO 'app_user'@'%';
FLUSH PRIVILEGES
mysql> GRANT SELECT, SHOW VIEW ON *.* TO 'app_user'@'%';
Query OK, 0 rows affected (0.09 sec)
mysql> FLUSH PRIVILEGES;
DROP USER 'app_user'@'%';
mysql> DROP USER 'app_user'@'%';
Query OK, 0 rows affected (0.07 sec)
First check the users
mysql> select user,host from mysql.user;
+-------------------+-----------+
| user | host |
+-------------------+-----------+
| app_user | % |
Alter the user password
ALTER USER 'app_user'@'%' IDENTIFIED BY 'newpassword';
-- https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html
mysql> ALTER USER 'app_user'@'%' IDENTIFIED BY 'newpassword';
SELECT user, host, password_last_changed
FROM mysql.user
WHERE user = 'app_user';
mysql> SELECT user, host, password_last_changed FROM mysql.user WHERE user = 'app_user';
+----------+------+-----------------------+
| user | host | password_last_changed |
+----------+------+-----------------------+
| app_user | % | 2024-04-28 10:57:45 |
+----------+------+-----------------------+
1 row in set (0.01 sec)