MySQL Grant command details

This example runs on MySQL 5.0 and later.

The simple format of commands granted to users by MySQL is as follows:

Grant permission on database objects to users

1. grant normal data users the right to query, insert, update, and delete all table data in the database.

Copy codeThe Code is as follows:
Grant select on testdb. * to common_user @ '%'
Grant insert on testdb. * to common_user @ '%'
Grant update on testdb. * to common_user @ '%'
Grant delete on testdb. * to common_user @ '%'

Alternatively, replace the following with a MySQL command:

Copy codeThe Code is as follows:
Grant select, insert, update, delete on testdb. * to common_user @ '%'

2. grant database developers to create tables, indexes, views, stored procedures, and functions... .

Grant permissions to create, modify, and delete MySQL Data Table structures.

Copy codeThe Code is as follows:
Grant create on testdb. * to developer @ '192. 192.% ';
Grant alter on testdb. * to developer @ '192. 192.% ';
Grant drop on testdb. * to developer @ '192. 192.% ';

Grant the MySQL foreign key operation permission.

Copy codeThe Code is as follows: grant references on testdb. * to developer @ '2017. 192.% ';

Grant the permission to operate MySQL temporary tables.

Copy codeThe Code is as follows:
Grant create temporary tables on testdb. * to developer @ '2017. 192.% ';

Grant the permission to operate MySQL indexes.

Grant index on testdb. * to developer @ '192. 192.% ';

Grant permissions to operate the MySQL view and view the source code.

Copy codeThe Code is as follows:
Grant create view on testdb. * to developer @ '192. 192.% ';
Grant show view on testdb. * to developer @ '192. 192.% ';

Grant permissions to operate MySQL stored procedures and functions.

Copy codeThe Code is as follows:
Grant create routine on testdb. * to developer @ '192. 192.% '; -- now, can show procedure status
Grant alter routine on testdb. * to developer @ '192. 192.% '; -- now, you can drop a procedure
Grant execute on testdb. * to developer @ '192. 192.% ';

3. grant common DBA permission to manage a MySQL database.

Copy codeThe Code is as follows:
Grant all privileges on testdb to dba @ 'localhost'

The keyword "privileges" can be omitted.

4. grant senior DBA permission to manage all databases in MySQL.

Copy codeThe Code is as follows:
Grant all on *. * to dba @ 'localhost'

5. MySQL grant permissions can be applied to multiple levels.

1. grant applies to the entire MySQL Server:

Copy codeThe Code is as follows:
Grant select on *. * to dba @ localhost; -- dba can query tables in all databases in MySQL.
Grant all on *. * to dba @ localhost; -- dba can manage all databases in MySQL

2. grant applies to a single database:

Copy codeThe Code is as follows:
Grant select on testdb. * to dba @ localhost; -- dba can query tables in testdb.

3. grant applies to a single data table:

Copy codeThe Code is as follows:
Grant select, insert, update, delete on testdb. orders to dba @ localhost;

When you authorize a user to multiple tables, you can execute the preceding statements multiple times. For example:

Copy codeThe Code is as follows:
Grant select (user_id, username) on smp. users to mo_user @ '% 'identified by '123 ';
Grant select on smp.mo _ sms to mo_user @ '%' identified by '123 ';

4. grant applies to columns in the table:

Copy codeThe Code is as follows: grant select (id, se, rank) on testdb. apache_log to dba @ localhost;

5. grant applies to stored procedures and functions:

Copy codeThe Code is as follows:
Grant execute on procedure testdb. pr_add to 'dba '@ 'localhost'
Grant execute on function testdb. fn_add to 'dba '@ 'localhost'

Vi. View MySQL user permissions

View Current user (own) permissions:

Copy codeThe Code is as follows: show grants;

View other MySQL user permissions:

Copy codeThe Code is as follows: show grants for dba @ localhost;

7. revoke permissions granted to MySQL users.

The syntax of revoke is similar to that of grant. You only need to replace the keyword "to" with "from:

Copy codeThe Code is as follows:
Grant all on *. * to dba @ localhost;
Revoke all on *. * from dba @ localhost;

VIII. Considerations for MySQL grant and revoke User Permissions

1. After the grant and revoke permissions are granted, the permissions can only take effect after the user reconnects to the MySQL database.

2. If you want to grant the authorized users, you can grant these permissions to other users. You need to select "grant option".

Copy codeThe Code is as follows: grant select on testdb. * to dba @ localhost with grant option;

This feature is generally unavailable. In practice, it is best for DBAs to manage database permissions in a unified manner.

In the case of the SELECT command denied to user 'username' @ 'hostname 'for table 'table name' error, You need to authorize the table name following it, that is, you need to authorize the core database.

I encountered a SELECT command denied to user 'my '@' % 'for table 'proc', which appeared when the stored procedure was called, I thought it would be enough to authorize the specified database. I don't need to worry about any stored procedures, functions, etc. Who knows I need to authorize the proc table of the database mysql?

There are five mysql authorization tables: user, db, host, tables_priv, and columns_priv.

The authorization table has the following functions:

User table
The user table lists the users that can connect to the server and their passwords, and specifies which global (Super user) permissions they have. All permissions enabled in the user table are global permissions and apply to all databases. For example, if you have enabled the DELETE permission, the users listed here can DELETE records from any table, so you should consider it carefully before doing so.

Db table
The database table lists the databases, and the user has the permission to access them. The permission specified here applies to all tables in a database.

Host table
The host table and db table are used in combination to control the database access permissions of a specific host at a good level, which may be better than using the database separately. This table is not affected by the GRANT and REVOKE statements, so you may find that you are not using it at all.

Tables_priv table
The tables_priv table specifies table-level permissions. The specified Permission applies to all columns in a table.

Columns_priv table
The columns_priv table specifies the column-level permission. The specified Permission applies to specific columns of a table.