How Do I Maintain the Original Service User Permission System After Definer Is Forcibly Converted During MySQL Migration?
Definer is used in views, stored procedures, triggers, and events. Definer does not restrict the permission to invoke objects, instead the permission to access the database. If you select Yes for Migrate Definer to User during MySQL migration, the Definers of all source database objects will be migrated to the user. The user continues to use the original services without authorization. (Users, permissions, and passwords are migrated). Other users do not have permissions on database objects unless these users are authorized.
The following procedures describe how to use database commands to authorize users.
- Ensure that the new user (Definer uses the specified account) has sufficient permission to execute view- and stored procedure-related SQL statements.
- Log in to the destination database using the MySQL official client or other tools.
- Run the following command to view details about permissions of the user to be authorized:
show grants for 'user'@'host';
- To ensure that the original service does not report an error, run the following command to grant the user the operation permissions the involved database objects do not have:
grant select,insert,update,delete on db_name.* to 'user'@'host';
Generally, the permissions to access the database are as follows: SELECT, CREATE, DROP, DELETE, INSERT, UPDATE, INDEX, EVENT, CREATE VIEW, CREATE ROUTINE, TRIGGER, and EXECUTE. You need to check the permissions that are missing based on the database object, and then perform the authorization operation.
For stored procedures and functions, ensure that the user has the EXECUTE permission. The authorization command is as follows:
grant execute on db_name.function_name to 'user'@'host';
- Use the authorized account to access the destination database. If the access is successful, the authorization is successful. Note: If the following information is displayed when a stored procedure or function is invoked in a Java project, the mysql.proc database must be authorized: Java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parametertypes
grant select on mysql.proc to 'user'@'host';
Real-Time Migration FAQs
- When Can I Stop a Migration Task?
- How Do I Maintain the Original Service User Permission System After Definer Is Forcibly Converted During MySQL Migration?
- What Can I Do If the Invoking Permission Problem Occurs After the MySQL Stored Procedure Is Migrated to the Cloud?
- How Do I Ensure that All Services on the Database Are Stopped?
- What Can I Do When Message "can not get agency token" Is Displayed in the Migration Log
- What Do I Do If the Maximum Index Length Has Been Reached During Migration from Oracle to MySQL?
- Why Is the Collation of Heterogeneous or Oracle Databases Converted to utf8mb4_bin After Those Databases Are Migrated to MySQL?
- What Can I Do If MyISAM Tables Are Not Supported by RDS for MySQL?
- What Are the Precautions for Migrating Data from an Earlier Version MySQL to MySQL 8.0?
- What Can I Do When OOM Occurs During the Migration of MongoDB Databases?
- How Do I Disable the Balancer?
- How Do I Export and Import Events and Triggers in Batches?
- How Can I Migrate Databases or Tables Whose Names Contain Uppercase Letters?
- How Do I Delete Orphaned Documents in MongoDB Sharded Clusters?
- What Can I Do If There Is an Extra Backslash (\) After a MySQL Account Is Migrated?
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbotmore