MySQL数据库用户管理和权限语法及实战案例_Mysql

引言

如果我们只能使用root用户，这样存在安全隐患。在多用户协同开发时，很容易因为新手的误操作，给数据库带来严重的安全问题。这时，就需要使用mysql的用户管理。

用户

用户信息

mysql中的用户，都存储在系统数据库mysql的user表中

mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| bit_index          |
| database1          |
| index_db           |
| mysql              |
| performance_schema |
| scott              |
| sys                |
| test               |
| test_db            |
| user_db            |
+--------------------+
11 rows in set (0.00 sec)

mysql> use mysql
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_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          |                                           |
| localhost | mysql.session | *thisisnotavalidpasswordthatcanbeusedhere |
| localhost | mysql.sys     | *thisisnotavalidpasswordthatcanbeusedhere |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.01 sec)

字段解释：

host：表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆
user：用户名
authentication_string：用户密码通过password函数加密后的
*_priv：用户拥有的权限
我们还可以通过select * from user \g查看每个用户的具体信息。

mysql> select * from user \g
*************************** 1. row ***************************
                  host: localhost
                  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: auth_socket
 authentication_string: 
      password_expired: n
 password_last_changed: 2025-01-20 12:21:54
     password_lifetime: null
        account_locked: n
*************************** 2. row ***************************
                  host: localhost
                  user: mysql.session
           select_priv: n
           insert_priv: n
           update_priv: n
           delete_priv: n
           create_priv: n
             drop_priv: n
           reload_priv: n
         shutdown_priv: n
          process_priv: n
             file_priv: n
            grant_priv: n
       references_priv: n
            index_priv: n
            alter_priv: n
          show_db_priv: n
            super_priv: y
 create_tmp_table_priv: n
      lock_tables_priv: n
          execute_priv: n
       repl_slave_priv: n
      repl_client_priv: n
      create_view_priv: n
        show_view_priv: n
   create_routine_priv: n
    alter_routine_priv: n
      create_user_priv: n
            event_priv: n
          trigger_priv: n
create_tablespace_priv: n
              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: *thisisnotavalidpasswordthatcanbeusedhere
      password_expired: n
 password_last_changed: 2025-01-20 12:21:54
     password_lifetime: null
        account_locked: y
*************************** 3. row ***************************
                  host: localhost
                  user: mysql.sys
           select_priv: n
           insert_priv: n
           update_priv: n
           delete_priv: n
           create_priv: n
             drop_priv: n
           reload_priv: n
         shutdown_priv: n
          process_priv: n
             file_priv: n
            grant_priv: n
       references_priv: n
            index_priv: n
            alter_priv: n
          show_db_priv: n
            super_priv: n
 create_tmp_table_priv: n
      lock_tables_priv: n
          execute_priv: n
       repl_slave_priv: n
      repl_client_priv: n
      create_view_priv: n
        show_view_priv: n
   create_routine_priv: n
    alter_routine_priv: n
      create_user_priv: n
            event_priv: n
          trigger_priv: n
create_tablespace_priv: n
              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: *thisisnotavalidpasswordthatcanbeusedhere
      password_expired: n
 password_last_changed: 2025-01-20 12:21:54
     password_lifetime: null
        account_locked: y
3 rows in set (0.00 sec)

创建用户

语法

create user '用户名'@'登陆主机/ip' identified by '密码';
如果登录主机被设置为%，则表示该用户可以在任何地方登陆user；

此设置方法需要谨慎使用

案例

mysql> create user 'wdd'@'localhost' identified by '123456';
query ok, 0 rows affected (0.00 sec)

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *999fd3326f738172cf3b546d7b69779554e9719e |
| localhost | mysql.session | *thisisnotavalidpasswordthatcanbeusedhere |
| localhost | mysql.sys     | *thisisnotavalidpasswordthatcanbeusedhere |
| localhost | wdd           | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

此时，我们便可以使用新账号新密码进行登录了，但是，此时新用户的权限病灭有被设置，所以大部分库都是看不到的。

wdd@vm-20-16-ubuntu:~/mysql$ mysql -u wdd -p;
enter password: 
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 7
server version: 5.7.29 mysql community server (gpl)

copyright (c) 2000, 2020, oracle and/or its affiliates. all rights reserved.

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)

修改用户密码

语法

自己改自己密码
set password=password('新的密码');
root用户修改指定用户的密码
set password for '用户名'@'主机名'=password('新的密码');

案例

自己修改自己的密码

mysql> set password=password('12321');
query ok, 0 rows affected, 1 warning (0.00 sec)

root用户修改任意用户的密码

mysql> set password for 'wdd'@'localhost'=password('1234abcd');
query ok, 0 rows affected, 1 warning (0.00 sec)

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *999fd3326f738172cf3b546d7b69779554e9719e |
| localhost | mysql.session | *thisisnotavalidpasswordthatcanbeusedhere |
| localhost | mysql.sys     | *thisisnotavalidpasswordthatcanbeusedhere |
| localhost | wdd           | *a28d6a233b76fc581a8e711b8966883c91c97612 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

删除用户

语法

drop user '用户名'@'主机名'

案例

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *999fd3326f738172cf3b546d7b69779554e9719e |
| localhost | mysql.session | *thisisnotavalidpasswordthatcanbeusedhere |
| localhost | mysql.sys     | *thisisnotavalidpasswordthatcanbeusedhere |
| localhost | wdd           | *a28d6a233b76fc581a8e711b8966883c91c97612 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> drop user 'wdd'@'localhost';
query ok, 0 rows affected (0.00 sec)

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *999fd3326f738172cf3b546d7b69779554e9719e |
| localhost | mysql.session | *thisisnotavalidpasswordthatcanbeusedhere |
| localhost | mysql.sys     | *thisisnotavalidpasswordthatcanbeusedhere |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

权限

权限列表

mysql数据库提供的权限列表：

查看和刷新用户的权限

查看用户权限

mysql> show grants for 'wdd'@'localhost';
+-----------------------------------------------+
| grants for wdd@localhost                      |
+-----------------------------------------------+
| grant usage on *.* to 'wdd'@'localhost'       |
| grant select on `test`.* to 'wdd'@'localhost' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| grants for root@localhost                                           |
+---------------------------------------------------------------------+
| grant all privileges on *.* to 'root'@'localhost' with grant option |
| grant proxy on ''@'' to 'root'@'localhost' with grant option        |
+---------------------------------------------------------------------+
2 rows in set (0.01 sec)

如果发现赋权限后，没有生效，执行如下指令：

mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)

给用户授权

刚创建的用户没有任何权限。需要给用户授权。

语法

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']

说明：

权限列表，多个权限用逗号分开

grant select on ...
grant select, delete, create on ....
grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限

*.* : 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）
库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)
identified by可选。如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户。

案例

终端a:(使用root账号)

mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| bit_index          |
| database1          |
| index_db           |
| mysql              |
| performance_schema |
| scott              |
| sys                |
| test               |
| test_db            |
| user_db            |
+--------------------+
11 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 |
+----------------+
| msg            |
| tmp            |
+----------------+
2 rows in set (0.00 sec)

mysql> grant select on test.* to 'wdd'@'localhost';
query ok, 0 rows affected (0.00 sec)

终端b:(使用wdd账号)

--没有设置查看权限前
mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
设置查看权限以后
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 |
+----------------+
| msg            |
| tmp            |
+----------------+
2 rows in set (0.00 sec)
--可以查看
mysql> select * from tmp;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 1990-02-24 |
|  2 | 1980-03-05 |
|  3 | 2025-02-18 |
+----+------------+
3 rows in set (0.00 sec)
--没有删除权限
mysql> delete from tmp;
error 1142 (42000): delete command denied to user 'wdd'@'localhost' for table 'tmp'

回收权限

语法

revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'；

示例

终端a:(root 账号)

mysql> revoke all on test.* from 'wdd'@'localhost';
query ok, 0 rows affected (0.00 sec)

终端b:(wdd 账号)

-- 回收权限之前
mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)
--回收权限以后
mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)