阅读 1317

重学MySQL系列(四):10分钟快速掌握MySQL用户与权限管理

原创作者,公众号【程序员读书】,欢迎关注公众号,转载文章请注明出处哦。

在这篇文章中,我们来聊聊怎么管理MySQL的用户及如何为不同用户分配不同的管理权限,其实,在日常使用MySQL的过程中,这部分的工作是由DBA(数据管理员)来完成的。

而我们作为一般的开发人员,只要拿到分配好的账号和密码连接到MySQL服务器访问特定的数据库,并不涉及到用户管理的内容。

不过,今天我们不妨切换到一个DBA视角,一起来详细了解MySQL有关用户及权限管理的相关知识。

权限数据表

MySQL中与系统用户及用户权限管理有关的信息都存储在系统数据库mysql中,这些数据表需要经过数据初始化,每个数据表有特定的数据结构。

数据表初始化

在安装好MySQL之后,需要初始化MySQL系统数据库,比如我们一般安装好MySQL之后,MySQL会帮我们创建默认的超级管理员root,我们也可以使用下面的语句在初始化时创建其他账号:

MySQL支持很多种不同的安装方式,一般情况下会帮我们初始化mysql系统数据库。

bin/mysqld --initialize --user=mysql
bin/mysqld --initialize-insecure --user=mysql
复制代码

权限表简介

MySQL系统数据库mysql中与用户及权限相关的数据表一共有6张,下面表格这几张数据表的说明。

系统数据库mysql中除了与权限管理有关的数据外,还有其他的数据表。

数据表 说明
user 用户帐户,全局特权和其他非特权列
db 数据库级别的管理权限
tables_priv 数据表级别的管理权限
columns_priv 数据列级别的管理权限
procs_priv 存储过程及函数的使用权限
proxies_priv 代理用户权限

权限表的结构

下面我们来看这些数据表的更详细表结构,我们看到下面的数据表中的字段可以大体分为四类,分别为scope columes,Privilege columns,Security columns,Resource control columns

scope columes用于限定是某个用户,某个数据库或者某个数据表,Privilege columns表示权限,在user表中表示全局权限,在db表中表示对某个数据库的操作权限,而Security columnsResource control columns的字段则为user表所独有。

user与db数据表

user数据表存储的是用户的账号信息及全局权限信息,而db数据表存储的是用户对于具体数据库的操作权限。

表名 user db
Scope columns Host Host
User Db
User
Privilege columns Select_priv Select_priv
Insert_priv Insert_priv
Update_priv Update_priv
Delete_priv Delete_priv
Index_priv Index_priv
Alter_priv Alter_priv
Create_priv Create_priv
Drop_priv Drop_priv
Grant_priv Grant_priv
Create_view_priv Create_view_priv
Show_view_priv Show_view_priv
Create_routine_priv Create_routine_priv
Alter_routine_priv Alter_routine_priv
Execute_priv Execute_priv
Ttrigger_priv Ttrigger_priv
Event_priv Event_priv
Create_tmp_table_priv Create_tmp_table_priv
Lock_tables_priv Lock_tables_priv
References_priv References_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Show_db_priv
Super_priv
Repl_slave_priv
Repl_client_priv
Create_user_priv
Create_tablespace_priv
Security columns ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin
authentication_string
password_expired
password_last_changed
password_lifetime
account_locked
Resource control columns max_questions
max_updates
max_connections
max_user_connections

上面是dbuser数据表的对比,在上面的对比中,我们看到db数据表与user有相同的字段,这些字段在db数据表,表示某个用户对某个数据表有哪些权限,而在user数据表中,则表示用户的全局权限。

tables_priv与columns_priv数据表

tables数据用于存储用户对数据表的权限,而columns_priv数据用于存储用户对数据列的权限。

表名 tables_priv 与columns_priv
Scope columns Host Host
Db Db
User User
Table_name Table_name
Column_name
Privilege columns Table_priv Column_priv
Column_priv
Other columns Timestamp Timestamp
Grantor

procs_priv数据表

procs_priv数据表用于存储用户对自定义函数以及存储过程的权限,其表结构比较简单,如下所示:

表名 procs_priv
Scope columns Host
Db
User
Routine_name
Routine_type
Privilege columns Proc_priv
Other columns Timestamp
Grantor

在上面的字段中,Routine_typeENUM类型,其取值为FUNCTIONPROCEDURE,而TimestampGrantor两个字段暂时没有什么作用。

proxies_priv数据表

proxies_priv存储的是代理用户的关系数据,什么是代理用户?可以简单地理解为一个用户将自己的权限授予给被另一个用户使用,比较A用户通过代理将权限授予B用户使用,那么B用户则称为代理用户。

proxies_priv数据表包含Host,User,Proxied_host,Proixed_user,Grantor,Timestamp,With_grant等字段。

用户管理

介绍了与权限管理相关的数据表之后,我们也知道MySQL将用户存储在哪个数据表中,以及MySQL如何组织用户的权限信息,接来在用户管理这块内容中,我们来了解的是MySQL数据库系统中,如何识别一个用户,如何创建、修改、删除用户以及如何为用户设置密码。

用户

MySQL的用户由用户名(User)主机名(Host)组成,主机名表示允许该用户从哪台主机连接到MySQL服务器,所以在MySQL中,表示一个用户的格式如下所示:

# username表示用户名,host_name表示主机名
'user_name'@'host_name'
复制代码

比如我使用root账号在MySQL本地服务器登录,那么此时的root账号的用户为:

'root'@'localhost'
复制代码

另外,在MySQL中,我们可以使用USER()CURRENT_USER()函数或者CURRENT_USER查询当前的用户,如:

mysql> SELECT USER();
mysql> SELECT CURRENT_USER();
mysql> SELECT CURRENT_USER;
复制代码

上面两个语句的查询结果为:

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

复制代码

用户名和主机名可以唯一地确定一个用户,所以即使用户名相同,主机名不同时,也表示两个不同的用户,比如下面的例子中用户名虽然都是test,由于主机名不同所以是两个用户。

'test'@'192.168.0.1'
'test'@'192.168.0.2'
复制代码

当我们在创建账号没有指定主机时,则主机名的值为'%',这表示允许账号从任意主机连接到MySQL服务器,比如:

'test'@'%'
复制代码

在上面的示例中,用户名和主机都是使用单引号括起来,不过,如果没有特殊的字符,可以不使用单引号,所以上面的用户可以这样表示:

test@localhost
复制代码

如果带有横杠(-),点(.),百分号(%),空格等特殊字符,则必须使用单引号,如:

# 正确示例
test@'192.168.0.1'
root@'%'

# 错误示例
test@192.168.0.1
test@%
复制代码

从上面的示例中我们也看到,用户名和主机名是分别使用单引号括起来的,如果使用一个单引号把用户名和主机号括号,则MySQL会认为是整个当作用户名,而主机名则用默认的'%',如下所示:

'root@localhost'

#上面的写法,MySQL会解析为
'root@localhost'@'%'
复制代码

另外,为了提高安全性,也可以为用户设置登录密码,不过密码并不是必填的,不过为了安全,最好还是设置密码。

MySQL用户名的最大长度为32个字符,这个字符长度在MySQL程序中是硬编码的,所以即使我们修改了存储账号的user数据表用户名长度,也是没有效果的。

查询用户权限

查询用户权限分为两种,一种是查询自己当前登录用户的权限,一种是查询其他用户的权限。

SHOW GRANTS [FOR user]
复制代码

查询当前用户权限

不使用FOR子句的话,可以当查询当前登录用户的权限,比如我当前登录了root用户,使用下面的语句进行查询:

mysql> SHOW GRANTS;
复制代码

结果如下:

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
复制代码

查询其他用户权限

如果你的账号有查询其他用户信息的权限,那么可以使用FOR子句后面加上想查询用户的账号信息,不过要注意的是,需要有查询别人权限的权限,才能使用下面的语句。

mysql> SHOW GRANTS FOR 'test'@'localhost';
复制代码

结果如下:

+------------------------------------------+
| Grants for 'test'@'localhost'                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)
复制代码

创建用户

MySQL创建用户使用CREATE USER语句,该语句比较复杂,其语句结构如下所示:

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

#参考我们上面的介绍
user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}
复制代码

上面的语法看着好像很复杂,其实很多选项是可以选择的,比如最简单的,我们可以使用下面的语句创建一个用户:

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
复制代码

修改用户

MySQL修改用户的信息使用ALTER USER语句,其用法如下所示:

ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
复制代码

从上面的用法中可以看出,ALTER USER语句和CREATE USER语句基本类似,各种选项基本相同,因此我们参考CREATE USER的用法。

比如我们要修改当前用户的密码,可以这样:

mysql> ALTER USER USER() IDENTIFIED BY 'test123457';
复制代码

删除用户

MySQL删除用户使用DROP USER语句,该语句用法如下:

DROP USER [IF EXISTS] user [, user] ...
复制代码

从上面的语法看现,MySQL支持删除一个或多个用户,其用法如下所示:

mysql> DROP USER 'test'@'test.example.com','test1'@'test1.example.com';;
复制代码

设置密码

如果在创建用户的时候没有指定密码,或者想重新设置某个用户的密码,可以使用SET PASSWORD语句,该语句的用法如下所示:

SET PASSWORD [FOR user] = password_option

password_option: {
    'auth_string'
  | PASSWORD('auth_string')
}
复制代码

上面的语句中,auth_string表示未加密的明文密码,也可以使用PASSWORD()函数进行加密,不过在MySQL5.7.6之后不推荐这种用法,在未来的版本这种用法会移除。

设置密码语句中的FOR子句可以省略,如果省略则表示修改当前用户的密码,如:

SET PASSWORD = "123456"
复制代码

使用FOR子句,则可以修改指定账号的密码,如下:

SET PASSWORD FOR CURRENT_USER() = "123456"
复制代码

MySQL用户管理中,并不推荐使用SET PASSWORD语句来设置用户密码,因为这个步骤在CREATE USERALTER USER便可以完成了。

授权管理

前面讲了MySQL权限的存储细节以及用户管理的相关知识,对这些有所了解之后,我们来详细了解如何给用户授权权限。

MySQL中,对于用户的权限划分可以分为两种,一种是数据管理相关的权限,比如对数据库、数据表进行操作,创建存储过程和视图的权限,另一种是对用户的管理权限,比如创建、删除用户,为用户分配权限。

用户授权

MySQL为我们提供了GRANT语句进行授权管理,其语法结构如下所示:

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_string'
}

tls_option: {
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'
}

resource_option: {
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}
复制代码

执行的条件

为别的用户授权,当前用户必须拥有授权的权限才可以,比如我们以普通用户test登录MySQL为别的用户分配权限,由于test没有这种权限,因此会报以下的错误:

ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)
复制代码

创建用户

使用GRANT进行授权时,如果该用户不存在,可以跟上GRANT语句后面跟上IDENTIFIED BY直接创建该用户,不过如果在创建用户时要设置更详细的信息,则应该使用CREATE USER语句来创建用户。

GRANT ALL ON *.* TO test_1@localhost IDENTIFIED BY "123456";
复制代码

上面的语句在用户不存在,会自动创建用户。

权限的级别

在分配权限时,想分配什么级别的权限,可以加在关键字ON后面,其格式如下所示:

 *
 *.*
 db_name.*
 db_name.tbl_name
 tbl_name
 db_name.routine_name
复制代码

比如我想给test@localhost用户分配school数据库的student表查询权限,可以这样写:

mysql> GRANT SELECT ON school.student TO 'test'@'localhost'复制代码

权限列表

下面是使用GRANT语句可以授予的全部权限,不同的权限有不同的作用域,比如有的是全局权限,有的只作用于数据库等,如下所示:

权限 权限说明及作用的级别
ALTER[PRIVILEGES] 除了GRANT OPTION和PROXY之外,以指定的访问级别授予所有特权。
ALTER 修改权限,作用于全局,数据库,数据表
ALTER_ROUTINE 修改存储过程,作用于全局,数据库,存储过程
CREATE 创建权限,作用于全局,数据库,数据表
CREATE_ROUTINE 创建存储过程的权限,作用于全局,数据库
CREATE_TABLESPACE 表空间和日志文件组的创建、更改、删除,全局权限
CREATE_TEMPORARY_TABLES 创建临时表的权限,作用于数据库,数据表
CREATE_USER 创建、删除,重命用和移除用户权限的权限,全局权限
CREATE_VIEW 创建视图权限,作用于全局,数据库,数据表
DELETE 删除数据权限,作用于全局,数据库,数据表
DROP 删除数据库、数据表、视图的权限,作用于全局,数据库,数据表
EVENT 使用事件的权限,作用于全局,数据库
EXECUTE 执行存储过程的权限,作用于全局,数据库,存储过程
FILE 读取或写入文件的权限,全局权限
GRANT_OPTION 允许授权或取消授权的权限,作用于全局,数据库,数据表,存储过程,代理
INDEX 使用索引的权限,作用于全局,数据库,数据表
INSERT 写入权限,作用于全局,数据库,数据表,数据列
LOCK_TABLES 在执行SELECT时可以启动LOCK_TABLES的权限,全局或数据库级别的权限
PROCESS 使用SHOW PROCESSLIST查询全部存储过程的权限,全局权限
PROXY 启用用户代理,作用级别从用户到用户
REFERENCES 创建外健权限,作用于全局,数据库,数据表,数据列
RELOAD 启动FLUSH操作,全局权限
REPLICATION CLIENT 使用户能够询问主服务器或从服务器在哪里,全局权限
REPLICATION SLAVE 启用复制从属服务器以从主服务器读取二进制日志事件,全局权限
SELECT 查询权限,作用于全局,数据库,数据表,数据列
SHOW_DATABASES 查询全部数据库,全局权限
SHOW_VIEWS 启用使用SHOW CREATE VIEW,作用于全局,数据库,数据表
SHUTDOWN 关闭数据库服务器权限,全局权限
SUPER 启用其他管理操作的使用,例如CHANGE MASTER TO,KILL,PURGE BINARY LOGS,SET GLOBAL和mysqladmin debug命令。 全局权限
TRIGGER 启用触发器的权限,作用于全局,数据库,数据表
UPDATE 更新权限,作用于全局,数据库,数据表,数据列
USAGE 无特权

创建超级管理员

使用All权限分配给用户时,用户虽然拥有所有数据库的操作权限,但并没有账号管理等权限,如果想创建一直跟root一样的超级管理员,可以加上WITH GRANT OPTION参数,如下所示:

mysql> GRANT ALL ON *.* TO 'super'@'localhost' WITH GRANT OPTION
复制代码

删除用户权限

删除权限与上面的授权是反向操作,可以删除不同级别的权限,就像上面可以授予不同级别的权限一样。

删除权限使用REVOKE语句,其语法结构如下:

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...
复制代码

删除数据库级别的权限:

mysql > REVOKE CREATE,DROP ON expenses.* FROM 'custom'@'host47.example.com';
复制代码

删除用户的全部权限:

mysql> REVOKE ALL ON *.* FROM 'finley'@'%.example.com';
复制代码

小结

MySQL作为数据库管理系统,里面保存企业的重要业务数据,因此保证数据库的安全性非常重要,如何保证数据库的安全性呢?用户和用户权限管理是一个很重要的方面。


如果你觉得文章不错,欢迎扫码关注,你的关注就是我写作的最大动力