mysql权限管理（2014-09-15）

本文比较碎片化，不过以问答的形式比较容易理解。

如何查看mysql的当前登录的用户？

select user();

 mysql -hlocalhost -uroot  与root@192.168.11.100 区别？

mysql -hlocalhost -uroot只能在本地进行登录，而root@192.168.11.100不能在本地登录。

mysql匿名账户

mysql的匿名账户即user为空的行，对应的为空的

匿名账户在最新的版本中只能有对test进行操作，可以进行创建表、删除表等操作，之前的匿名用户的操作权限比较多，所以建议在安装完mysql之后第一件事就是把不用的用户都删除，只保留root@localhost

可以使用如下语句：

delete from mysql.user where user != 'root' or host ! ='localhost';

flush privileges; //  修改后的mysql用户信息会暂时存在内存中，为了即时生效，需要使用flush privileges将内存中权限的数据刷新到磁盘中。

如何创建一个新的用户？

创建新的用户常用的有两种方法，即create user 、grant方法，个人常用的是grant方法，create user方法，在创建完用户之后还需要再进行授权，需要多些操作。当然也可以直接对mysql.user表进行insert操作，建议不进行这样操作，之前有进行过这样的操作，有出现权限问题，即root用户下只有没有mysql库。

create user 例子：

create user ' t_user'@'192.168.1.15' identified by '123456';

flush privileges;

 这样创建之后只能看到一个库，其他库是看不见的

grant 例子：

grant select,insert on dbanme.* to username@'ip地址' identified by '密码';

flush privileges;

如：

 grant select on daosen.* to 'admin'@'192.168.11.212'  identified by '123456';

使用grant时需要注意的是，如果账号存在，则添加权限，若没有用户则创建用户并赋权限。若admin用户存在，再使用grant 中使用identified by 会覆盖之前密码。如grant select on daosen.* to 'admin'@'192.168.11.212'  identified by 'admin';这时的admin的密码就会被修改为admin 。如下图：

admin授权密码为123456

登录：

root用户登录下再对admin用户授同样的权限，只修改下密码：

使用admin密码123456登录，报密码错误：

使用admin密码admin登录，正常登录：

注：添加完一个用户之后需要到mysql.user表中确认用户是否添加成功，是否有相关的权限。

如何更改用户密码？

方法一：

（推荐使用）

set password for ‘wubx’@’localhost’ = password(‘NewPass’); 

flush privileges; 

方法二：(不推荐)

update mysql.user set password=password(‘密码’) where user=’用户名’ and host=’主机名’;

flush privileges; 

方法三：

mysqladmin –u用户名 password ‘新密码’–hhost_name

 如何设置mysql用户的过期？

 mysql账户、密码过期设置，5.6.17之后的功能

Alter user 帐号名 password expire;

如何修改用户名？

rename user 旧账号@旧IP to 新账号@新IP

通过这命令不仅可以修改用户名，同时也可以修改用户对应的IP地址

mysql用户名最多只能支持多少个字符？

mysql用户名最多只能支付16个字符，若超过可能会出现内存溢出现象，我们规定mysql的用户名称以字母开头。

MySQL的帐号权限管理最低的要求？

保持最小权限授权，操作最少的用户。另外对生产库需要做两件事

1. 只保留root@localhost用户

2.删除test数据 ，要把test库删掉，truncate mysql.db;需要再执行下flush privileges;

 为什么不用视图？

mysql中没有物化视图，不会带来查询性能的提升，也只是查询语句而已。

如何创建具有可以将自己的权限再授权给其他用户的新用户？

grant all priviliges on *.*   to  admin@'localhost' with grant option;

需要加上with grant option;即可

生产环境中是否保留test库？

建议删除test库，另外有一个特别的地方，凡以test开头的数据，新建的没授权的用户都可以在上面进行创建表等操作。可以查看下这类库的权限。

mysql库中的host表是否有用？

目前没有发过这表的用处，可能后期官方会去除，但目前的版本中若没有这个表数据库会无法启动。

如何收回权限？

 revoke delete from wubx@192.168.11.212; 

 revoke  delete on daosen.* from wubx@localhost; 

注：使用revoke前一定要查下这个用户是否有这个权限

对mysql的master及slave采用什么权限？

只要REPLICATION SLAVE权限，如下：

GRANT REPLICATION SLAVE ON *.* TO 'slave001'@'192.168.0.99' IDENTIFIED BY '123456';

对生产环境中应用采用什么权限？

个人觉得主要是insert、update、delete、select操作，一般就可以了。

show processlist与  show full processlist有什么区别吗 ？

show full processlist 较长的SQL不截断，show processlist会进行截断。 

show processlist各字段的意义？

.SHOW PROCESSLIST作用： 显示了正在运行的进程线程。 
. SHOW PROCESSLIST 将生成以下列: 
. –  Id:连接标识符 
. –  User:发出语句的 MySQL 用户 
. –  Host:发出语句的客户机的主机名 
. –  db:选择默认数据库,否则为 NULL 
. –  Command:线程正在执行的命令类型 
. –  Time:线程处于当前状态的时间(秒) 
. –  State:指示线程正在执行的内容的操作、事件或状态 
. –  Info:线程正在执行的语句;否则为 NULL 

常出现的权限问题及解决方法

忘记密码怎么办？

1.把skip-grant-tables 这个配置文件my.cnf到加到参数文件，重启，然后更改密码

 把参数去掉，再重启就ＯＫ了,连接到mysql中

2.进入mysql，操作mysql.user 

update mysql.user set password=pasword('newpassord') where user=XXXX and host=XXXX;

 flush privileges;

生产对于已经授权的用户列表，拒绝某台机器连接？

基本环境介绍服务帐号  app_dev@’192.168.10.%’ ,基于这样的用户：　192.168.10.%用户段的IP都可以连接进来，但运行中发现某台机器不是我们要的机器或是这台机器经常干一些让我们喜欢的事，假设这个IP是:192.168.10.11，连接MySQL的IP还有十几个，我们怎么拒绝192.168.10.11这个IP连接呢？

对192.168.10.11的IP的进行特别再赋权限，这样可以只有app_dev@'192.168.10.11' 可以使用，即最小匹配。如：

对某一段的IP赋：grant all privileges on *.* to admin@'192.168.10.%' identified by '123456';

进行限制：grant all privileges on *.* to admin@'192.168.10.11' ；

连接数统计

通过information_schema.processlist分析数据库的当前连接数，分析哪个用户、哪个主机、哪个IP等。

当前的连接数：

 select count(*) from information_schema.processlist;

  select  * from information_schema.processlist; //等价于： show processlist

 

若只要输出 ip:port 的形式，substring_index(字符串，分割符，需要的第几个字段）

 select substring_index(host,':', 1) from information_schema.processlist;

统计每个IP连接数：

select substring_index(host,":", 1) ip, count(*) from information_schema.processlist group by ip;

 到库级别的ip连接数查看：
select db, substring_index(host,":", 1) ip, count(*)  from information_schema.processlist  group by db, ip;