MYSQL Tutorial: MySQL User account management _ MySQL

MYSQL Tutorial: MySQL User account management is mainly managed using two SQL commands: grant (authorization) and revoke (withdrawal. These two commands are essentially performed through user (connection and global permissions), db (Database-level permissions), tables_priv (Data Table-level permissions), and columns_priv (data column-level permissions) four permission tables are used to assign permissions. The host permission table is not affected by the two commands. The following describes the details of user permission management.

GRANT syntax description:

GRANT privileges (columns) # privileges indicates the permissions granted, and columns indicates the columns to be applied (optional)
ON what # set the permission level, global level, database level, data table level, and data column level
TO account # Users granted permissions, in the username and host name format of "user_name" @ "host_name"
Identified by 'password' # set the user account password
REQUIRE encryption requirements # set an SSL connection account
WITH grant or resource management options; # set options for account management and resources (number of server connections or queries)
Example:

Mysql> grant all on db. * to 'test' @ 'localhost' identified by 'test ';
After running the preceding example, the test user can only access the database from the local machine using the 'test' password.

Mysql> grant all on db. * to 'test' @ '%' identified by 'test ';
After running the preceding example, you can use the 'test' password to access the database on any computer. '%' Represents any character, '_' represents any character. The host name can also be an IP address.

If no host part is specified, the default value is any host, that is, 'test' and 'test' @ '%' are equivalent.

Table 4.1. access permission Table

Permission Description
CREATE TEMPORARY TABLES
EXECUTE the stored procedure (not supported currently)
FILE operating system FILE
Grant option: GRANT this account permissions to other users.
Lock tables lock the specified data table
PROCESS to view information about running threads
RELOAD: RELOAD the permission table or refresh logs and buffers.
Replication client can query master/slave server host names
Replication slave runs an image SLAVE server
Show databases can run the show databases command
SHUTDOWN the database server
SUPER can kill the thread and perform superuser operations.

ALTER can modify the table and index structure
CREATE a database and a data table
DELETE data rows in a data table
DROP delete data tables and data rows
Create or delete an INDEX
INSERT data rows
REFERENCES (not supported currently)
SELECT to query data rows
UPDATE data rows

ALL permissions, but not GRANT.
USAGE has no permission

Table 4.2. permission scope (set by the ON clause)

Permitted range
ON *. * Global permissions apply to all databases
ON * Global permissions. If no default database is specified, all databases are used. Otherwise, the default database applies.
ON db_name. * Database-level permissions apply to all data tables in the specified database
ON db_name.tbl_name: Data Table-level permission, acting ON all data columns in the data table
ON tbl_name Data Table-level permissions apply to all data columns in the specified data table in the default database

USAGE permission USAGE: modifies account items unrelated to permissions, for example:

Mysql> grant usage on *. * TO account identified by 'new _ password'; # change the password
Mysql> grant usage on *. * TO account require ssl; # enable SSL connection
Mysql> grant usage on *. * TO account WITH MAX_CONNECTIONS_PER_HOUR 10; # set Resources
Users WITH the with grant option permission can GRANT their own permissions to other users, such:

Mysql> grant all on db. * TO 'test' @ '%' identified by 'password' with grant option;
In this way, the test user has the right to grant this permission to other users.
Restrict resource usage, for example:

Mysql> grant all on db. * TO account identified by 'password' WITH MAX_CONNECTIONS_PER_HOUR 10 MAX_QUERIES_PER_HOUR 200 MAX_UPDATES_PER_HOUR 50;
Allow the account user to connect to the server up to 20 times per hour, and issue a maximum of 200 query commands per hour (the maximum number of update commands is 50)
The default value is zero, that is, there is no limit. FLUSH USER_RESOURCES and flush privileges can be used to clear resource limit counters.

REVOKE syntax description:

Mysql> REVOKE privileges (columns) ON what FROM account;
Example:

Mysql> revoke select on db. * FROM 'test' @ 'localhost ';
Delete the permission for the test account to query the database from the local machine
REVOKE can delete permissions, but cannot delete accounts, even if the account has no permissions. Therefore, the user data table contains records of this account. to completely DELETE the account, you must use the DELETE command to DELETE the records of the user data table, such:

% Mysql-u root-p
Mysql> use mysql
Mysql> delete from user where User = 'test' and Host = 'localhost ';
Mysql fulsh privileges;
REVOKE cannot delete the REQUIRE and resource usage configurations. They use GRANT to delete objects, for example:

Grant usage on *. * TO account require none; # Delete the SSL connection option of the account
Grant usage on *. * TO account WITH MAX_CONNECTIONS_PER_HOUR 0 MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0; # Delete the resource limit of the account