1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
include/master-slave.inc
[connection master]
connection master;
SET sql_mode=ORACLE;
CREATE PACKAGE pack AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY pack AS
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 10;
END;
PROCEDURE p1 AS
BEGIN
SELECT f1();
END;
END pack;
$$
connection slave;
connection slave;
SELECT * FROM mysql.proc WHERE db='test' AND name='pack';
db test
name pack
type PACKAGE
specific_name pack
language SQL
sql_data_access CONTAINS_SQL
is_deterministic NO
security_type DEFINER
param_list
returns
body AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END
definer root@localhost
created #
modified #
sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
comment
character_set_client latin1
collation_connection latin1_swedish_ci
db_collation latin1_swedish_ci
body_utf8
aggregate NONE
db test
name pack
type PACKAGE BODY
specific_name pack
language SQL
sql_data_access CONTAINS_SQL
is_deterministic NO
security_type DEFINER
param_list
returns
body AS
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 10;
END;
PROCEDURE p1 AS
BEGIN
SELECT f1();
END;
END
definer root@localhost
created #
modified #
sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
comment
character_set_client latin1
collation_connection latin1_swedish_ci
db_collation latin1_swedish_ci
body_utf8
aggregate NONE
SELECT * FROM mysql.proc WHERE db='test' AND name LIKE 'pack.%';
SET @@sql_mode=ORACLE;
SELECT pack.f1();
pack.f1()
10
CALL pack.p1();
f1()
10
SET @@sql_mode=DEFAULT;
connection master;
DROP PACKAGE pack;
connection slave;
connection slave;
SELECT COUNT(*) FROM mysql.proc WHERE db='test' AND name='pack';
COUNT(*)
0
#
# Creating a package with a COMMENT
#
connection master;
CREATE PACKAGE p1 COMMENT 'package-p1-comment' AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 COMMENT 'package-body-p1-comment' AS
PROCEDURE p1 AS
BEGIN
NULL;
END;
END;
$$
SELECT definer, name, security_type, type, `comment` FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
definer name security_type type comment
root@localhost p1 DEFINER PACKAGE package-p1-comment
root@localhost p1 DEFINER PACKAGE BODY package-body-p1-comment
connection slave;
SELECT definer, name, security_type, type, `comment` FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
definer name security_type type comment
root@localhost p1 DEFINER PACKAGE package-p1-comment
root@localhost p1 DEFINER PACKAGE BODY package-body-p1-comment
connection master;
DROP PACKAGE p1;
connection slave;
#
# Creating a package with a different DEFINER
#
connection master;
CREATE DEFINER=xxx@localhost PACKAGE p1 AS
PROCEDURE p1;
END;
$$
Warnings:
Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist
CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS
PROCEDURE p1 AS
BEGIN
NULL;
END;
END;
$$
Warnings:
Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist
SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
definer name security_type type
xxx@localhost p1 DEFINER PACKAGE
xxx@localhost p1 DEFINER PACKAGE BODY
connection slave;
SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
definer name security_type type
xxx@localhost p1 DEFINER PACKAGE
xxx@localhost p1 DEFINER PACKAGE BODY
connection master;
DROP PACKAGE p1;
connection slave;
#
# Creating a package with a different DEFINER + SQL SECURITY INVOKER
#
connection master;
CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS
PROCEDURE p1;
END;
$$
Warnings:
Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist
CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS
PROCEDURE p1 AS
BEGIN
NULL;
END;
END;
$$
Warnings:
Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist
SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
definer name security_type type
xxx@localhost p1 INVOKER PACKAGE
xxx@localhost p1 INVOKER PACKAGE BODY
connection slave;
SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
definer name security_type type
xxx@localhost p1 INVOKER PACKAGE
xxx@localhost p1 INVOKER PACKAGE BODY
connection master;
DROP PACKAGE p1;
connection slave;
include/rpl_end.inc
|
