User and user rights configuration _mysql under Mysql database

Problem: Use a thigh write remote tool to manage the MySQL database, found that all the data can be displayed, but can not be deleted, modified and so on.

Thinking: You can read the information in the database remotely, indicating that the current host can connect to the database remotely. You cannot delete, modify these operations, and indicate that some permissions are not given to the current remote user.

　　Workaround:

View Current user rights

Show grants for username

Displays the permissions under the current user as: Select,insert,update,delete

GRANT USAGE on *.* to ' username ' @ ' host ' identified by PASSWORD ' *bb318072e265c419b3e1e19a4dad1fa969b9b4d4 '//can only log on locally Operation User
GRANT SELECT, INSERT, UPDATE, DELETE on ' dbname '. * to ' usename ' @ ' host '//This user has Select/insert/update/delelte privileges

In this way, it should be possible to delete, modify these permissions, but the remote tool can not operate.

After careful investigation, found that the thigh write this tool on the database operations are basically performed through the function, I this user's permissions do not give stored procedures, storage functions related permissions, of course, can not be related to operation

As a result, add stored procedures and stored function permissions to the user

GRANT DELETE, INDEX, EXECUTE, CREATE routine, ALTER routine on ' dbname '. * to ' username ' @ ' host '

View user permissions as

Grant USAGE on *.* to ' username ' @ ' host ' identified by PASSWORD ' *938d2d224d12dad427ab953b931ea6df0cf0656a '
grant SELECT, INSERT, UPDATE, DELETE, INDEX, EXECUTE, CREATE routine, ALTER routine on ' dbname '. * to ' username ' @ ' host '

Use the remote tool again to properly use the

-----------------------------------------------------------------------------------------------------

　Attaching: Importing Database Custom functions

Mysqldump-uroot-ntd-r dbname > Dbname.sql

Error message found in import

ERROR 1418 (HY000): This function has none of the deterministic, NO SQL, or reads SQL DATA in its declaration and binary Loggi NG is enabled (for your *might* want to use the less safe log_bin_trust_function_creators variable)

Error message 1481, when importing a custom function is equivalent to creating a custom function into the database, but because a security parameter is not turned on, the log_bin_trust_function_creators defaults to 0 (off).

is not allowed to synchronize function (that is, not allow the creation of functions), open this parameter, you can create a successful.

View log_bin_trust_function_creators values

> Show variables like "%func%"
--------------------------------------
| variable_name | value|
--------------------------------|-----
|log_bin_trust_function_creators| Off |
--------------------------------------

Value is off, which means that you are not allowed to create functions, modify this value, you can

> Set global Log_bin_trust_function_creators=1;
>show variables like "%func%"
--------------------------------------
| variable_name | value|
--------------------------------|-----
|log_bin_trust_function_creators| On |

Note: When the import is complete, remember to set the value back to 0 (that is, off), for specific reasons.

The above is a small set to introduce the MySQL database users and user rights configuration, hope to help everyone, if you have any questions welcome to my message, small series will promptly reply to everyone!