The SYSTEM_USER Dynamic Privilege

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.

mysql> CREATE USER admin_user, elite_user;
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT CREATE USER ON *.* TO admin_user;
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT SYSTEM_USER ON *.* TO elite_user;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT USER();
+----------------------+
| USER()               |
+----------------------+
| admin_user@localhost |
+----------------------+
1 row in set (0.00 sec)
 
mysql> ALTER USER elite_user IDENTIFIED BY 'blahblah';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

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

mysql> CREATE USER power_user;
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT CREATE USER, SYSTEM_USER ON *.* TO power_user;
Query OK, 0 rows affected (0.01 sec)

Try to modify power_user through the newly created user.

mysql> SELECT USER();
+------------------------+
| USER()                 |
+------------------------+
| power_user@localhost   |
+------------------------+
1 row in set (0.00 sec)
 
mysql> ALTER USER power_user IDENTIFIED BY 'blahblah';
Query OK, 0 rows affected (0.01 sec)

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.

mysql> CREATE ROLE elite_role;
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT SYSTEM_USER ON *.* TO elite_role;
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT elite_role to admin_user;
Query OK, 0 rows affected (0.01 sec)

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

mysql> SHOW GRANTS;
+----------------------------------------------+
| Grants for admin_user@%                      |
+----------------------------------------------+
| GRANT CREATE USER ON *.* TO `admin_user`@`%` |
| GRANT `elite_role`@`%` TO `admin_user`@`%`   |
+----------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> ALTER USER elite_user IDENTIFIED BY 'blahblah';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
 
mysql> SET ROLE elite_role;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW GRANTS;
+----------------------------------------------+
| Grants for admin_user@%                      |
+----------------------------------------------+
| GRANT CREATE USER ON *.* TO `admin_user`@`%` |
| GRANT SYSTEM_USER ON *.* TO `admin_user`@`%` |
| GRANT `elite_role`@`%` TO `admin_user`@`%`   |
+----------------------------------------------+
3 rows in set (0.00 sec)
 
mysql> ALTER USER elite_user IDENTIFIED BY 'blahblah';
Query OK, 0 rows affected (0.01 sec)

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.

mysql> SHOW GRANTS;
+-----------------------------------------------------------------------+
| Grants for admin_user@%                                               |
+-----------------------------------------------------------------------+
| GRANT SUPER, CREATE USER ON *.* TO `admin_user`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> REVOKE SYSTEM_USER ON *.* FROM elite_user;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

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

mysql> SHOW GRANTS;
+-----------------------------------------------------------------------+
| Grants for admin_user@%                                               |
+-----------------------------------------------------------------------+
| GRANT SUPER, CREATE USER ON *.* TO `admin_user`@`%` WITH GRANT OPTION |
| GRANT SYSTEM_USER ON *.* TO `admin_user`@`%` WITH GRANT OPTION        |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> REVOKE SYSTEM_USER ON *.* FROM power_user;
Query OK, 0 rows affected (0.01 sec)

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.

mysql> SHOW GRANTS FOR admin_user;
+----------------------------------------------------------------+
| Grants for admin_user@%                                        |
+----------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO `admin_user`@`%` WITH GRANT OPTION |
| GRANT SET_USER_ID ON *.* TO `admin_user`@`%`                   |
| GRANT ALL PRIVILEGES ON `test`.* TO `admin_user`@`%`           |
+----------------------------------------------------------------+
3 rows in set (0.00 sec)
 
mysql> SHOW GRANTS FOR elite_user;
+----------------------------------------------------------------+
| Grants for power_user@%                                        |
+----------------------------------------------------------------+
| GRANT EXECUTE ON *.* TO `elite_user`@`%` WITH GRANT OPTION     |
| GRANT SYSTEM_USER ON *.* TO `elite_user`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> SHOW GRANTS FOR regular_user;
+--------------------------------------------+
| Grants for regular_user@%                  |
+--------------------------------------------+
| GRANT EXECUTE ON *.* TO `regular_user`@`%` |
+--------------------------------------------+
1 row in set (0.00 sec)

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

mysql> CREATE DEFINER= elite_user PROCEDURE test.gp() GRANT SYSTEM_USER ON *.* TO CURRENT_USER();
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

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

mysql> CREATE DEFINER= regular_user PROCEDURE test.gp() GRANT SYSTEM_USER ON *.* TO CURRENT_USER();
Query OK, 0 rows affected (0.01 sec)
 
mysql> call test.gp();
ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation

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!