10.1 使用語法:

通過在 mysql 中輸入 help grant 得到如下幫助信息

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

GRANT SELECT ON db2.invoice TO'jeffrey'@'localhost';

GRANT USAGE ON*.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;?

10.2 第一種創建用戶及授權方法：

創建用戶

mysql> create user oldboy@'localhost' identified by 'oldboy';

Query OK, 0 rows affected (0.00 sec)?

查看用戶其權限

mysql> show grants for oldboy@'localhost';+---------------------------------------------------------------------------------------------------------------+

| Grants foroldboy@localhost|

+---------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'oldboy'@'localhost'IDENTIFIED BY PASSWORD'*7495041D24E489A0096DCFA036B166446FDDD992' |

+---------------------------------------------------------------------------------------------------------------+

1 row in set (0.00sec)?

授權用戶權限

mysql> grant all on oldboy_gbk.* to oldboy@'localhost';

Query OK, 0 rows affected (0.04sec)

mysql> show grants for oldboy@'localhost';+---------------------------------------------------------------------------------------------------------------+

| Grants foroldboy@localhost|

+---------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'oldboy'@'localhost'IDENTIFIED BY PASSWORD'*7495041D24E489A0096DCFA036B166446FDDD992' |

| GRANT ALL PRIVILEGES ON `oldboy_gbk`.* TO 'oldboy'@'localhost'

|

+---------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)?

10.3 第二種創建用戶及授權方法：

mysql> grant all on oldboy_gbk.* to oldgirl@'localhost' identified by 'oldgirl';

Query OK, 0 rows affected (0.00sec)

列表說明:

grant all on dbname.*to username@’lcoalhost’ identified by ‘password’

授 權命令對應權限

目標：庫和表 用戶名和客戶端主機 用戶密碼

mysql> show grants for oldgirl@'localhost';+----------------------------------------------------------------------------------------------------------------+

| Grants foroldgirl@localhost|

+----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'oldgirl'@'localhost'IDENTIFIED BY PASSWORD'*4FD27385BB43242FE02158144D4C211F75A03F76' |

| GRANT ALL PRIVILEGES ON `oldboy_gbk`.* TO 'oldgirl'@'localhost'

|

+----------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)?

10.4 創建用戶及授權哪個網段的主機可以連接 oldboy_gbk 庫

提示：如果是 web 連接數據庫的用戶，盡量不要授權 all，而是 select，insert，update，delete

10.4.1 第一種方法：

mysql> grant all on oldboy_gbk.* to oldgirl@'172.16.1.%' identified by 'oldgirl';

Query OK, 0 rows affected (0.00sec)%表示 172.16.1.1-255 網段?

10.4.2 第二種方法：

mysql> grant all on oldboy_gbk.* to oldgirl@'172.16.1.0/255.255.255.0' identified by 'oldgirl';

Query OK, 0 rows affected (0.00sec)

提示:不能這樣寫 oldgirl@’172.16.1.0/24’?

10.5 關于 mysql 回收某個用戶權限

語法格式：

REVOKE

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION

FROM user [, user] ...?

實例:查看 oldboy 用戶回收權限前的權限

mysql> show grants for oldboy@'localhost';+---------------------------------------------------------------------------------------------------------------+

| Grants foroldboy@localhost|

+---------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'oldboy'@'localhost'IDENTIFIED BY PASSWORD'*7495041D24E489A0096DCFA036B166446FDDD992' |

| GRANT ALL PRIVILEGES ON `oldboy_gbk`.* TO 'oldboy'@'localhost'

|

+---------------------------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)?

查看回收 oldboy 用戶的 insert 權限之后的權限

mysql> REVOKE INSERT ON oldboy_gbk.* FROM 'oldboy'@'localhost';

Query OK, 0 rows affected (0.00sec)

mysql>flush privileges;

Query OK, 0 rows affected (0.00sec)

mysql> show grants for 'oldboy'@'localhost';+---------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------+

| Grants foroldboy@localhost|

+---------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'oldboy'@'localhost'IDENTIFIED BY PASSWORD'*7495041D24E489A0096DCFA036B166446FDDD992'

|

|GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER,

CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,

CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `oldboy_gbk`.*TO'oldboy'@'localhost' |

+---------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)?

10.6 企業生產環境如何授權用戶權限(mysql 主庫)

博客，CMS 等產品的數據庫授權：

對于 web 連接用戶授權盡量采用最小化原則，很多開源軟件都是 web 界面安裝，因此，在安裝期間除了 select，insert，update，delete4 個權限外，還需要 create，drop 等比較危險的權限

mysql> grant insert,delete,update,select on blog.* to blog@'172.16.1.%' identified by 'blog';

Query OK, 0 rows affected (0.00sec)

mysql> show grants for blog@'172.16.1.%';+--------------------------------------------------------------------------------------------------------------+

| Grants for blog@172.16.1.%

|

+--------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'blog'@'172.16.1.%'IDENTIFIED BY PASSWORD'*A5BA49C964C6DB89302E2EA293048E9224B33F34' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'172.16.1.%'

|

+--------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)?

常規情況下授權 select，insert，update，delete4 個權限即可，有的開源軟件，例如 discuz bbs，

還需要 create，drop 等比較危險的權限，生成數據庫表后，要收回 create、drop 權限

mysql> revoke drop,create on blog.* from blog@'172.16.1.%';

Query OK, 0 rows affected (0.00sec)

to your MySQL server versionfor the right syntax to use near 'from blog@'172.16.1.%'' at line 1mysql> show grants for blog@'172.16.1.%';+--------------------------------------------------------------------------------------------------------------+

| Grants for blog@172.16.1.%

|

+--------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'blog'@'172.16.1.%'IDENTIFIED BY PASSWORD'*A5BA49C964C6DB89302E2EA293048E9224B33F34' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'172.16.1.%'

|

+--------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)?

總結

以上是生活随笔為你收集整理的mysql 创建用户权限_10.创建 MySQL 用户及赋予用户权限的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。