Implementation of MySQL GRANT user authorization

Authorization is to grant certain permissions to a user. For example, you can grant a newly created user the permission to query all databases and tables. MySQL provides the GRANT statement to set permissions for users.

In MySQL, only users with the GRANT privilege can execute the GRANT statement. The syntax format is as follows:

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]

in:

• The priv_type parameter indicates the permission type;
• The columns_list parameter indicates which columns the permission applies to. If this parameter is omitted, it applies to the entire table.
• database.table is used to specify the level of permissions;
• The user parameter represents a user account, which consists of a user name and a host name in the format of "'username'@'hostname'";
• The IDENTIFIED BY parameter is used to set a password for the user;
• The password parameter is the user's new password.

The WITH keyword is followed by one or more with_option arguments. This parameter has 5 options, which are described in detail as follows:

• GRANT OPTION: The authorized user can grant these permissions to other users;
• MAX_QUERIES_PER_HOUR count: Set the number of queries that can be executed every hour;
• MAX_UPDATES_PER_HOUR count: Sets the number of updates that can be performed every hour.
• MAX_CONNECTIONS_PER_HOUR count: Set the number of connections that can be established per hour;
• MAX_USER_CONNECTIONS count: Sets the number of connections a single user can have simultaneously.

The following groups of permissions can be granted in MySQL:

• Column permissions are related to a specific column in a table. For example, you can use the UPDATE statement to update the value of the name column in the students table.
• Table permissions are related to all data in a specific table. For example, you can use the SELECT statement to query the permissions of all data in the students table.
• Database permissions are related to all tables in a specific database. For example, you can grant permission to create a new table in the existing database mytest.
• User permissions are related to all databases in MySQL. For example, you can delete an existing database or create a new database.

Correspondingly, the values ​​that can be used to specify permission levels in the GRANT statement have the following formats:

• *: Indicates all tables in the current database.
• *.*: indicates all tables in all databases.
• db_name.*: represents all tables in a database, db_name specifies the database name.
• db_name.tbl_name: represents a table or view in a database. db_name specifies the database name, and tbl_name specifies the table name or view name.
• db_name.routine_name: represents a stored procedure or function in a database. routine_name specifies the name of the stored procedure or function.
• TO clause: If the privilege is granted to a non-existent user, MySQL will automatically execute a CREATE USER statement to create the user, but a password must be set for the user at the same time.

Permission Type Description

The following explains the types of permissions in the GRANT statement (please refer to the section "Detailed Explanation of MySQL User Table" for more information).

1) When granting database permissions, <permission type> can be specified as the following values:

2) When granting table permissions, <permission type> can be specified as the following values:

3) When granting column permissions, the value of <permission type> can only be specified as SELECT, INSERT, and UPDATE, and the column name list column-list must be added after the permission.

4) The most efficient permissions are user permissions.
When granting user permissions, the <permission type> can be any of the following values ​​in addition to all the values ​​specified when granting database permissions:

• CREATE USER: Grants the user the ability to create and delete new users.
• SHOW DATABASES: Grants the user the permission to use the SHOW DATABASES statement to view the definitions of all existing databases.

Example 1

Use the GRANT statement to create a new user testUser with a password of testPwd. User testUser has query and insert permissions for all data and is granted GRANT permissions. The SQL statement and execution process are as follows.

mysql> GRANT SELECT,INSERT ON *.*
    -> TO 'testUser'@'localhost'
    -> IDENTIFIED BY 'testPwd'
    -> WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.05 sec)

Use the SHOW GRANTS statement to query the permissions of user testUser, as shown below.

mysql> SHOW GRANTS FOR 'testUser'@'localhost';
+-------------------------------------------------------------------------+
| Grants for testUser@localhost |
+-------------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'testUser'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

The results show that testUser has query and insert permissions on all tables in all databases and can grant these permissions to other users.

expand

Database administrators must be particularly careful when authorizing ordinary users. Improper authorization may cause fatal damage to the database. Once you discover that a user has too many permissions, you should use the REVOKE statement to revoke the permissions as soon as possible. Please note that it is best not to grant SUPER and GRANT permissions to ordinary users. For more information about deleting user privileges, see MySQL REVOKE: Deleting User Privileges.

This is the end of this article about the implementation of MySQL GRANT user authorization. For more relevant MySQL GRANT user authorization content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!