如何管理和授权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的账号，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。