Liquibase Pro and MySQL GRANT Best Practices

When using Liquibase Pro stored logic with MySQL, MySQL has a default feature that limits access to stored logic bodies when the user requesting access is not the user who created them.

Also, the access can be limited when the user is not the DEFINER in MySQL. In this case, the generate-changelog, diff-changelog, and snapshot commands run by a user who is not the DEFINER cannot check the procedures, functions, triggers, views, and check constraints because of permissions that are not controlled. The lack of permissions results in the changelog that cannot be used for STOLO exports.

To see procedures, functions, triggers, views, and check constraints, you need to set user’s GRANTs.

Liquibase Pro Stored Logic and GRANTs in MySQL 5.7 and MySQL 8.0

As permissions are different for MySQL 5.7 and MySQL 8.0, follow the information for the version that you are using.

Minimum GRANTs for Liquibase Pro with MySQL 5.7 (Standard or RDS)

In MySQL, set the following permissions required to capture procedures, functions, triggers, views, and check constraints:

GRANT SELECT ON mysql.proc TO 'your_user'@'your_host';
GRANT SELECT, SHOW VIEW, CREATE VIEW ON your_db.* TO 'your_user'@'your_host';
GRANT CREATE ROUTINE, ALTER ROUTINE ON your_db.* TO 'your_user'@'your_host';
GRANT TRIGGER 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 'testuser'@'%';

Note: For more information about GRANT statement in MySQL 5.7, which grants privileges to MySQL user accounts, see GRANT Statement.

Minimum GRANTs for Liquibase Pro with MySQL 8.0 (Standard or RDS)

In MySQL, set the following permissions required to capture procedures, functions, triggers, views, and check constraints:

GRANT CREATE ROUTINE, ALTER ROUTINE ON your_db.* TO 'your_user'@'your_host';
GRANT CREATE VIEW, SHOW VIEW ON your_db.* TO 'your_user'@'your_host';
GRANT TRIGGER ON your_db.* to 'your_user'@'your_host';
GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT ,REFERENCES, UPDATE ON your_db.* TO 'your_user'@'your_host';
GRANT PROCESS ON *.* TO 'your_user'@'your_host';
GRANT SELECT ON *.* TO 'your_user'@'your_host';

Note: For more information about GRANT statement in MySQL 8.0, which grants privileges to MySQL user accounts, see GRANT Statement.

Additionally, there are a few configuration requirements and notes:

In MySQL 5.7, if a user does not have the SELECT ON mysql.proc permission, Liquibase Pro cannot get procedure or function bodies.
In MySQL 8.0, if a user does not have the GRANT CREATE ROUTINE and ALTER ROUTINE permissions, Liquibase Pro cannot get procedure or function bodies. The DEFINER, or the user who created the procedure or function, without the GRANT CREATE ROUTINE and ALTER ROUTINE permissions can only retrieve stored logic bodies.
If a user does not have GRANTs to SELECT and SHOW views, Liquibase Pro cannot access the view definition. Also, Liquibase Pro will not capture the view configuration for ALGORITHM, DEFINER, or SQL SECURITY. Review the generated changelog and update the view SQL to include an additional configuration.
If the stored logic bodies or views are null due to the lack of permissions, Liquibase Pro writes the string "[CANNOT READ OBJECT BODY];" to the generated SQL. However, when a changelog where Liquibase Pro replaced null bodies with the string is generated, it will not be deployable. If you encounter such an issue, you can either expand your Liquibase Pro connection user permissions or edit the generated changelog to include stored logic bodies skipped due to the lack of permissions.
If you use AWS RDS, configure the MySQL instance to allow the creation of stored logic objects. You can configure it using a parameter group that sets log_bin_trust_function_creators=1. Also, restart the MySQL instance to apply the modified parameter group.

How to See User GRANTs in MySQL 5.7 and MySQL 8.0

Both MySQL 5.7 and MySQL 8.0 have the same syntax for checking user GRANTs:

SHOW GRANTS FOR 'YOUR_USER'@'YOUR_HOST';

Known issues in MySQL 5.7 and MySQL 8.0

Liquibase Pro is not able to snapshot view definitions if the Liquibase Pro user isn't the DEFINER of the view. This issue is caused due to a bug in MySQL, which you can review in the MySQL bug reporting page.

As a workaround, grant the SHOW VIEW permissions to your Liquibase Pro user on a specific view after this view has been created. For example:

GRANT SHOW VIEW ON YOUR_DB.YOUR_VIEW TO 'YOUR_USER'@'YOUR_HOST';

Another way is to configure the Liquibase Pro connection user to be the user that creates views. In this case, the user becomes the DEFINER and can see all objects they create.