Configuration of user and user permissions in the MySQL database, mysql user permissions
Problem: when you use a remote tool written in a thigh to manage the Mysql database, all data is displayed normally, but you cannot delete or modify the data.
Idea: You can remotely read the information in the database, indicating that the current host can remotely connect to the database. However, you cannot delete or modify these operations, which means that some permissions are not granted to the current remote user.
Solution:
View Current User Permissions
> show grants for username
Displays the permissions of the current user: select, insert, update, delete
Grant usage on *. * TO 'username' @ 'host' identified by password' * BB318072E265C419B3E1E19A4DAD1FA969B9B4D4 '// you can only grant select, INSERT, UPDATE, delete on 'dbname '. * TO 'usename' @ 'host' // This user has select, insert, update, and delelte permissions.
In this case, you should have the permission to delete or modify the objects, but you cannot perform operations on remote tools.
After careful troubleshooting, I found that the thigh-Writing Tool basically performs database operations through functions. I did not grant the stored procedure or storage function related permissions to this user's permissions, of course, you cannot perform related operations.
Therefore, the stored procedure and stored function permissions are added to the user.
GRANT DELETE, INDEX, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `dbName`.* TO 'username'@'host'
View user permissions
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'
Remote tools can be used correctly.
Bytes -----------------------------------------------------------------------------------------------------
Appendix: Import Database User-Defined Functions
mysqldump -uroot -ntd -R dbName > dbName.sql
Import error message found
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Error Code 1481: when a user-defined function is imported, it is equivalent to creating a user-defined function to the database. However, because a security parameter is not enabled, log_bin_trust_function_creators is 0 (that is, OFF) by default ),
Function synchronization is not allowed (that is, function creation is not allowed). If this parameter is enabled, the function can be created successfully.
View the log_bin_trust_function_creators Value
> show variables like "%func%"--------------------------------------|Variable_name |Value|--------------------------------|----- |log_bin_trust_function_creators| OFF |--------------------------------------
If the value is OFF, the function cannot be created. modify this value.
> set global log_bin_trust_function_creators=1;>show variables like "%func%"--------------------------------------|Variable_name |Value|--------------------------------|----- |log_bin_trust_function_creators| ON |--------------------------------------
Note: After the import is complete, set the value back to 0 (that is, OFF). The specific reason is not detailed.