mysql創(chuàng)建用戶(hù)并賦予用戶(hù)權(quán)限詳細(xì)操作教程

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'

mysql> create user 'lisi'@'localhost' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

mysql> select password(123123);
+-------------------------------------------+
| password(123123)  |
+-------------------------------------------+
| *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec
//上面的碼就是加密過(guò)后的123123

select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> use mysql;

mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string  | host |
+---------------+-------------------------------------------+-----------+
| root |   | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| lisi | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
//lisi就是我們添加的

[root@web3 ~]# mysql -ulisi -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.20-log Source distribution
//成功

RENAME USER 'old_user'@'localhost' TO 'new_user'@'host'

mysql> RENAME USER 'lisi'@'localhost' TO 'zhangsan'@'192.168.200.4';
Query OK, 0 rows affected (0.00 sec)

use mysql

mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+---------------+
| user  | authentication_string   | host  |
+---------------+-------------------------------------------+---------------+
| root  |      | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| zhangsan | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | 192.168.200.4 |
+---------------+-------------------------------------------+---------------+
4 rows in set (0.00 sec)

SET PASSWORD = PASSWORD('password')

SET PASSWORD='username'@'host'=PASSWORD('password')；

mysql_safe --skip-grant-tables
//這時(shí)mysql已經(jīng)啟動(dòng)
mysql 直接不使用密碼進(jìn)入，使用update修改密碼
注：修改密碼后 刷新數(shù)據(jù)庫(kù)
flush privileges；
然后就去嘗試登錄新密碼root

grant all on *.* to lisi@'192.168.1.%' identified by '111111';

SHOW GRANTS FOR 'username'@'主機(jī)地址'；

 revoke all on *.* from lisi@'192.168.1.%';