release/src/router/mysql/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc

   1 #### suite/funcs_1/storedproc/storedproc_06.inc
   2
   3 # This test cannot be used for the embedded server because we check here
   4 # privileges.
   5 --source include/not_embedded.inc
   6
   7 --source suite/funcs_1/storedproc/load_sp_tb.inc
   8
   9 # ==============================================================================
  10 # (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
  11 #
  12 # 3.1.6 Privilege checks:
  13 #
  14 #  1. Ensure that no user may create a stored procedure without the
  15 #     GRANT CREATE ROUTINE privilege.
  16 #  2. Ensure that root always has the GRANT CREATE ROUTINE privilege.
  17 #  3. Ensure that a user with the GRANT CREATE ROUTINE privilege can always
  18 #     create both a procedure and a function, on any appropriate database.
  19 #  4. Ensure that the default security provision of a stored procedure is
  20 #     SQL SECURITY DEFINER.
  21 #  5. Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
  22 #     called/executed by any user, using only the privileges (including
  23 #     database access privileges) associated with the user who created
  24 #     the stored procedure.
  25 #  6. Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
  26 #     called/executed by any user, using only the privileges (including
  27 #     database access privileges) associated with the user executing
  28 #     the stored procedure.
  29 #
  30 # ==============================================================================
  31 let $message= Section 3.1.6 - Privilege Checks:;
  32 --source include/show_msg80.inc
  33
  34
  35 connection default;
  36 USE db_storedproc_1;
  37 --source suite/funcs_1/include/show_connection.inc
  38
  39 # ------------------------------------------------------------------------------
  40 let $message= Testcase 3.1.6.1:
  41               -----------------
  42 Ensure that no user may create a stored procedure without the GRANT CREATE
  43 ROUTINE privilege.;
  44 --source include/show_msg80.inc
  45
  46 create user 'user_1'@'localhost';
  47
  48 grant all on db_storedproc_1.* to 'user_1'@'localhost';
  49 revoke create routine on db_storedproc_1.* from 'user_1'@'localhost';
  50 flush privileges;
  51
  52 --disable_warnings
  53 DROP PROCEDURE IF EXISTS sp1;
  54 --enable_warnings
  55
  56 connect (user1a, localhost, user_1, , db_storedproc_1);
  57 --source suite/funcs_1/include/show_connection.inc
  58
  59 USE db_storedproc_1;
  60
  61 delimiter //;
  62 --error ER_DBACCESS_DENIED_ERROR
  63 CREATE PROCEDURE sp1(v1 char(20))
  64 BEGIN
  65     SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
  66 END//
  67 delimiter ;//
  68
  69 disconnect user1a;
  70
  71 # add privilege again and check
  72 connection default;
  73 USE db_storedproc_1;
  74 --source suite/funcs_1/include/show_connection.inc
  75
  76 GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
  77 connect (user1b, localhost, user_1, , db_storedproc_1);
  78 --source suite/funcs_1/include/show_connection.inc
  79
  80 USE db_storedproc_1;
  81
  82 delimiter //;
  83 CREATE PROCEDURE sp1(v1 char(20))
  84 BEGIN
  85     SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
  86 END//
  87 delimiter ;//
  88 disconnect user1b;
  89
  90 # cleanup
  91 connection default;
  92 USE db_storedproc_1;
  93 --source suite/funcs_1/include/show_connection.inc
  94
  95 DROP USER 'user_1'@'localhost';
  96 DROP PROCEDURE sp1;
  97
  98
  99 # ------------------------------------------------------------------------------
 100 let $message= Testcase 3.1.6.2:
 101               -----------------
 102 Ensure that root always has the GRANT CREATE ROUTINE privilege.
 103 (checked by other testscases);
 104 --source include/show_msg80.inc
 105
 106
 107 # ------------------------------------------------------------------------------
 108 let $message= Testcase 3.1.6.3:
 109               -----------------
 110 Ensure that a user with the GRANT CREATE ROUTINE privilege can always create
 111 both a procedure and a function, on any appropriate database.
 112 --source include/show_msg80.inc
 113
 114
 115 create user 'user_1'@'localhost';
 116
 117 grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
 118 flush privileges;
 119
 120 #  disconnect default;
 121 connect (user2, localhost, user_1, , db_storedproc_1);
 122 --source suite/funcs_1/include/show_connection.inc
 123
 124 --disable_warnings
 125 DROP PROCEDURE IF EXISTS sp3;
 126 DROP FUNCTION IF EXISTS fn1;
 127 --enable_warnings
 128
 129 delimiter //;
 130 CREATE PROCEDURE sp3(v1 char(20))
 131 BEGIN
 132    SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
 133 END//
 134 delimiter ;//
 135
 136 delimiter //;
 137 CREATE FUNCTION fn1(v1 int) returns int
 138 BEGIN
 139    return v1;
 140 END//
 141 delimiter ;//
 142
 143 disconnect user2;
 144
 145 # cleanup
 146 connection default;
 147 USE db_storedproc_1;
 148 --source suite/funcs_1/include/show_connection.inc
 149
 150 drop user 'user_1'@'localhost';
 151 DROP PROCEDURE sp3;
 152 DROP FUNCTION fn1;
 153
 154
 155 # ------------------------------------------------------------------------------
 156 let $message= Testcase 3.1.6.4:
 157               -----------------
 158 Ensure that the default security provision of a stored procedure is SQL SECURITY
 159 DEFINER.;
 160 --source include/show_msg80.inc
 161
 162 CREATE USER 'user_1'@'localhost';
 163
 164 grant update on db_storedproc_1.t6 to 'user_1'@'localhost';
 165 grant execute on db_storedproc_1.* to 'user_1'@'localhost';
 166 flush privileges;
 167
 168 USE db_storedproc_1;
 169
 170 --disable_warnings
 171 DROP PROCEDURE IF EXISTS sp4;
 172 --enable_warnings
 173
 174 delimiter //;
 175 CREATE PROCEDURE sp4(v1 char(20))
 176 BEGIN
 177    SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
 178 END//
 179 delimiter ;//
 180
 181 #disconnect default;
 182
 183 connect (user3, localhost, user_1, , db_storedproc_1);
 184 --source suite/funcs_1/include/show_connection.inc
 185
 186 USE db_storedproc_1;
 187 CALL sp4('a');
 188
 189 --vertical_results
 190 SELECT SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
 191        ROUTINE_BODY, ROUTINE_DEFINITION, IS_DETERMINISTIC,
 192        SQL_DATA_ACCESS, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT
 193 FROM information_schema.routines
 194  WHERE routine_schema LIKE 'db_sto%';
 195 --horizontal_results
 196
 197 disconnect user3;
 198
 199 # cleanup
 200 connection default;
 201 --source suite/funcs_1/include/show_connection.inc
 202 DROP PROCEDURE sp4;
 203 DROP USER 'user_1'@'localhost';
 204
 205
 206 # ------------------------------------------------------------------------------
 207 let $message= Testcase 3.1.6.5:
 208               -----------------
 209 Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
 210 called/executed by any user, using only the privileges (including database
 211 access privileges) associated with the user who created the stored procedure.;
 212 --source include/show_msg80.inc
 213
 214 USE db_storedproc_1;
 215 CREATE TABLE t3165 ( c1 char(20), c2 char(20), c3 date);
 216 INSERT INTO t3165 VALUES ('inserted', 'outside of SP', NULL);
 217
 218 # creates procedures
 219 create user 'user_1'@'localhost';
 220
 221 #executes procedure
 222 create user 'user_2'@'localhost';
 223
 224 grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
 225 grant SELECT on db_storedproc_1.* to 'user_2'@'localhost';
 226 grant execute on db_storedproc_1.* to 'user_2'@'localhost';
 227 flush privileges;
 228
 229 connect (user5_1, localhost, user_1, , db_storedproc_1);
 230 --source suite/funcs_1/include/show_connection.inc
 231
 232 delimiter //;
 233 CREATE PROCEDURE sp5_s_i () sql security definer
 234 BEGIN
 235    SELECT * from db_storedproc_1.t3165;
 236    insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000);
 237 END//
 238
 239 CREATE PROCEDURE sp5_sel () sql security definer
 240 BEGIN
 241    SELECT * from db_storedproc_1.t3165;
 242 END//
 243
 244 CREATE PROCEDURE sp5_ins () sql security definer
 245 BEGIN
 246    insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000);
 247 END//
 248 delimiter ;//
 249
 250 disconnect user5_1;
 251
 252 connect (user5_2, localhost, user_2, , db_storedproc_1);
 253 --source suite/funcs_1/include/show_connection.inc
 254
 255 --error ER_TABLEACCESS_DENIED_ERROR
 256 CALL sp5_s_i();
 257 --error ER_TABLEACCESS_DENIED_ERROR
 258 CALL sp5_ins();
 259 --error ER_TABLEACCESS_DENIED_ERROR
 260 CALL sp5_sel();
 261
 262 # now 'add' INSERT to DEFINER
 263 connection default;
 264 --source suite/funcs_1/include/show_connection.inc
 265 --error ER_TABLEACCESS_DENIED_ERROR
 266 CALL sp5_sel();
 267 grant insert on db_storedproc_1.* to 'user_1'@'localhost';
 268 flush privileges;
 269
 270 connection user5_2;
 271 --source suite/funcs_1/include/show_connection.inc
 272 --error ER_TABLEACCESS_DENIED_ERROR
 273 CALL sp5_s_i();
 274 CALL sp5_ins();
 275 --error ER_TABLEACCESS_DENIED_ERROR
 276 CALL sp5_sel();
 277
 278 # now 'add' SELECT to DEFINER
 279 connection default;
 280 --source suite/funcs_1/include/show_connection.inc
 281 --error ER_TABLEACCESS_DENIED_ERROR
 282 CALL sp5_sel();
 283 grant SELECT on db_storedproc_1.* to 'user_1'@'localhost';
 284 #grant execute on db_storedproc_1.* to 'user_2'@'localhost';
 285 flush privileges;
 286
 287 connection user5_2;
 288 --source suite/funcs_1/include/show_connection.inc
 289 CALL sp5_s_i();
 290 CALL sp5_ins();
 291 CALL sp5_sel();
 292
 293 # now revoke INSERT FROM DEFINER
 294 connection default;
 295 --source suite/funcs_1/include/show_connection.inc
 296 REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost';
 297 flush privileges;
 298
 299 connection user5_2;
 300 --source suite/funcs_1/include/show_connection.inc
 301 --error ER_TABLEACCESS_DENIED_ERROR
 302 CALL sp5_s_i();
 303 --error ER_TABLEACCESS_DENIED_ERROR
 304 CALL sp5_ins();
 305 CALL sp5_sel();
 306
 307 # now revoke SELECT FROM DEFINER
 308 connection default;
 309 --source suite/funcs_1/include/show_connection.inc
 310 REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost';
 311 flush privileges;
 312
 313 connection user5_2;
 314 --source suite/funcs_1/include/show_connection.inc
 315 --error ER_TABLEACCESS_DENIED_ERROR
 316 CALL sp5_s_i();
 317 --error ER_TABLEACCESS_DENIED_ERROR
 318 CALL sp5_ins();
 319 --error ER_TABLEACCESS_DENIED_ERROR
 320 CALL sp5_sel();
 321
 322 # cleanup
 323 disconnect user5_2;
 324 connection default;
 325 --source suite/funcs_1/include/show_connection.inc
 326
 327 DROP PROCEDURE sp5_s_i;
 328 DROP PROCEDURE sp5_sel;
 329 DROP PROCEDURE sp5_ins;
 330 DROP TABLE t3165;
 331 DROP USER 'user_1'@'localhost';
 332 DROP USER 'user_2'@'localhost';
 333
 334
 335 # ------------------------------------------------------------------------------
 336 let $message= Testcase 3.1.6.6:
 337               -----------------
 338 Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
 339 called/executed by any user, using only the privileges (including database
 340 access privileges) associated with the user executing the stored procedure.;
 341 --source include/show_msg80.inc
 342
 343 USE db_storedproc_1;
 344 CREATE TABLE t3166 ( c1 char(30) );
 345 INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP');
 346
 347 # DEFINER
 348 create user 'user_1'@'localhost';
 349
 350 # INVOKER
 351 create user 'user_2'@'localhost';
 352
 353 GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
 354 GRANT SELECT  ON db_storedproc_1.* TO 'user_2'@'localhost';
 355 GRANT EXECUTE ON db_storedproc_1.* TO 'user_2'@'localhost';
 356 FLUSH PRIVILEGES;
 357
 358 connect (user6_1, localhost, user_1, , db_storedproc_1);
 359 --source suite/funcs_1/include/show_connection.inc
 360
 361 delimiter //;
 362 CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER
 363 BEGIN
 364    SELECT * from db_storedproc_1.t3166;
 365    insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i');
 366 END//
 367
 368 CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER
 369 BEGIN
 370    SELECT * from db_storedproc_1.t3166;
 371 END//
 372
 373 CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER
 374 BEGIN
 375    insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins');
 376 END//
 377 delimiter ;//
 378
 379 disconnect user6_1;
 380
 381 connect (user6_2, localhost, user_2, , db_storedproc_1);
 382 --source suite/funcs_1/include/show_connection.inc
 383
 384 --error ER_TABLEACCESS_DENIED_ERROR
 385 CALL sp3166_s_i();
 386 --error ER_TABLEACCESS_DENIED_ERROR
 387 CALL sp3166_ins();
 388 CALL sp3166_sel();
 389
 390 # now 'add' INSERT to INVOKER
 391 connection default;
 392 --source suite/funcs_1/include/show_connection.inc
 393 CALL sp3166_sel();
 394 GRANT INSERT  ON db_storedproc_1.* TO 'user_2'@'localhost';
 395 FLUSH PRIVILEGES;
 396 disconnect user6_2;
 397
 398 connect (user6_3, localhost, user_2, , db_storedproc_1);
 399 --source suite/funcs_1/include/show_connection.inc
 400 CALL sp3166_s_i();
 401 CALL sp3166_ins();
 402 CALL sp3166_sel();
 403 disconnect user6_3;
 404
 405 # now 'remove' SELECT from INVOKER
 406 connection default;
 407 --source suite/funcs_1/include/show_connection.inc
 408 CALL sp3166_sel();
 409 REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost';
 410 FLUSH PRIVILEGES;
 411
 412 connect (user6_4, localhost, user_2, , db_storedproc_1);
 413 --source suite/funcs_1/include/show_connection.inc
 414 --error ER_TABLEACCESS_DENIED_ERROR
 415 CALL sp3166_s_i();
 416 CALL sp3166_ins();
 417 --error ER_TABLEACCESS_DENIED_ERROR
 418 CALL sp3166_sel();
 419 disconnect user6_4;
 420
 421 # now 'remove' EXECUTE FROM INVOKER
 422 connection default;
 423 CALL sp3166_s_i();
 424 --source suite/funcs_1/include/show_connection.inc
 425 REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost';
 426 FLUSH PRIVILEGES;
 427
 428 connect (user6_5, localhost, user_2, , db_storedproc_1);
 429 --source suite/funcs_1/include/show_connection.inc
 430 --error ER_PROCACCESS_DENIED_ERROR
 431 CALL sp3166_s_i();
 432 --error ER_PROCACCESS_DENIED_ERROR
 433 CALL sp3166_ins();
 434 --error ER_PROCACCESS_DENIED_ERROR
 435 CALL sp3166_sel();
 436 disconnect user6_5;
 437
 438 # cleanup
 439 connection default;
 440 --source suite/funcs_1/include/show_connection.inc
 441
 442 DROP PROCEDURE sp3166_s_i;
 443 DROP PROCEDURE sp3166_sel;
 444 DROP PROCEDURE sp3166_ins;
 445 DROP TABLE t3166;
 446 DROP USER 'user_1'@'localhost';
 447 DROP USER 'user_2'@'localhost';
 448
 449
 450 # ==============================================================================
 451 # USE the same .inc to cleanup before and after the test
 452 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
 453
 454 # ==============================================================================
 455 --echo
 456 --echo .                               +++ END OF SCRIPT +++
 457 --echo --------------------------------------------------------------------------------
 458 # ==============================================================================