This is a 3 part blog series:

• Part 1: The SYSTEM_USER Dynamic Privilege
• Part 2 : Partial Revokes from Database Objects
• Part 3 : How to create multiple accounts for an app?

To modify users, you must have the CREATE USER privilege or the UPDATE privilege on the mysql schema. We have added the SYSTEM_USER dynamic privilege to make it possible to protect users against the first case: modification by other users who have the CREATE USER privilege. An account that has the SYSTEM_USER privilege cannot be modified by an account that does not have SYSTEM_USER, even if that account has CREATE USER.

In this article we cover the first use case. Preventing users from being modified by accounts that have the ‘UPDATE’ privilege on the mysql schema is  covered in  next blog post.

How does the SYSTEM_USER privilege work?

The SYSTEM_USER privilege enforces the convention that if you are modifying a user who is granted the SYSTEM_USER privilege, then in addition to the privileges required to modify users, you also need to have the SYSTEM_USER privilege. In other words, a user who has the SYSTEM_USER and CREATE USER privileges can modify users who have the SYSTEM_USER privilege.

Let us understand better through examples:

As root, create two users. One user has the CREATE USER privilege and the other has the SYSTEM_USER privilege.

Now, try to change the password of the elite_user from the admin_user.

Go back to the root user session and create another user who has both privileges.

Try to modify elite_user through the newly created user.

What if a privilege is granted through the roles?

If a role is granted to a user then the user gets the capabilities of the role in a given session only after activating that role. If a user is not granted SYSTEM_USER directly but only through one or more roles, sessions for the user do not have SYSTEM_USER unless a role with SYSTEM_USER is activated. Once such a role is activated, the user becomes capable of modifying other users who have the SYSTEM_USER privilege.

Let us understand through examples.

Create a role that has the SYSTEM_USER privilege. Grant that role to admin_user who we created in the previous example.

Even through the role is granted to the admin_user, that user is able to modify elite_user only after activating that role.

When can the SYSTEM_USER privilege be granted to or revokes from other users?

Only if you have the SYSTEM_USER privilege granted with GRANT OPTION.  As we can see even if the user has the SUPER privilege with GRANT OPTION it cannot revoke SYSTEM_USER from the other user.

If the user has SYSTEM_USER with GRANT OPTION, it is able to revoke SYSTEM_USER from the other user.

Can you escalate the privileges through the function or stored programs?

If you have the SET_USER_ID privilege then you can create a stored program with the DEFINER attribute and set the DEFINER attribute to any user.

It means you could grant yourself the SYSTEM_USER privilege by specifying the user in definer attribute who has SYSTEM_USER, clever, no ?

Well, not really. To set the DEFINER to a user who has the SYSTEM_USER privilege you must also have the SYSTEM_USER privilege in additional the SET_USER_ID privilege.

Let us understand through examples:

There are three users. As shown here, admin_user has privileges to create stored programs, elite_user can execute programs and has SYSTEM_USER, and regular_user can execute programs but does not have SYSTEM_USER.

admin_user tries to create a stored program using a definer that has the SYSTEM_USER privilege but it gets an error.

Attempting to grant the SYSTEM_USER privilege within the stored program is also foiled.

The behavior of other database objects that accepts the DEFINER attribute is similar to what is demonstrated above. These objects are :   function, view, trigger, event.

There will be interesting scenarios in case of roles. I should not have fun all alone, so I leave those scenarios for you to try 🙂

Which users are granted SYSTEM_USER privilege in the new or upgraded MySQL installations?

As a starting point there must be at least one user who has the SYSTEM_USER privilege.  At the time of upgrading the server, users who have the SET_USER_ID privilege are granted the SYSTEM_USER privilege.  If no users have the SET_USER_ID privilege, then users who have SUPER privilege are granted the SYSTEM_USER privilege.  We chose the SET_USER_ID for making this decision because it is one of the administrative privileges. It enables a user to specify any account in the DEFINER attribute of a view or stored program, which is an operation to which SYSTEM_USER applies.

How does the SYSTEM_USER privilege relate to the system_user() function or OS users ?

The system_user()  function returns the current mysql user who is connected to the server. It is the synonym of user() function.   Often operating system users are also often referred to as system users.

As you may already have noticed, the SYSTEM_USER privilege is not related to either system_user() or OS users.  We named the privilege as ‘SYSTEM_USER’ because it aptly described the intent.

Conclusion

In this blog post we learned the purpose of SYSTEM_USER privilege and how does it work. In the next blog post, we shall take a closer look at how to create partial revokes on the database objects in order to prevent modifying the users in the mysql schema directly.

In the final blog post  of this series, we shall put together the pieces of the puzzle to create the multiple users for an app.

Thank you for using MySQL!