Configure user and user permissions in the MySQL database

Configure user and user permissions in the MySQL database

Problem: when using a remote tool written by a great god to manage the MySQL database, all the data can be properly displayed, but cannot be deleted or modified.

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

1	> show grants for username

Displays the permissions of the current user: select, insert, update, delete

12	GRANT USAGE ON *.* TO 'username'@'host' IDENTIFIED BY PASSWORD '*BB318072E265C419B3E1E19A4DAD1FA969B9B4D4' // Only users that cannot be logged on locallyGRANT SELECT, INSERT, UPDATE, DELETE ON `dbName`.* TO 'usename'@'host'// This user has select/insert/update/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.

1	GRANT DELETE, INDEX, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `dbName`.* TO 'username'@'host'

View user permissions

?

12	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

?

1	mysqldump -uroot -ntd -R dbName > dbName.sql

Import error message found

?

1	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

123456

> 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.

12345678

> 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.

This article permanently updates the link address: