Mysql User Authorization (GRANT) Syntax and Example Interpretation

Mysql User Authorization (GRANT) Syntax

After successfully creating a user account, no action can be performed and the user needs to be assigned appropriate access rights. You can use the SHOW GRANT FOR statement to query the user's permissions.

Note: The newly created user only has permission to log in to the MySQL server, and does not have any other permissions, so no other operations can be performed.

USAGE ON*.* means that the user has no permissions to any database or any table.

Grant user permission syntax

For newly created MySQL users, they must be authorized, and the GRANT statement can be used to implement authorization of the newly created user.

Syntax format:

GRANT
&lt;Permission Type&gt; [ ( &lt;List name&gt; ) ] [ , &lt;Permission Type&gt; [ ( &lt;List name&gt; ) ] ]
ON &lt;Object&gt; &lt;Permission level&gt; TO &lt;user&gt;
in&lt;user&gt;Format：
&lt;user名&gt; [ IDENTIFIED ] BY [ PASSWORD ] &lt;Password&gt;
[ WITH GRANT OPTION]
| MAX_QUERIES_PER_HOUR &lt;frequency&gt;
| MAX_UPDATES_PER_HOUR &lt;frequency&gt;
| MAX_CONNECTIONS_PER_HOUR &lt;frequency&gt;
| MAX_USER_CONNECTIONS &lt;frequency&gt;

The syntax description is as follows:

(1) <Column Name>

Optional. Used to specify which specific columns to grant permissions to.

(2) ON clause

Used to specify the object and level of permission grant, such as giving the database or table name to grant permission after the ON keyword.

(3) <Permission Level>

The level used to specify permissions. The permissions that can be granted are as follows:

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

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

1-: Represents all tables in the current database.
2-.: Represents all tables in all databases.
3-db_name.: Represents all tables in a database, db_name specifies the database name.
4-db_name.tbl_name: represents a table or view in a database. db_name specifies the database name, and tbl_name specifies the table or view name.
5-tbl_name: represents a table or view. tbl_name specifies the table name or view name.
6-db_name.routine_name: represents a stored procedure or function in a database. routine_name specifies the stored procedure name or function name.
7-TO clause: Used to set user passwords and specify user user assigned permissions. If a password is specified for a user present in the system in the TO clause, the new password will overwrite the original password; if the permission is granted to a non-existent user, MySQL will automatically execute a CREATE USER statement to create the user, but the password must be specified for the user.

Use of <Permission Type> in GRANT statement

The description is as follows:

(1) When granting database permissions, <Permission Type> can be specified as the following values:

SELECT: means granting permissions to users to access all tables and views in a specific database using the SELECT statement.
INSERT: means that the user can use the INSERT statement to add data rows to all tables in a specific database.
DELETE: means granting permissions to use the DELETE statement to delete data rows of all tables in a specific database.
UPDATE: means granting permissions to users that can use the UPDATE statement to update the values of all data tables in a specific database.
REFERENCES: means granting permissions to users that can create foreign keys to specific databases.
CREATE: Indicates the permissions that authorized users can use the CREATE TABLE statement to create new tables in a specific database.
ALTER: means that the user can use the ALTER TABLE statement to modify all data tables in a specific database.
SHOW VIEW: means granting permissions to view the view definitions of existing views in a specific database.
CREATE ROUTINE: means granting permissions to users that can create stored procedures and stored functions for specific databases.
ALTER ROUTINE: means granting permissions to users that can update and delete existing stored procedures and stored functions in the database.
INDEX: means granting permissions to users that can define and delete indexes on all data tables in a specific database.
DROP: means granting permissions to the user that can delete all tables and views in a specific database.
CREATE TEMPORARY TABLES: means granting permissions to users that can create temporary tables in a specific database.
CREATE VIEW: means granting permissions to users to create new views in a specific database.
EXECUTE ROUTINE: means granting permissions to users to call stored procedures and stored functions that can be called for a specific database.
LOCK TABLES: means granting permissions to lock existing data tables for a specific database.
ALLorALL PRIVILEGES: Indicates all the above permissions.

(2) When granting table permissions, <Permission Type> can be specified as the following values:

SELECT: Grants permissions to use SELECT statements to access specific tables.
INSERT: Grants permission to use the INSERT statement to add data rows to a specific table.
DELETE: Grants permission to use the DELETE statement to delete data rows from a specific table.
DROP: Grant the user permission to delete the data table.
UPDATE: Grants permissions to users that can use the UPDATE statement to update specific data tables.
ALTER: Grants permissions to use the ALTER TABLE statement to modify the data table.
REFERENCES: Grants permissions to the user that can create a foreign key to refer to a specific data table.
CREATE: Grants permissions to create a data table with a specific name.
INDEX: Grants permissions to users that can define indexes on tables.
ALLorALL PRIVILEGES: All permission names.

(3) When granting column permissions, the value of <Permission Type> can only be specified as SELECT, INSERT and UPDATE. At the same time, the column name list needs to be added to the permissions.

(4) The most efficient permission is user permission.

When granting user permissions, <Permission Type> can be specified as all values when granting database permissions, and can also be the following values:

1-CREATE USER: means granting permissions to create and delete new users.
2-SHOW DATABASES: means that the user can use the SHOW DATABASES statement to view the definitions of all existing databases.

[Example] Use the GRANT statement to create a new user testUser with the password testPwd. User testUser has query and insert permissions for all data, and grants GRANT permissions. The entered SQL statement and execution process are shown below.

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 SELECT statement to query the user's permissions for testUser as shown below.

mysql> SELECT Host,User,Select_priv,Grant_priv
    -> FROM 
    -> WHERE User='testUser';
+-----------+----------+-------------+------------+
| Host      | User     | Select_priv | Grant_priv |
+-----------+----------+-------------+------------+
| localhost | testUser | Y           | Y          |
+-----------+----------+-------------+------------+
1 row in set (0.01 sec)

The role of WITH GRANT OPTION

Add user statement to the database:

grant all privileges on testdb.* to ‘test_user'@'localhost' identified by “jack” with grant option;

WITH GRANT OPTION This option means that the user can authorize the permissions he has to others.

Note: People often do not specify the WITH GRANT OPTION option when creating an operation user, so that the user cannot use the GRANT command to create a user or authorize other users later.

If you don't want this user to have grant permission, you can do not add this sentence

Use SELECT statement to query permissions for all users

As shown below:

MySQL [mysql]>  SELECT Host,Db,User,Select_priv,Grant_priv FROM  ; 
+--------------+--------------------+---------------+-------------+------------+
| Host         | Db                 | User          | Select_priv | Grant_priv |
+--------------+--------------------+---------------+-------------+------------+
| localhost    | performance_schema |  | Y           | N          |
| localhost    | sys                |      | N           | N          |
| %            | cloud_manager_v2   | feeduser      | Y           | N          |
| %            | xiaojin_airflow    | rw_airflow    | Y           | N          |
| 10.20.250.12 | xiaojin_airflow    | rw_airflow    | Y           | N          |
| 10.20.250.13 | xiaojin_airflow    | rw_airflow    | Y           | N          |
| 10.20.250.12 | data_center        | rw_airflow    | Y           | N          |
| 10.20.250.13 | data_center        | rw_airflow    | Y           | N          |
+--------------+--------------------+---------------+-------------+------------+
8 rows in set (0.00 sec)
 
MySQL [mysql]>  SELECT Host,User,Select_priv,Grant_priv FROM  ; 
+--------------+---------------+-------------+------------+
| Host         | User          | Select_priv | Grant_priv |
+--------------+---------------+-------------+------------+
| localhost    | root          | Y           | Y          |
| localhost    |  | N           | N          |
| localhost    |      | N           | N          |
| %            | root          | Y           | Y          |
| %            | feeduser      | N           | N          |
| %            | rw_airflow    | N           | N          |
| 10.20.250.12 | rw_airflow    | N           | N          |
| 10.20.250.13 | rw_airflow    | N           | N          |
+--------------+---------------+-------------+------------+
8 rows in set (0.00 sec)

MySQL authorization GRANT ALL PRIVILEGES three examples

Table modification method

It may be that your account is not allowed to log in from remotely, and can only be found in localhost. At this time, just log in to mysql on the computer in localhost, change the "host" item in the "user" table in the "mysql" database, and change it from "localhost" to "%"

mysql -u root -p vmware
mysql>use mysql;  
mysql>update user set host = '%' where user = 'root';  
mysql>select host, user from user;

Authorization Law

For example, if you want myuser to use mypassword to connect to mysql server from any host.

1-If you want to allow user myuser to connect to mysql server from a host with ip as 192.168.1.6 and use mypassword as password

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY  'mypassword' WITH GRANT OPTION; 
FLUSH   PRIVILEGES;

2-If you want to allow user myuser to connect to the dk database of mysql server from a host with ip as 192.168.1.6 and use mypassword as password

GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;   
FLUSH   PRIVILEGES;

Note that FLUSH PRIVILEGES must be obtained after authorization; otherwise it will not take effect immediately.

Another way

Run on the machine where mysql is installed:

1）、d:\mysql\bin\&gt;mysql -h localhost -u root 
//This should be able to enter the MySQL server2）、mysql&gt;GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION 
// Give any host permission to access data3）、mysql&gt;FLUSH PRIVILEGES 
//The modification takes effect4）、mysql&gt;EXIT 
//quitMySQLserver

This way you can log in as root on any other host!

other

mysql> grant all privileges on *.* to 'energy_pf'@'192.168.2.65' identified by 'energy_pf' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> plush privileges;

Allows user energy_pf to connect to any database (.) of mysql server from a host with ip of 192.168.2.65 and uses energy_pf as password

MySQL adds, deletes, modify and check reports (INSERT, DROP, UPDATE, SELECT, CREATE) command denied to user ‘xx’@’localhost’ for table ‘test’

View user permissions

show grants;

result

+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.01 sec)
————————————————

Create mysql user

mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Command description:

(1) username: the newly created username, login name used to link the database

(2) host: Specify the host on which the user can log in. If it is a local user, localhost can be used. If you want the user to log in from any remote host, you can use the wildcard %

(3) password: The user's login password can be empty. If it is empty, the user can log in to the server without a password.

Here are some common creation examples:

mysql&gt; CREATE USER 'mumu'@'localhost' IDENTIFIED BY '123456';  # Only local loginmysql&gt; CREATE USER 'thomas'@'192.168.1.73' IDENDIFIED BY '123456';# Only 192.168.1.73 loginmysql&gt; CREATE USER 'lin'@'%' IDENTIFIED BY '123456'; # Any remote host, password requiredmysql&gt; CREATE USER 'huea'@'%' IDENTIFIED BY '';#Arbitrary remote host, no password requiredmysql&gt; CREATE USER 'thomas'@'%';# Any remote host，No password required

The above completes the creation of the user and the user can also successfully connect, but once the database is added, deleted, modified and checked, an error will be reported:

（INSERT、DROP、UPDATE、SELECT、CREATE, ALTERwait）command denied to user 'xxx'@'localhost' for table 'table'

The reason for this problem is that the user does not have these permissions. The solution is to authorize the user to add, delete, modify and check permissions.

User permission authorization

mysql> GRANT privileges ON  TO 'username'@'host'

Command description:

privileges: The permissions to be granted to users, such as INSERT, DROP, UPDATE, SELECT, CREATE, ALTER, etc.
databasename: Database, if you want to grant the user the corresponding operation permissions for all databases and tables, it can be represented, such as .*
tablename: Data table, if you want to grant the user the corresponding operation permissions for all databases and tables, it can be represented, such as .*
username: Username, login name used to link to the database
host: Specify the host on which the user can log in. If localhost is available for localhost, if you want the user to log in from any remote host, you can use the wildcard %

Here are some common creation examples:

mysql&gt; GRANT SELECT, INSERT ON  TO 'thomas'@'%'; # Assign thomas user table query and insert permissions to test librarymysql&gt; GRANT ALL ON *.* TO 'thomas'@'%'; # Assign all permissions to all databases and tables to thomasmysql&gt; GRANT ALL ON test.* TO 'thomas'@'%'; # Assign all permissions to all tables in the test library to thomas

Note: The above authorization method gives the user permissions. The user does not have the permission to create a new user, nor does he have the permission to authorize the user's permissions.

(1) Create a user

mysql> CREATE USER 'lin'@'%' IDENTIFIED BY '123456';
1227 - Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

(2) User authorization

mysql> GRANT INSERT ON `comment`.* TO 'lin'@'%';
1044 - Access denied for user 'lin'@'%' to database 'comment'

If you want the created user to create and authorize permissions, you need to use the following command:

mysql> GRANT privileges ON  TO 'username'@'host' WITH GRANT OPTION;

That is, add 'WITH GRANT OPTION' after the regular authorized command. If you want the executed command to take effect immediately, you can execute the following command after executing the statement:

mysql> FLUSH PRIVILEGES;

The essential function of the FLUSH PRIVILEGES command is to extract the user information/permission settings in the current user and privilege tables from the mysql library (the built-in library of the MySQL database) into memory. After the MySQL user data and permissions have been modified, it is hoped that it will take effect directly without restarting MySQL service, so you need to execute this command. Usually after modifying the settings of the ROOT account, you are afraid that you will not be able to log in after restarting. Then you can directly flush and see if the permission settings take effect. Don't take too much risk!!

Setting and changing user passwords

SET PASSWORD FOR 'username'@'host' = '123456';
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

If you are currently logged in:

SET PASSWORD = '123456';

Revoke user permissions

The syntax of REVOKE and GRANT is similar. You just need to change the keyword "to" to "from":

REVOKE privilege ON  FROM 'lin'@'host';

Delete users

DROP USER 'lin'@'host';

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.