MySQL:The user specified as a definer does not exist (error 1449)-Solutions

MySQL:The user specified as a definer does not exist (error 1449)-Solutions

The self-explanatory error that led this post is:
MySQL error 1449: The user specified as a definer does not exist.
I wrote about DEFINER & INVOKER SQL SECURITY in MySQL long back in early 2012 which covers the explanation of how they work WRT stored routines in MySQL!

Here I’ll try to extend it little more with examples for the error and provide 3 solutions.

We will create a simple procedure to return count from table ‘a’ of database ‘test’ and a specific user as a DEFINER.

mysql> grant all on test.* to 'spuser'@'localhost' identified by 'sppass';
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
### Doc: If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.


mysql> DROP PROCEDURE IF EXISTS myproc;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE DEFINER='spuser'@'localhost' PROCEDURE myproc()
    -> BEGIN
    ->   select count(*) from test.a;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> call test.myproc();
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Alright the procedure call above worked fine as expected.
Now let’s create a trouble! Let’s drop a user and try to see what do we get here.

mysql> drop user 'spuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> call test.myproc();
ERROR 1449 (HY000): The user specified as a definer ('spuser'@'localhost') does not exist

Hmmm… This is the error I wanted to point & explain. I encourage you to refer the DEFINER & INVOKER in SQL SECURITY explained in my previous article and ofcourse MySQL documentation is a bible.

Well so as the error says it is expecting a user which is not present. So the easy way out here is to create the dropped user, reload privileges and make a call.

Solution: 1

mysql> grant all privileges on test.* to 'spuser'@'localhost' identified by 'sppass';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> call test.myproc();
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Though we might not want to get into troubles at first! So how can we avoid from getting in to this situation?

The answer is using “SQL SECURITY” defined as “INVOKER”. SQL SECURITY here defines that the procedure will run under the INVOKER’s privileges. (Default is DEFINER)
Specifying INVOKER we are free from the dependency of DEFINER user.

Let’s test as follows:
– Create procedure with SQL SECURITY specified as INVOKER.
– Drop definer user
– call the procedure and…

Solution: 2

mysql> DROP PROCEDURE IF EXISTS myproc;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE DEFINER='spuser'@'localhost' PROCEDURE myproc()
    -> SQL SECURITY INVOKER
    -> BEGIN
    ->   select count(*) from test.a;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> drop user 'spuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> call test.myproc();
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Hmmm so this look good! No error & stored procedure works well…even if we lost the user who created the procedure!
But there is an obvious understanding that the SQL SECURITY INVOKER clause may behave differently depending on privileges of the user who calls it.

Alright, finally I’ll add one more way to resolve the error: “MySQL error 1449: The user specified as a definer does not exist”
The stored procedure or say MySQL routines are stored in mysql.proc table which also reflects in information_schema.ROUTINES table.
One can directly update the mysql.proc table’s DEFINER column to replace deleted user with existing user. Let’s do that.

Solution: 3

mysql> DROP PROCEDURE IF EXISTS myproc;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE DEFINER='spuser'@'localhost' PROCEDURE myproc()
    -> BEGIN
    ->   select count(*) from test.a;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> select * from mysql.proc where name='myproc';
+------+------+-----------+---------------+----------+-----------------+------------------+---------------+------------+---------+------------------------------------------+------------------+---------------------+---------------------+----------+---------+----------------------+----------------------+-------------------+------------------------------------------+
| db   | name | type      | specific_name | language | sql_data_access | is_deterministic | security_type | param_list | returns | body                                     | definer          | created             | modified            | sql_mode | comment | character_set_client | collation_connection | db_collation      | body_utf8                                |
+------+------+-----------+---------------+----------+-----------------+------------------+---------------+------------+---------+------------------------------------------+------------------+---------------------+---------------------+----------+---------+----------------------+----------------------+-------------------+------------------------------------------+
| test | myproc | PROCEDURE | myproc        | SQL      | CONTAINS_SQL    | NO               | DEFINER       |            |         | BEGIN
  select count(*) from test.a;
END | spuser@localhost | 2015-03-20 23:57:53 | 2015-03-20 23:57:53 |          |         | utf8                 | utf8_general_ci      | latin1_swedish_ci | BEGIN
  select count(*) from test.a;
END |
+------+------+-----------+---------------+----------+-----------------+------------------+---------------+------------+---------+------------------------------------------+------------------+---------------------+---------------------+----------+---------+----------------------+----------------------+-------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> drop user 'spuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> call test.myproc();
ERROR 1449 (HY000): The user specified as a definer ('spuser'@'localhost') does not exist

mysql> update mysql.proc set  definer='root@localhost' where name='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from routines where routine_name='myproc';
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                       | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------+-----------------+----------------+----------------------+----------------------+--------------------+
| myproc          | def             | test           | myproc         | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL | NULL               | NULL           | NULL           | SQL          | BEGIN
  select count(*) from test.a;
END | NULL          | NULL              | SQL             | NO               | CONTAINS SQL    | NULL     | DEFINER       | 2015-03-20 23:58:51 | 2015-03-20 23:57:53 |          |                 | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------+-----------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.02 sec)

mysql> exit
Bye
root@ubuntu:~# mysql -uroot -pkedar
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> call test.myproc();
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

Conclusion: The error “Definer does not exist” can be resolved by atleast 3 ways:
– Create the DEFINER (user).
– Change the DEFINER by updating mysql.proc.
– Bring in the INVOKER (with caution).

Well as such we updated DEFINER column in mysql.proc table to avoid user creation.
Do you see a possibility of 4th solution to update security_type column of mysql.proc to INVOKER? See if that works! 

Let me know.

References:
1. http://dev.mysql.com/doc/refman/5.0/en/stored-programs-security.html
2. http://kedar.nitty-witty.com/blog/access-control-in-mysql-stored-routines-by-example-definer-invoker-sql-security

Update:
Why “flush tables” commands are “strikethrough-ed” –> because they’re not required & they better stay to convey the same message of what Jaime wrote to pass-on.