MySQL—用户和权限管控

技术公众号：后端技术解忧铺
关注微信公众号：CodingTechWork，一起学习进步。

用户

创建用户

语法

CREATE USER 'user'[@'host'] 
[IDENTIFIED BY [PASSWORD] 'password '] 
[, ...]


其中：

  1. CREATE USER：用于在mysql服务器上创建新用户账号，用户使用引号标示，后面跟@符号和用引号标示的主机IP地址或者主机名（本机使用localhost或者127.0.0.1作为主机），使用通配符%，则表示作为主机允许客户端指定用户从任意主机上进行连接；
  2. IDENTIFIED BY：子句后面跟密码，用引号标示纯文本，无需使用PASSWORD()函数进行加密（加密工作自动完成），若未指定password子句，则默认为空密码，需要使用SET PASSWORD进行密码设置。
  3. ,：可以使用逗号分隔列表指定多个用户账号。

示例

mysql> CREATE USER 'userA'@'localhost'
    -> IDENTIFIED BY '1QAZ!qaz',
    -> 'userA'@'linux01'
    -> IDENTIFIED BY '2WSX#edc';
Query OK, 0 rows affected (0.48 sec)

删除用户

语法

DROP USER 'user'@'host'


其中

  1. user：为用户名
  2. host：为主机名或IP

示例

mysql> DROP USER 'userA'@'linux01';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host, user, password_last_changed FROM mysql.user WHERE user = 'userA';
+-----------+-------+-----------------------+
| host      | user  | password_last_changed |
+-----------+-------+-----------------------+
| localhost | userA | 2021-05-05 14:49:37   |
+-----------+-------+-----------------------+
1 row in set (0.00 sec)

修改用户

语法

RENAME USER 'old_user'[@'old_host']
TO 'new_user'[@'new_host'] [, ...];


其中

  1. old_user和old_host：为更改前的用户和主机ip或地址；
  2. new_user和new_host：为需更改后的用户和主机ip或地址；
  3. 逗号：多个账户一起更改时，使用逗号进行列表分割。

示例

mysql> RENAME USER 'userA'@'localhost' 
    -> TO 'userD'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host, user, password_last_changed FROM mysql.user WHERE user = 'userA';
+------+-------+-----------------------+
| host | user  | password_last_changed |
+------+-------+-----------------------+
| %    | userA | 2021-05-05 16:07:26   |
+------+-------+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT host, user, password_last_changed FROM mysql.user WHERE user = 'userD';
+-----------+-------+-----------------------+
| host      | user  | password_last_changed |
+-----------+-------+-----------------------+
| localhost | userD | 2021-05-05 15:35:18   |
+-----------+-------+-----------------------+
1 row in set (0.00 sec)

查看用户

语法

SELECT User, Host, ..., 
FROM mysql.user
WHERE user = 'xxx';


其中

  1. user：为用户名
  2. mysql.user：数据库中的用户一般都在mysql.user表中可以查询到。

示例

mysql> SELECT host, user, password_last_changed FROM mysql.user WHERE user = 'userA';
+-----------+-------+-----------------------+
| host      | user  | password_last_changed |
+-----------+-------+-----------------------+
| localhost | userA | 2021-05-05 14:49:37   |
| linux01   | userA | 2021-05-05 14:49:37   |
+-----------+-------+-----------------------+
2 rows in set (0.00 sec)

  创建了带有密码的两个账户，是同一个人，但是一个账号是允许用户登录到服务器宿主数据库，在mysql客户机或在服务器上的一些其他客户端localhost中运行；另一个账户允许用户使用客户端连接一个名为linux01的主机。

设置密码

语法

SET PASSWORD [FOR 'user'@'host'] = PASSWORD('password')
  1. 若未指定FOR子句，则默认为当前用户账户，需慎重。
  2. PASSWORD()将会对给定密码进行加密。

示例

SET PASSWORD FOR 'userA'@'%' = PASSWORD('3EDC#edc');
Query OK, 0 rows affected, 1 warning (0.00 sec)

权限控制

查看系统权限

语法

SHOW PRIVILEGES

示例

mysql> SHOW PRIVILEGES;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)

查看用户权限

语法

SHOW GRANTS FOR 'user'@'host';


其中

  1. user：为用户名
  2. host：为主机名或IP

示例

mysql> SHOW GRANTS FOR 'userA'@'localhost';
+-------------------------------------------+
| Grants for userA@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'userA'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'userB'@'localhost' G
*************************** 1. row ***************************
Grants for userB@localhost: GRANT USAGE ON *.* TO 'userB'@'localhost'
*************************** 2. row ***************************
Grants for userB@localhost: GRANT SELECT ON `db01`.* TO 'userB'@'localhost'
*************************** 3. row ***************************
Grants for userB@localhost: GRANT SELECT, INSERT, UPDATE ON `db123`.`tab02` TO 'userB'@'localhost'
3 rows in set (0.00 sec)

赋予所有权限

语法

GRANT ALL PRIVILEGES 
ON *.* TO 'user'@'host' 
IDENTIFIED BY 'password' 
WITH GRANT OPTION;


其中

  1. user：为用户名
  2. host：为主机名或IP
  3. password：为用户名密码
  4. ALL：为用户赋予所有基本权限
  5. WITH GRANT OPTION：使用户具备GRANT权限

示例

mysql> GRANT ALL PRIVILEGES ON *.* 
    -> TO 'userA'@'localhost'
    -> IDENTIFIED BY '1QAZ!qaz'
    -> WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW GRANTS FOR 'userA'@'localhost';
+----------------------------------------------------------------------+
| Grants for userA@localhost                                           |
+----------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'userA'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

赋予指定权限

语法

GRANT SELECT, INSERT, UPDATE... ON db_name.tab_name 
TO 'user'@'host' 
IDENTIFIED BY 'password';


其中

  1. db_name: 数据库名
  2. tab_name：表名
  3. user：用户名
  4. host：主机名或IP
  5. password：用户名密码
mysql> CREATE USER 'userB'@'localhost' 
    -> IDENTIFIED BY '1QAZ!qaz'
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'userB'@'localhost';
+-------------------------------------------+
| Grants for userB@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'userB'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT SELECT ON db01.* 
    -> TO 'userB'@'localhost' 
    -> IDENTIFIED BY '1QAZ!qaz';
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> GRANT SELECT, INSERT, UPDATE ON db123.tab02
    -> TO 'userB'@'localhost' 
    -> IDENTIFIED BY '1QAZ!qaz';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW GRANTS FOR 'userB'@'localhost';
+------------------------------------------------------------------------+
| Grants for userB@localhost                                             |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'userB'@'localhost'                              |
| GRANT SELECT ON `db01`.* TO 'userB'@'localhost'                        |
| GRANT SELECT, INSERT, UPDATE ON `db123`.`tab02` TO 'userB'@'localhost' |
+------------------------------------------------------------------------+
3 rows in set (0.00 sec)

限制连接类型

语法

GRANT privileges ON [TABLE | FUNCTION | PROCEDURE]  {[{database|*}.{table|*}] | *} 
TO 'user'@'host' 
[IDENTIFIED BY [PASSWORD] 'password']
[,...] 
[REQUIRE NONE | 
[{SSL | X509} [AND]]
[CIPHER 'cipher' [AND]] 
[ISSUER 'issue' [AND]] 
[SUBJECT 'subject']]


其中：

  1. REQUIRE NONE：默认值
  2. REQUIRE SSL：限制用户只可以进行SSL加密连接，用户账户的mysql客户机将启动带有--ssl-ca选项的客户机。
  3. REQUIRE X509：要求用户账户拥有一个有效的CA证明
  4. REQUIRE ISSUER：要求用户提供有效的证明，由CA发布的X.509证明。
  5. REQUIRE SUBJECT：需要具有给定主题的用户账户使用过X.509证明。
  6. user：用户名
  7. host：主机名或IP
  8. password：用户名密码

示例

mysql> CREATE USER 'userC'@'localhost' 
    -> IDENTIFIED BY '1QAZ!qaz';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON db01.*
    -> TO 'userC'@'localhost'
    -> IDENTIFIED BY '1QAZ!qaz'
    -> REQUIRE SSL;
Query OK, 0 rows affected, 1 warning (0.00 sec)

限制连接数

语法

GRANT privilege[, ...] [(column[, ...])] [,...]
ON [TABLE | FUNCTION | PROCEDURE] {[database|*}.{table|*}] | *}
TO 'user'@'host'
[IDENTIFIED BY [PASSWORD] 'password'] [, ...]
[WITH [MAX_QUERIES_PER_HOUR count | 
		MAX_UPDATES_PER_HOUR count |
		MAX_CONNECTIONS_PER_HOUR count | 
		MAX_USER_CONNECTIONS count] ... ]
  1. MAX_QUERIES_PER_HOUR：为用户指定每小时执行查询的最大数量，值为0，则表示无限制。
  2. MAX_UPDATES_PER_HOUR：为用户指定每小时发出的最大更新数目，值为0，则表示无限制。
  3. MAX_CONNECTIONS_PER_HOUR：为用户指定每小时可以同时连接服务器的最大数量，值为0，则表示无限制。
  4. MAX_USER_CONNECTIONS：为用户指定同时连接的最大数目。缺省或者值为0，则表示与系统变量max_user_connections值同步。
  5. user：用户名
  6. host：主机名或IP
  7. password：用户名密码

示例

mysql> GRANT SELECT ON db01.*
    -> TO 'userA'@'%'
    -> IDENTIFIED BY '1QAZ!qaz'
    -> WITH MAX_QUERIES_PER_HOUR 1000
    -> MAX_CONNECTIONS_PER_HOUR 100;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW GRANTS FOR 'userA'@'%';
+-----------------------------------------+
| Grants for userA@%                      |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'userA'@'%'       |
| GRANT SELECT ON `db01`.* TO 'userA'@'%' |
+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT max_questions, max_connections FROM mysql.user WHERE User = 'userA' AND Host = '%';
+---------------+-----------------+
| max_questions | max_connections |
+---------------+-----------------+
|          1000 |             100 |
+---------------+-----------------+
1 row in set (0.00 sec)

回收权限

语法

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

REVOKE privilege[, ...] [(column[, ...])]
ON {[{database|*}.{table|*}] | *}
FROM 'user'@'host' [, ...]

示例

mysql> SHOW GRANTS FOR 'userA'@'%';
+-----------------------------------------+
| Grants for userA@%                      |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'userA'@'%'       |
| GRANT SELECT ON `db01`.* TO 'userA'@'%' |
+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION 
    -> FROM 'userA'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'userA'@'%';
+-----------------------------------+
| Grants for userA@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'userA'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)

附录

GRANT和REVOKE权限

权限 描述
ALL [PRIVILEGES] 赋予所有的基本权限，但不包括GRANT OPTION。
ALTER 赋予使用ALTER TABLE语句权限
ALTER ROUTINE 赋予权限允许用户账户修改或者删除存储程序，包括ALTER FUNCTION 和ALTER PROCEDURE语句DROP FUNCTION和DROP PROCEDURE
CREATE 赋予CREATE TABLE语句权限
CREATE ROUTINE 赋予权限允许账户创建存储程序，包括CREATE FUNCTION 和CRATE PROCEDURE语句，对于用户创建的任何存储程序，都具有ALTER ROUTINE权限
CREATE TEMPORARY TABLES 赋权允许使用CREATE TEMPORARY TABLES语句
CREATE VIEW 赋予权限允许使用CREATE VIEW语句
DELETE 赋权允许使用DELETE语句
DROP 赋权允许用户执行DROP TABLE和TRUNCATE语句
EVENT 赋权允许用户为事件调度器程序创建事件，允许使用CREATE EVENT、ALTER EVEN和DROP EVEN语句
EXECUTE 允许存储程序的执行
FILE 允许使用SELECT ... INFO OUTFILE从一个表导出记录到一个文件中，和LOAD DATA INFILE语句从一个文件向一个表中导入数据
GRANT OPTION 允许使用GRANT语句向用户授权，使用WITH GRANT OPTION进行指定
INDEX 赋权允许使用CREATE INDEX 和DROP INDEX语句
INSERT 赋权允许使用INSERT语句
LOCK TABLES 赋权允许具有SELECT权限的用户使用LOCK TABLES语句
PROCESS 赋权允许使用SHOW FULL PROCESSLIST语句
RELOAD 赋权允许使用FLUSH和RESET语句
REPLICATION CLIENT 赋权允许用户查询主服务器和从属服务器的状态信息
REPLICATION SLAVE 需要复制主服务器，允许从主服务器上读取二进制日志事件
SELECT 赋权允许使用SELECT语句
SHOW DATABASES 赋权对所有数据库，允许使用SHOW DATABASES语句，而不仅仅是用户具有权限时才可以使用
SHOW VIEW 赋权允许使用SHOW CREATE VIEW语句
SHUTDOWN 赋权允许使用电邮shutdown选项的mysqladmin实用工具
SUPER 赋权允许使用CHANGE MASTER、KILL、PURGE MASTER LOGS和SET GLOBAL语句，以及带有debug选项的命令行使用工具mysqladmin
TRIGGER 赋权允许用户账户创建和删除触发器，如CREATE TRIGGER和DROP TRIGGER语句
UPDATE 赋权允许用户使用UPDATE语句
USAGE 赋权允许创建没有权限的用户或者在不影响现有权限的情况下修改资源限制。
烧不死的鸟就是凤凰
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Andya/p/14732289.html