如何管理和授權MYSQL的賬號

本文主要給大家介紹如何管理和授權MYSQL的賬號，文章內容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下如何管理和授權MYSQL的賬號吧。

用戶賬號：'user'@'host'

創建格式：CREATE USER 'username'@'host' [IDENTIFIED BY 'password']

mysql> create user 'test'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User,Host,Password FROM user;

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

| User | Host      | Password                                  |

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

| root | localhost | *9F69E47E519D9CA02116BF5796684F7D0D45F8FA |

| test | %         |                                           |

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

刪除用戶：DROP USER 'username'@'host';

mysql> DROP USER 'test'@'%';

Query OK, 0 rows affected (0.00 sec)

授權權限：GRANT priv_type,... ON [object_type] db_name.tb_name TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

                如果用戶不存在GRANT直接創建

mysql> HELP GRANT;

Name: 'GRANT'

Description:

Syntax:

GRANT

    priv_type [(column_list)]

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

    ON [object_type] priv_level

    TO user_specification [, user_specification] ...

    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]

    [WITH with_option ...]

object_type:

    TABLE

  | FUNCTION

  | PROCEDURE

priv_level:

    *

  | *.* 所有庫的所有表

  | db_name.* 指定庫的所有表

  | db_name.tbl_name 指定庫的指定表

  | tbl_name

  | db_name.routine_name 指定庫的存儲例程

user_specification:

    user [IDENTIFIED BY [PASSWORD] 'password']

ssl_option:

    SSL

  | X509

  | CIPHER 'cipher'

  | ISSUER 'issuer'

  | SUBJECT 'subject'

with_option:

    GRANT OPTION

  | MAX_QUERIES_PER_HOUR count

  | MAX_UPDATES_PER_HOUR count

  | MAX_CONNECTIONS_PER_HOUR count

  | MAX_USER_CONNECTIONS count

查看指定用戶授權：SHOW GRANTS FOR 'user'@'host';

mysql> SHOW GRANTS FOR 'root'@'localhost';

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

| Grants for root@localhost                                                                                                              

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' WITH GRANT OPTION |

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

查看當前用戶授權：SHOW GRANTS FOR CURRENT_USER;

回收授權：REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host';

mysql> HELP REVOKE;

Name: 'REVOKE'

Description:

Syntax:

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] ...

看完以上關于如何管理和授權MYSQL的賬號，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。