涉及MySQL用戶及權(quán)限管理的簡單介紹

發(fā)布時間：2020-05-25 16:37:20 來源：網(wǎng)絡(luò) 閱讀：223 作者：三月欄目：MySQL數(shù)據(jù)庫

本文主要給大家介紹MySQL用戶及權(quán)限管理，文章內(nèi)容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下MySQL用戶及權(quán)限管理吧。

MySQL用戶及權(quán)限管理

MySQL 默認(rèn)管理員用戶為root，但是這個用戶權(quán)限太大，一般只在管理數(shù)據(jù)庫時候才用。為了MySQL的安全性，在實(shí)際應(yīng)用中用戶權(quán)限要最小化，應(yīng)該只分配使用到的權(quán)限。

1 mysql授權(quán)表

MySQL云服務(wù)器通過MySQL權(quán)限表來控制用戶對數(shù)據(jù)庫的訪問，MySQL權(quán)限表存放在mysql數(shù)據(jù)庫里，由mysql_install_db腳本初始化。這些MySQL權(quán)限表分別user，db，table_priv，columns_priv和host。下面分別介紹一下這些表的結(jié)構(gòu)和內(nèi)容：

user權(quán)限表：記錄允許連接到云服務(wù)器的用戶帳號信息，里面的權(quán)限是全局級的。
db權(quán)限表：記錄各個帳號在各個數(shù)據(jù)庫上的操作權(quán)限。
table_priv權(quán)限表：記錄數(shù)據(jù)表級的操作權(quán)限。在這里指定的一個權(quán)限適用于一個表的所有列。
columns_priv權(quán)限表：記錄數(shù)據(jù)列級的操作權(quán)限；這里指定的權(quán)限適用于一個表的特定列；
host權(quán)限表：配合db權(quán)限表對給定主機(jī)上數(shù)據(jù)庫級操作權(quán)限作更細(xì)致的控制。這個權(quán)限表不受GRANT和REVOKE語句的影響。

2 用戶及密碼管理

2.1 創(chuàng)建用戶

命令:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

新用戶創(chuàng)建完成暫時無法登陸，因?yàn)槲覀冞€沒有為這個用戶分配相應(yīng)權(quán)限，

說明：

username：你將創(chuàng)建的用戶名
host：指定該用戶在哪些主機(jī)上可以登陸，本地用戶用localhost，如果想讓該用戶可以從任意遠(yuǎn)程主機(jī)登陸，可以使用通配符%
password：該用戶的登陸密碼，密碼可以為空，如果為空則該用戶可以不需要密碼登陸云服務(wù)器
不管是授權(quán)，還是撤銷授權(quán)，都要指定響應(yīng)的host（即 @ 符號后面的內(nèi)容），因?yàn)橐陨霞案衩顚?shí)際上都是在操作mysql 數(shù)據(jù)庫中的user表;

例：

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101' IDENDIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

2.2 密碼設(shè)置

設(shè)置root密碼：

設(shè)置密碼：password1

mysqladmin -uroot -password password1

修改root密碼：

由password1修改為password2

mysqladmin -uroot -p password1 password password2

管理員設(shè)置其它用戶密碼

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

設(shè)置當(dāng)前登陸用戶（自己）密碼:

SET PASSWORD = PASSWORD("newpassword");

2.3 查看當(dāng)前用戶

SELECT USER();

2.4 刪除用戶

DROP USER 'username'@'host';

2.5 查看mysql所有用戶

查看mysql的所有用戶及權(quán)限、加密密碼、最后修改密碼時間；

MYSQL-5.6

select host,user,password from mysql.user;

MYSQL-5.7

SELECT user,host,authentication_string,password_last_changed FROM mysql.user;

2.6 數(shù)據(jù)表結(jié)構(gòu):

DESCRIBE table1;

3 權(quán)限

3.1 授權(quán)GRANT

GRANT 語法:

GRANT privileges (columns)
ON what
TO user IDENTIFIED BY "password"
WITH GRANT OPTION

權(quán)限列表:

CREATE：創(chuàng)建數(shù)據(jù)庫和表。
INSERT：插入；向表中插入新行。
DELETE：刪除表中已有的記錄。
DROP：拋棄(刪除)數(shù)據(jù)庫和表。
ALTER：修改表和索引。
INDEX：創(chuàng)建或拋棄索引。
REFERENCE：未用。
SELECT：檢索表中的記錄。
UPDATE：修改現(xiàn)存表記錄。
FILE：讀或?qū)懺品?wù)器上的文件。
PROCESS：查看云服務(wù)器中執(zhí)行的線程信息或殺死線程。
RELOAD：重載授權(quán)表或清空日志、主機(jī)緩存或表緩存。
SHUTDOWN：關(guān)閉云服務(wù)器。
ALL：所有權(quán)限，ALL PRIVILEGES同義詞。
USAGE：特殊的 "無權(quán)限" 權(quán)限。
用戶賬戶包括 "username" 和 "host" 兩部分，后者表示該用戶被允許從何地接入。tom@'%' 表示任何地址，默認(rèn)可以省略。還可以是 "tom@192.168.1.%"、"tom@%.abc.com" 等。數(shù)據(jù)庫格式為 db@table，可以是 "test." 或 ".*"，前者表示 test 數(shù)據(jù)庫的所有表，后者表示所有數(shù)據(jù)庫的所有表。
子句 WITH GRANT OPTION表示該用戶可以為其他用戶分配權(quán)限。
grant, revoke 用戶權(quán)限后，該用戶只有重新連接 MySQL 數(shù)據(jù)庫，權(quán)限才能生效。

只分配權(quán)限：和CREATE USER一起使用；

GRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host';

說明:

all：要授予用戶的操作權(quán)限，如SELECT，INSERT，UPDATE等，如果要授予所的權(quán)限則使用ALL；
databasename：數(shù)據(jù)庫名；
tablename：表名，如果要授予該用戶對所有數(shù)據(jù)庫和表的相應(yīng)操作權(quán)限則可用表示，如.*
username：用戶；
host：IP范圍；

新建賬戶并分配權(quán)限：grant

GRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host' IDENTIFIED BY 'password';

說明：

其它同上；
password：密碼；

例子:

GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%';
GRANT SELECT,INSERT ON test.user TO 'pig'@'%';

新建賬戶并授予用戶管理其它用戶的權(quán)限

GRANT PRIVILEGES ON databasename.tablename TO 'username'@'localhost' WITH GRANT OPTION;

說明：

其它同上；
WITH GRANT OPTION：授予用戶管理其它用戶的權(quán)限；
WITH GRANT OPTION 一般配置的用戶的host配localhost，即只能本地登陸；

常用操作

grant 查詢、插入、更新、刪除MySQL 數(shù)據(jù)表結(jié)構(gòu)權(quán)限。
```
grant select,insert,update,delete on databasename.tablename to 'username'@'host';
```
grant 創(chuàng)建,卸載MySQL 數(shù)據(jù)表的權(quán)限。
```
grant create,drop on databasename.tablename to 'username'@'host';
```

grant 作用在表中的列上：

grant select(id, se, rank) on databasename.tablename to 'username'@'host';

grant 操作 MySQL 外鍵權(quán)限。

grant references on databasename.tablename to 'username'@'host';

grant 操作 MySQL 臨時表權(quán)限。

grant create temporary tables on databasename.tablename to 'username'@'host';

grant 操作 MySQL 索引權(quán)限。

grant index on databasename.tablename to 'username'@'host';

grant 操作 MySQL 視圖、查看視圖源代碼權(quán)限。

grant create view on databasename.tablename to 'username'@'host';
grant show view on databasename.tablename to 'username'@'host';

grant 操作 MySQL 存儲過程、函數(shù) 權(quán)限。

grant create routine on databasename.tablename to 'username'@'host';
grant alter routine on databasename.tablename to 'username'@'host';
grant execute on databasename.tablename to 'username'@'host';
grant execute on procedure testdb.pr_add to 'dba'@'localhost';
grant execute on function testdb.fn_add to 'dba'@'localhost';

grant 普通 DBA 管理某個 MySQL 數(shù)據(jù)庫的權(quán)限。
```
grant all privileges on databasename.* to dba@'localhost';
```
grant 高級 DBA 管理 MySQL 中所有數(shù)據(jù)庫的權(quán)限。
```
grant all on *.* to dba@'localhost'
```
關(guān)鍵字 privileges 如果省略的話，后面需要刷新權(quán)限。

3.2 刷新權(quán)限

修改完權(quán)限以后一定要刷新服務(wù)，或者重啟服務(wù)：

FLUSH PRIVILEGES;

3.3 查看用戶權(quán)限

SHOW GRANTS [FOR 'username'@'host'];

默認(rèn)查看自己的權(quán)限，加for查看指定用戶的權(quán)限；

3.4 撤銷權(quán)限：revoke

如果此時發(fā)現(xiàn)剛剛給的權(quán)限太大了，如果我們只是想授予它在某個數(shù)據(jù)庫上的權(quán)限，那么需要切換到root 用戶撤銷剛才的權(quán)限，重新授權(quán)：

REVOKE ALL PRIVILEGES ON databasename.tablename FROM 'username'@'host';

說明:

privilege, databasename, tablename：同授權(quán)部分

注意:
假如你在給用戶'pig'@'%'授權(quán)的時候是這樣的（或類似的）：GRANT SELECT ON test.user TO 'pig'@'%'，則在使用REVOKE SELECT ON . FROM 'pig'@'%';命令并不能撤銷該用戶對test數(shù)據(jù)庫中user表的SELECT 操作。
相反，如果授權(quán)使用的是GRANT SELECT ON . TO 'pig'@'%';則REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤銷該用戶對test數(shù)據(jù)庫中user表的Select權(quán)限。

具體信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看。

4 摘要

4.1 grant和revoke可以在幾個層次上控制訪問權(quán)限

整個云服務(wù)器，使用 grant ALL 和revoke ALL
整個數(shù)據(jù)庫，使用on database.*
特定表，使用on database.table
特定的列
特定的存儲過程

4.2 user表中host列的值的意義

%：匹配所有主機(jī)
localhost： localhost不會被解析成IP地址，直接通過UNIXsocket連接
127.0.0.1：會通過TCP/IP協(xié)議連接，并且只能在本機(jī)訪問；
::1 ::1：就是兼容支持ipv6的，表示同ipv4的127.0.0.1

4.3 也可以通過安全策略刪除無用的用戶

推薦剛裝完mysqld服務(wù)時在shell運(yùn)行mysql_secure_installation命令

mysql_secure_installation

看完以上關(guān)于MySQL用戶及權(quán)限管理，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業(yè)知識信息，可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。

向AI問一下細(xì)節(jié)