Liquibase and MySQL User Grants for Stored Routines

The MySQL grant system controls which users can view and execute stored routines. In Liquibase 4.2, we added a feature for Liquibase Pro users that certifies our stored logic support for MySQL 5.7 and 8.0. Neat! So let’s dive into what you need to know about using MySQL grants with Liquibase so your team can see the procedures, functions, triggers, views, and check constraints in the changelog.

Let’s start with an example of a formatted SQL changelog that could not be fully generated due to a lack of privileges:

-- changeset gemfire:1607369260892-8
CREATE PROCEDURE HelloWorldProcedure (  )
[CANNOT READ OBJECT BODY];

Why is the phrase “cannot read object body” in my MySQL changelog?

MySQL prohibits access to stored routine bodies when the user requesting access is not the routine DEFINER. This default MySQL configuration is problematic for Liquibase Pro, which requires access to stored routine bodies to generate changelogs and perform drift detection. If your Liquibase connection user does not have a minimum set of grants to leverage Liquibase Pro, this is the error you�ll see in the log or output to the console:

WARNING:
The current MySQL user does not have permissions to access Stored Logic object bodies needed for this Liquibase command. Please search the changelog for '[CANNOT READ OBJECT BODY]' to locate inaccessible objects. 

Minimum MySQL grants for use with Liquibase Pro

Liquibase Pro has a minimum set of privileges required to capture procedures, functions, triggers, views, and check constraints. 

Here’s a helpful query to verify user grants on your Liquibase Pro connection:

SHOW GRANTS FOR ‘pro_user’@'hostname';

Grant statements vary depending on the version of MySQL you�re using.

Required grants for using Liquibase Pro with MySQL 5.7

GRANT SELECT ON mysql.proc TO 'pro_user'@'hosname';
GRANT SELECT, CREATE VIEW, SHOW VIEW ON your_db.* TO 'pro_user'@’hostname’;
GRANT TRIGGER ON your_db.* TO 'your_user'@'your_host';
GRANT CREATE ROUTINE, ALTER ROUTINE ON your_db.* TO 'your_user'@'your_host';
GRANT ALTER,CREATE, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON your_db.* TO 'your_user'@'your_host';
GRANT PROCESS ON *.* TO 'daticalUser'@'%';
flush privileges;

Required grants for using Liquibase Pro with MySQL 8.0

GRANT CREATE ROUTINE, ALTER ROUTINE ON your_db.* TO pro_user@'hostname';
GRANT CREATE VIEW, SHOW VIEW ON your_db.* to ‘pro_user’@'hostname’';
GRANT TRIGGER ON your_db.* to ‘pro_user’@’hostname';
GRANT ALTER,CREATE,DELETE,DROP,INDEX,INSERT,SELECT,REFERENCES,UPDATE on your_db.* to ‘pro_user’@’hostname’';

See our documentation for more details and best practices for using MySQL grants with Liquibase.

Sharing view privileges

If you want to share any view of a stored routine with a Liquibase user who is not the view DEFINER, you must grant the following privilege:�

GRANT SHOW VIEW ON your_db.your_view TO ‘pro_user’@’hostname’;

The grant can only be applied after the view is created on the database that Liquibase Pro will use for a snapshot.

Still getting an error?

If Liquibase Pro continues to return CANNOT READ OBJECT BODY for views, you may be running into a known MySQL limitation. We can help you figure out if this is the case or if something else is going on. Liquibase Pro trial users and customers can always contact us for help and support in making sure you are getting the most out of using Liquibase with MySQL stored routines!

Give it a try!

If you need to update schemas and stored routines in your MySQL databases, give Liquibase Pro a try. It’s completely free for 30 days and comes with some pretty amazing support to get you started.