Liquibase Pro and MySQL Security Context Limitation

MySQL provides security contexts to determine how Stored Logic behaves when being executed during a deployment.

The three configuration options are:

  • DEFINER
  • ALGORITHM
  • SQL SECURITY

When you generate a changelog using the Liquibase generate-changelog command, the generate-changelog command does not retrieve MySQL security context settings defined for functions, procedures, triggers, or views.

Deploying the changelog may result in the unexpected behavior during stored logic execution due to security context differences. The limitation affects views, procedures, functions, and triggers.

To have the security context defined for functions, procedures, triggers, or views in your changelog file, you need to examine and edit the generated changelog to include security contexts omitted by generate-changelog.

Note: If there are views, procedures, functions, or triggers that need a security context defined prior to deploying to another database, update the generated SQL to include the context.

Examples

Procedure with a security context

You can use the same example for a function, however, replace the PROCEDURE object type with FUNCTION.

CREATE
	[OR REPLACE]
	[DEFINER = user]
	PROCEDURE sp_name ([func_parameter[,...]])
	RETURNS type
	[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
	[DEFINER = user]
	[SQL SECURITY { DEFINER | INVOKER }]
routine_body

View with a security context

CREATE
	[OR REPLACE]
	[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
	[DEFINER = user]
	[SQL SECURITY { DEFINER | INVOKER }]
	VIEW view_name [(column_list)]
	AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]

Trigger with a security context

CREATE
	[DEFINER = { user | CURRENT_USER }]
	TRIGGER trigger_name trigger_time trigger_event
	ON tbl_name FOR EACH ROW trigger_stmt

Related links