MySQL学习笔记之MySQL5.7用户管理

最新推荐文章于 2024-03-14 13:29:32 发布

coder_szc

最新推荐文章于 2024-03-14 13:29:32 发布

阅读量528

点赞数

分类专栏：数据库文章标签： mysql 用户管理

本文链接：https://blog.csdn.net/qq_37475168/article/details/132257611

版权

数据库专栏收录该内容

31 篇文章 0 订阅

订阅专栏

文章目录

用户创建
用户修改
删除用户
权限管理

用户创建

基本格式：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 changed

mysql> 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 -A

Database 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: 0

mysql> 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 -A

Database changed

mysql> 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: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
authentication_string: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
      password_expired: N
password_last_changed: 2022-01-15 16:07:32
     password_lifetime: NULL
        account_locked: N
......
*************************** 4. row ***************************
                  Host: %
                  User: szc
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
authentication_string: *6691484EA6B50DDDE1926A220DA01FA9E575C18A
      password_expired: N
password_last_changed: 2022-01-15 20:30:48
     password_lifetime: NULL
        account_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: Host
   Type: char(60)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 2. row ***************************
  Field: Db
   Type: char(64)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 3. row ***************************
  Field: User
   Type: char(32)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 4. row ***************************
  Field: Table_name
   Type: char(64)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 5. row ***************************
  Field: Grantor
   Type: char(93)
   Null: NO
    Key: MUL
Default:
  Extra:
*************************** 6. row ***************************
  Field: Timestamp
   Type: timestamp
   Null: NO
    Key:
Default: CURRENT_TIMESTAMP
  Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
  Field: Table_priv
   Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')
   Null: NO
    Key:
Default:
  Extra:
*************************** 8. row ***************************
  Field: Column_priv
   Type: set('Select','Insert','Update','References')
   Null: NO
    Key:
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)、执行索引操作(增、删、触发或查看)。