MySQL如何新建用戶并授權(quán)

更新時間：2024年10月09日 16:52:02 作者：Chenry.r

本文主要介紹了如何在MySQL中創(chuàng)建新用戶并管理其權(quán)限,包括增刪改查、創(chuàng)建表、刪除表等操作,文中詳細說明了MySQL 5.7.18和MySQL 8.0版本中的權(quán)限配置,以及如何根據(jù)需要添加或刪除權(quán)限的步驟,旨在提供實用的數(shù)據(jù)庫管理技巧

前言

有些時候因開發(fā)需要，管理數(shù)據(jù)庫賬號和賬號對應(yīng)的權(quán)限和密碼，對自己的操作進行記錄，

主要操作的就是想創(chuàng)建一個新用戶，并賦予，增刪改查、創(chuàng)建表、刪除表、等一些權(quán)限，并允許任何主句連接。

一、技術(shù)講解

1.MySQL基礎(chǔ)信息

mysql> SELECT VERSION();                     -- 查看MySQL版本 
+-----------+
| VERSION() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

mysql> select user,host from mysql.user;      -- 查詢MySQL的所有用戶信息
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'root'@'localhost';    -- 查看'root'@'localhost'對應(yīng)的權(quán)限
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

從上面的代碼上不難看到我使用的MySQL版本是5.7.18，有一個只能本地連接的超管（root）；

其中'root'@'localhost'對應(yīng)的權(quán)限解釋

這些是 MySQL 數(shù)據(jù)庫中的授權(quán)語句，用于授予用戶權(quán)限。下面是這些語句的解釋：

1. GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION:
   1.GRANT ALL PRIVILEGES：授予所有權(quán)限給指定的用戶。
   2.ON *.*：作用于所有的數(shù)據(jù)庫和表。
   3.TO 'root'@'localhost'：將權(quán)限授予用戶名為 'root'、主機名為 'localhost' 的用戶。
   4.WITH GRANT OPTION：授予該用戶授權(quán)其他用戶的權(quán)限。
2. GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION:
   1.GRANT PROXY：授予用戶代理權(quán)限，允許用戶以其他用戶的身份進行數(shù)據(jù)庫操作。
   2.ON ''@''：代理用戶為空，即允許任何用戶代理任何其他用戶。
  3.TO 'root'@'localhost'：將代理權(quán)限授予用戶名為 'root'、主機名為 'localhost' 的用戶。
  4.WITH GRANT OPTION：授予該用戶授權(quán)其他用戶的權(quán)限。
總結(jié)起來，這些語句的作用是授予用戶 'root'@'localhost' 所有的數(shù)據(jù)庫和表的權(quán)限，并允許該用戶授權(quán)其他用戶。同時，還授予了用戶 'root'@'localhost' 代理任何用戶進行數(shù)據(jù)庫操作的權(quán)限。

2.MySQL的權(quán)限

如果在MySQL8.0版本里運行

SHOW GRANTS FOR ‘root'@‘%';

不難發(fā)現(xiàn)下面的權(quán)限的（超級管理員root的權(quán)限）

SELECT：允許從數(shù)據(jù)庫中選擇數(shù)據(jù)。
INSERT：允許向數(shù)據(jù)庫中插入數(shù)據(jù)。
UPDATE：允許更新數(shù)據(jù)庫中的數(shù)據(jù)。
DELETE：允許從數(shù)據(jù)庫中刪除數(shù)據(jù)。
CREATE：允許創(chuàng)建新的數(shù)據(jù)庫和表。
DROP：允許刪除數(shù)據(jù)庫和表。
RELOAD：允許重新加載MySQL服務(wù)器的配置文件。
SHUTDOWN：允許關(guān)閉MySQL服務(wù)器。
PROCESS：允許查看當(dāng)前正在運行的查詢進程。
FILE：允許讀取和寫入文件系統(tǒng)中的文件。
REFERENCES：允許在表之間創(chuàng)建外鍵約束。
INDEX：允許創(chuàng)建和刪除索引。
ALTER：允許修改表的架構(gòu)，例如更改列、添加/刪除約束等。
SHOW DATABASES：允許查看所有數(shù)據(jù)庫列表。
SUPER：允許執(zhí)行特權(quán)操作，例如設(shè)置最大連接數(shù)、關(guān)閉自動關(guān)閉線程等。
CREATE TEMPORARY TABLES：允許創(chuàng)建臨時表。
LOCK TABLES：允許對表進行鎖定操作，例如排他鎖定或共享鎖定。
EXECUTE：允許執(zhí)行存儲過程和函數(shù)。
REPLICATION SLAVE：允許作為從服務(wù)器進行復(fù)制（復(fù)制訂閱者）。
REPLICATION CLIENT：允許作為復(fù)制客戶端，例如從主服務(wù)器獲取日志數(shù)據(jù)。
CREATE VIEW：允許創(chuàng)建視圖對象。
SHOW VIEW：允許查看視圖對象。
CREATE ROUTINE：允許創(chuàng)建存儲過程和函數(shù)。
ALTER ROUTINE：允許修改存儲過程和函數(shù)的定義。
CREATE USER：允許創(chuàng)建新用戶。
EVENT：允許創(chuàng)建和管理事件調(diào)度程序。
TRIGGER：允許創(chuàng)建和管理觸發(fā)器。
CREATE TABLESPACE：允許創(chuàng)建新的表空間。
CREATE ROLE：允許創(chuàng)建新角色（用戶組）。
DROP ROLE：允許刪除角色（用戶組）。

二、操作步驟

1.創(chuàng)前介紹

1.創(chuàng)建用戶
   create user 'wmlc_dev'@'%' identified by '123456';
   創(chuàng)建用戶名為wmlc_dev，密碼是123456的用戶（wmlc_dev，123456可指定），'wmlc_dev'@'%' 里的%是指允許從任何主機（'%'）連接；
   如果要是指定主機的話可以把%換為要指定的IP即可
2.授予權(quán)限
   grant select, insert, update, delete on wmlc.* to 'wmlc_dev'@'%';
   授予用戶名為wmlc_dev，數(shù)據(jù)庫為` wmlc `的查詢、插入、更新和刪除操作的權(quán)限。（wmlc 是數(shù)據(jù)庫名稱，可指定）;
   如果想授予新用戶在所有數(shù)據(jù)庫和所有表上選擇和鎖定表的權(quán)限的話可改、 wmlc.* 為  *.* 即可
3.刷新權(quán)限緩存
   flush privileges;
4.做連接測試。

2.實操介紹

mysql> CREATE DATABASE wmlc CHARACTER SET utf8;     -- 創(chuàng)建我們需要的數(shù)據(jù)庫
Query OK, 1 row affected (0.00 sec)

mysql> show databases;                              -- 查詢數(shù)據(jù)庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wmlc               |
| yeb                |
+--------------------+
6 rows in set (0.02 sec)

mysql> create user 'wmlc_dev'@'%' identified by '123456';  -- 創(chuàng)建新用戶
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;                 
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| wmlc_dev  | %         |
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

mysql> grant select, insert, update, delete on wmlc.* to 'wmlc_dev'@'%';    -- 給新用戶授權(quán)
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'wmlc_dev'@'%';          --查詢新用戶的權(quán)限
+--------------------------------------------------------------------+
| Grants for wmlc_dev@%                                              |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wmlc_dev'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wmlc`.* TO 'wmlc_dev'@'%' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  flush privileges;                          -- 刷新
Query OK, 0 rows affected (0.02 sec)

mysql> exit;
Bye
root@43e97fa6b64f:/# mysql -uwmlc_dev -p123456      -- 測試創(chuàng)建的新賬號是否可以登錄
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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 |
| wmlc               |
+--------------------+
2 rows in set (0.01 sec)

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

mysql> CREATE TABLE `t_sys_user`  (
    ->   `id` int(11) NOT NULL,
    ->   `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '',
    ->   PRIMARY KEY (`id`) USING BTREE
    -> ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 當(dāng)我們創(chuàng)建表時會發(fā)現(xiàn)沒有權(quán)限創(chuàng)建
ERROR 1142 (42000): CREATE command denied to user 'wmlc_dev'@'localhost' for table 't_sys_user'

-- 在添加兩個權(quán)限 CREATE：允許創(chuàng)建新的數(shù)據(jù)庫和表。DROP：允許刪除數(shù)據(jù)庫和表。（添加權(quán)限需要切換回可以添加權(quán)限的賬號，這里我就不做過多介紹了，我是又切換回root賬號進行授權(quán)的
mysql> grant CREATE, DROP on wmlc.* to 'wmlc_dev'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'wmlc_dev'@'%'; 
+----------------------------------------------------------------------------------+
| Grants for wmlc_dev@%                                                            |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wmlc_dev'@'%'                                             |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `wmlc`.* TO 'wmlc_dev'@'%' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.02 sec)
-- 然后在重新登錄到我們新建的wmlc_dev賬號登錄操作就可以了
mysql> DROP TABLE IF EXISTS `t_sys_user`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t_sys_user`  (
    ->   `id` int(11) NOT NULL,
    ->   `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '',
    ->   PRIMARY KEY (`id`) USING BTREE
    -> ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
Query OK, 0 rows affected (0.01 sec)

總結(jié)

有的時候也可能需要給某些賬號刪除一些權(quán)限；添加或刪除那些權(quán)限根據(jù)自己的需求來定。

刪除操作如下：

要刪除MySQL用戶的某些權(quán)限，您可以使用以下方法：

 1.登錄到MySQL服務(wù)器，使用root用戶身份打開命令行終端窗口(有其它授權(quán)賬號也可以)。
 2.確定要刪除權(quán)限的用戶。如果要刪除多個用戶的權(quán)限，請在每個用戶名下重復(fù)以下步驟。
 3.運行以下命令以撤銷用戶在特定數(shù)據(jù)庫上的特定權(quán)限
   REVOKE <privilege> ON <database_name>.<table_name> FROM '<user_name>@<host>';
 其中，<privilege>是要撤銷的權(quán)限，如SELECT、INSERT、UPDATE、DELETE等。
      <database_name>是要撤銷權(quán)限的數(shù)據(jù)庫名稱，
      <table_name>是要撤銷權(quán)限的表名稱，
      <user_name>是要刪除權(quán)限的用戶名，
      <host>是用戶的主機名。
      
例如，如果要刪除名為 wmlc_dev 的用戶在名為 wmlc 的數(shù)據(jù)庫中所有表的CREATE和DROP 權(quán)限，可以運行以下命令：
REVOKE  CREATE, DROP ON wmlc.* FROM 'wmlc_dev'@'%';
運行以下命令以刷新權(quán)限，以便更改生效：
FLUSH PRIVILEGES;
完成上述步驟后，指定用戶的特定權(quán)限應(yīng)該已被刪除。
請注意，如果刪除了一個用戶在所有數(shù)據(jù)庫和表上的所有權(quán)限，該用戶將無法連接到MySQL服務(wù)器。

實操如下：

-- 我們這里刪除了剛才我們添加兩個權(quán)限 CREATE：允許創(chuàng)建新的數(shù)據(jù)庫和表。DROP：允許刪除數(shù)據(jù)庫和表
mysql> REVOKE  CREATE, DROP ON wmlc.* FROM 'wmlc_dev'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'wmlc_dev'@'%';
+--------------------------------------------------------------------+
| Grants for wmlc_dev@%                                              |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wmlc_dev'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wmlc`.* TO 'wmlc_dev'@'%' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

補充部分：

-- 假設(shè)新用戶名為'wmlc_dev'@'%'
mysql> SHOW GRANTS FOR 'wmlc_dev'@'%';
-- 添加權(quán)限（創(chuàng)建數(shù)據(jù)庫/表,刪除數(shù)據(jù)庫/表,查詢,添加,修改,刪除）
mysql> grant CREATE, DROP, select, insert, update, delete on *.* to 'develop'@'%';
-- 撤銷用戶的所有權(quán)限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'wmlc_dev'@'%';
mysql> FLUSH PRIVILEGES;
 
-- 刪除用戶在授權(quán)表中的記錄（如果直接在授權(quán)表中添加了記錄）
mysql> DELETE FROM mysql.user WHERE User='wmlc_dev' AND Host='%';
mysql> FLUSH PRIVILEGES;

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: