Mysql Rights Management Grant command makes notes _mysql

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

Copy Code code as follows:

Grant permission on database object to user [identified by ' Password ']

The most commonly used, to get the master and slave synchronization, to the slave user settings from the library have all the permissions, permissions all
Only allow it to log on from 192.168.0.2 and limit the use of password funsion (passwords are enclosed in single/double quotes)

Copy Code code as follows:

Grant all on *.* to slave@192.168.0.2 identified by ' funsion ';

After the execution, remember to use FLUSH privileges; Refresh Permissions

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

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.

Copy Code code as follows:

Grant Create, Alter, 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 MySQL indexing permissions.

Copy Code code as follows:

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

Open Permissions for all IP:

Copy Code code as follows:

Grant all privileges in *.* to ' root ' @ '% ' identified by ' 123456 ' with Grant OPTION;

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 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.% ';

After the execution, remember to use FLUSH privileges; Refresh Permissions

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

Copy Code code as follows:

Grant all privileges on TestDB to dba@ ' localhost '

Where 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;

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 by simply replacing the keyword to the From:

Copy Code code as follows:

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

# ************************************* FAQ Solution ************************************** #

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.

If you encounter a SELECT command denied to user ' I ' @ '% ' for table ' proc ', is to invoke the stored procedure, the original thought that as long as the specified database authorization on the line, what stored procedures, functions and so do not have to pipe, who knows also to the database

MySQL's 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.