MySQL如何新建用戶(hù)并授權(quán)
更新時(shí)間：2024年10月09日 16:52:02 作者：Chenry.r
本文主要介紹了如何在MySQL中創(chuàng)建新用戶(hù)并管理其權(quán)限,包括增刪改查、創(chuàng)建表、刪除表等操作,文中詳細(xì)說(shuō)明了MySQL 5.7.18和MySQL 8.0版本中的權(quán)限配置,以及如何根據(jù)需要添加或刪除權(quán)限的步驟,旨在提供實(shí)用的數(shù)據(jù)庫(kù)管理技巧
前言
有些時(shí)候因開(kāi)發(fā)需要，管理數(shù)據(jù)庫(kù)賬號(hào)和賬號(hào)對(duì)應(yīng)的權(quán)限和密碼，對(duì)自己的操作進(jìn)行記錄，
主要操作的就是想創(chuàng)建一個(gè)新用戶(hù)，并賦予，增刪改查、創(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; -- 查詢(xún)MySQL的所有用戶(hù)信息 +-----------+-----------+ | user | host | +-----------+-----------+ | mysql.sys | localhost | | root | localhost | +-----------+-----------+ 2 rows in set (0.00 sec) mysql> SHOW GRANTS FOR 'root'@'localhost'; -- 查看'root'@'localhost'對(duì)應(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，有一個(gè)只能本地連接的超管（root）；
其中'root'@'localhost'對(duì)應(yīng)的權(quán)限解釋 這些是 MySQL 數(shù)據(jù)庫(kù)中的授權(quán)語(yǔ)句，用于授予用戶(hù)權(quán)限。下面是這些語(yǔ)句的解釋?zhuān)? 1. GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION: 1.GRANT ALL PRIVILEGES：授予所有權(quán)限給指定的用戶(hù)。 2.ON *.*：作用于所有的數(shù)據(jù)庫(kù)和表。 3.TO 'root'@'localhost'：將權(quán)限授予用戶(hù)名為 'root'、主機(jī)名為 'localhost' 的用戶(hù)。 4.WITH GRANT OPTION：授予該用戶(hù)授權(quán)其他用戶(hù)的權(quán)限。 2. GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION: 1.GRANT PROXY：授予用戶(hù)代理權(quán)限，允許用戶(hù)以其他用戶(hù)的身份進(jìn)行數(shù)據(jù)庫(kù)操作。 2.ON ''@''：代理用戶(hù)為空，即允許任何用戶(hù)代理任何其他用戶(hù)。 3.TO 'root'@'localhost'：將代理權(quán)限授予用戶(hù)名為 'root'、主機(jī)名為 'localhost' 的用戶(hù)。 4.WITH GRANT OPTION：授予該用戶(hù)授權(quán)其他用戶(hù)的權(quán)限。 總結(jié)起來(lái)，這些語(yǔ)句的作用是授予用戶(hù) 'root'@'localhost' 所有的數(shù)據(jù)庫(kù)和表的權(quán)限，并允許該用戶(hù)授權(quán)其他用戶(hù)。同時(shí)，還授予了用戶(hù) 'root'@'localhost' 代理任何用戶(hù)進(jìn)行數(shù)據(jù)庫(kù)操作的權(quán)限。
2.MySQL的權(quán)限
如果在MySQL8.0版本里運(yùn)行
SHOW GRANTS FOR ‘root'@‘%';
不難發(fā)現(xiàn)下面的權(quán)限的 （超級(jí)管理員root的權(quán)限）
SELECT：允許從數(shù)據(jù)庫(kù)中選擇數(shù)據(jù)。INSERT：允許向數(shù)據(jù)庫(kù)中插入數(shù)據(jù)。UPDATE：允許更新數(shù)據(jù)庫(kù)中的數(shù)據(jù)。DELETE：允許從數(shù)據(jù)庫(kù)中刪除數(shù)據(jù)。CREATE：允許創(chuàng)建新的數(shù)據(jù)庫(kù)和表。DROP：允許刪除數(shù)據(jù)庫(kù)和表。RELOAD：允許重新加載MySQL服務(wù)器的配置文件。SHUTDOWN：允許關(guān)閉MySQL服務(wù)器。PROCESS：允許查看當(dāng)前正在運(yùn)行的查詢(xún)進(jìn)程。FILE：允許讀取和寫(xiě)入文件系統(tǒng)中的文件。REFERENCES：允許在表之間創(chuàng)建外鍵約束。INDEX：允許創(chuàng)建和刪除索引。ALTER：允許修改表的架構(gòu)，例如更改列、添加/刪除約束等。SHOW DATABASES：允許查看所有數(shù)據(jù)庫(kù)列表。SUPER：允許執(zhí)行特權(quán)操作，例如設(shè)置最大連接數(shù)、關(guān)閉自動(dòng)關(guān)閉線(xiàn)程等。CREATE TEMPORARY TABLES：允許創(chuàng)建臨時(shí)表。LOCK TABLES：允許對(duì)表進(jìn)行鎖定操作，例如排他鎖定或共享鎖定。EXECUTE：允許執(zhí)行存儲(chǔ)過(guò)程和函數(shù)。REPLICATION SLAVE：允許作為從服務(wù)器進(jìn)行復(fù)制（復(fù)制訂閱者）。REPLICATION CLIENT：允許作為復(fù)制客戶(hù)端，例如從主服務(wù)器獲取日志數(shù)據(jù)。CREATE VIEW：允許創(chuàng)建視圖對(duì)象。SHOW VIEW：允許查看視圖對(duì)象。CREATE ROUTINE：允許創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)。ALTER ROUTINE：允許修改存儲(chǔ)過(guò)程和函數(shù)的定義。CREATE USER：允許創(chuàng)建新用戶(hù)。EVENT：允許創(chuàng)建和管理事件調(diào)度程序。TRIGGER：允許創(chuàng)建和管理觸發(fā)器。CREATE TABLESPACE：允許創(chuàng)建新的表空間。CREATE ROLE：允許創(chuàng)建新角色（用戶(hù)組）。DROP ROLE：允許刪除角色（用戶(hù)組）。
二、操作步驟
1.創(chuàng)前介紹
1.創(chuàng)建用戶(hù) create user 'wmlc_dev'@'%' identified by '123456'; 創(chuàng)建用戶(hù)名為wmlc_dev，密碼是123456的用戶(hù)（wmlc_dev，123456可指定），'wmlc_dev'@'%' 里的%是指允許從任何主機(jī)（'%'）連接； 如果要是指定主機(jī)的話(huà)可以把%換為要指定的IP即可 2.授予權(quán)限 grant select, insert, update, delete on wmlc.* to 'wmlc_dev'@'%'; 授予用戶(hù)名為wmlc_dev，數(shù)據(jù)庫(kù)為` wmlc `的查詢(xún)、插入、更新和刪除操作的權(quán)限。（wmlc 是數(shù)據(jù)庫(kù)名稱(chēng)，可指定）; 如果想授予新用戶(hù)在所有數(shù)據(jù)庫(kù)和所有表上選擇和鎖定表的權(quán)限的話(huà)可改、 wmlc.* 為 *.* 即可 3.刷新權(quán)限緩存 flush privileges; 4.做連接測(cè)試。
2.實(shí)操介紹
mysql> CREATE DATABASE wmlc CHARACTER SET utf8; -- 創(chuàng)建我們需要的數(shù)據(jù)庫(kù)
Query OK, 1 row affected (0.00 sec)
mysql> show databases; -- 查詢(xún)數(shù)據(jù)庫(kù)
+--------------------+
| 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)建新用戶(hù)
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'@'%'; -- 給新用戶(hù)授權(quán)
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'wmlc_dev'@'%'; --查詢(xún)新用戶(hù)的權(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 -- 測(cè)試創(chuàng)建的新賬號(hào)是否可以登錄
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)建表時(shí)會(huì)發(fā)現(xiàn)沒(méi)有權(quán)限創(chuàng)建
ERROR 1142 (42000): CREATE command denied to user 'wmlc_dev'@'localhost' for table 't_sys_user'
-- 在添加兩個(gè)權(quán)限 CREATE：允許創(chuàng)建新的數(shù)據(jù)庫(kù)和表。DROP：允許刪除數(shù)據(jù)庫(kù)和表。（添加權(quán)限需要切換回可以添加權(quán)限的賬號(hào)，這里我就不做過(guò)多介紹了，我是又切換回root賬號(hào)進(jìn)行授權(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賬號(hào)登錄操作就可以了
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é)
有的時(shí)候也可能需要給某些賬號(hào)刪除一些權(quán)限；添加或刪除那些權(quán)限根據(jù)自己的需求來(lái)定。
刪除操作如下：
要?jiǎng)h除MySQL用戶(hù)的某些權(quán)限，您可以使用以下方法：
1.登錄到MySQL服務(wù)器，使用root用戶(hù)身份打開(kāi)命令行終端窗口(有其它授權(quán)賬號(hào)也可以)。
2.確定要?jiǎng)h除權(quán)限的用戶(hù)。如果要?jiǎng)h除多個(gè)用戶(hù)的權(quán)限，請(qǐng)?jiān)诿總€(gè)用戶(hù)名下重復(fù)以下步驟。
3.運(yùn)行以下命令以撤銷(xiāo)用戶(hù)在特定數(shù)據(jù)庫(kù)上的特定權(quán)限
REVOKE <privilege> ON <database_name>.<table_name> FROM '<user_name>@<host>';
其中，<privilege>是要撤銷(xiāo)的權(quán)限，如SELECT、INSERT、UPDATE、DELETE等。
<database_name>是要撤銷(xiāo)權(quán)限的數(shù)據(jù)庫(kù)名稱(chēng)，
<table_name>是要撤銷(xiāo)權(quán)限的表名稱(chēng)，
<user_name>是要?jiǎng)h除權(quán)限的用戶(hù)名，
<host>是用戶(hù)的主機(jī)名。
例如，如果要?jiǎng)h除名為 wmlc_dev 的用戶(hù)在名為 wmlc 的數(shù)據(jù)庫(kù)中所有表的CREATE和DROP 權(quán)限，可以運(yùn)行以下命令：
REVOKE CREATE, DROP ON wmlc.* FROM 'wmlc_dev'@'%';
運(yùn)行以下命令以刷新權(quán)限，以便更改生效：
FLUSH PRIVILEGES;
完成上述步驟后，指定用戶(hù)的特定權(quán)限應(yīng)該已被刪除。
請(qǐng)注意，如果刪除了一個(gè)用戶(hù)在所有數(shù)據(jù)庫(kù)和表上的所有權(quán)限，該用戶(hù)將無(wú)法連接到MySQL服務(wù)器。實(shí)操如下：
-- 我們這里刪除了剛才我們添加兩個(gè)權(quán)限 CREATE：允許創(chuàng)建新的數(shù)據(jù)庫(kù)和表。DROP：允許刪除數(shù)據(jù)庫(kù)和表 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)
補(bǔ)充部分：
-- 假設(shè)新用戶(hù)名為'wmlc_dev'@'%' mysql> SHOW GRANTS FOR 'wmlc_dev'@'%'; -- 添加權(quán)限（創(chuàng)建數(shù)據(jù)庫(kù)/表,刪除數(shù)據(jù)庫(kù)/表,查詢(xún),添加,修改,刪除） mysql> grant CREATE, DROP, select, insert, update, delete on *.* to 'develop'@'%'; -- 撤銷(xiāo)用戶(hù)的所有權(quán)限 mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'wmlc_dev'@'%'; mysql> FLUSH PRIVILEGES; -- 刪除用戶(hù)在授權(quán)表中的記錄（如果直接在授權(quán)表中添加了記錄） mysql> DELETE FROM mysql.user WHERE User='wmlc_dev' AND Host='%'; mysql> FLUSH PRIVILEGES;
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
深入解析MySQL中的Redo Log、Undo Log和Binlog
本文詳細(xì)介紹了MySQL中的RedoLog、UndoLog和Binlog的背景、業(yè)務(wù)場(chǎng)景、功能、底層實(shí)現(xiàn)原理以及使用措施,通過(guò)Java代碼示例展示了如何與這些日志進(jìn)行交互,進(jìn)一步深化了對(duì)MySQL日志系統(tǒng)的理解,理解并合理使用這些日志,可以有效地提升數(shù)據(jù)庫(kù)的性能和可靠性2024-10-10
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.21免安裝版配置教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2020-07-07
在 MySQL 中解決深度分頁(yè)問(wèn)題的核心思路是減少掃描的數(shù)據(jù)量,尤其是避免通過(guò) LIMIT offset, size 導(dǎo)致的大范圍數(shù)據(jù)掃描,以下是三種優(yōu)化方法及其原理、適用場(chǎng)景和注意事項(xiàng),需要的朋友可以參考下2025-02-02
MySQL Administrator 登錄報(bào)錯(cuò)的解決方法
使用MySQL Administrator 登錄，報(bào)錯(cuò)： Either the server service or the configuration file could not be found.Startup variable and service section are there for disabled.2010-12-12
如何使用Maxwell實(shí)時(shí)同步mysql數(shù)據(jù)
這篇文章主要介紹了如何使用Maxwell實(shí)時(shí)同步mysql數(shù)據(jù)，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-04-04
Windows XP系統(tǒng)、xampp集成環(huán)境如何重置mysql密碼，接下來(lái)為大家詳細(xì)介紹下，感興趣的各位可以參考下哈，希望可以幫助到你們2013-03-03
Mysql 導(dǎo)入導(dǎo)出csv 中文亂碼問(wèn)題的解決方法
這篇文章介紹了Mysql 導(dǎo)入導(dǎo)出csv 中文亂碼問(wèn)題的解決方法，有需要的朋友可以參考一下2013-09-09
mysql中xtrabackup全量備份/增量備份及恢復(fù)
本文詳細(xì)介紹了使用XtraBackup工具進(jìn)行MySQL數(shù)據(jù)庫(kù)的全量備份、增量備份以及恢復(fù)的詳細(xì)步驟,文章詳細(xì)列出了所需目錄結(jié)構(gòu)、配置文件和命令,為數(shù)據(jù)庫(kù)管理員提供了一套完整的備份恢復(fù)解決方案2024-09-09
本文詳細(xì)介紹了sql的四種語(yǔ)言，包括數(shù)據(jù)定義語(yǔ)言（DDL）、數(shù)據(jù)操作語(yǔ)言（DML）、數(shù)據(jù)控制語(yǔ)言（DCL）和事物控制語(yǔ)言（TCL）。在這幾種語(yǔ)言中有疑惑的可以來(lái)看看這篇文章。2016-07-07
Spring中的InitializingBean和SmartInitializingSingleton的區(qū)別詳解
這篇文章主要介紹了Spring中的InitializingBean和SmartInitializingSingleton的區(qū)別詳解,InitializingBean只有一個(gè)接口方法afterPropertiesSet(),在BeanFactory初始化完這個(gè)bean,并且把bean的參數(shù)都注入成功后調(diào)用一次afterPropertiesSet()方法,需要的朋友可以參考下2024-01-01