day07_mysql权限
一、创建用户并授权
格式:GRANT 权限 ON 库.表 TO '用户名'@'指定IP' identified by '密码';
GRANT ALL PRIVILEGES ON *.* TO 'lifei'@'%' identified by 'lifei';
flush privileges;【让上面授权的操作生效】
GRANT ALL PRIVILEGES ON *.* TO 'lifei2'@'192.168.8.100' identified by 'lifei2';
flush privileges;【让上面授权的操作生效】
验证:在windows中用Navicat Lite for MySQL工具,使用上面的2个用户,登录咱们的Mysql服务器!
GRANT select ON *.* TO 'lifei3'@'%' identified by 'lifei3';
flush privileges;【让上面授权的操作生效】
验证:
mysql -u lifei -plifei
mysql> create database haha;
mysql> use haha;
mysql> create table hah(a int);
mysql> insert into hah values(10);
mysql> select * from hah;
mysql -u lifei3 -plifei3
mysql> create database haha;
show grants for 用户;【查看指定用户拥有的权限】
revoke all privileges on *.* from '用户'@'%';【收回某用户所有权限】
mysql -u root -pmysql
mysql>revoke all privileges on *.* from 'lifei'@'%';
mysql> flush privileges;
mysql> exit
mysql -u lifei -plifei
mysql> create database hehe;
当用户权限是USAGE时,这个权限最小,他只能登录!
【万能的修改密码】
update mysql.user set password=password('新密码') where user='用户名';
update mysql.user set password=password('nimei') where user='lifei';
【让权限生效】
flush privileges;
mysql -u root -pmysql
mysql> update mysql.user set password=password('haha') where user='lifei';
mysql> flush privileges;
mysql> exit
mysql -u lifei -phaha
show full processlist; 【查看有哪些用户在登录】
kill 指定用户的id 【强制退出指定用户】
mysql -u lifei -phaha
mysql -u root -pmysql
mysql> show full processlist;
+----+-------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+------+---------+------+-------+-----------------------+
| 30 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 31 | lifei | localhost | NULL | Sleep | 4 | | NULL |
+----+-------+-----------+------+---------+------+-------+-----------------------+
mysql> kill 31;
【lifei窗口操作时提示:】
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...