涉及MySQL用户及权限管理的简单介绍

本文主要给大家介绍MySQL用户及权限管理，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下MySQL用户及权限管理吧。 

MySQL用户及权限管理

MySQL 默认管理员用户为root，但是这个用户权限太大，一般只在管理数据库时候才用。为了MySQL的安全性，在实际应用中用户权限要最小化，应该只分配使用到的权限。

1 mysql授权表

MySQL云服务器通过MySQL权限表来控制用户对数据库的访问，MySQL权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些MySQL权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

• user权限表：记录允许连接到云服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。在这里指定的一个权限适用于一个表的所有列。
• columns_priv权限表：记录数据列级的操作权限；这里指定的权限适用于一个表的特定列；
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

2 用户及密码管理

2.1 创建用户

命令:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• 新用户创建完成暂时无法登陆，因为我们还没有为这个用户分配相应权限，

说明：

• username：你将创建的用户名
• host：指定该用户在哪些主机上可以登陆，本地用户用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
• password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆云服务器
• 不管是授权，还是撤销授权，都要指定响应的host（即 @ 符号后面的内容），因为以上及格命令实际上都是在操作mysql 数据库中的user表;

例：

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101' IDENDIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

2.2 密码设置

设置root密码：

设置密码：password1

mysqladmin -uroot -password password1

修改root密码：

由password1修改为password2

mysqladmin -uroot -p password1 password password2

管理员设置其它用户密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

设置当前登陆用户（自己）密码:

SET PASSWORD = PASSWORD("newpassword");

2.3 查看当前用户

SELECT USER();

2.4 删除用户

DROP USER 'username'@'host';

2.5 查看mysql所有用户

查看mysql的所有用户及权限、加密密码、最后修改密码时间；

• MYSQL-5.6

select host,user,password from mysql.user;

• MYSQL-5.7

SELECT user,host,authentication_string,password_last_changed FROM mysql.user;

2.6 数据表结构:

DESCRIBE table1;

3 权限

3.1 授权GRANT

GRANT 语法:

GRANT privileges (columns)
ON what
TO user IDENTIFIED BY "password"
WITH GRANT OPTION

权限列表:

• CREATE：创建数据库和表。
• INSERT：插入；向表中插入新行。
• DELETE：删除表中已有的记录。
• DROP：抛弃(删除)数据库和表。
• ALTER：修改表和索引。
• INDEX：创建或抛弃索引。
• REFERENCE：未用。
• SELECT：检索表中的记录。
• UPDATE：修改现存表记录。
• FILE：读或写云服务器上的文件。
• PROCESS：查看云服务器中执行的线程信息或杀死线程。
• RELOAD：重载授权表或清空日志、主机缓存或表缓存。
• SHUTDOWN：关闭云服务器。
• ALL：所有权限，ALL PRIVILEGES同义词。
• USAGE：特殊的 "无权限" 权限。
• 用户账户包括 "username" 和 "host" 两部分，后者表示该用户被允许从何地接入。tom@'%' 表示任何地址，默认可以省略。还可以是 "tom@192.168.1.%"、"tom@%.abc.com" 等。数据库格式为 db@table，可以是 "test." 或 ".*"，前者表示 test 数据库的所有表，后者表示所有数据库的所有表。

• 子句 WITH GRANT OPTION表示该用户可以为其他用户分配权限。

• grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

只分配权限：和CREATE USER一起使用；

GRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host';

说明:

• all：要授予用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL；
• databasename：数据库名；
• tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
• username：用户；
• host：IP范围；

新建账户并分配权限：grant

GRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host' IDENTIFIED BY 'password';

说明：

• 其它同上；
• password：密码；

例子:

GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%';
GRANT SELECT,INSERT ON test.user TO 'pig'@'%';

新建账户并授予用户管理其它用户的权限

GRANT PRIVILEGES ON databasename.tablename TO 'username'@'localhost' WITH GRANT OPTION;

说明：

• 其它同上；
• WITH GRANT OPTION：授予用户管理其它用户的权限；
• WITH GRANT OPTION 一般配置的用户的host配localhost，即只能本地登陆；

常用操作

• grant 查询、插入、更新、删除MySQL 数据表结构权限。

  grant select,insert,update,delete on databasename.tablename to 'username'@'host';

• grant 创建,卸载MySQL 数据表的权限。

  grant create,drop on databasename.tablename to 'username'@'host';

• grant 作用在表中的列上：

  grant select(id, se, rank) on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 外键权限。

  grant references on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 临时表权限。

  grant create temporary tables on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 索引权限。

  grant index on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 视图、查看视图源代码 权限。

  grant create view on databasename.tablename to 'username'@'host';
  grant show view on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 存储过程、函数 权限。

  grant create routine on databasename.tablename to 'username'@'host';
  grant alter routine on databasename.tablename to 'username'@'host';
  grant execute on databasename.tablename to 'username'@'host';
  grant execute on procedure testdb.pr_add to 'dba'@'localhost';
  grant execute on function testdb.fn_add to 'dba'@'localhost';

• grant 普通 DBA 管理某个 MySQL 数据库的权限。

  grant all privileges on databasename.* to dba@'localhost';

• grant 高级 DBA 管理 MySQL 中所有数据库的权限。

  grant all on *.* to dba@'localhost'

• 关键字 privileges 如果省略的话，后面需要刷新权限。

3.2 刷新权限

修改完权限以后 一定要刷新服务，或者重启服务：

FLUSH PRIVILEGES;

3.3 查看用户权限

SHOW GRANTS [FOR 'username'@'host'];

默认查看自己的权限，加for查看指定用户的权限；

3.4 撤销权限：revoke

如果此时发现刚刚给的权限太大了，如果我们只是想授予它在某个数据库上的权限，那么需要切换到root 用户撤销刚才的权限，重新授权：

REVOKE ALL PRIVILEGES ON databasename.tablename FROM 'username'@'host';

说明:

• privilege, databasename, tablename：同授权部分

注意:
假如你在给用户'pig'@'%'授权的时候是这样的（或类似的）：GRANT SELECT ON test.user TO 'pig'@'%'，则在使用REVOKE SELECT ON . FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作。
相反，如果授权使用的是GRANT SELECT ON . TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select权限。

具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看。

4 摘要

4.1 grant和revoke可以在几个层次上控制访问权限

• 整个云服务器，使用 grant ALL 和revoke ALL
• 整个数据库，使用on database.*
• 特定表，使用on database.table
• 特定的列
• 特定的存储过程

4.2 user表中host列的值的意义

• %：匹配所有主机
• localhost： localhost不会被解析成IP地址，直接通过UNIXsocket连接
• 127.0.0.1： 会通过TCP/IP协议连接，并且只能在本机访问；
• ::1 ::1：就是兼容支持ipv6的，表示同ipv4的127.0.0.1

4.3 也可以通过安全策略删除无用的用户

推荐刚装完mysqld服务时在shell运行mysql_secure_installation命令

mysql_secure_installation

看完以上关于MySQL用户及权限管理，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的数据库栏目的。