MemSQL follows the definer security model for objects such as views and procedures. In other words, a user who has access to execute a view (for example) will have access to the underlying objects so long as the view’s creator (i.e., definer) does. However, if the definer user is dropped, or their access is sufficiently reduced, then no one will be able to execute the view. What are the best practices to avoid problems like this in an environment that consists of many developers who will be defining objects such as views? Should all development be done using a shared user that will not be dropped? Presumably, unless you’re a super user, you can’t specify a definer other than yourself, as that would be a security hole if you could create a view with a definer such as root. Is there any way to use invoker security instead?
We don’t support invoker security. If the definer of a routine is dropped, it can’t be executed any more. But you could get the definition of a routine by having root running SHOW CREATE PROCEDURE. Then root could drop it and redefine it or have somebody else redefine it who has appropriate privileges.
I don’t have a firm answer for you about best practices, but having a small set of users owned by the application and using them to create routines seems like a reasonable solution for many shops. Then if someone leaves, dropping their userid won’t make routines not runnable.
Just my 2 cents:
You might want to first decide how many different security domains you want and corresponding have at-least those many users which you can use as “definers” for Views/Table Valued Functions/Stored Procedure/User-defined functions. This will allow you to assign different permissions to each of these “definers” and correspondingly, once you know what security permissions an object should require/given, you can decide on the appropriate “definer” to create that object.
You probably want to chose those users as “definers” which will have stable security permissions (i.e. less frequently modified).
You can have a single “definer” but that will sort of defeat the purpose of a security model as all objects will be restricted to same permissions (unless you don’t particularly want to use the security permissions for objects or you just want to limit all the objects to subsets of permissions e.g. you can remove INSERT, UPDATE,DELETE permissions from “definer” to prevent objects from modifying data)
You control which users can be “definers” as they would need permissions like CREATE VIEW/ ALTER VIEW.