MYSQL用户管理

MySQL用户的特点和注意事项
MySQL是多用户的数据库管理系统，可以通过授予或撤销权限来控制允许或不允许用户执行的操作
用户从职权上可分为超级用户和普通用户
root用户不同于操作系统的root用户，默认对所有数据库和表具有完全访问权限
普通用户在创建数据库时具有连接数据库的权限，仅对information_schema数据库中的表的部分行具有读取权限（select）
对于实际的生产库，应尽量避免应用程序使用root用户直接操纵数据库
应根据业务需求建立普通用户并授权使其能够完成权限内的任务，防止未经授权用户访问超出其特权的数据
MySQL用户验证的三个基本要素
MySQL 使用“用户名”、“客户端主机”和“密码” 三个基本要素来验证用户
MySQL数据库使用“用户名”和“客户端主机”来区分不同的用户，例如：root@192.168.2.3和root@localhost为不同的用户
MySQL的用户以行的形式存放在mysql数据库的user表中
查看mysql用户：
select user,host,authentication_string from mysql.user;
或者：select * from mysql.user\G

客户端主机（host）的含义
MySQL早期应用于超市，每个pos机具有不同的IP，客户希望能够区分来自于不同pos机的刷卡记录，不希望收银员在超市以外的地方访问MySQL数据库，因此MySQL使用host列来限制客户机连接数据库以提高安全性，只有host列中指定的IP或主机名才能使用用户名和密码连接数据库。例如：用户知道root@localhost的密码，但是用户想从远程主机（IP:192.168.2.1）来连接数据库是不会成功的。

允许的客户端主机名（host）格式示例：
主机名： localhost
合格的主机名： mis.offcn.com
IP 地址： 192.168.2.1
IP 网络地址加掩码： 10.1.100.0/255.255.255.0
模式或通配符： % (任意字符) 或 _ (任意1个字符)
%.offcn.com
192.168.%
192.168.2.1_
%
使用包含 % 通配符字符的host允许用户从整个域或子网中的任何主机连接数据库，存在潜在安全隐患
MySQL创建用户
create user ‘username’@‘host’ identified by ‘passwoed’;
username最长16个字符，如果包含特殊字符如：下划线 _ 必须使用单引号，如果没有单引号可选，password必须用单引号括起来，否则报错
示例：
create user zhang@localhost identified by ‘zhang’;
create user ma identified bu ‘ma’;
create user ‘tom’ @ ‘192.168.2.1’ identified by ‘tom’

测试连接
-本地
->mysql -uzhang -p 可以本地连接
->mysql -uma -p 可以本地连接
->mysql -utom -p 不可以本地连接，只能‘192.168.2.1’的主机可以连接；
Windows远程连接（192.168.2.1，mysql服务器ip 192.168.2.3）；
->mysql -uzhang -p192.168.2.3 不可以连接；
->mysql -uma -p192.168.2.3 可以连接 因为host=%
->mysql -utom -p192.168.2.3 可以连接；
MySQL设置用户密码
修改自己的密码
set password=password(‘newpass’);
password为口令函数，把口令加密存放在mysql数据库的user表的authentication_string列中
修改其他用户密码（要有相应的权限）
set password for ‘username’@‘host’=password(newpass)
示例：
set password for tom@192.168.2.1=password(‘newpass’);
set password=password(‘root’)
MySQL重命名用户
使用rename user命令可以给用户名和客户机端主机改名
示例：把Tom@192.168.2.1改名tim@localhost；
rename user Tom@192.168.2.1 to tim@localhost
MySQL删除用户
使用drop user语句可以删除用户
示例删除用户tim@localhost；
drop user tim@localhost；
MySQL权限级别
全局 .
数据库级 .*
表级 .
列级
存储过程 .

MySQL GRANT授权语句
grant语句可以用来创建用户也可以修改用户的权限
grant语法：
grant 权限（colname）on . to username@host identified by ‘passed’;
示例：
创建用户wang，可以select，test库的stu表的sno列
grant select(sno) on test.stu to wang@localhost identified by ‘wang’
MySQL GRANT语句
测试（wang）
1：mysql -uwang -p
2: show databases;
3:use test;
4:show tables;
5:select *from stu 报错，只能查看sno这一列
6：select sno from stu
测试（root）
select *from mysql.tables_priv where user=‘wang’;
select from mysql.columns_priv where user=‘wang’;
MySQL显示用户权限
使用show grants语句查看用户拥有的权限
测试（root）
show grants;
show grants for wang@localhost;
MySQL REVOKE撤销授权语句
revoke语句可以用来撤销用户所拥有的权限
grant语法：
revoke 权限（colname）on . from user@host;
示例(root)，收回用户wang的select test库的stu表的sno列权限：
revoke select(sno) on test.stu from wang@localhost;
show grants for wang@localhost;
MySQL 权限管理练习
1:授予wang用户test库所有表的select权限，test.stu表sname列select和update权限
grant select on test. to wang@localhost;
grant select(sname),update(sname) on test.stu to wang@localhost;
show grants for wang@localhost;
2:查看mysql库下的授权表, user, db, tables_priv, columns_priv
select *from mysql.db where user=‘wang’\G;
select *from mysql.tables.priv where user=‘wang’
select from mysql.columns.priv where user=‘wang’
3:收回wang用户test库所有表的select权限，test.stu表sname列select和update权限
revoke select on test. from wang@localhost;
revoke select(sname),update(sname) on test.stu from wang@localhost;
show grants for wang@localhost;
4:授予wang用户全局级别的select权限
grant select on . to wang@localhost;
select * from mysql.user where user=‘wang’\G

5:收回wang用户test库所有表的select权限，test.stu表sname列select和update权限
revoke select on test.* from wang@localhost;
revoke select(sname),update(sname) on test.stu from wang@localhost;
MySQL 查看进程状态
MySQL数据库用show processlist查看服务器进程个用户连接线程的状态
SHOW PROCESSLIST 将生成以下列：
Id: 用户连接标识符
User: 发出语句的 MySQL 用户
Host: 发出语句的客户机的主机名
db: 用户选择的数据库，未选为 NULL
Command: 线程正在执行的命令类型
Time: 线程处于当前状态的时间（秒）
State: 指示线程正在执行的内容的操作、事件或状态
Info: 线程正在执行的语句；否则为 NULL

查看当前用户连接标识符(id)
mysql> select connection_id();
可以用kill命令杀掉用户连接
mysql> kill 3; --当前连接被杀掉会自动重连
mysql> show processlist;
授予普通用户wang查看当前所有连接用户线程的状态（普通用户仅能看到自己的连接线程状态）
mysql> grant process on . to wang@localhost;
mysql> show grants for wang@localhost;
mysql> revoke process on . from wang@localhost;