Mysql Grant Rights Management

the simple format that MySQL gives to user rights commands can be summarized as:GrantPermissions onDatabase objects toUser[identified by ' password ']most commonly used, when the master-slave synchronization, to the slave from the library user settings have all permissions, permission all only allow it from 192.168.0.2 login, and limit the use of password funsion (password to use a single/enclosed in double quotes)Grant  All  on *.*  toSlave@192.168.0.2Identified by 'funsion'after execution, remember to use FLUSHPrivileges; Refresh Permissions One,GrantNormal data users, the right to query, insert, UPDATE, delete all table data in the database. Grant Select,Insert,Update,Delete  onTestDB.*  to[Email protected]'%'Second,Grantdatabase developers, creating tables, indexes, views, stored procedures, functions ..... and other permissions. Grant Create,Alter,Drop  onTestDB.*  to[Email protected]'192.168.0.%';Grantoperation MySQL FOREIGN key permissions. Grant References  onTestDB.*  to[Email protected]'192.168.0.%';Grantmanipulate MySQL Index permissions. Grant Index  onTestDB.*  to[Email protected]'192.168.0.%'; Open permissions for all IPs:GRANT  All Privileges  on *.*  to 'Root'@'%'Identified by '123456'  with GRANT OPTION;GrantOperation MySQL Temp table permissions. Grant Create TemporaryTables onTestDB.*  to[Email protected]'192.168.0.%'; Grantmanipulate MySQL View, view source code permissions. Grant Create View  onTestDB.*  to[Email protected]'192.168.0.%';GrantShowView  onTestDB.*  to[Email protected]'192.168.0.%';Grantoperation MySQL stored procedures, function permissions. Grant CreateRoutine onTestDB.*  to[Email protected]'192.168.0.%';--Now , can show procedure statusGrant AlterRoutine onTestDB.*  to[Email protected]'192.168.0.%';--Now , can drop a procedureGrant Execute         onTestDB.*  to[Email protected]'192.168.0.%'after execution, remember to use FLUSHPrivileges; Refresh Permissions three,GrantThe normal DBA manages permissions for a MySQL database. Grant  All Privileges  onTestDB to[Email protected]'localhost'where the keywordPrivilegescan be omitted. Four,GrantThe Advanced DBA manages permissions for all databases in MySQL. Grant  All  on *.*  to[Email protected]'localhost'Five, MySQLGrantpermissions can be used on multiple levels, respectively. 1.Grantfunction on the entire MySQL server:Grant Select  on *.*  toDba@localhost;--DBAs can query tables in all databases in MySQL. Grant  All     on *.*  toDba@localhost;--DBAs can manage all databases in MySQL2.Grantfunction on a single database:Grant Select  onTestDB.*  toDba@localhost;--DBAs can query the tables in TestDB. 3.Grantfunction on a single data table:Grant Select,Insert,Update,Delete  onTestdb.orders toDba@localhost; vi. View MySQL user rights view current user (Own) permissions: show grants; view other MySQL user rights: Show grants forDba@localhostSeven, revoke the permissions that have been given to the MySQL user. RevokeWithGrantThe syntax is similar, just need to put the keyword toInto fromcan:Grant   All  on *.*  toDba@localhost;Revoke  All  on *.*  fromDba@localhost;# *************************************FAQs Solutions**************************************#遇到SELECTCommand denied to User 'User name'@'Host name'  for Table 'Table name'The solution to this error is to authorize the table name behind it, that is, to authorize the core database. If you encounter a select command denied to User 'my'@'%'  for Table 'proc', is the call to the stored procedure, the original thought that as long as the specified database authorization on the line, what stored procedures, functions and so no longer tube, who knows also to put the database MySQL proc table authorization MySQL Authorization form a total of 5 tables:User, DB, Host, Tables_priv, and Columns_priv. The contents of the authorization form are used as follows:[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 on the user table are global permissions and apply to all databases. For example, if you have 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 access to the database for a particular host 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. 

Mysql Grant Rights Management