1
2
3
4
5
6
7
8

mysql> select user,host from mysql.user where user = 'test';
+------+------------+
| user | host       |
+------+------------+
| test | 172.17.0.2 |
| test | 172.17.0.3 |
+------+------------+
2 rows in set (0.01 sec)

1
2

mysql> call test_proc;
ERROR 1449 (HY000): The user specified as a definer ('test'@'%') does not exist

1
2
3
4
5

-- drop user test@'%';
CREATE USER 'test'@'172.17.0.2' IDENTIFIED BY '123456';
CREATE USER 'test'@'172.17.0.3' IDENTIFIED BY '123456';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, EXECUTE ON *.* TO 'test'@'172.17.0.2';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, EXECUTE ON *.* TO 'test'@'172.17.0.3';

1
2
3
4
5
6

delimiter ;;
CREATE definer='test' PROCEDURE test_proc() 
BEGIN 
	select 1;
END ;;
delimiter ;

1
2
3
4
5
6
7

mysql> select routine_name,definer from information_schema.routines where routine_name='test_proc';
+--------------+---------+
| ROUTINE_NAME | DEFINER |
+--------------+---------+
| test_proc    | test@%  |
+--------------+---------+
1 row in set (0.00 sec)

1
2
3
4
5
6

delimiter ;;
CREATE definer='test'@'%' PROCEDURE test_proc() 
BEGIN 
	select 1;
END ;;
delimiter ;

1
2
3
4
5
6
7

mysql> select routine_name,definer from information_schema.routines where routine_name='test_proc';
+--------------+---------+
| ROUTINE_NAME | DEFINER |
+--------------+---------+
| test_proc    | test@%  |
+--------------+---------+
1 row in set (0.00 sec)

1
2

mysql> call test_proc;
ERROR 1449 (HY000): The user specified as a definer ('test'@'%') does not exist

1
2
3
4
5
6
7

mysql> select routine_name,definer from information_schema.routines where routine_name='test_proc';
+--------------+---------+
| ROUTINE_NAME | DEFINER |
+--------------+---------+
| test_proc    | test@%  |
+--------------+---------+
1 row in set (0.00 sec)

1
2

CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, EXECUTE ON *.* TO 'test'@'%';

1
2
3
4
5
6
7

mysql> call test_proc;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

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

1

drop procedure test_proc;

1
2
3
4
5
6

delimiter ;;
CREATE definer='test'@'172.17.0.2' PROCEDURE test_proc() 
BEGIN 
	select 1;
END ;;
delimiter ;

1
2
3
4
5
6
7

mysql> select routine_name,definer from information_schema.routines where routine_name='test_proc';
+--------------+-----------------+
| ROUTINE_NAME | DEFINER         |
+--------------+-----------------+
| test_proc    | test@172.17.0.2 |
+--------------+-----------------+
1 row in set (0.01 sec)

1
2
3
4
5
6
7

mysql> call test_proc;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)