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




posted @ 2016-03-21 21:36  暗夜小精灵~~  阅读(117)  评论(0编辑  收藏  举报