1. 用户管理

1.1 登录MySQL服务器

启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"

下面详细介绍命令中的参数：

  • -h参数 后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。
  • -P参数 后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号。
  • -u参数 后面接用户名，username为用户名。
  • -p参数 会提示输入密码。
  • DatabaseName参数 指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。
  • -e参数 后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。
iss730001006220@iss730001006220deiMac ios % mysql -u root -p dbtest1 -e "select * from test"
Enter password: 
+------+------------+
| id   | tel        |
+------+------------+
|    1 | 1648934903 |
|    2 |     840323 |
|    3 |      69043 |
|    4 |      55555 |
+------+------------+
iss730001006220@iss730001006220deiMac ios %

1.2 创建用户

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
  • 用户名参数表示新建用户的账户，由 用户（User） 和 主机名（Host） 构成；
  • “[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
  • CREATE USER语句可以同时创建多个用户
mysql> create user 'zhang3' identified by '123123';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | zhang3           |
| %         | zhangsan         |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

mysql> create user 'lisi'@'localhost' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | zhang3           |
| %         | zhangsan         |
| localhost | lisi             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
7 rows in set (0.00 sec)

mysql>

1.3 修改用户

  • 刷新权限： flush privileges; （ps：增删改 需要刷新，删除不用）
mysql> update user set user = 'wang5' where user = 'zhang3' and host = '%';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql>

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | wang5            |
| %         | zhangsan         |
| localhost | lisi             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
7 rows in set (0.00 sec)

mysql>

1.4 删除用户

方式1：使用DROP方式删除（推荐）
使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下：

DROP USER user[,user]…;

举例

mysql> drop user lisi;  # 默认删除host为%的用户
ERROR 1396 (HY000): Operation DROP USER failed for 'lisi'@'%'
mysql> drop user lisi@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user wang5;
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | zhangsan         |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

mysql>

方式2：使用DELETE方式删除

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;

执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：

FLUSH PRIVILEGES;

注意：不推荐通过 DELETE FROM USER u WHERE USER='li4' 进行删除，系统会有残留信息保留。
而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

1.5 设置当前用户密码

1. 使用ALTER USER命令来修改当前用户密码 用户可以使用ALTER命令来修改自身密码，如下语句代表修改当前登录用户的密码。基本语法如下：

ALTER USER USER() IDENTIFIED BY 'new_password';

2. 使用SET语句来修改当前用户密码 使用root用户登录MySQL后，可以使用SET语句来修改密码，具体SQL语句如下：

SET PASSWORD='new_password';

该语句会自动将密码加密后再赋给当前用户

修改其它用户密码

1. 使用ALTER语句来修改普通用户的密码 可以使用ALTER USER语句来修改普通用户的密码。基本语法形式如下：

ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…;

2. 使用SET命令来修改普通用户的密码 使用root用户登录到MySQL服务器后，可以使用SET语句来修改普通用户的密码。SET语句的代码如下：

SET PASSWORD FOR 'username'@'hostname'='new_password';

举例

mysql> alter user zhangsan identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> set password for 'zhangsan'@'%'='123123';
Query OK, 0 rows affected (0.00 sec)

mysql>

2. 权限管理

2.1 权限列表

MySQL到底都有哪些权限呢？

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 role                | Server Admin                          | To create new roles                                   |
| 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                  |
| Drop role                  | Server Admin                          | To drop roles                                         |
| 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                    |
| XA_RECOVER_ADMIN           | Server Admin                          |                                                       |
| SHOW_ROUTINE               | Server Admin                          |                                                       |
| TABLE_ENCRYPTION_ADMIN     | Server Admin                          |                                                       |
| SERVICE_CONNECTION_ADMIN   | Server Admin                          |                                                       |
| ROLE_ADMIN                 | Server Admin                          |                                                       |
| CONNECTION_ADMIN           | Server Admin                          |                                                       |
| CLONE_ADMIN                | Server Admin                          |                                                       |
| FLUSH_USER_RESOURCES       | Server Admin                          |                                                       |
| REPLICATION_SLAVE_ADMIN    | Server Admin                          |                                                       |
| APPLICATION_PASSWORD_ADMIN | Server Admin                          |                                                       |
| REPLICATION_APPLIER        | Server Admin                          |                                                       |
| FLUSH_OPTIMIZER_COSTS      | Server Admin                          |                                                       |
| RESOURCE_GROUP_ADMIN       | Server Admin                          |                                                       |
| ENCRYPTION_KEY_ADMIN       | Server Admin                          |                                                       |
| BINLOG_ENCRYPTION_ADMIN    | Server Admin                          |                                                       |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin                          |                                                       |
| BINLOG_ADMIN               | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ENABLE     | Server Admin                          |                                                       |
| AUDIT_ADMIN                | Server Admin                          |                                                       |
| RESOURCE_GROUP_USER        | Server Admin                          |                                                       |
| SET_USER_ID                | Server Admin                          |                                                       |
| SYSTEM_VARIABLES_ADMIN     | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ARCHIVE    | Server Admin                          |                                                       |
| FLUSH_STATUS               | Server Admin                          |                                                       |
| SYSTEM_USER                | Server Admin                          |                                                       |
| SESSION_VARIABLES_ADMIN    | Server Admin                          |                                                       |
| GROUP_REPLICATION_ADMIN    | Server Admin                          |                                                       |
| FLUSH_TABLES               | Server Admin                          |                                                       |
| BACKUP_ADMIN               | Server Admin                          |                                                       |
+----------------------------+---------------------------------------+-------------------------------------------------------+
62 rows in set (0.01 sec)

mysql>
  • CREATE和DROP权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
  • SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作。
  • SELECT权限只有在它们真正从一个表中检索行时才被用到。
  • INDEX权限 允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
  • ALTER权限 可以使用ALTER TABLE来更改表的结构和重新命名表。
  • CREATE ROUTINE权限 用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限 用来执行保存的程序。
  • GRANT权限 允许授权给其他用户，可用于数据库、表和保存的程序。
  • FILE权限 使用户可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

2.2 授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：

  • 1、只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
  • 2、创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
  • 3、为每个用户 设置满足密码复杂度的密码 。
  • 4、定期清理不需要的用户 ，回收权限或者删除用户。

2.3 授予权限

给用户授权的方式有 2 种，分别是通过把 角色赋予用户给用户授权 和 直接给用户授权 。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。
授权命令：

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
  • 该权限如果发现没有该用户，则会直接新建一个用户
  • 给zhangsan用户用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限
mysql> grant select,insert,delete,update on atguigudb.* to zhangsan@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| atguigudb          |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql>
  • 授予通过网络方式登录的zhangsan用户 ，对所有库所有表的全部权限。注意这里唯独不包括grant的权限（指：WITH GRANT OPTION）
GRANT ALL PRIVILEGES ON *.* TO zhangsan@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| atguigudb          |
| dbtest1            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

2.4 查看权限

  • 查看当前用户权限
SHOW GRANTS; 
# 或 
SHOW GRANTS FOR CURRENT_USER; 
# 或 
SHOW GRANTS FOR CURRENT_USER();
  • 查看某用户的全局权限
SHOW GRANTS FOR 'user'@'主机地址' ;

2.5 收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用 REVOKE语句 取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。
注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。

  • 收回权限命令
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
mysql> revoke select,insert,update,delete on atguigudb.* from zhangsan@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>
  • 注意： 须用户重新登录后才能生效