Mysql's Grant command detailed _mysql

This example is run in MySQL version 5.0 and above.

The simple format that MySQL gives user permission commands can be summarized as:

Grant permission on database object to user

One, grant ordinary data user, query, insert, UPDATE, delete all table data in the database right.

Copy Code code 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 it with a MySQL command:

Copy Code code as follows:

Grant SELECT, INSERT, UPDATE, delete on testdb.* to common_user@ '% '

Second, grant database developers, create tables, indexes, views, stored procedures, functions ... and other permissions.

Grant creates, modifies, and deletes MySQL data table structure permissions.

Copy Code code as follows:

Grant create on testdb.* to developer@ ' 192.168.0.% ';
Grant alter on testdb.* to developer@ ' 192.168.0.% ';
Grant drop on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL foreign key permissions.

Copy Code code as follows:

Grant references on testdb.* to developer@ ' 192.168.0.% ';

Grant operates the MySQL temporary table permission.

Copy Code code as follows:

Grant create temporary tables on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL indexing permissions.

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

Grant operates the MySQL view and views the view source code permissions.

Copy Code code as follows:

Grant CREATE view on testdb.* to developer@ ' 192.168.0.% ';
Grant Show view on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL stored procedures, function permissions.

Copy Code code as follows:

Grant create routine on testdb.* to developer@ ' 192.168.0.% '; --now, can show procedure status
Grant alter routine on testdb.* to developer@ ' 192.168.0.% '; --now, can drop a procedure
Grant execute on testdb.* to developer@ ' 192.168.0.% ';

Third, grant ordinary DBA management of a MySQL database permissions.

Copy Code code as follows:

Grant all privileges on TestDB to dba@ ' localhost '

Among them, the keyword "privileges" can be omitted.

The grant advanced DBA manages permissions for all databases in MySQL.

Copy Code code as follows:

Grant all on *.* to dba@ ' localhost '

MySQL grant permissions, respectively, can function at multiple levels.

1. Grant functions on the entire MySQL server:

Copy Code code as follows:

Grant SELECT on *.* to Dba@localhost; --The DBA can query the tables in all databases in MySQL.
Grant all on *.* to Dba@localhost; --DBA can manage all databases in MySQL

2. Grant functions on a single database:

Copy Code code as follows:

Grant SELECT on testdb.* to Dba@localhost; --DBAs can query the tables in TestDB.

3. Grant functions on a single datasheet:

Copy Code code as follows:

Grant SELECT, INSERT, UPDATE, delete on testdb.orders to Dba@localhost;

Here, when you authorize multiple tables for a user, you can execute the above statements more than once. For example:

Copy Code code as follows:

Grant Select (user_id,username) on smp.users to mo_user@ '% ' identified by ' 123345 ';
Grant SELECT on smp.mo_sms to mo_user@ '% ' identified by ' 123345 ';

4. Grant acts on the columns in the table:

Copy Code code as follows:

Grant Select (ID, SE, rank) on Testdb.apache_log to Dba@localhost;

5. Grant functions on stored procedures, functions:

Copy Code code 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 rights

View current user (Own) permissions:

Copy Code code as follows:

Show grants;

To view additional MySQL user rights:

Copy Code code as follows:

Show grants for Dba@localhost;

Vii. revoke permissions that have been given to the MySQL user.

Revoke is similar to Grant's syntax, simply replace the keyword "to" with "from":

Copy Code code as follows:

Grant all on *.* to Dba@localhost;
Revoke all in *.* from Dba@localhost;

Viii. MySQL Grant, REVOKE user rights considerations

1. Grant, revoke user rights, the user can only reconnect to the MySQL database, permissions to take effect.

2. If you want to allow authorized users to grant these permissions to other users, you need option "grant option"

Copy Code code as follows:

Grant SELECT on testdb.* to dba@localhost with GRANT option;

This feature is generally not used. In practice, database permissions are best managed by DBAs.

This error is encountered with the SELECT command denied to user ' username ' ' @ ' host name ' for table ', the solution is to have the following table name authorized, that is, to authorize the core database as well.

I encountered the select command denied to user ' I ' @ '% ' for table ' proc ', which occurs when the stored procedure is invoked, as long as it is necessary to authorize the specified database, what stored procedures, functions, etc. Who knows also to put the database MySQL proc table authorization

The MySQL authorization form has 5 tables: User, DB, host, Tables_priv, and Columns_priv.

The contents of the authorization table are used for the following purposes:

User table
The user table lists the users and their passwords that can connect to the server, and it specifies what global (superuser) permissions they have. Any permissions that are enabled in the user table are global permissions and apply to all databases. For example, if you have the Delete permission enabled, the users listed here can delete records from any table, so think carefully before you do so.

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

Host table
The host table is used in conjunction with the DB table to control the access of a particular host to the database at a better level, which may be better than using DB alone. 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, and one of the permissions specified here applies to all columns of a table.

Columns_priv table
The COLUMNS_PRIV table specifies column-level permissions. The permissions specified here apply to a specific column of a table.