MySQL用户管理

2024-04-01 12:54:02
开发
35

如果我们只能使用root用户，这样存在安全隐患。这时，就需要使用MySQL的用户管理。

1.用户

1.1 用户信息

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> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *C02596EA6553530BF4C723C950D4E055782C299D |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

host：表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆

user：用户名

authentication_string：用户密码通过password函数加密后的

*_priv：用户拥有的权限

我们也可以使用select语句查看所有的用户信息。

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: mysql_native_password
 authentication_string: *C02596EA6553530BF4C723C950D4E055782C299D
      password_expired: N
 password_last_changed: 2024-03-29 11:00:08
     password_lifetime: NULL
        account_locked: N

值得注意的是，user表中，user(用户名)和host（主机）共同构成了主键，所以MySQL可以存在同名用户，只要同名用户使用不同的主机登录即可。因为user和host共同承担了主键，只有两个都相同才构成主键冲突。

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

1.2 创建用户

create user '用户名'@'登陆主机/ip' identified by '密码';

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

主机号为%说明可以从任何地方登录。

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *C02596EA6553530BF4C723C950D4E055782C299D |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| %         | cola          | *03394B44597A30608A339BDA3263EE4F21E077C9 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

可以看到我们刚刚创建的用户已经被写入user表中了。

[root@hecs-198768 ~]# mysql -u cola -p;
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 155
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> select user();
+----------------+
| user()         |
+----------------+
| cola@localhost |
+----------------+
1 row in set (0.00 sec)

我们就可以用刚刚创建的用户登录了。

1.3修改用户密码

自己改自己的密码

set password=password('新的密码');

mysql> set password = password('123qwe#@!!@#');
Query OK, 0 rows affected, 1 warning (0.00 sec)

超级用户修改任意用户密码

set password for '用户名'@'主机名'=password('新的密码')；

mysql> set password for 'cola'@'%' = password('hsu@177118');
Query OK, 0 rows affected, 1 warning (0.00 sec)

1.4 删除用户

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

mysql> drop user 'cola'@'%';
Query OK, 0 rows affected (0.00 sec)

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

在user表中的数据也不存在了。

2.数据库的权限

数据库对应的权限：

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

2.1给用户授权 grant on to

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

我们重新创建一个用户，并打开。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

我们会发现，新创建的用户中，并看不见root账户中的数据库，原因就是没有权限，如果想让新用户看到数据库，可以给他权限。

语法：

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

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

grant select on ...

grant select, delete, create on ....

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

案例：在root账号下赋予scott数据库中所有文件的select权限。

但是因为只开放了select权限，如果我们想插入一个数据就会失败。

mysql> insert into dept values (50, 'HR', 'BEIJING');
ERROR 1142 (42000): INSERT command denied to user 'cola'@'localhost' for table 'dept'

我们也可以将一个数据库的权限全部开放。

我们将student数据库中所有的权限开放后，就可以成功插入了。

2.2查看权限

使用 show grants for 'user'@'host'

mysql> show grants for 'cola'@'%';
+---------------------------------------------------+
| Grants for cola@%                                 |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'cola'@'%'                  |
| GRANT SELECT ON `scott`.* TO 'cola'@'%'           |
| GRANT ALL PRIVILEGES ON `student`.* TO 'cola'@'%' |
+---------------------------------------------------+
3 rows in set (0.00 sec)

可以看到我们刚刚给那两个数据库增加的权限都可以看见

2.3删除权限 revoke on from

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

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

mysql> show grants for 'cola'@'%';
+-----------------------------------------+
| Grants for cola@%                       |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'cola'@'%'        |
| GRANT SELECT ON `scott`.* TO 'cola'@'%' |
+-----------------------------------------+
2 rows in set (0.00 sec)

删除成功。

原文地址:https://blog.csdn.net/weixin_74310945/article/details/137195472 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：https://www.suanlizi.com/kf/1774661475491778560.html 如若内容造成侵权/违法违规/事实不符，请联系《酸梨子》网邮箱：1419361763@qq.com进行投诉反馈，一经查实，立即删除！

阅读全部