MySQL Account management and authorization

Username: ' User ' @ ' host '

Creation format: 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 |                                           %         | |

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

Delete users: Drop user ' username ' @ ' host ';

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

Query OK, 0 rows Affected (0.00 sec)

Authorization permission: Grant Priv_type,... On [object_type] db_name.tb_name to ' user ' @ ' host ' [identified by ' Password '] [with GRANT OPTION];

If the user does not exist, grant directly creates

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:

*

| * * All tables for all libraries

| db_name.* Specify all tables for the library

| Db_name.tbl_name Specify A table for the specified library

| Tbl_name

| Db_name.routine_name storage routines for the specified library

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

View specified user authorization: Show GRANTS for ' user ' @ ' host ';

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

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

| Grants for [email protected]

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

| GRANT all privileges on * * to ' root ' @ ' localhost ' identified by PASSWORD ' *9F69E47E519D9CA02116BF5796684F7D0D45F8FA ' with GRANT OPTION |

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

View Current user authorizations: show GRANTS for Current_User;

Recycling Authorization: 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 Account management and authorization