User and user permission configuration under MySQL database_MySQL

Problem: When using a remote tool written by a certain author to manage the Mysql database, I found that all data can be displayed normally, but operations such as deletion and modification cannot be performed.

Idea: The information in the database can be read remotely, indicating that the current host can remotely connect to the database. However, these operations such as deletion and modification cannot be performed, indicating that some permissions are not granted to the current remote user.

　Solution:

　View current user permissions

show grants for username

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

GRANT USAGE ON *.* TO 'username'@'host' IDENTIFIED BY PASSWORD '*BB318072E265C419B3E1E19A4DAD1FA969B9B4D4' //只可以在本地登陆的 不能操作的用户
GRANT SELECT, INSERT, UPDATE, DELETE ON `dbName`.* TO 'usename'@'host' //此用户拥有select/insert/update/delelte权限

It seems that it should have the permissions to delete and modify, but it cannot be operated on the remote tool.

After careful investigation, I found that the operations of the database written by this tool are basically performed through functions. My user permissions do not grant relevant permissions to stored procedures and stored functions. Of course, I cannot perform related operations

　So, add stored procedure and stored function permissions to users

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

　View user permissions are

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 and use it correctly

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

　Attachment: Import database custom function

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

Found an error message when importing

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 message 1481, when importing a custom function, it is equivalent to creating a custom function into the database, but because a security parameter is not turned on, log_bin_trust_function_creators defaults to 0 (that is, OFF),

The synchronization of functions is not allowed (that is, the creation of functions is not allowed). If you enable this parameter, you can create it successfully.

　View log_bin_trust_function_creators value

> show variables like "%func%"
--------------------------------------
|Variable_name |Value|
--------------------------------|-----
|log_bin_trust_function_creators| OFF |
--------------------------------------

　The value is OFF, which means that creating functions is not allowed. Modify this value and you can

> 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 completed, remember to set the value back to 0 (i.e. OFF). The specific reasons will not be detailed.

The above is the user and user permission configuration under the MySQL database introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time!