Docs: Features: Definers

All stored procedures, functions, views, and triggers always have a notion of a “definer” user on the database server. Even if the object was created without an explicit DEFINER clause, the user who ran the CREATE will become the definer.

The choice of definer has important security implications. The associated definer’s privilege grants are used whenever the stored logic is invoked by default, unless a SQL SECURITY INVOKER clause was explicitly present in the CREATE. Due to these security implications, some companies wish to restrict which database users may be chosen as definers. Skeema’s definer linting functionality allows you to configure such a policy.

Managing definers can be operationally complex on some managed database platforms. Elevated privileges are required in order to create a database object with a DEFINER that differs from the current user. Database-as-a-service products (such as AWS RDS and GCP CloudSQL) may not provide these necessary privileges, which can make DEFINER clauses problematic unless you use the same database user name across all objects in all environments. Skeema Premium’s DEFINER stripping feature helps simplify operations in this situation.

Linting definers

To restrict which users may appear in DEFINER clauses, you can configure the allow-definer and lint-definer options. This is useful for limiting the security privileges of routines, views, and triggers. It also protects against accidentally creating orphaned objects when running Skeema with a privileged database user. Otherwise, if you simply have a typo in a DEFINER clause, you can inadvertently create an unusable “orphaned” object if your Skeema user happens to have the SET_USER_ID (MySQL 8.0+), ALLOW_NONEXISTENT_DEFINER (MySQL 8.2+), SET USER (MariaDB 10.5+), or SUPER privilege.

The allow-definer option should be set to a comma-separated list of user@host values, indicating which users to permit as definers. Each value can optionally use SQL LIKE-style wildcards of % and _. For example, allow-definer=root@%,procdef@192.168.% will permit a definer of root with any hostname, or procdef with any IP beginning with the prefix 192.168.

The default value for allow-definer is %@%, which is intentionally permissive of all possible DEFINER users. You must override this option if you wish to restrict what DEFINER users are permissible; otherwise, the linter will not flag any definers.

Meanwhile, the lint-definer option controls what happens when Skeema’s linter encounters a DEFINER user who isn’t present in the allow-definer list. The default value of lint-definer is “error”, meaning that a fatal error is logged and the command’s exit code will reflect this. You can reduce the severity of this situation to a non-fatal warning by setting lint-definer=warning.

You can disable definer linting entirely by leaving allow-definer at its permissive default of %@%, or by setting lint-definer=ignore.

Stripping DEFINER clauses

Commands such as skeema init and skeema pull rely on the database’s SHOW CREATE output when writing object definitions to the filesystem. Since all stored procedures, functions, views, and triggers always have a notion of a DEFINER user in the database, this means the CREATE statements in your .sql files will contain these clauses, just like when running a traditional mysqldump.

When executing a CREATE, any database user account may specify a DEFINER equal to the current user, but specifying a different user as the DEFINER requires elevated privileges. Database-as-a-service platforms (such as AWS RDS and GCP CloudSQL) may not provide these privileges, especially in older database server versions which lacked finer-grained administrative permissions. This situation can be especially problematic if your company wishes to use differing database user account names between prod/stage/dev environments: if you dump definitions from prod (for example), you won’t be able to deploy those definitions as-is to other environments due to the DEFINER clause mismatch.

To solve this problem, Skeema Premium supports the strip-definer option, which can remove the DEFINER clause from .sql files. When enabled, definer stripping affects command behavior as follows:

• skeema init will omit all DEFINER clauses as it writes new *.sql files.
• skeema format will remove DEFINER clauses from *.sql files as it rewrites them.
• skeema lint with the format option enabled (as it is by default) will remove DEFINER clauses just like skeema format.
• skeema pull will remove DEFINER clauses from either all definitions (with format enabled, as it is by default) or just new/modified definitions (with format disabled, e.g. skeema pull --skip-format).
• skeema diff and skeema push will completely ignore DEFINER clauses when comparing or creating objects, regardless of whether or not the DEFINER clauses are still present in *.sql files.
• When linting all objects in skeema lint, or linting modified objects in skeema diff and skeema push, the definer linting options have no effect since DEFINER clauses are ignored entirely.

The strip-definer option defaults to value “auto”, which will enable the definer-stripping behavior whenever Skeema’s user lacks the SUPER privilege, or the newer finer-grained privileges SET_USER_ID (MySQL 8.0+), SET_ANY_DEFINER (MySQL 8.2+), or SET USER (MariaDB 10.5+). If you instead wish to explicitly enable or disable the behavior (regardless of user privilege grants), you may specify the option like a boolean. For example:

• To always strip definers even if you have elevated privileges, specify --strip-definer on the command-line or just strip-definer in an option file. As with booleans, the =true value is implied and may be omitted.
• To keep definers intact even though you don’t have elevated privileges, specify --skip-strip-definer or --strip-definer=false on the command-line, or skip-strip-definer or strip-definer=false in an option file.

In Skeema Premium v1.8.1 through v1.10.0, a bug prevented definer stripping from being applied to stored procedures and functions in workspace contexts. If you encounter DEFINER-related workspace DDL errors, simply upgrade to the latest version of Skeema to resolve this problem. Alternatively, manually remove DEFINER clauses from any CREATE PROCEDURE or CREATE FUNCTION statements in your *.sql files as a workaround without upgrading.

Note that Skeema Cloud Linter operates with full privileges on an ephemeral containerized database instance. This means it treats “auto” as “false”, so that the definer linting options still work properly by default. To strip DEFINER clauses from diffs generated by Cloud Linter, put either strip-definer or loose-strip-definer in a .skeema config file. (The loose prefix can be used to maintain compatibility with Skeema Community Edition.)