summaryrefslogtreecommitdiff
path: root/mysql-test/main/skip_grants.test
blob: 5f79404e7e47edbff135a8cde67c8af1ed2c574a (plain)
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
# This tests not performed with embedded server
-- source include/not_embedded.inc

use test;

#
# BUG#16777: Can not create trigger nor view w/o definer if --skip-grant-tables
# specified
#
# Also, the following test cases have been moved here:
#   - test that we can create VIEW if privileges check switched off has been
#     moved here;
#   - test that we can create and drop procedure without warnings (BUG#9993);
#   - BUG#17595: "DROP FUNCTION IF EXISTS" crashes server;
#   - BUG#13504: creation view with DEFINER clause if --skip-grant-tables
#

# Prepare.

--disable_warnings

DROP VIEW IF EXISTS v1;
DROP VIEW IF EXISTS v2;
DROP VIEW IF EXISTS v3;

DROP TABLE IF EXISTS t1;

DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p3;

DROP FUNCTION IF EXISTS f1;
DROP FUNCTION IF EXISTS f2;
DROP FUNCTION IF EXISTS f3;

--enable_warnings

# Test case.

CREATE TABLE t1(c INT);

# - try to create with implicit definer (definer would be ''@'');

CREATE TRIGGER t1_bi BEFORE INSERT ON t1
  FOR EACH ROW
    SET @a = 1;

CREATE VIEW v1 AS SELECT * FROM t1;

CREATE PROCEDURE p1()
  SELECT 1;

CREATE FUNCTION f1() RETURNS INT
  RETURN 1;

# - try to create with explicit definer;

CREATE DEFINER=a@b TRIGGER ti_ai AFTER INSERT ON t1
  FOR EACH ROW
    SET @b = 1;

CREATE DEFINER=a@b VIEW v2 AS SELECT * FROM t1;

CREATE DEFINER=a@b PROCEDURE p2()
  SELECT 2;

CREATE DEFINER=a@b FUNCTION f2() RETURNS INT
  RETURN 2;

# - try to create with explicit definer with empty host;

CREATE DEFINER=a@'' TRIGGER ti_bu BEFORE UPDATE ON t1
  FOR EACH ROW
    SET @c = 1;

CREATE DEFINER=a@'' VIEW v3 AS SELECT * FROM t1;

CREATE DEFINER=a@'' PROCEDURE p3()
  SELECT 3;

CREATE DEFINER=a@'' FUNCTION f3() RETURNS INT
  RETURN 3;

# - check that empty host name is treated correctly;

SHOW CREATE VIEW v3;

SHOW CREATE PROCEDURE p3;

SHOW CREATE FUNCTION f3;

# Cleanup.

DROP TRIGGER t1_bi;
DROP TRIGGER ti_ai;
DROP TRIGGER ti_bu;

DROP VIEW v1;
DROP VIEW v2;
DROP VIEW v3;

DROP TABLE t1;

DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;

DROP FUNCTION f1;
DROP FUNCTION f2;
DROP FUNCTION f3;

#
# Bug #26807 "set global event_scheduler=1" and --skip-grant-tables crashes server
#
set global event_scheduler=1;
set global event_scheduler=0;

#
# Bug#26285 Selecting information_schema crahes server
#
select count(*) from information_schema.COLUMN_PRIVILEGES;
select count(*) from information_schema.SCHEMA_PRIVILEGES;
select count(*) from information_schema.TABLE_PRIVILEGES;
select count(*) from information_schema.USER_PRIVILEGES;
--echo End of 5.0 tests

--echo #
--echo # Bug#29817 Queries with UDF fail with non-descriptive error
--echo # if mysql.proc is missing
--echo #
--error ER_SP_DOES_NOT_EXIST
select no_such_function(1);

--echo End of 5.1 tests

#
# MDEV-8280 crash in 'show global status' with --skip-grant-tables
#
show global status like 'Acl%';