当前位置：首页 > article >正文

用户管理【MySQL】

article 2024/12/27 2:32:17

文章目录

用户
创建用户
删除用户
修改密码
权限
- 回收权限

用户

MySQL当中默认有一个名为mysql的数据库

在这里插入图片描述

查看该数据库中的表，可以看到其中有一个名为user的表

在这里插入图片描述

user表中存储的就是MySQL中用户相关的信息

mysql> select * from user limit 1\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: *71A037B042978EDC234B6AD277AF39C442D7A70A
      password_expired: N
 password_last_changed: 2024-10-18 22:33:22
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

user：表示该用户的用户名。
host：表示该用户可以从哪个主机登录，localhost表示只能从本机登录，%表示可以从任意地方登录。
authentication_string：表示该用户的密码经过password函数加密后的值。
xxx_priv：表示该用户是否拥有对应权限。

查看用户信息

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

创建用户

CREATE USER ‘用户名’@‘登录主机’ IDENTIFIED BY ‘密码’;

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

mysql> select USER , HOST, authentication_string from user;
+---------------+-----------+-------------------------------------------+
| USER          | HOST      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *71A037B042978EDC234B6AD277AF39C442D7A70A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| hello         | localhost | *F6BFE547058863F3CF6CF0E114C92A793E8237FB |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

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

删除用户

mysql> select USER , HOST, authentication_string from user;
+---------------+-----------+-------------------------------------------+
| USER          | HOST      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *71A037B042978EDC234B6AD277AF39C442D7A70A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| hello         | localhost | *F6BFE547058863F3CF6CF0E114C92A793E8237FB |
| cxq           | localhost | *71A037B042978EDC234B6AD277AF39C442D7A70A |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> drop user hello@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>  select USER , HOST, authentication_string from user;
+---------------+-----------+-------------------------------------------+
| USER          | HOST      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *71A037B042978EDC234B6AD277AF39C442D7A70A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| test          | %         | *71A037B042978EDC234B6AD277AF39C442D7A70A |
| cxq           | localhost | *71A037B042978EDC234B6AD277AF39C442D7A70A |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

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

修改密码

mysql>  select USER , HOST, authentication_string from user;
+---------------+-----------+-------------------------------------------+
| USER          | HOST      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *71A037B042978EDC234B6AD277AF39C442D7A70A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| test          | %         | *71A037B042978EDC234B6AD277AF39C442D7A70A |
| cxq           | localhost | *71A037B042978EDC234B6AD277AF39C442D7A70A |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

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

mysql>  select USER , HOST, authentication_string from user;
+---------------+-----------+-------------------------------------------+
| USER          | HOST      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *71A037B042978EDC234B6AD277AF39C442D7A70A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| test          | %         | *93EAAC60542E46B10FFC35A22433B973472D6B8D |
| cxq           | localhost | *71A037B042978EDC234B6AD277AF39C442D7A70A |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> update user set authentication_string=password('123456') where user='test';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

权限

在这里插入图片描述

给用户授权

GRANT 权限列表 ON 库名.对象名 TO ‘用户名’@‘登录地址’ [IDENTIFIED BY ‘密码’];

‘用户名’@‘登录地址’：表示给哪一个用户授权。
库名.对象名：表示要授予用户哪个数据库下的哪个对象的权限。
权限列表：表示要授予用户何种权限，多个权限之间用逗号隔开。
IDENTIFIED BY '密码’可选：如果用户存在，则在授予权限的同时修改该用户的密码，如果用户不存在，则创建该用户

grant all on root_DB.user to 'cxq'@localhost;
mysql> show grants for 'cxq'@localhost;
+---------------------------------------------------------------+
| Grants for cxq@localhost                                      |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cxq'@'localhost'                       |
| GRANT ALL PRIVILEGES ON `root_DB`.* TO 'cxq'@'localhost'      |
| GRANT ALL PRIVILEGES ON `root_DB`.`user` TO 'cxq'@'localhost' |
| GRANT ALL PRIVILEGES ON `rootDB`.`user` TO 'cxq'@'localhost'  |
+---------------------------------------------------------------+
4 rows in set (0.00 sec)

回收权限

REVOKE 权限列表 ON 库名.对象名 FROM ‘用户名’@‘登录地址’;

mysql> revoke insert on root_DB.user from 'cxq'@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'cxq'@localhost;
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for cxq@localhost                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cxq'@'localhost'                                                                                                        |
| GRANT ALL PRIVILEGES ON `root_DB`.* TO 'cxq'@'localhost'                                                                                       |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `root_DB`.`user` TO 'cxq'@'localhost' |
| GRANT ALL PRIVILEGES ON `rootDB`.`user` TO 'cxq'@'localhost'                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)


mysql> revoke all on root_DB.user from 'cxq'@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'cxq'@localhost;
+--------------------------------------------------------------+
| Grants for cxq@localhost                                     |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cxq'@'localhost'                      |
| GRANT ALL PRIVILEGES ON `root_DB`.* TO 'cxq'@'localhost'     |
| GRANT ALL PRIVILEGES ON `rootDB`.`user` TO 'cxq'@'localhost' |
+--------------------------------------------------------------+
3 rows in set (0.00 sec)

查看全文

http://www.kler.cn/a/393071.html