今天在一个修改过权限的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)
原因分析
登录查看全部
参与评论
手机查看
返回顶部