mysql中用户权限配置

一、创建用户并授权

   格式：GRANT 权限 ON 库.表 TO '用户名'@'指定IP' identified by '密码';

select distinct user from mysql.user; 【查询当前库默认有哪些用户，默认只有1个root】


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;【让上面授权的操作生效】


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 权限 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...