Mysql permission management grant command to take notes

The simple format of commands granted to users by MySQL is as follows:
Copy codeThe Code is as follows: grant permission on database object to user [identified by 'Password']

The most common method is to set all permissions for the slave user in the slave database for master-slave synchronization.
Only allow users to log on from 192.168.0.2 and use the password funsion (the password should be enclosed in single/double quotation marks)
Copy codeCode: grant all on *. * to slave@192.168.0.2 identified by 'funsion ';
After the execution is complete, remember to use flush privileges; refresh the permission

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, insert, update, delete on testdb. * to common_user @ '%'

2. grant database developers the permissions to create tables, indexes, views, stored procedures, and functions.
Copy codeThe Code is as follows: grant create, alter, 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 indexes.
Copy codeThe Code is as follows: grant index on testdb. * to developer @ '2017. 192.% ';

Open permissions to all IP addresses:
Copy codeThe Code is as follows: grant all privileges on *. * TO 'root' @ '%' identified by '000000' with grant option;

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 permissions to operate the MySQL view and view the source code.
Copy codeThe Code is as follows: grant create view on testdb. * to developer @ '2017. 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.% ';

After the execution is complete, remember to use flush privileges; refresh the permission

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;

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 from:
Copy codeThe Code is as follows:
Grant all on *. * to dba @ localhost;
Revoke all on *. * from dba @ localhost;

# ************************************ FAQs solution ************************************** #

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.

If you encounter a SELECT command denied to user 'my '@' % 'for table 'proc', it appears when you call the stored procedure, I thought it would be enough to authorize the specified database. I don't need to worry about any stored procedures, functions, and so on.

Mysql proc table authorization

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.