How to create and maintain users and roles, assign privileges mysql 8.0.

         How to create and maintain users and roles, assign privileges mysql 8.0.

mysql.user: User accounts, global privileges, and other non-privilege columns
mysql.db: Database-level privileges
mysql.tables_priv: Table-level privileges
mysql.columns_priv: Column-level privileges
mysql.procs_priv: Stored procedure and function privileges
mysql.proxies_priv: Proxy-user privileg

mysql> select DISTINCT User FROM mysql.user;
+------------------+
| User             |
+------------------+
| pankaj           |
| rpl_user         |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
6 rows in set (0.00 sec)

mysql> SELECT VALIDATE_PASSWORD_STRENGTH('weak');
+------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('weak') |
+------------------------------------+
|                                 25 |
+------------------------------------+
1 row in set (0.17 sec)

mysql> create user pankaj@localhost identified by 'Pankajsingh_98765';
Query OK, 0 rows affected (0.15 sec)

mysql> GRANT ALL ON pankaj.* TO 'user'@'localhost';
   
Query OK, 0 rows affected (0.07 sec)

[root@master ~]# mysql -u user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 287
Server version: 8.0.13 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| pankaj             |
+--------------------+
2 rows in set (0.07 sec)

mysql> GRANT ALL ON *.* TO 'user'@'localhost';
Query OK, 0 rows affected (0.11 sec)

[root@master ~]# mysql -u user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 290
Server version: 8.0.13 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| pankaj             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> GRANT SELECT ON pankaj.ram TO 'user'@'localhost';
Query OK, 0 rows affected (0.10 sec)

mysql> GRANT ALL ON *.* TO 'user'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
Query OK, 0 rows affected (0.11 sec)

mysql>GRANT SELECT, INSERT ON *.* TO 'user'@'localhost';

mysql>GRANT SELECT, INSERT ON mydb.* TO 'user'@'localhost';

mysql>GRANT SELECT, INSERT ON mydb.mytbl TO 'user'@'localhost';

mysql>GRANT SELECT (col1), INSERT (col1, col2) ON pankaj.emp TO 'user'@'localhost';

mysql>GRANT CREATE ROUTINE ON pankaj.* TO 'user'@'localhost';

mysql>GRANT EXECUTE ON PROCEDURE pankaj.emp TO 'user'@'localhost';

mysql> show grants for 'tiger'@'localhost';

mysql> REVOKE select ON pankaj.ram FROM 'tiger'@'localhost';

mysql> REVOKE select ON pankaj.* FROM 'tiger'@'localhost';

mysql> REVOKE select ON *.* FROM 'tiger'@'localhost';


mysql> SELECT host,user from mysql.user;
+-----------------+------------------+
| host            | user             |
+-----------------+------------------+
| 192.168.142.152 | rpl_user         |
| localhost       | mysql.infoschema |
| localhost       | mysql.session    |
| localhost       | mysql.sys        |
| localhost       | root             |
| localhost       | user             |
+-----------------+------------------+
6 rows in set (0.00 sec)


mysql> rename user 'user'@'localhost' to 'tiger'@'localhost';
Query OK, 0 rows affected (0.10 sec)

mysql> SELECT host,user from mysql.user;
+-----------------+------------------+
| host            | user             |
+-----------------+------------------+
| 192.168.142.152 | rpl_user         |
| localhost       | mysql.infoschema |
| localhost       | mysql.session    |
| localhost       | mysql.sys        |
| localhost       | root             |
| localhost       | tiger            |
+-----------------+------------------+
6 rows in set (0.00 sec)


mysql> SELECT Host, User FROM mysql.user WHERE User='rpl_user';
+-----------------+----------+
| Host            | User     |
+-----------------+----------+
| 192.168.142.152 | rpl_user |
+-----------------+----------+

mysql> SELECT Host, User FROM mysql.user;
+-----------------+------------------+
| Host            | User             |
+-----------------+------------------+
| 192.168.142.152 | pankaj           |
| 192.168.142.152 | rpl_user         |
| 192.168.142.153 | pankaj           |
| localhost       | mysql.infoschema |
| localhost       | mysql.session    |
| localhost       | mysql.sys        |
| localhost       | root             |
| localhost       | user             |
+-----------------+------------------+
8 rows in set (0.01 sec)

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.05 sec)



mysql> alter user 'rpl_user'@192.168.142.152 identified by 'Pankajsingh_98765';
Query OK, 0 rows affected (0.05 sec)


1.drop user

mysql> DROP USER 'slave_user'@'%';
Query OK, 0 rows affected (0.25 sec)