文章目录

用户创建
用户修改
- 修改用户名
- 修改密码
- - 修改自己的密码
  - 修改其他用户的密码
删除用户
权限管理
- 查看所有权限
- 授予权限
- 回收权限
- 权限表
- - columns_priv
  - procs_priv
  - tables_priv

用户创建

基本格式：create user '用户名' identified by '密码';

mysql> create user 'szc' identified by 'szc123';
Query OK, 0 rows affected (0.00 sec)

使用新的用户登录、查看数据库表和自己的权限：

[root@scentos szc]# mysql -uszc -pszc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.36 MySQL Community Server (GPL)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)mysql> show grants;
+-----------------------------------------+
| Grants for szc@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'szc'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)

可见默认情况下，普通用户的权限很小。

可以在用户名后面加上@'主机'来限制该用户登录的主机，不写为%，即不限主机：

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

查看所有用户的用户名和登录主机，首先切换到mysql数据库，再查看user表：

mysql> use mysql -A;
Database changedmysql> select user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| szc           | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| szc           | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

当然也可以手动指定新用户的登录主机为%：

mysql> create user 'szc1'@'%' identified by 'szc123';
Query OK, 0 rows affected (0.00 sec)

用户修改

修改用户名

update用户表即可，不过要刷新一下权限才能生效：

mysql> update user set user = 'szc_new' where user = 'szc' and host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看结果：

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

修改密码

修改自己的密码

mysql> alter user user() identified by '123abc';
Query OK, 0 rows affected (0.01 sec)

或：

mysql> set password = 'abc123';
Query OK, 0 rows affected (0.00 sec)

推荐第一种方式。

修改其他用户的密码

当然得有这样的权限：

mysql> alter user 'szc'@'%' identified by '123abc';
Query OK, 0 rows affected (0.00 sec)

也可以这样：

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

格式为：set password for '用户名'@'主机' = '密码';

删除用户

一般采用drop user的方式，这样即时生效：

mysql> drop user 'szc1';
Query OK, 0 rows affected (0.00 sec)mysql> select user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| szc           | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| szc_new       | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

默认删除登录主机为%的用户，我们最好手动指定要删除的用户主机：

mysql> drop user 'szc_new'@'localhost';
Query OK, 0 rows affected (0.00 sec)mysql> select user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| szc           | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

权限管理

查看所有权限

root用户拥有所有权限，因此可以通过root用户调用show privileges查看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 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)

授予权限

格式：grant 权限1, 权限2, .... , 权限n，on 数据库名.表名 to 用户名@用户地址 identified by 用户密码，示例如下：

mysql> grant select, update on test.* to 'szc'@'%' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

这样'szc'@'%'就有了test数据库下对所有表的查询和更新权限，该用户也跟着有了查看该数据库的权限：

[root@scentos szc]# mysql -uszc -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.36 MySQL Community Server (GPL)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| emp_test       |
| test1          |
| test_myisam    |
| test_view      |
| test_view_2    |
+----------------+
5 rows in set (0.00 sec)mysql> select * from test1;
+--------+
| info   |
+--------+
| szc    |
| 测试   |
+--------+
2 rows in set (0.00 sec)mysql> update test1 set info = 'szc1' where info = 'szc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from test1;
+--------+
| info   |
+--------+
| szc1   |
| 测试   |
+--------+
2 rows in set (0.00 sec)mysql> delete from test1 where info = 'szc';
ERROR 1142 (42000): DELETE command denied to user 'szc'@'localhost' for table 'test1'

注意grant权限是增量的，即grant新权限不会覆盖老的权限，而是取新老权限的并集：

mysql> grant delete on test.* to 'szc'@'%' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

测试：

[root@scentos szc]# mysql -uszc -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.36 MySQL Community Server (GPL)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changedmysql> delete from test1 where info = 'szc1';
Query OK, 1 row affected (0.00 sec)mysql> select * from test1;
+--------+
| info   |
+--------+
| 测试   |
+--------+
1 row in set (0.00 sec)

赋予某个用户全部权限：

mysql>  grant all privileges on *.* to 'szc'@'%' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

但是这些权限不包括赋予权限的权限：

mysql> select * from user\G
*************************** 1. row ***************************Host: %User: rootSelect_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv: YFile_priv: YGrant_priv: YReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: Y
Create_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: Y
Create_tablespace_priv: Yssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_password
authentication_string: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1Bpassword_expired: N
password_last_changed: 2022-01-15 16:07:32password_lifetime: NULLaccount_locked: N
......
*************************** 4. row ***************************Host: %User: szcSelect_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv: YFile_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: Y
Create_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: Y
Create_tablespace_priv: Yssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_password
authentication_string: *6691484EA6B50DDDE1926A220DA01FA9E575C18Apassword_expired: N
password_last_changed: 2022-01-15 20:30:48password_lifetime: NULLaccount_locked: N
4 rows in set (0.00 sec

回收权限

命令格式：remove 权限1, 权限2, ...., 权限n on 数据库名.表名 from 用户名@用户主机;，示例如下：

mysql> grant delete on test.test1 to 'szc'@'%' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

注意，如果某个权限是以数据库名.*的名称赋予的，则必须通过一样的方式回收，而不能通过数据库名.表名回收。反过来，如果某个权限是以数据库名.表名的名称赋予的，则既可以通过库名.表名的方式回收，也可以通过库名.*的方式回收。

回收全库全表的全部权限：

mysql> revoke all privileges on *.* from 'szc'@'%';
Query OK, 0 rows affected (0.00 sec)

注意：

删除某个用户前，必须回收该用户的所有权限；
增删某个用户的权限后，该用户重新登录后方可生效。

权限表

MySQL服务器通过权限表控制用户对数据库的访问，权限表包括columns_priv、procs_privs和tables_priv，分别存储用户对列、存储函数(存储过程)和数据表的权限：
在这里插入图片描述

columns_priv

该表的字段如下：

mysql> desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                     | NO   | PRI |                   |                             |
| Db          | char(64)                                     | NO   | PRI |                   |                             |
| User        | char(32)                                     | NO   | PRI |                   |                             |
| Table_name  | char(64)                                     | NO   | PRI |                   |                             |
| Column_name | char(64)                                     | NO   | PRI |                   |                             |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                             |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

Column_priv说明了用户对某个列的操作权限，为Select、Insert、Update和References中一个或多个，分别表示查看、增加、修改和向其他表建立外键。

procs_priv

该表的字段如下：

mysql> desc procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                                   | Null | Key | Default           | Extra                       |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Host         | char(60)                               | NO   | PRI |                   |                             |
| Db           | char(64)                               | NO   | PRI |                   |                             |
| User         | char(32)                               | NO   | PRI |                   |                             |
| Routine_name | char(64)                               | NO   | PRI |                   |                             |
| Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |                             |
| Grantor      | char(93)                               | NO   | MUL |                   |                             |
| Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |                             |
| Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

Proc_priv说明了用户对某个存储过程或函数的操作权限，为Execute、Alter Routine和Grant中一个或多个，分别表示执行、修改过程体函数体和授予或回收某个用户执行权限。

tables_priv

该表的字段如下：

mysql> desc tables_priv\G
*************************** 1. row ***************************Field: HostType: char(60)Null: NOKey: PRI
Default:Extra:
*************************** 2. row ***************************Field: DbType: char(64)Null: NOKey: PRI
Default:Extra:
*************************** 3. row ***************************Field: UserType: char(32)Null: NOKey: PRI
Default:Extra:
*************************** 4. row ***************************Field: Table_nameType: char(64)Null: NOKey: PRI
Default:Extra:
*************************** 5. row ***************************Field: GrantorType: char(93)Null: NOKey: MUL
Default:Extra:
*************************** 6. row ***************************Field: TimestampType: timestampNull: NOKey:
Default: CURRENT_TIMESTAMPExtra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************Field: Table_privType: set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')Null: NOKey:
Default:Extra:
*************************** 8. row ***************************Field: Column_privType: set('Select','Insert','Update','References')Null: NOKey:
Default:Extra:
8 rows in set (0.01 sec)

Table_priv说明了用户对某个表的操作权限，为Select、Insert、Update、Delete、Create、Drop、Grant、References、Index、Alter、Create View、Show view和Trigger中一个或多个，分别表示查看表数据、增加表数据、修改表数据、删除表数据、创建表、删除表、和其他表建立外键关系、增删索引、修改表结构、创建视图、查看视图的创建状态(show create view或explain)、执行索引操作(增、删、触发或查看)。