用户管理【MySQL】

用户

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

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

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

mysql> select * from user limit 1\G
*************************** 1. row ***************************Host: localhostUser: 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: YCreate_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_passwordauthentication_string: *71A037B042978EDC234B6AD277AF39C442D7A70Apassword_expired: Npassword_last_changed: 2024-10-18 22:33:22password_lifetime: NULLaccount_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)