MySQL user Management

How to create user in mysql? MySQL user account management.

**Login into the MySQL and create a new database user.

Tips: table = mysql.user

1. We are going to create user test and test1

Here, user test is can only connect to the local mysql,

test1 can connect to other mysql in other hosts also.

mysql> create user 'test'@'localhost' identified by 'test';
Query OK, 0 rows affected (0.10 sec)

mysql>

mysql> create user 'test1'@'%' identified by 'test1';
Query OK, 0 rows affected (0.10 sec)

mysql>

mysql> flush privileges ;
Query OK, 0 rows affected (0.01 sec)

mysql>

root@Server:~# hostname -i
192.168.1.10
root@Server:~#
root@Server:~# mysql -h 192.168.1.11 -u test1 -p'test1'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2. Setting User limits:

– MAX_QUERIES_PER_HOUR
– MAX_UPDATES_PER_HOUR
– MAX_CONNECTIONS_PER_HOUR
– MAX_USER_CONNECTIONS

exampe syntax: 
create user 'test'@'localhost' identified with mysql_native_password by 'test' with MAX_QUERIES_PER_HOUR 5;

3. User Expire:

create user test@'localhost' identified by 'test' password expire interval 3 day;
create user test1@'localhost' identified by 'test1' password expire interval 50 day FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;

4. Password policy:

— Validate password plugin
— Used to manage the User passwords.

policies:
0 or LOW –> Length
1 or MEDIUM –> Length, numeric, lowercase/uppercase, and special characters
2 or STRONG –> Length, numeric, lowercase/uppercase, and special characters, dictionary file

mysql> select * from mysql.user where user like 'test' \G

select user,host,plugin,authentication_string from mysql.user where user like 'test';

mysql> create user test1@localhost identified with mysql_native_password by 'test1';
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> select user,host,plugin,authentication_string from mysql.user where user in ('test', 'san');
+------+-----------+-----------------------+------------------------------------------------------------------------+
| user | host | plugin | authentication_string |
+------+-----------+-----------------------+------------------------------------------------------------------------+
| test| % | caching_sha2_password | $A$005$7^
+h:k
1>
m~9NZ.hcpxdRIkfaeEXvLeQsEs.H9VXbPfTUbF5/gxvV83 |
| test1 | localhost | mysql_native_password | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+-----------+-----------------------+------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>