如何給MySql創(chuàng)建連接用戶并授權

如何給MySql創(chuàng)建連接用戶并授權

站在用戶的角度思考問題，與客戶深入溝通，找到高碑店網站設計與高碑店網站推廣的解決方案，憑借多年的經驗，讓設計與互聯(lián)網技術結合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：網站制作、成都網站建設、企業(yè)官網、英文網站、手機端網站、網站推廣、國際域名空間、雅安服務器托管、企業(yè)郵箱。業(yè)務覆蓋高碑店地區(qū)。

grant all privileges on *（權限數據庫）.*（權限表） to '（用戶名）'@'（IP）'identified by '（密碼）';

創(chuàng)建用戶并授予某個庫某個表所有權限

mysql中不能設置列的權限

一、用戶管理

1、登錄mysql

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL語句"

比如

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"

-h參數 后面接主機名或者主機IP，hostname為主機，hostIP為主機IP。

-P參數 后面接MySQL服務的端口，通過該參數連接到指定的端口。MySQL服務的默認端口是3306，

不使用該參數時自動連接到3306端口，port為連接的端口號。

-u參數 后面接用戶名，username為用戶名。

-p參數 會提示輸入密碼。

DatabaseName參數 指明登錄到哪一個數據庫中。如果沒有該參數，就會直接登錄到MySQL數據庫

中，然后可以使用USE命令來選擇數據庫。

-e參數 后面可以直接加SQL語句。登錄MySQL服務器以后即可執(zhí)行這個SQL語句，然后退出MySQL

服務器。

2、創(chuàng)建用戶

CREATE USER 用戶名 [IDENTIFIED BY '密碼'][,用戶名 [IDENTIFIED BY '密碼']];

比如

CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';

用戶名參數表示新建用戶的賬戶，由 用戶（User） 和 主機名（Host） 構成；

“[ ]”表示可選，也就是說，可以指定用戶登錄時需要密碼驗證，也可以不指定密碼驗證，這樣用戶

可以直接登錄。不過，不指定密碼的方式不安全，不推薦使用。如果指定密碼值，這里需要使用

IDENTIFIED BY指定明文密碼值。

CREATE USER語句可以同時創(chuàng)建多個用戶。

3、更新用戶

UPDATE mysql.user SET USER='li4' WHERE USER='wang5';

FLUSH PRIVILEGES;

4、刪除用戶

方式1：使用DROP方式刪除（推薦）

使用DROP USER語句來刪除用戶時，必須用于DROP USER權限。DROP USER語句的基本語法形式如下

DROP USER user[,user]…;

比如

DROP USER li4 ; # 默認刪除host為%的用戶

DROP USER 'kangshifu'@'localhost';

方式2：使用DELETE方式刪除

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;

FLUSH PRIVILEGES;

比如

DELETE FROM mysql.user WHERE Host='localhost' AND User='Emily';

FLUSH PRIVILEGES;

注意：不推薦通過 DELETE FROM USER u WHERE USER='li4' 進行刪除，系統(tǒng)會有殘留信息保 留。而drop user命令會刪除用戶以及對應的權限，執(zhí)行命令后你會發(fā)現mysql.user表和mysql.db表 的相應記錄都消失了。

5、 設置當前用戶密碼

舊的寫法

# 修改當前用戶的密碼：（MySQL5.7測試有效）

SET PASSWORD = PASSWORD('123456');

推薦寫法

1. 使用ALTER USER命令來修改當前用戶密碼 用戶可以使用ALTER命令來修改自身密碼，如下語句代表修 改當前登錄用戶的密碼?；菊Z法如下：

ALTER USER USER() IDENTIFIED BY 'new_password';

2. 使用SET語句來修改當前用戶密碼 使用root用戶登錄MySQL后，可以使用SET語句來修改密碼，具體 SQL語句如下： 該語句會自動將密碼加密后再賦給當前用戶。

SET PASSWORD='new_password';

6、修改其他用戶密碼

1. 使用ALTER語句來修改普通用戶的密碼 可以使用ALTER USER語句來修改普通用戶的密碼?；菊Z法形 式如下：

ALTER USER user [IDENTIFIED BY '新密碼']

[,user[IDENTIFIED BY '新密碼']]…;

2. 使用SET命令來修改普通用戶的密碼 使用root用戶登錄到MySQL服務器后，可以使用SET語句來修改普 通用戶的密碼。SET語句的代碼如下：

SET PASSWORD FOR 'username'@'hostname'='new_password';

3. 使用UPDATE語句修改普通用戶的密碼（不推薦）

UPDATE MySQL.user SET authentication_string=PASSWORD("123456")

WHERE User = "username" AND Host = "hostname";

7、 MySQL8密碼管理(了解

7.1、密碼過期策略

在MySQL中，數據庫管理員可以 手動設置 賬號密碼過期，也可以建立一個 自動 密碼過期策略。 過期策略可以是 全局的 ，也可以為 每個賬號 設置單獨的過期策略

ALTER USER user PASSWORD EXPIRE;

比如

ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE;

方式①：使用SQL語句更改該變量的值并持久化

SET PERSIST default_password_lifetime = 180; # 建立全局策略，設置密碼每隔180天過期

方式②：配置文件my.cnf中進行維護

[mysqld]

default_password_lifetime=180 #建立全局策略，設置密碼每隔180天過期

手動設置指定時間過期方式2：單獨設置

每個賬號既可延用全局密碼過期策略，也可單獨設置策略。在 CREATE USER 和 ALTER USER 語句上加 入 PASSWORD EXPIRE 選項可實現單獨設置策略。下面是一些語句示例。

#設置kangshifu賬號密碼每90天過期：

CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

#設置密碼永不過期：

CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;

ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;

#延用全局密碼過期策略：

CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;

ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;

7.2、密碼重置策略

手動設置密碼重用：全局

方式1：使用sql

SET PERSIST password_history = 6; #設置不能選擇最近使用過的6個密碼

SET PERSIST password_reuse_interval = 365; #設置不能選擇最近一年內的密碼

方式2：配置文件

[mysqld]

password_history=6

password_reuse_interval=365

手動設置密碼重用：單獨設置

#不能使用最近5個密碼：

CREATE USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;

ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;

#不能使用最近365天內的密碼：

CREATE USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

#既不能使用最近5個密碼，也不能使用365天內的密碼

CREATE USER 'kangshifu'@'localhost'

PASSWORD HISTORY 5

PASSWORD REUSE INTERVAL 365 DAY;

ALTER USER 'kangshifu'@'localhost'

PASSWORD HISTORY 5

PASSWORD REUSE INTERVAL 365 DAY;

二、權限管理

1、 權限列表

MySQL到底都有哪些權限呢？

mysql show privileges;

（1） CREATE和DROP權限 ，可以創(chuàng)建新的數據庫和表，或刪除（移掉）已有的數據庫和表。如果將 MySQL數據庫中的DROP權限授予某用戶，用戶就可以刪除MySQL訪問權限保存的數據庫。 （2） SELECT、INSERT、UPDATE和DELETE權限 允許在一個數據庫現有的表上實施操作。 （3） SELECT權限 只有在它們真正從一個表中檢索行時才被用到。 （4） INDEX權限 允許創(chuàng)建或刪除索引，INDEX適用于已 有的表。如果具有某個表的CREATE權限，就可以在CREATE TABLE語句中包括索引定義。 （5） ALTER權 限 可以使用ALTER TABLE來更改表的結構和重新命名表。 （6） CREATE ROUTINE權限 用來創(chuàng)建保存的 程序（函數和程序），ALTER ROUTINE權限用來更改和刪除保存的程序， EXECUTE權限 用來執(zhí)行保存的 程序。 （7） GRANT權限 允許授權給其他用戶，可用于數據庫、表和保存的程序。 （8） FILE權限 使用 戶可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE語句讀或寫服務器上的文件，任何被授予FILE權 限的用戶都能讀或寫MySQL服務器上的任何文件（說明用戶可以讀任何數據庫目錄下的文件，因為服務 器可以訪問這些文件）。

2、 授予權限的原則

權限控制主要是出于安全因素，因此需要遵循以下幾個 經驗原則 ：

1、只授予能 滿足需要的最小權限 ，防止用戶干壞事。比如用戶只是需要查詢，那就只給select權限就可 以了，不要給用戶賦予update、insert或者delete權限。

2、創(chuàng)建用戶的時候 限制用戶的登錄主機 ，一般是限制成指定IP或者內網IP段。

3、為每個用戶 設置滿足密碼復雜度的密碼 。

4、 定期清理不需要的用戶 ，回收權限或者刪除用戶。

3、 授予權限

給用戶授權的方式有 2 種，分別是通過把 角色賦予用戶給用戶授權 和 直接給用戶授權 。用戶是數據庫的 使用者，我們可以通過給用戶授予訪問數據庫中資源的權限，來控制使用者對數據庫的訪問，消除安全 隱患。 授權命令：

GRANT 權限1,權限2,…權限n ON 數據庫名稱.表名稱 TO 用戶名@用戶地址 [IDENTIFIED BY ‘密碼口令’];

比如給li4用戶用本地命令行方式，授予atguigudb這個庫下的所有表的插刪改查的權限

GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost ;

比如授予通過網絡方式登錄的joe用戶 ，對所有庫所有表的全部權限，密碼設為123。注意這里唯獨不包

括grant的權限

GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';

我們在開發(fā)應用的時候，經常會遇到一種需求，就是要根據用戶的不同，對數據進行橫向和縱向的 分組。 所謂橫向的分組，就是指用戶可以接觸到的數據的范圍，比如可以看到哪些表的數據；

所謂縱向的分組，就是指用戶對接觸到的數據能訪問到什么程度，比如能看、能改，甚至是 刪除。

4、 查看權限

查看當前用戶權限

SHOW GRANTS;

# 或

SHOW GRANTS FOR CURRENT_USER;

# 或

SHOW GRANTS FOR CURRENT_USER();

查看某用戶的全局權限

SHOW GRANTS FOR 'user'@'主機地址' ;

5、收回權限

收回權限就是取消已經賦予用戶的某些權限。收回用戶不必要的權限可以在一定程度上保證系統(tǒng)的安全 性。MySQL中使用 REVOKE語句 取消用戶的某些權限。使用REVOKE收回權限之后，用戶賬戶的記錄將從 db、host、tables_priv和columns_priv表中刪除，但是用戶賬戶記錄仍然在user表中保存（刪除user表中 的賬戶記錄使用DROP USER語句）。 注意：在將用戶賬戶從user表刪除之前，應該收回相應用戶的所有權限。

REVOKE 權限1,權限2,…權限n ON 數據庫名稱.表名稱 FROM 用戶名@用戶地址;

比如

#收回全庫全表的所有權限

REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';

#收回mysql庫下的所有表的插刪改查權限

REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

注意： 須用戶重新登錄后才能生效

三、權限表

1、user表

user表是MySQL中最重要的一個權限表， 記錄用戶賬號和權限信息 ，有49個字段。如下圖：

這些字段可以分成4類，分別是范圍列（或用戶列）、權限列、安全列和資源控制列

范圍列（或用戶列）

權限列

安全列

安全列只有6個字段，其中兩個是ssl相關的（ssl_type、ssl_cipher），用于 加密 ；兩個是x509 相關的（x509_issuer、x509_subject），用于 標識用戶 ；另外兩個Plugin字段用于 驗證用戶身份 的插件， 該字段不能為空。如果該字段為空，服務器就使用內建授權驗證機制驗證用戶身份。

資源控制列

資源控制列的字段用來 限制用戶使用的資源 ，包含4個字段，分別為： ①max_questions，用戶每小時允許執(zhí)行的查詢操作次數； ②max_updates，用戶每小時允許執(zhí)行的更新 操作次數； ③max_connections，用戶每小時允許執(zhí)行的連接操作次數； ④max_user_connections，用戶 允許同時建立的連接次數。 查看字段：

DESC mysql.user;

查看用戶, 以列的方式顯示數據： SELECT * FROM mysql.user \G;

查詢特定字段：

SELECT host,user,authentication_string,select_priv,insert_priv,drop_priv

FROM mysql.user;

2、db表

使用DESCRIBE查看db表的基本結構： DESCRIBE mysql.db;

1. 用戶列 db表用戶列有3個字段，分別是Host、User、Db。這3個字段分別表示主機名、用戶名和數據庫 名。表示從某個主機連接某個用戶對某個數據庫的操作權限，這3個字段的組合構成了db表的主鍵。

2. 權限列 Create_routine_priv和Alter_routine_priv這兩個字段決定用戶是否具有創(chuàng)建和修改存儲過程的權限。

3、 tables_priv表和columns_priv表

tables_priv表用來 對表設置操作權限 ，columns_priv表用來對表的 某一列設置權限 。tables_priv表和 columns_priv表的結構分別如圖： desc mysql.tables_priv;

tables_priv表有8個字段，分別是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和 Column_priv，各個字段說明如下：

Host 、 Db 、 User 和 Table_name 四個字段分別表示主機名、數據庫名、用戶名和表名。

Grantor表示修改該記錄的用戶。

Timestamp表示修改該記錄的時間。

Table_priv 表示對象的操作權限。包括Select、Insert、Update、Delete、Create、Drop、Grant、

References、Index和Alter。

Column_priv字段表示對表中的列的操作權限，包括Select、Insert、Update和References。

desc mysql.columns_priv;

4、 procs_priv表

procs_priv表可以對 存儲過程和存儲函數設置操作權限 ，表結構如圖： desc mysql.procs_priv;

四、訪問控制

1、連接核實階段

當用戶試圖連接MySQL服務器時，服務器基于用戶的身份以及用戶是否能提供正確的密碼驗證身份來確 定接受或者拒絕連接。即客戶端用戶會在連接請求中提供用戶名、主機地址、用戶密碼，MySQL服務器 接收到用戶請求后，會使用user表中的host、user和authentication_string這3個字段匹配客戶端提供信 息。 服務器只有在user表記錄的Host和User字段匹配客戶端主機名和用戶名，并且提供正確的密碼時才接受 連接。如果連接核實沒有通過，服務器就完全拒絕訪問；否則，服務器接受連接，然后進入階段2等待 用戶請求。

2、 請求核實階段

一旦建立了連接，服務器就進入了訪問控制的階段2，也就是請求核實階段。對此連接上進來的每個請 求，服務器檢查該請求要執(zhí)行什么操作、是否有足夠的權限來執(zhí)行它，這正是需要授權表中的權限列發(fā) 揮作用的地方。這些權限可以來自user、db、table_priv和column_priv表。 確認權限時，MySQL首先 檢查user表 ，如果指定的權限沒有在user表中被授予，那么MySQL就會繼續(xù) 檢 查db表 ，db表是下一安全層級，其中的權限限定于數據庫層級，在該層級的SELECT權限允許用戶查看指 定數據庫的所有表中的數據；如果在該層級沒有找到限定的權限，則MySQL繼續(xù) 檢查tables_priv表 以 及 columns_priv表 ，如果所有權限表都檢查完畢，但還是沒有找到允許的權限操作，MySQL將 返回錯 誤信息 ，用戶請求的操作不能執(zhí)行，操作失敗。

提示： MySQL通過向下層級的順序（從user表到columns_priv表）檢查權限表，但并不是所有的權 限都要執(zhí)行該過程。例如，一個用戶登錄到MySQL服務器之后只執(zhí)行對MySQL的管理操作，此時只 涉及管理權限，因此MySQL只檢查user表。另外，如果請求的權限操作不被允許，MySQL也不會繼 續(xù)檢查下一層級的表。

五、角色管理

1、創(chuàng)建角色

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

角色名稱的命名規(guī)則和用戶名類似。如果 host_name省略，默認為% ， role_name不可省略 ，不可為 空。

練習：我們現在需要創(chuàng)建一個經理的角色，就可以用下面的代碼：

CREATE ROLE 'manager'@'localhost';

2、給角色賦予權限

創(chuàng)建角色之后，默認這個角色是沒有任何權限的，我們需要給角色授權。給角色授權的語法結構是：

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

上述語句中privileges代表權限的名稱，多個權限以逗號隔開?？墒褂肧HOW語句查詢權限名稱，圖11-43 列出了部分權限列表。

SHOW PRIVILEGES\G;

練習1：我們現在想給經理角色授予商品信息表、盤點表和應付賬款表的只讀權限，就可以用下面的代碼 來實現：

GRANT SELECT ON demo.settlement TO 'manager';

GRANT SELECT ON demo.goodsmaster TO 'manager';

GRANT SELECT ON demo.invcount TO 'manager';

3、查看角色權限

賦予角色權限之后，我們可以通過 SHOW GRANTS 語句，來查看權限是否創(chuàng)建成功了：

只要你創(chuàng)建了一個角色，系統(tǒng)就會自動給你一個“ USAGE ”權限，意思是 連接登錄數據庫的權限 。代碼的 最后三行代表了我們給角色“manager”賦予的權限，也就是對商品信息表、盤點表和應付賬款表的只讀權 限。 結果顯示，庫管角色擁有商品信息表的只讀權限和盤點表的增刪改查權限。

4、回收角色權限

角色授權后，可以對角色的權限進行維護，對權限進行添加或撤銷。添加權限使用GRANT語句，與角色 授權相同。撤銷角色或角色權限使用REVOKE語句。 修改了角色的權限，會影響擁有該角色的賬戶的權限。 撤銷角色權限的SQL語法如下：

REVOKE privileges ON tablename FROM 'rolename';

練習：撤銷school_write角色的權限。 （

（1）使用如下語句撤銷school_write角色的權限。

REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';

（2）撤銷后使用SHOW語句查看school_write對應的權限，語句如下。

SHOW GRANTS FOR 'school_write';

5、刪除角色

當我們需要對業(yè)務重新整合的時候，可能就需要對之前創(chuàng)建的角色進行清理，刪除一些不會再使用的角 色。刪除角色的操作很簡單，你只要掌握語法結構就行了。

DROP ROLE role [,role2]...

注意， 如果你刪除了角色，那么用戶也就失去了通過這個角色所獲得的所有權限 。 練習：執(zhí)行如下SQL刪除角色school_read。

DROP ROLE 'school_read';

6、給用戶賦予角色

角色創(chuàng)建并授權后，要賦給用戶并處于 激活狀態(tài) 才能發(fā)揮作用。給用戶添加角色可使用GRANT語句，語 法形式如下：

GRANT role [,role2,...] TO user [,user2,...];

在上述語句中，role代表角色，user代表用戶?？蓪⒍鄠€角色同時賦予多個用戶，用逗號隔開即可。 練習：給kangshifu用戶添加角色school_read權限。 （1）使用GRANT語句給kangshifu添加school_read權 限，SQL語句如下。

GRANT 'school_read' TO 'kangshifu'@'localhost';

（2）添加完成后使用SHOW語句查看是否添加成功，SQL語句如下。

SHOW GRANTS FOR 'kangshifu'@'localhost';

（3）使用kangshifu用戶登錄，然后查詢當前角色，如果角色未激活，結果將顯示NONE。SQL語句如 下。

SELECT CURRENT_ROLE();

7、激活角色

方式1：使用set default role 命令激活角色

SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

使用 SET DEFAULT ROLE 為下面4個用戶默認激活所有已擁有的角色如下：

SET DEFAULT ROLE ALL TO

'dev1'@'localhost',

'read_user1'@'localhost',

'read_user2'@'localhost',

'rw_user1'@'localhost';

方式2：將activate_all_roles_on_login設置為ON

show variables like 'activate_all_roles_on_login';

SET GLOBAL activate_all_roles_on_login=ON;

這條 SQL 語句的意思是，對 所有角色永久激活 。運行這條語句之后，用戶才真正擁有了賦予角色的所有 權限。

8、撤銷用戶角色

REVOKE role FROM user;

練習：撤銷kangshifu用戶的school_read角色。 （1）撤銷的SQL語句如下

REVOKE 'school_read' FROM 'kangshifu'@'localhost';

（2）撤銷后，執(zhí)行如下查詢語句，查看kangshifu用戶的角色信息

SHOW GRANTS FOR 'kangshifu'@'localhost';

9、設置強制角色

設置強制角色(mandatory role)

方式1：服務啟動前設置

[mysqld]

mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'

方式2：運行時設置

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系統(tǒng)重啟后仍然 有效 SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系統(tǒng)重啟后失效

求助 關于mysql 安裝后 初始化授權表的問題

1.生成授權表實際上是完成了mysql的初始化。你在執(zhí)行這個腳本之后，mysql里就會出現名為mysql的庫。mysql庫里有常用的權限表等系統(tǒng)表。比如設置用戶權限的user表。

2.后面的參數--user所指定的用戶是你想要運行mysqld進程用戶名。這個是可變的，不一定非要是mysql。設置這個用戶以后，所有通過mysqld進程創(chuàng)建的文件都會屬于這個用戶。網上的教程里多用mysql用戶是為了在生產環(huán)境中更方便管理。

不知道這樣說你能不能明白。歡迎追問。

網站名稱：mysql中怎么給表授權 mysql的授權語句
當前URL：http://jinyejixie.com/article18/ddcdjdp.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供網站排名、服務器托管、電子商務、關鍵詞優(yōu)化、定制網站、微信小程序

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)