当前位置: 澳门新濠3559 > 数据库 > 正文

发现确实没有test这个用户,我们创建了一个myd

时间:2019-12-09 01:05来源:数据库
发现确实没有test这个用户,我们创建了一个mydbadmin的账号澳门新濠3559。  1 CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()2 BEGIN3 SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;4 END; 在默认

发现确实没有test这个用户,我们创建了一个mydbadmin的账号澳门新濠3559。 

1 CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
2    BEGIN
3        SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
4    END;

在默认情况下,MySQL将检查创建者的权限。假设用户A创建了存储过程p()访问表T,并把execute的权限赋给了B,即使用户B没有访问表T的权限,也能够通过执行存储过程p()访问表T。

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

【函数的创建语句】

并把执行该存储过程的权限赋给用户b:

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

 MySQL中存在的用户是 : admin@192.168.%.%

  1. b@(none) 05:58:20>call portal.p();  
  2. +----------+  
  3. | COUNT(*) |  
  4. +----------+  
  5. |       25 |  
  6. +----------+  
  7. 1 row in set (0.00 sec)  
  8.   
  9. Query OK, 0 rows affected (0.00 sec)  

    -> WHERE TABLE_NAME='v_student';

  案例一:DEFINER

 

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

   DEFINER模式下,默认DEFINER=CURRENT_USER,在存储过程执行时,mysql会检查DEFINER定义的用户'user_name'@'host_name'的权限;

 

 

 此时,会遇到报错,提示”ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation“

 

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

 修复DEFINER='admin@192.168.%.%',或者去掉 DEFINER参数,都可以恢复正常。

 

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

案例二:创建存储过程

这时候,已用户b连接后通过执行存储过程可以获得t表的访问权限:

澳门新濠3559 1

 

  1. root@(none) 05:54:28>grant execute on procedure portal.p to b;  
  2. Query OK, 0 rows affected (0.00 sec)  

 

   默认情况下,使用DEFINER方式,此时调用存储过程的用户必须有存储过程的EXECUTE权限,并且DEFINER指定的用户必须是在mysql.user表中存在的用户。

接着,以用户a创建存储过程p():

 

   案例一中是存在问题的,存储过程的调用者和拥有者都是admin@192.168.1.1,但是DEFINER却是admin@%,这是由于创建存储过程的命令是由root用户执行的,所以没有遇到案例二中的报错。

 

 

其中,mysql.proc是固定的,definer即要改为的用户名,name为存储过程名,db为数据库名

[sql] view plaincopyprint?

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> 
 1 -- 查看存储过程的创建语句:
 2 
 3 show create procedure 存储过程名;
 4 
 5 -- 查看存储过程的信息:
 6 
 7 show procedure status like '存储过程名'G
 8 
 9 -- 查看存储过程的Definer信息:
10 
11 select db,name,type,sql_security,definer from mysql.proc where  type='PROCEDURE' and db='数据库名' ;
12 
13 -- 修改存储过程的DEFINER:
14 
15 update  mysql.proc  set `definer` ='admin@192.168.%.%' where db like 'db_%';

 

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)

   在这个案例中,虽然存储过程语句中仍然带有DEFINER参数,但是由于SQL SECURITY指定了INVOKER,所以在存储过程执行的时候,会以调用者的额身份去执行。此时这个存储过程是否能成功执行,取决于调用者是否有mysql.user表的查询权限。

 

 

1 CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
2        SQL SECURITY INVOKER
3    BEGIN
4        SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
5    END;

下面看一个例子:

 

1 The user specified as a definer ('test'@'%') does not exist 1449

如果你不想使用MySQL的默认设置,可以在定义存储程序和视图时在create语句里使用definer

account字句指定定义者,这样在执行存储程序和视图时,将检查definer的权限,而不是创建者的权限。

举个例子,当你用root 创建一个存储过程时,在默认情况下,在执行该存储过程时,执行者将获得root的权限,但当你加上definer = A后,执行者只能获得A的权限。

但是definer还是没能完全解决上面提到的安全隐患,别急,MySQL还提供了SQL SECURITY选项来控制权限,它有两个取值:

1)DEFINER:以定义者的权限执行(默认)

2)INVOKER:以调用者的权限执行

如果你不想在存储程序或试图在执行时的权限多于调用者,就设置SQL SECURITY INVOKER即可。

例如,下面的试图将访问mysql.user,并设置了SQL SECURITY INVOKER选项,这样如果调用者没有访问mysql.user的权限,则无法通过权限检查。

 

[sql] view plaincopyprint?

 

  1. create sql security invoker view v  
  2.   as select * from mysql.user;  

注意:因为触发器和事件是由系统调用的,没有调用者的概念,所以它们没有SQL SECURITY选项。

参考资料:

说明:

 

 

保持definer安全性

 

 

引申阅读:mysql存储过程的definer和invoker

 

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

 存储过程的DEFINER是   : admin@%

[sql] view plaincopyprint?

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

 使用用户admin@192.168.1.1连接mysql,该用户有test库的all privileges,执行创建存储过程的操作:

 

 

查看了自己mysql的用户表后,发现确实没有test这个用户,但是我程序用的是root登录的,所以感觉有些莫名其妙。

  1. b@(none) 05:58:40>select count(*) from portal.t;  
  2. ERROR 1142 (42000): SELECT command denied to user 'b'@'192.168.1.15' for table 't'  

 

   INVOKER模式下,在存储过程执行时,会检查存储过程调用者的权限。

 

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

   此时admin@192.168.1.1是可以访问数据库的,因为它符合admin@192.168.%.%的授权规则,但是当它调用DEFINER='admin@%'的存储过程的时候,mysql会检查mysql.user用户表中是否存在admin@%这个用户,mysql的检查结果是admin@%这个用户不存在,此时就会返回报错,提示“Ther user specified as a definer ('admin@%') does not exist.。

  1. DELIMITER $$  
  2. USE portal$$  
  3. CREATE PROCEDURE `p`()  
  4. BEGIN  
  5.   SELECT COUNT(*) FROM portal.t;  
  6. END$$  
  7. DELIMITER ;  

 

1 ALTER PROCEDURE proc_name SQL SECURITY INVOKER 
2 ALTER PROCEDURE proc_name SQL SECURITY DEFINER

当存储过程、函数、触发器和视图创建后,不单单创建者要执行,其它用户也可能需要执行,换句话说,执行者有可能不是创建者本身,那么在执行存储过程时,MySQL是如何做权限检查的?

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

1 mysql>update mysql.proc set DEFINER='root@localhost' WHERE NAME='' AND db='mydb';
  1. root@(none) 05:39:45>create table portal.t as select * from mysql.user;  
  2. Query OK, 25 rows affected (0.16 sec)  
  3. Records: 25  Duplicates: 0  Warnings: 0  
  4.   
  5. root@(none) 05:39:55>create user a identified by 'a';  
  6. Query OK, 0 rows affected (0.02 sec)  
  7.   
  8. root@(none) 05:40:51>create user b identified by 'b';  
  9. Query OK, 0 rows affected (0.00 sec)  
  10.   
  11. root@(none) 05:40:59>grant all privileges on portal.* to a;  
  12. Query OK, 0 rows affected (0.01 sec)  

 

后来通过查资料发现,是由于自己存储过程设置的安全性为definer,而当时的那个数据库存在test这个用户且用的test用户创建的存储过程。

 

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)

【案例】

[sql] view plaincopyprint?

 

 

 

 

澳门新濠3559 2

[sql] view plaincopyprint?

 

 1 delimiter //    -- 声明分隔符(命令结束符)
 2 
 3 create
 4 
 5  definer = user@hostname | current_user
 6 
 7  function 函数名(参数)
 8 
 9  return 返回值类型
10 
11  comment '注释'
12 
13  sql security definer | invoker   -- sql 的安全设置
14 
15 begin
16 
17   函数的body
18 
19 end
20 //
21 
22 delimiter ;    -- 声明分隔符(命令结束符)

[sql] view plaincopyprint?

 

 

MySQL这样的设置有一定的道理,但同时也带来了安全隐患:比如如果一个用户通过创建一个存储过程来访问敏感数据,则可以调用该存储过程的所有用户都能访问敏感数据。

 

1)在navicat上进行修改

 

 

我在用程序调用存储过程时,总是提示错误:

但如果直接访问将出现权限错误:

 

【definer和invoker的解释】

首先,我们创建一个表test.t和两个用户a,b,并把权限赋予用户a

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

2)通过sql语句修改

 

【用户操作存储过程的权限】

 

   创建存储过程的时候可以指定 SQL SECURITY属性,设置为 DEFINER 或者INVOKER,用来告诉mysql在执行存储过程的时候,是以DEFINER用户的权限来执行,还是以调用者的权限来执行。

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

   在这个案例中,不论哪个用户A调用存储过程,存储过程都会以'admin'@'localhost'的权限去执行,即使这个用户A没有查询mysql.user表的权限。

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

将安全性修改为invoker

 

1)在navicat上进行修改

 

澳门新濠3559 3

    -> SET DEFINER='root@localhost'

澳门新濠3559 4

 

         存储过程中定义的DEFINER是    : admin@%

 

所以解决方法主要有以下两种:

今天在一个修改过权限的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” 错误,花了点时间研究并重现该错误,并将其整理在此篇文章。

 存储过程的调用者是   : admin@192.168.1.1

 

2)通过sql语句进行修改

 

 

 

 

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

将定义者从test改为在该服务器存在的用户(一般每个服务器都有root@localhost)

澳门新濠3559 5

最近在做一个项目,由于服务器切换,所以需要将原有服务器的mysql数据表以及存储过程导入到另一个服务器的mysql数据库中。导入完成之后以为一切是那么的简单,却没有想到总还是出现了一些莫名其妙的问题。

 

【存储过程常用命令】

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>

         MySQL中存在的用户是     : admin@192.168.%.%

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

 案例二:INVOKER

 

 1 delimiter //    -- 声明分隔符(命令结束符)
 2 
 3 create 
 4 
 5  definer = user@hostname | current_user 
 6 
 7  procedure 存储过程名 (参数)
 8 
 9  comment '注释'
10 
11  sql security definer | invoker   -- sql 的安全设置
12 
13 begin
14 
15   存储过程的body
16 
17 end
18 
19 //
20 
21 delimiter ;    -- 声明分隔符(命令结束符)

 

【存储过程的创建语法】

mysql> UPDATE information_schema.VIEWS

案例一:调用存储过程

 

  如果SQL SECURITY子句指定为DEFINER,存储过程将使用存储过程的DEFINER执行存储过程,验证调用存储过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象的权限;
  如果SQL SECURITY子句指定为INVOKER,那么MySQL将使用当前调用存储过程的用户执行此过程,并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限;

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

1 ALTER ROUTINE -- 编辑或删除存储过程
2 
3 CREATE ROUTINE -- 创建存储过程
4 
5 EXECUTE          -- 运行存储过程
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> 
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> 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)

 

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

 

 

 

 

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> 

澳门新濠3559 6

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

 

 

 

 

 

 

 

 

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

 

 

 

 

澳门新濠3559 7

 

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

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

 

 

原因分析

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

 

 

 

 

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)

 

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)
SELECT CONCAT("alter DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") 

FROM information_schema.VIEWS 

WHERE DEFINER=''mydbadmin@%';

 

 

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)

 

 

 

 

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>

编辑:数据库 本文来源:发现确实没有test这个用户,我们创建了一个myd

关键词: