代码改变世界

ERROR 1045 (28000): Access denied for user xxx & ERROR 1449 (HY000): The user specified as a definer xxx does not exists

2018-03-07 17:04  潇湘隐者  阅读(...)  评论(...编辑  收藏

 

今天在一个修改过权限的MySQL数据库遇到了ERROR 1045 (28000): Access denied for user 'xxx'@'xxx.xxx.xxx.xxx' (using password: YES)ERROR 1449 (HY000): The user specified as a definer ('xxx'@'xx') does not exist 错误,花了点时间研究并重现该错误,并将其整理在此篇文章。

 

在测试数据库MyDB,我们创建了一个mydbadmin的账号,任意IP地址都可以访问该数据库,如下所示:

 

mysql> GRANT ALL PRIVILEGES ON `MyDB`.* TO 'mydbadmin'@'%' IDENTIFIED BY 'mydbadmin13s5';
Query OK, 0 rows affected (0.03 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

然后以这个mydbadmin登录数据库,创建一个视图v_student. 当然,你也可以创建存储过程或是函数等其他对象,它们也都会遇到这个错误。

 

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id   | int(11)     | YES  |     | NULL    |       |
| stu_name | varchar(12) | YES  |     | NULL    |       |
| sex      | int(11)     | YES  |     | NULL    |       |
| grade    | int(11)     | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
 
mysql> create or replace view v_student
    -> as
    ->   select stu_name, sex, age
    ->   from student
    ->   where grade >=3;
Query OK, 0 rows affected (0.02 sec)

 

 

假如现在检查时发现任意IP都可以访问这个账号是不符合安全规范的,然后删除了这个账号(如果你用rename user 也会遇到这个问题),重建了该账号。此时你在较大权限的用户下就会遇到ERROR 1449 (HY000): The user specified as a definer ('mydbadmin'@'%') does not exist错误。如下所示:

 

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> select user,host from mysql.user where user='mydbadmin';
+-----------+------+
| user      | host |
+-----------+------+
| mydbadmin | %    |
+-----------+------+
1 row in set (0.00 sec)
 
mysql> 
mysql> drop user mydbadmin@'%';
Query OK, 0 rows affected (0.02 sec)
 
mysql> select count(*) from v_student;
ERROR 1449 (HY000): The user specified as a definer ('mydbadmin'@'%') does not exist
mysql> 
mysql> select user,host from mysql.user where user='mydbadmin';
Empty set (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON `MyDB`.* TO 'mydbadmin'@'192.168.%' IDENTIFIED BY 'mydbadmin135';
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> 

 

然后你以在客户端使用mydbadmin登录后,查询视图就会报ERROR 1045 (28000): Access denied for user 'mydbadmin'@'192.168.%' (using password: YES)

 

mysql> select user();
+-------------------------+
| user()                  |
+-------------------------+
| mydbadmin@192.168.7.43 |
+-------------------------+
1 row in set (0.01 sec)
 
mysql> select * from v_student;
ERROR 1045 (28000): Access denied for user 'mydbadmin'@'192.168.%' (using password: YES)

 

clip_image001

 

 

原因分析

 

 

出现这个问题,是因为账号mydbadmin@`%`已经不存在了。而视图指定DEFINERmydbadmin@`%`,此时创建者不存在了。而SQL SECURITY也是DEFINER,所以MySQL认为现在的用户无权限访问该视图。所以有下面一些方法来解决这个错误。

 

 

 

解决方案

 

 

1:重建视图(存储过程或函数)即可解决问题。不过对于数据库视图很多的情况,这个方法略显笨拙和繁琐。

 

 

mysql> show create view v_student;
mysql> create or replace view v_student
    -> as
    ->     select stu_name, sex, age
    ->     from student
    ->     where grade >=3;
Query OK, 0 rows affected (0.00 sec)

 

当然可以批量生成相关SQL,类似于下面SQL

 

SELECT CONCAT("alter DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") 
FROM information_schema.VIEWS 
WHERE DEFINER=''mydbadmin@%';

 

 

 

2:如果创建视图(存储过程或函数)时,使用SQL SECURITY INVOKER,就可以避免出现这种情况。

 

clip_image002

 

 

如下所示,创建视图时指定SQL SECURITY INVOKER.

 

SQL SECURITY { DEFINER | INVOKER } :指明谁有权限来执行。默认情况下,系统指定为DEFINE.

DEFINER 表示按定义者拥有的权限来执行

INVOKER 表示用调用者的权限来执行。

 

mysql> use MyDB;
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> select user();
+-------------------------+
| user()                  |
+-------------------------+
| mydbadmin@192.168.7.218 |
+-------------------------+
1 row in set (0.00 sec)
mysql> create or replace definer='mydbadmin'@'%' 
    -> sql security invoker
    -> view  v_student
    -> as
    ->     select stu_name, sex, age
    ->     from student
    ->     where grade >=3;
Query OK, 0 rows affected (0.00 sec)

 

 

删除用户mydbadmin@'%',此时你会发现还能执行。

 

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> drop user mydbadmin@'%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select count(*) from v_student;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
 
mysql>

 

clip_image003

 

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON `MyDB`.* TO 'mydbadmin'@'192.168.%' IDENTIFIED BY 'mydbadmin135';
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

 

 

mysql> select user();
+-------------------------+
| user()                  |
+-------------------------+
| mydbadmin@192.168.7.218 |
+-------------------------+
1 row in set (0.00 sec)
 
mysql> select count(*) from v_student;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
 
mysql>

 

 

3:这种方法只针对于存储过程或函数 ,对于视图而言,由于information_schema.VIEWS是无法修改的。所以无法使用此方法。如下测试所示:

 

 

mysql> UPDATE information_schema.VIEWS

    -> SET DEFINER='root@localhost'

    -> WHERE TABLE_NAME='v_student';

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

 

 

mysql> select user();
+-------------------------+
| user()                  |
+-------------------------+
| mydbadmin@192.168.7.34 |
+-------------------------+
1 row in set (0.00 sec)
 
mysql> use MyDB;
Database changed
mysql> DELIMITER &&
mysql> CREATE DEFINER='mydbadmin'@'%' PROCEDURE prc_my_test()
    -> BEGIN  
    ->      SELECT COUNT(*) FROM student;
    -> END &&
Query OK, 0 rows affected (0.03 sec)
 
mysql> DELIMITER ;
mysql> 

 

 

 

 

mysql>  select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> select db, name, type ,security_type, definer
    -> from mysql.proc
    -> where name='prc_my_test';
+------+-------------+-----------+---------------+-------------+
| db   | name        | type      | security_type | definer     |
+------+-------------+-----------+---------------+-------------+
| MyDB | prc_my_test | PROCEDURE | DEFINER       | mydbadmin@% |
+------+-------------+-----------+---------------+-------------+
1 row in set (0.00 sec)
 
mysql> call prc_my_test();
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> drop user mydbadmin@'%';
Query OK, 0 rows affected (0.01 sec)
 
mysql> call prc_my_test();
ERROR 1449 (HY000): The user specified as a definer ('mydbadmin'@'%') does not exist
mysql> update mysql.proc set  definer='root@localhost' where name='prc_my_test';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select db, name, type ,security_type, definer
    -> from mysql.proc
    -> where name='prc_my_test';
+------+-------------+-----------+---------------+----------------+
| db   | name        | type      | security_type | definer        |
+------+-------------+-----------+---------------+----------------+
| MyDB | prc_my_test | PROCEDURE | DEFINER       | root@localhost |
+------+-------------+-----------+---------------+----------------+
1 row in set (0.00 sec)
 
mysql>  call prc_my_test();
ERROR 1449 (HY000): The user specified as a definer ('mydbadmin'@'%') does not exist
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql>  call prc_my_test();
ERROR 1449 (HY000): The user specified as a definer ('mydbadmin'@'%') does not exist
mysql> exit
Bye

 

 

如上所示,必须退出重新登录,才能生效,如果更新完mysql.proc后,不退出当前会话,依然会报ERROR 1449 (HY000): The user specified as a definer ('mydbadmin'@'%') does not exist错误。

 

root@DB-Server ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.6.20-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
 
Copyright (c) 2000, 2014, 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 MyDB;
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 prc_my_test();
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)
 
Query OK, 0 rows affected (0.01 sec)

 

 

这种方法,对于存储过程或函数,意义在于可以批量修改,非常快捷方便。唯一比较遗憾的是对于VIEW,无法使用。

 

 

4: 重新创建账号'mydbadmin'@'%' ,不过像这个案例,本身是处于安全考虑,限制能够访问数据库的IP,那么此时这种方案就不太可行,如果只是误删用户,那么这种方案就比较有效。

 

mysql> drop user 'mydbadmin'@'%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select count(*) from v_student;
ERROR 1449 (HY000): The user specified as a definer ('mydbadmin'@'%') does not exist
mysql> GRANT ALL PRIVILEGES ON `MyDB`.* TO 'mydbadmin'@'%' IDENTIFIED BY 'mydbadmin13s5';
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select count(*) from v_student;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
 
mysql> 

 

clip_image004

 

 

另外,对于存储过程、函数、定时事件、视图,都可以参考上面方法进行,其中定时事件主要修改mysql.event下的表。

 

 

 update mysql.event set definer='root@localhost';

 

 

 

参考资料:

 

http://kedar.nitty-witty.com/blog/solutions-mysql-error-1449-the-user-specified-as-a-definer-does-not-exist