文章目錄
- DCL
- 1. 基本介紹
- 2. 用戶管理
- 2.1 查詢用戶
- 2.2 創建用戶
- 2.3 修改用戶密碼
- 2.4 刪除用戶
- 3. 權限控制
- 3.1 查詢權限
- 3.2 授予權限
- 3.3 撤銷權限
- 4. DCL總結
DCL
更多數據庫MySQL系統內容就在以下專欄：
專欄鏈接：數據庫MySQL
1. 基本介紹
DCL英文全稱是Data Control Language(數據控制語言)， 用來管理數據庫用戶、控制數據庫的訪問權限。
itcast 和 heima 是我們的用戶，而DCL就是控制有哪些用戶能夠訪問數據庫，那么，每一個用戶又有訪問不同數據庫的權限。
2. 用戶管理
2.1 查詢用戶
基本語法：
USE mysql;
SELECT * FROM user;
- 在MySQL中，用戶的信息，用戶所具有的權限的信息，都是存放在系統數據庫mysql的user表中的。
- 默認在MySQL數據庫中有五個用戶，表中的第一個字段host 是主機的意思。在這五個當中，我們只使用過root這一個用戶，其他的并沒有使用過。
- 我們在刪除一個用戶的時候，需要通過用戶名和host 主機地址，同時定位。
- 主機地址，表示的當前用戶只能在哪個主機上訪問MySQL服務器，localhost 表示只能在本機訪問，不能遠程訪問。
mysql> use mysql;
Database changed
mysql>
mysql> select * from user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| % | scott | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$"+4)*-%1 B4y/g_diDXo2r2UZxrw6JaXyvywXlP0IJfl5JA44SMArbOkaC | N | 2024-04-25 20:22:19 | NULL | N | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2024-04-11 20:35:58 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2024-04-11 20:35:58 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2024-04-11 20:35:58 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$}|@dFq\)wgJ|>{)8MR9roBZWBb9ltDJxjKz5LPd7OL6YU7/tlbgMxFxTu65 | N | 2024-04-11 20:36:04 | NULL | N | Y | Y | NULL | NULL | NULL | NULL |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
5 rows in set (0.06 sec)mysql>
2.2 創建用戶
基本語法：
CREATE USER '用戶名' @ '主機名' IDENTIFIED BY '密碼';
用戶名：新創建的用戶名。
主機名：在哪一個主機上用戶可以訪問當前MySQL。
密碼：訪問密碼。
案例1：
創建用戶 itcast ，只能在當前主機localhost 訪問，密碼：111111；
mysql>
mysql> create user 'itcast'@'localhost' identified by '111111';
Query OK, 0 rows affected (0.01 sec)
mysql>
- 查詢一下，看是不是增加了一個用戶；
mysql>
mysql> select * from user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| % | scott | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$"+4)*-%1 B4y/g_diDXo2r2UZxrw6JaXyvywXlP0IJfl5JA44SMArbOkaC | N | 2024-04-25 20:22:19 | NULL | N | N | N | NULL | NULL | NULL | NULL |
| localhost | itcast | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$O"+Vv4M6xe r0FD1DszyHBQABltzRNOfKdRvZG8bE2Lu5ROVpeuXWgZsx5 | N | 2024-06-05 19:53:17 | NULL | N | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2024-04-11 20:35:58 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2024-04-11 20:35:58 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2024-04-11 20:35:58 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$}|@dFq\)wgJ|>{)8MR9roBZWBb9ltDJxjKz5LPd7OL6YU7/tlbgMxFxTu65 | N | 2024-04-11 20:36:04 | NULL | N | Y | Y | NULL | NULL | NULL | NULL |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
6 rows in set (0.06 sec)mysql>
- 使用itcast 用戶訪問mysql :
C:\Users\Lenovo>mysql -u itcast -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.36 MySQL Community Server - GPLCopyright (c) 2000, 2024, 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>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.01 sec)mysql>
- 使用root 用戶訪問MySQL數據庫：
Microsoft Windows [版本 10.0.22631.3672]
(c) Microsoft Corporation。保留所有權利。C:\Users\Lenovo>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.36 MySQL Community Server - GPLCopyright (c) 2000, 2024, 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 |
+--------------------+
| company |
| demo |
| information_schema |
| itcast |
| itxiaobu |
| mysql |
| performance_schema |
| sys |
| text |
+--------------------+
9 rows in set (0.00 sec)mysql>
mysql>
- 我們在使用不同的用戶訪問同一個mysql數據庫，其能訪問的數據庫是不一樣的：
itcast用戶能訪問的：
root用戶能訪問的：
這是因為我們僅僅只是創建了itcast 這個用戶，它可以訪問mysql數據庫，但是它沒有訪問其他數據庫的權限。
案例2：
創建用戶 xiaobu 可以在任意主機訪問該數據庫，密碼：123456；
mysql>
mysql> create user 'xiaobu'@'%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
mysql>
可以在任意主機訪問，使用%
2.3 修改用戶密碼
基本語法：
ALTER USER '用戶名' @ '主機名' IDENTIFIED WITH mysql_native_passward BY '新密碼';
案例：
修改用戶 xiaobu 的訪問密碼為 111111；
mysql> alter user 'xiaobu'@'%' identified with mysql_native_password by '111111';
Query OK, 0 rows affected (0.01 sec)
mysql>
2.4 刪除用戶
基本語法：
DROP USER '用戶名' @ '主機名';
案例：
刪除itcast@localhost用戶；
mysql>
mysql> drop user 'itcast'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql>
3. 權限控制
MySQL中定義了很多種權限，但是常用的就以下幾種：
| 權限 | 說明 |
|---|---|
| ALL, ALL PRIVILEGES | 所有權限 |
| SELECT | 查詢數據 |
| INSERT | 插入數據 |
| UPDATE | 修改數據 |
| DELECT | 刪除數據 |
| ALTER | 修改表 |
| DROP | 刪除數據庫/表/視圖 |
| CREATE | 創建數據庫/表 |
3.1 查詢權限
基本語法：
SHOW GRANTS FOR '用戶名'@'主機名'；
案例：
查詢用戶xiaobu的權限
mysql>
mysql> show grants for 'xiaobu'@'%';
+------------------------------------+
| Grants for xiaobu@% |
+------------------------------------+
| GRANT USAGE ON *.* TO `xiaobu`@`%` |
+------------------------------------+
1 row in set (0.03 sec)mysql>
3.2 授予權限
基本語法:
GRANT 權限列表 ON 數據庫名.表名 TO '用戶名'@'主機名';
案例：
賦予用戶xiaobu 訪問itcast數據庫的所有表的權限
mysql>
mysql> grant all on itcast.* to 'xiaobu'@'%';
Query OK, 0 rows affected (0.01 sec)mysql>
3.3 撤銷權限
基本語法：
REVOKE 權限列表 ON 數據庫名.表名 FROM '用戶名'@'主機名';
案例：
撤銷用戶xiaobu 訪問itcast數據庫所有表的權限
mysql>
mysql> revoke all on itcast.* from 'xiaobu'@'%';
Query OK, 0 rows affected (0.00 sec)mysql>
注意：
- 多個權限之間，使用逗號分割。
- 授權時，數據庫名和表名可以使用* 進行通配，代表所有。
4. DCL總結
![【2024最新華為OD-C/D卷試題匯總】[支持在線評測] 土地分配 (100分) - 三語言AC題解(Python/Java/Cpp)](http://pic.xiahunao.cn/【2024最新華為OD-C/D卷試題匯總】[支持在線評測] 土地分配 (100分) - 三語言AC題解(Python/Java/Cpp))
Archetype,Chunk,NativeArray)
