持续学习&持续更新中…
学习态度:守破离
【宋红康 MySQL数据库 】【高级篇】【04】用户、权限、角色管理
用户管理
用户权限坚持
最小分配
原则
登录MySQL服务器
[root@mysql8_01 mysql]# mysql -h localhost -P 3306 -u root -p dbtest01 -e "select * from emp01"
Enter password:
+------+-----------+
| id | name |
+------+-----------+
| 10 | Tom |
| 12 | 数据库 |
+------+-----------+
[root@mysql8_01 mysql]#
创建用户
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| dbtest01 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE mysql;
Database changed
mysql> SELECT host, user FROM user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
4 rows in set (0.00 sec)
mysql> CREATE USER 'zhang3' IDENTIFIED BY '$ABC_123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> CREATE USER 'zhang3' IDENTIFIED BY '$ABC_abc_123';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT host, user FROM user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| % | zhang3 |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
5 rows in set (0.00 sec)
mysql> CREATE USER 'zhang3' IDENTIFIED BY '$ABC_abc_123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'zhang3'@'%'
mysql> CREATE USER 'zhang3'@'localhost' IDENTIFIED BY '$ABC_abc_123';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT host, user FROM user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| % | zhang3 |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | zhang3 |
+-----------+------------------+
6 rows in set (0.00 sec)
mysql> CREATE USER 'li4'@'%' IDENTIFIED BY '$ABC_abc_123';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT host, user FROM user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | li4 |
| % | root |
| % | zhang3 |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | zhang3 |
+-----------+------------------+
7 rows in set (0.00 sec)
mysql>
登录li4账号:
[root@mysql8_01 mysql]# mysql -u li4 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.25 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 li4@% |
+---------------------------------+
| GRANT USAGE ON *.* TO `li4`@`%` |
+---------------------------------+
1 row in set (0.00 sec)
mysql>
修改用户
直接操作mysql.user这张表就行
mysql> USE mysql;
Database changed
mysql> UPDATE user SET user='zs' WHERE host='%' AND user='zhang3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT host, user FROM user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | li4 |
| % | root |
| % | zs |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | zhang3 |
+-----------+------------------+
7 rows in set (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
删除用户
权限管理
权限列表
SHOW PRIVILEGES;
授予权限的原则
授予权限
GRANT INSERT ON dbtest01.emp01 TO 'li4'@'%';
GRANT UPDATE, SELECT ON dbtest01.* TO 'li4'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'li4'@'%'; # 拥有除过授予其他用户权限(grant权限)的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'li4'@'%' WITH GRANT OPTION; # 拥有所有权限,会失控,最好不要使用
查看权限
收回权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zs'@'%';
小总结
权限表
使用
use mysql;show tables;
查看
使用:
desc mysql.user;
查看
DESCRIBE mysql.db;
desc mysql.tables_priv;
desc mysql.columns_priv;
desc mysql.procs_priv;
mysql> use mysql
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| role_edges |
| 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 |
+------------------------------------------------------+
35 rows in set (0.00 sec)
mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | 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 unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.01 sec)
mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | 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 | |
| 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 | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_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 | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
mysql> desc tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | varchar(288) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.00 sec)
mysql> desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | 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 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.00 sec)
mysql> desc mysql.procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | 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 | varchar(288) | NO | MUL | | |
| Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.01 sec)
mysql>
访问控制
角色管理
角色的理解
创建角色
CREATE ROLE 'manager';
create role 'manager', 'boss'@'%';
给角色赋予权限
GRANT SELECT ON dbtest01.* TO 'boss'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'boss';
SHOW PRIVILEGES\G;
查看角色的权限
SHOW GRANTS FOR 'boss'@'%';
回收角色的权限
REVOKE SELECT ON dbtest01.emp01 FROM 'manager'@'%';
删除角色
DROP ROLE 'boss';
给用户赋予角色
mysql> SELECT host, user FROM mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | boss |
| % | li4 |
| % | manager |
| % | root |
| % | zs |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
8 rows in set (0.00 sec)
mysql> GRANT 'manager'@'%' TO 'zs'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
激活角色
SET DEFAULT ROLE 'manager'@'%' TO 'zs';
查看当前会话已激活的角色:
SELECT CURRENT_ROLE();
撤销用户的角色
REVOKE 'manager'@'%' FROM 'zs'@'%';
设置强制角色(mandatory role)
小结
参考
尚硅谷宋红康: MySQL数据库(入门到高级,菜鸟到大牛).
本文完,感谢您的关注支持!