Properly removing users in MySQL
Removing users in MySQL is an easy task, but there are some drawbacks to check carefully before dropping a user. Not taking these possible issues into consideration can render your application unusable. First it is important to understand the concept of user in MySQL. A user has three main functions: authentication, privileges and resources. This is different from other databases: in MySQL users don’t own objects. An object belongs to a database and there is no direct relationship between objects and users. This simplifies the process of deleting a user because no objects will be erased. But users, as I wrote before, have an associated set of privileges that define what database sessions can do, and the privileges applied both in stored programs and view execution. At the same time, procedures, functions, triggers, and views have two possible security contexts: invoker and definer. If they are created using the invoker security model, then the user calling the routine or view must have enough privileges to execute the code within the routine or view. But if created using the definer security model, the code can be executed if the definer has enough privileges to execute it. And yes, the default security model in MySQL is definer. This means that, unless declared otherwise, most routines will check the privileges for the user that created the routine. If somebody removes that user, querying the view or executing the code will fail with error. All the procedures, functions, views and triggers created by that user with the default options will become unusable. How do we fix this? The quick and dirty way is to recreate the user with a different password to avoid user logins. It is not an elegant solution but probably this is the first thing you will do while you solve the problem correctly. Another alternative, if you are running MySQL 5.7 is account locking, this feature disables login for that account but allows code and views to be executed. In any case, it is a good practice to make a backup of the user creation scripts prior to dropping the user. Percona toolkit has the utility pt-show-grants for that purpose. The elegant way to avoid the problem is to check that there are not routines or views using the definer security model and configured to run with the user privileges of the user you want to remove. There are several tables in the mysql user catalog that provide you with this information. Unfortunately there is not an easy way to change this attributes. The best thing you can do is drop and recreate those objects using different security characteristics. Let see an example: Connect to the database using an account with enough privileges to create users, databases and procedures and create a new database only for testing purposes.
mysql> create database remove_test; Query OK, 1 row affected (0,05 sec)Create a user with privileges on the database created in previous step:
mysql> grant all privileges on remove_test.* to test_user@'%' identified by 'test'; Query OK, 0 rows affected, 1 warning (0,20 sec)Connect to the database using the user created:
$ mysql -u test_user -ptest remove_testCreate a view, check the attributes and execute it. We are going to use a couple of functions that I will explain a bit later.
mysql> create view test_view as select current_user(),user(); Query OK, 0 rows affected (0,05 sec) mysql> show create view test_view\G *************************** 1. row *************************** View: test_view Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`test_user`@`%` SQL SECURITY DEFINER VIEW `test_view` AS select current_user() AS `current_user()`,user() AS `user()` character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0,00 sec) mysql> select * from test_view; +----------------+---------------------+ | current_user() | user() | +----------------+---------------------+ | test_user@% | test_user@localhost | +----------------+---------------------+ 1 row in set (0,00 sec)As we can see, the view has been created with SQL SECURITY DEFINER and DEFINER=`test_user`@`%`. The view returns the value from two functions: current_user() returns the value that matches against the mysql.user table and that defines the privileges the current session or current execution context has. The function user() returns the host you are connected from and the username specified as connection identifier. Now we reconnect to the database using the privileged account from the first step, and requery the view:
$ mysql -u root -p remove_test mysql> select * from test_view; +----------------+----------------+ | current_user() | user() | +----------------+----------------+ | test_user@% | root@localhost | +----------------+----------------+ 1 row in set (0,00 sec)The view is executed by root@localhost but with the privileges of test_user@%. It is important to note that if you monitor the execution of a routine or view, using SHOW PROCESSLIST or query against information_schema.processlist, the contents of user and host are the same that current_user() return, not the ones returned by user(). Now we will drop the user and query once again the view.
mysql> drop user test_user@'%';
Query OK, 0 rows affected (0,07 sec)
mysql> select * from test_view;
ERROR 1449 (HY000): The user specified as a definer ('test_user'@'%') does not exist In case you don’t see the error and still get the correct results, this is because the contents of the view are stored in the query cache. Clear their contents and repeat the query.
mysql> reset query cache; Query OK, 0 rows affected (0,00 sec)How to validate if it is safe to remove a user? You must query the information_schema tables to find objects than can bring problems and recreate them.
mysql> select routine_schema as db, -> routine_type as object_type, -> routine_name as object_name -> from routines -> where security_type='DEFINER' -> and definer='test_user@%' -> union all -> select trigger_schema as db, -> 'TRIGGER' as object_type, -> trigger_name as object_name -> from triggers -> where definer='test_user@%' -> union all -> select table_schema as db, -> 'VIEW' as object_type, -> table_name as object_name -> from views -> where security_type='DEFINER' -> and definer='test_user@%'; +-------------+-------------+-------------+ | db | object_type | object_name | +-------------+-------------+-------------+ | remove_test | VIEW | test_view | +-------------+-------------+-------------+ 1 row in set (0,02 sec)Replace test_user@% with the value of the user you want to remove. This way you get a list of the objects you must change to avoid problems. As I said before the best thing you can do to change the definer is recreating the objects. To obtain the creation script you should use SHOW CREATE VIEW, SHOW CREATE TRIGGER, SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION. If you want to avoid problems in the future, my recommendation is that for every application, create an account for that application, lock it with an unknown password or using account locking feature. Grant that account all the privileges needed to run the application and make sure all the views, procedures, functions and triggers have that account in the definer field.
