江明涛的博客
入门MySQL——用户与权限
Blog MySQL
入门MySQL——用户与权限
Blog MySQL

入门MySQL——用户与权限

前言： 

前面几篇文章为大家介绍了各种SQL语法的使用，本篇文章将主要介绍MySQL用户及权限相关知识，如果你不是DBA的话可能平时用的不多，但是了解下也是好处多多。

1.创建用户

官方推荐创建语法为：

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
}

通常我们常用的创建语法为：

CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>

语法说明如下： 

1) <用户名> 指定创建用户账号，格式为 ‘user_name’@’host_name’。这里user_name是用户名，host_name为主机名，即用户连接 MySQL 时所在主机的名字。若在创建的过程中，只给出了账户的用户名，而没指定主机名，则主机名默认为“%”，表示一组主机。

2) PASSWORD 可选项，用于指定散列口令，即若使用明文设置口令，则需忽略PASSWORD关键字；若不想以明文设置口令，且知道 PASSWORD() 函数返回给密码的散列值，则可以在口令设置语句中指定此散列值，但需要加上关键字PASSWORD。 

3) IDENTIFIED BY子句 用于指定用户账号对应的口令，若该用户账号无口令，则可省略此子句。

4) <口令> 指定用户账号的口令，在IDENTIFIED BY关键字或PASSWOED关键字之后。给定的口令值可以是只由字母和数字组成的明文，也可以是通过 PASSWORD() 函数得到的散列值。

使用 CREATE USER 语句应该注意以下几点：

  • 如果使用 CREATE USER 语句时没有为用户指定口令，那么 MySQL 允许该用户可以不使用口令登录系统，然而从安全的角度而言，不推荐这种做法。
  • 使用 CREATE USER 语句必须拥有 MySQL 中 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限。
  • 使用 CREATE USER 语句创建一个用户账号后，会在系统自身的 MySQL 数据库的 user 表中添加一条新记录。若创建的账户已经存在，则语句执行时会出现错误。
  • 新创建的用户拥有的权限很少。他们可以登录 MySQL，只允许进行不需要权限的操作，如使用 SHOW 语句查询所有存储引擎和字符集的列表等。
  • 如果两个用户具有相同的用户名和不同的主机名，MySQL 会将他们视为不同的用户，并允许为这两个用户分配不同的权限集合。

示例：

#注意：test_user@'%' 和 test_user@'localhost' 是两个不同的用户
CREATE USER 'test_user'@'%' identified by '123456';
CREATE USER 'test_user'@'localhost' identified by '123456789';

2.更改用户

更改用户信息主要包括重命名，改密码，锁定或解锁用户。下面将通过案例为大家展示这些用法：

#重命名用户
RENAME USER 'test_user'@'%' to 'test'@'%';

#修改密码
ALTER USER 'test'@'%' identified by '123456789';

#锁定或解锁用户
ALTER USER 'test'@'%' ACCOUNT LOCK;
ALTER USER 'test'@'%' ACCOUNT UNLOCK;

3.删除用户

MySQL 数据库中可以使用 DROP USER 语句来删除一个或多个用户账号以及相关的权限。 官方推荐语法格式：

DROP USER [IF EXISTS] user [, user] ...

使用 DROP USER 语句应该注意以下几点：

  • DROP USER 语句可用于删除一个或多个 MySQL 账户，并撤销其原有权限。
  • 使用 DROP USER 语句必须拥有 MySQL 中的 mysql 数据库的 DELETE 权限或全局 CREATE USER 权限。
  • 在 DROP USER 语句的使用中，若没有明确地给出账户的主机名，则该主机名默认为“%”。

4.用户授权

当成功创建用户后，还不能执行任何操作，需要为该用户分配适当的访问权限。可以使用 SHOW GRANT FOR 语句来查询用户的权限。

注意：新创建的用户只有登录 MySQL 服务器的权限，没有任何其他权限，不能进行其他操作。 USAGE ON . 表示该用户对任何数据库和任何表都没有权限。

对于新建的 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
}

首先大家要知道，权限是分级别的。可以授予的权限有如下几组：

  • 列级别，和表中的一个具体列相关。例如，可以使用 UPDATE 语句更新表 students 中 student_name 列的值的权限。
  • 表级别，和一个具体表中的所有数据相关。例如，可以使用 SELECT 语句查询表 students 的所有数据的权限。
  • 数据库级别，和一个具体的数据库中的所有表相关。例如，可以在已有的数据库 mytest 中创建新表的权限。
  • 全局，和 MySQL 中所有的数据库相关。例如，可以删除已有的数据库或者创建一个新的数据库的权限。

下表是所有可授予的权限及其意义：

权限意义和可授予级别
ALL [PRIVILEGES]授予在指定的访问级别的所有权限，除了 GRANT OPTION和 PROXY。
ALTER启用ALTER TABLE。级别：全局，数据库，表。
ALTER ROUTINE允许更改或删除存储过程。级别：全局，数据库。
CREATE启用数据库和表创建。级别：全局，数据库，表。
CREATE ROUTINE启用存储过程创建。级别：全局，数据库。
CREATE TABLESPACE启用要创建，更改或删除的表空间和日志文件组。等级：全局。
CREATE TEMPORARY TABLES启用CREATE TEMPORARY TABLE。级别：全局，数据库。
CREATE USER允许使用CREATE USER， DROP USER， RENAME USER，和 REVOKE ALL PRIVILEGES。等级：全球。
CREATE VIEW启用要创建或更改视图。级别：全局，数据库，表。
DELETE启用DELETE。级别：全局，数据库，表。
DROP启用要删除数据库，表和视图。级别：全局，数据库，表。
EVENT启用事件使用。级别：全局，数据库。
EXECUTE使用户能够执行存储过程。级别：全局，数据库。
FILE使用户能够使服务器读取或写入文件。等级：全局。
GRANT OPTION启用授予其他帐户或从其他帐户中删除的权限。级别：全局，数据库，表，代理。
INDEX启用要创建或删除索引。级别：全局，数据库，表。
INSERT启用INSERT。级别：全局，数据库，表，列。
LOCK TABLES允许使用LOCK TABLES您拥有该SELECT 权限的表。级别：全局，数据库。
PROCESS使用户能够查看所有进程SHOW PROCESSLIST。等级：全局。
PROXY启用用户代理。级别：从用户到用户。
REFERENCES启用外键创建。级别：全局，数据库，表，列。
RELOAD启用FLUSH操作。等级：全局。
REPLICATION CLIENT使用户可以询问主服务器或从服务器的位置。等级：全局。
REPLICATION SLAVE启用复制从属以从主服务器读取二进制日志。等级：全局。
SELECT启用SELECT。级别：全局，数据库，表，列。
SHOW DATABASES启用SHOW DATABASES以显示所有数据库。等级：全局。
SHOW VIEW启用SHOW CREATE VIEW。级别：全局，数据库，表。
SHUTDOWN启用mysqladmin shutdown。等级：全局。
SUPER能够使用如其他命令 CHANGE MASTER TO， KILL， PURGE BINARY LOGS， SET GLOBAL，和中mysqladmin的调试命令。等级：全局。
TRIGGER启用触发操作。级别：全局，数据库，表。
UPDATE启用UPDATE。级别：全局，数据库，表，列。
USAGE“ no privileges ”的同义词

其实grant语句可以直接创建用户并授权，这里建议大家先用create user语句创建好用户之后再单独进行授权。下面我将用示例为大家展示如何授权：

#全局权限
GRANT super,select on *.* to 'test_user'@'%';

#库权限
GRANT select,insert,update,delete,create,alter,execute on `testdb`.* to 'test_user'@'%';

#表权限
GRANT select,insert on `testdb`.tb to 'test_user'@'%';

#列权限
GRANT select (col1), insert (col1, col2) ON `testdb`.mytbl to 'test_user'@'%';

5.回收权限

在MySQL中，可以使用 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 语法和 GRANT 语句的语法格式相似，但具有相反的效果。
  • 第一种语法格式用于回收某些特定的权限。
  • 第二种语法格式用于回收特定用户的所有权限。
  • 要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。

一般情况下我们先会使用show grants语法查询该用户的权限，如果发现权限过大，会用revoke语法回收权限。示例如下：

#查看用户权限
mysql> show grants for 'test_user'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for test_user@%                                                                              |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'%'                                                               |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, EXECUTE ON `testdb`.* TO 'test_user'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

#发现权限过大，想回收drop，alter权限 则可以这样回收：
REVOKE drop,alter on `testdb`.* from 'test_user'@'%';

总结： 

本篇文章为大家介绍了如何创建，更改，删除用户以及如何授于和回收权限。希望大家能对MySQL中用户管理这一块能有更深的认识。

上次更新时间 13 3 月, 2023 at 09:59 上午