MySQL數(shù)據(jù)庫(kù)高危權(quán)限回收的實(shí)現(xiàn)

更新時(shí)間：2026年01月30日 09:14:07 作者：DomDanrtsey

本文介紹MySQL數(shù)據(jù)庫(kù)的基本操作指令,包括查看和切換數(shù)據(jù)庫(kù)等,詳細(xì)講解了如何統(tǒng)計(jì)環(huán)境中的用戶(hù)及權(quán)限,并提供了權(quán)限回收的方法,包括回收高危權(quán)限和重新授權(quán)必要權(quán)限的過(guò)程,感興趣的可以了解一下

1. 基本操作指令

查看當(dāng)前系統(tǒng)數(shù)據(jù)庫(kù)

# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| erp              |
+--------------------+
3 rows in set (0.00 sec)

切換數(shù)據(jù)庫(kù)

mysql> use erp;
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

2. 權(quán)限回收準(zhǔn)備工作

統(tǒng)計(jì)當(dāng)前環(huán)境所有的用戶(hù)（%表示所有機(jī)器可訪問(wèn)；127.0.0.1、localhost表示本機(jī)可訪問(wèn)）

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+-----------------------------------------+
| query                                   |
+-----------------------------------------+
| User: 'root'@'%';                       |
| User: 'root'@'127.0.0.1';               |
| User: 'root'@'::1';                     |
| User: ''@'localhost';                   |
| User: 'root'@'localhost';               |
| User: 'erp'@'%';                       |
+-----------------------------------------+
5 rows in set (0.00 sec)

統(tǒng)計(jì)業(yè)務(wù)用戶(hù)具有哪些權(quán)限以用戶(hù)名erp舉例如下

mysql> show grants for 'erp'@'%';
+------------------------------------------------------------------------------------+
| Grants for erp@%                                                         |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'erp'@'%' IDENTIFIED BY PASSWORD '*******'      |
| GRANT ALL PRIVILEGES ON `ump`.* TO 'ump'@'%'                          |
+------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3. 操作回收高危權(quán)限

回收所有權(quán)限或回收某一權(quán)限，比如drop權(quán)限

mysql> revoke all privileges on erp.* from 'erp'@'%';
mysql> revoke drop on erp.* from 'erp'@'%';
mysql> flush privileges;

備注：
（1）列舉部分特殊的服務(wù)器權(quán)限及其功能說(shuō)明：

super：擁有此權(quán)限允許用戶(hù)終止任何查詢(xún)；修改全局變量的SET語(yǔ)句；使用CHANGE MASTER，PURGE MASTER LOGS
shutdown：關(guān)閉數(shù)據(jù)庫(kù)
show databases：查看數(shù)據(jù)庫(kù)
replication client：查詢(xún)master server、slave server狀態(tài)
replication slave：查看從服務(wù)器
reload：擁有此權(quán)限才可執(zhí)行flush [tables | logs | privileges]
process：擁有此權(quán)限才可以執(zhí)行SHOW PROCESSLIST和KILL命令
file：擁有file權(quán)限才可以執(zhí)行 select ..into outfile和load data infile…操作

（2）普通用戶(hù)權(quán)限及其功能說(shuō)明：

all：允許任何操作(usage權(quán)限不能被回收)
usage：只允許登錄
alter：修改數(shù)據(jù)庫(kù)的表
alter routine：修改/刪除存儲(chǔ)過(guò)程
create：創(chuàng)建表
create routine：創(chuàng)建存儲(chǔ)過(guò)程
create temporary tables：創(chuàng)建臨時(shí)表
create：創(chuàng)建新的數(shù)據(jù)庫(kù)或表
create view：創(chuàng)建視圖
delete：刪除表數(shù)據(jù)
drop：刪除數(shù)據(jù)庫(kù)/表
event：創(chuàng)建/更改/刪除/查看事件
execute：執(zhí)行權(quán)限
grant option：將自身所擁有的權(quán)限授予其他用戶(hù)
index：創(chuàng)建/刪除索引
insert：添加表數(shù)據(jù)
lock tables：鎖表
references：將其它表的一個(gè)字段作為某一個(gè)表的外鍵約束
select：查詢(xún)表數(shù)據(jù)
show view：查看視圖
trigger：創(chuàng)建觸發(fā)器
update：更新表數(shù)據(jù)

重新授權(quán)必要權(quán)限

mysql> grant select,insert,alter,update,delete,create,execute on erp.* to 'erp'@'%' ;
mysql> flush privileges;

確認(rèn)權(quán)限

mysql> show grants for 'erp'@'%';

4. 注意事項(xiàng)

1）、file, process, super為危險(xiǎn)權(quán)限，切勿權(quán)限授予管理員以外的賬號(hào)；

mysql> revoke file,process,super on erp.* from 'erp'@'%';

2）、查看某個(gè)或所有用戶(hù)的服務(wù)器權(quán)限，確認(rèn)普通賬號(hào)沒(méi)有授權(quán)上述三種危險(xiǎn)權(quán)限

mysql> select * from mysql.user where user='erp'\G;
*************************** 1. row ***************************
                  Host: %
                  User: erp
              Password: *33F471D4D8A84CD6C0
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              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: 
      password_expired: N
1 row in set (0.00 sec)
mysql> select * from mysql.user \G;

3）、授予某張表權(quán)限，權(quán)限信息保存在mysql.tables_priv表中

mysql> grant select on dbname.tablename to 'username'@'%' with grant option;
mysql> select * from mysql.tables_priv;
select * from mysql.tables_priv;
+-----------+-----+-------+------------+----------------+---------------------+-------+
| Host | Db  | User  | Table_name  | Grantor | Timestamp | Table_priv | Column_priv |
+-----------+-----+-------+------------+----------------+---------------------+-------+
| % | dbname | username | tablename | root@localhost | 0000-00-00 00:00:00 | Select,Grant |             |
+-----------+-----+-------+------------+----------------+---------------------+-------+

4）、授予某個(gè)字段權(quán)限，權(quán)限信息保存在mysql.columns_priv表中

mysql> grant select(Column_name) on dbname.tablename to 'username'@'%' with grant option;
mysql> select * from mysql.columns_priv;
select * from mysql.columns_priv;
+-----------+-----+-------+------------+-------------+---------------------+----------+
| Host   | Db  | User  | Table_name | Column_name | Timestamp   | Column_priv |
+-----------+-----+-------+------------+-------------+---------------------+----------+
| % | dbname | username | tablename | Column_name | 0000-00-00 00:00:00 | Select|
+-----------+-----+-------+------------+-------------+---------------------+----------+

5）、當(dāng)使用如下命令回收權(quán)限時(shí)，它回收的只是全局的權(quán)限，username用戶(hù)其他的權(quán)限，比如對(duì)dbname數(shù)據(jù)庫(kù)的權(quán)限，對(duì)tablename表的權(quán)限，對(duì)某個(gè)Column_name字段的權(quán)限仍然持有。

mysql> revoke all privileges on *.* from 'username'@'localhost';

所以為了回收用戶(hù)的所有權(quán)限，要使用如下命令

mysql> revoke all privileges,grant option from 'username'@'%';

5. 結(jié)束

到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)高危權(quán)限回收的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫(kù)高危權(quán)限回收內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: