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)
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)
