1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/auth.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,2324 @@
1.4 +# 2003 April 4
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is testing the sqlite3_set_authorizer() API
1.16 +# and related functionality.
1.17 +#
1.18 +# $Id: auth.test,v 1.43 2008/07/02 13:13:52 danielk1977 Exp $
1.19 +#
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
1.25 +# defined during compilation.
1.26 +if {[catch {db auth {}} msg]} {
1.27 + finish_test
1.28 + return
1.29 +}
1.30 +
1.31 +rename proc proc_real
1.32 +proc_real proc {name arguments script} {
1.33 + proc_real $name $arguments $script
1.34 + if {$name=="auth"} {
1.35 + db authorizer ::auth
1.36 + }
1.37 +}
1.38 +
1.39 +do_test auth-1.1.1 {
1.40 + db close
1.41 + set ::DB [sqlite3 db test.db]
1.42 + proc auth {code arg1 arg2 arg3 arg4} {
1.43 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1.44 + return SQLITE_DENY
1.45 + }
1.46 + return SQLITE_OK
1.47 + }
1.48 + db authorizer ::auth
1.49 + catchsql {CREATE TABLE t1(a,b,c)}
1.50 +} {1 {not authorized}}
1.51 +do_test auth-1.1.2 {
1.52 + db errorcode
1.53 +} {23}
1.54 +do_test auth-1.1.3 {
1.55 + db authorizer
1.56 +} {::auth}
1.57 +do_test auth-1.1.4 {
1.58 + # Ticket #896.
1.59 + catchsql {
1.60 + SELECT x;
1.61 + }
1.62 +} {1 {no such column: x}}
1.63 +do_test auth-1.2 {
1.64 + execsql {SELECT name FROM sqlite_master}
1.65 +} {}
1.66 +do_test auth-1.3.1 {
1.67 + proc auth {code arg1 arg2 arg3 arg4} {
1.68 + if {$code=="SQLITE_CREATE_TABLE"} {
1.69 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.70 + return SQLITE_DENY
1.71 + }
1.72 + return SQLITE_OK
1.73 + }
1.74 + catchsql {CREATE TABLE t1(a,b,c)}
1.75 +} {1 {not authorized}}
1.76 +do_test auth-1.3.2 {
1.77 + db errorcode
1.78 +} {23}
1.79 +do_test auth-1.3.3 {
1.80 + set ::authargs
1.81 +} {t1 {} main {}}
1.82 +do_test auth-1.4 {
1.83 + execsql {SELECT name FROM sqlite_master}
1.84 +} {}
1.85 +
1.86 +ifcapable tempdb {
1.87 + do_test auth-1.5 {
1.88 + proc auth {code arg1 arg2 arg3 arg4} {
1.89 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1.90 + return SQLITE_DENY
1.91 + }
1.92 + return SQLITE_OK
1.93 + }
1.94 + catchsql {CREATE TEMP TABLE t1(a,b,c)}
1.95 + } {1 {not authorized}}
1.96 + do_test auth-1.6 {
1.97 + execsql {SELECT name FROM sqlite_temp_master}
1.98 + } {}
1.99 + do_test auth-1.7.1 {
1.100 + proc auth {code arg1 arg2 arg3 arg4} {
1.101 + if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
1.102 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.103 + return SQLITE_DENY
1.104 + }
1.105 + return SQLITE_OK
1.106 + }
1.107 + catchsql {CREATE TEMP TABLE t1(a,b,c)}
1.108 + } {1 {not authorized}}
1.109 + do_test auth-1.7.2 {
1.110 + set ::authargs
1.111 + } {t1 {} temp {}}
1.112 + do_test auth-1.8 {
1.113 + execsql {SELECT name FROM sqlite_temp_master}
1.114 + } {}
1.115 +}
1.116 +
1.117 +do_test auth-1.9 {
1.118 + proc auth {code arg1 arg2 arg3 arg4} {
1.119 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1.120 + return SQLITE_IGNORE
1.121 + }
1.122 + return SQLITE_OK
1.123 + }
1.124 + catchsql {CREATE TABLE t1(a,b,c)}
1.125 +} {0 {}}
1.126 +do_test auth-1.10 {
1.127 + execsql {SELECT name FROM sqlite_master}
1.128 +} {}
1.129 +do_test auth-1.11 {
1.130 + proc auth {code arg1 arg2 arg3 arg4} {
1.131 + if {$code=="SQLITE_CREATE_TABLE"} {
1.132 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.133 + return SQLITE_IGNORE
1.134 + }
1.135 + return SQLITE_OK
1.136 + }
1.137 + catchsql {CREATE TABLE t1(a,b,c)}
1.138 +} {0 {}}
1.139 +do_test auth-1.12 {
1.140 + execsql {SELECT name FROM sqlite_master}
1.141 +} {}
1.142 +
1.143 +ifcapable tempdb {
1.144 + do_test auth-1.13 {
1.145 + proc auth {code arg1 arg2 arg3 arg4} {
1.146 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1.147 + return SQLITE_IGNORE
1.148 + }
1.149 + return SQLITE_OK
1.150 + }
1.151 + catchsql {CREATE TEMP TABLE t1(a,b,c)}
1.152 + } {0 {}}
1.153 + do_test auth-1.14 {
1.154 + execsql {SELECT name FROM sqlite_temp_master}
1.155 + } {}
1.156 + do_test auth-1.15 {
1.157 + proc auth {code arg1 arg2 arg3 arg4} {
1.158 + if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
1.159 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.160 + return SQLITE_IGNORE
1.161 + }
1.162 + return SQLITE_OK
1.163 + }
1.164 + catchsql {CREATE TEMP TABLE t1(a,b,c)}
1.165 + } {0 {}}
1.166 + do_test auth-1.16 {
1.167 + execsql {SELECT name FROM sqlite_temp_master}
1.168 + } {}
1.169 +
1.170 + do_test auth-1.17 {
1.171 + proc auth {code arg1 arg2 arg3 arg4} {
1.172 + if {$code=="SQLITE_CREATE_TABLE"} {
1.173 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.174 + return SQLITE_DENY
1.175 + }
1.176 + return SQLITE_OK
1.177 + }
1.178 + catchsql {CREATE TEMP TABLE t1(a,b,c)}
1.179 + } {0 {}}
1.180 + do_test auth-1.18 {
1.181 + execsql {SELECT name FROM sqlite_temp_master}
1.182 + } {t1}
1.183 +}
1.184 +
1.185 +do_test auth-1.19.1 {
1.186 + set ::authargs {}
1.187 + proc auth {code arg1 arg2 arg3 arg4} {
1.188 + if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
1.189 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.190 + return SQLITE_DENY
1.191 + }
1.192 + return SQLITE_OK
1.193 + }
1.194 + catchsql {CREATE TABLE t2(a,b,c)}
1.195 +} {0 {}}
1.196 +do_test auth-1.19.2 {
1.197 + set ::authargs
1.198 +} {}
1.199 +do_test auth-1.20 {
1.200 + execsql {SELECT name FROM sqlite_master}
1.201 +} {t2}
1.202 +
1.203 +do_test auth-1.21.1 {
1.204 + proc auth {code arg1 arg2 arg3 arg4} {
1.205 + if {$code=="SQLITE_DROP_TABLE"} {
1.206 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.207 + return SQLITE_DENY
1.208 + }
1.209 + return SQLITE_OK
1.210 + }
1.211 + catchsql {DROP TABLE t2}
1.212 +} {1 {not authorized}}
1.213 +do_test auth-1.21.2 {
1.214 + set ::authargs
1.215 +} {t2 {} main {}}
1.216 +do_test auth-1.22 {
1.217 + execsql {SELECT name FROM sqlite_master}
1.218 +} {t2}
1.219 +do_test auth-1.23.1 {
1.220 + proc auth {code arg1 arg2 arg3 arg4} {
1.221 + if {$code=="SQLITE_DROP_TABLE"} {
1.222 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.223 + return SQLITE_IGNORE
1.224 + }
1.225 + return SQLITE_OK
1.226 + }
1.227 + catchsql {DROP TABLE t2}
1.228 +} {0 {}}
1.229 +do_test auth-1.23.2 {
1.230 + set ::authargs
1.231 +} {t2 {} main {}}
1.232 +do_test auth-1.24 {
1.233 + execsql {SELECT name FROM sqlite_master}
1.234 +} {t2}
1.235 +
1.236 +ifcapable tempdb {
1.237 + do_test auth-1.25 {
1.238 + proc auth {code arg1 arg2 arg3 arg4} {
1.239 + if {$code=="SQLITE_DROP_TEMP_TABLE"} {
1.240 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.241 + return SQLITE_DENY
1.242 + }
1.243 + return SQLITE_OK
1.244 + }
1.245 + catchsql {DROP TABLE t1}
1.246 + } {1 {not authorized}}
1.247 + do_test auth-1.26 {
1.248 + execsql {SELECT name FROM sqlite_temp_master}
1.249 + } {t1}
1.250 + do_test auth-1.27 {
1.251 + proc auth {code arg1 arg2 arg3 arg4} {
1.252 + if {$code=="SQLITE_DROP_TEMP_TABLE"} {
1.253 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.254 + return SQLITE_IGNORE
1.255 + }
1.256 + return SQLITE_OK
1.257 + }
1.258 + catchsql {DROP TABLE t1}
1.259 + } {0 {}}
1.260 + do_test auth-1.28 {
1.261 + execsql {SELECT name FROM sqlite_temp_master}
1.262 + } {t1}
1.263 +}
1.264 +
1.265 +do_test auth-1.29 {
1.266 + proc auth {code arg1 arg2 arg3 arg4} {
1.267 + if {$code=="SQLITE_INSERT" && $arg1=="t2"} {
1.268 + return SQLITE_DENY
1.269 + }
1.270 + return SQLITE_OK
1.271 + }
1.272 + catchsql {INSERT INTO t2 VALUES(1,2,3)}
1.273 +} {1 {not authorized}}
1.274 +do_test auth-1.30 {
1.275 + execsql {SELECT * FROM t2}
1.276 +} {}
1.277 +do_test auth-1.31 {
1.278 + proc auth {code arg1 arg2 arg3 arg4} {
1.279 + if {$code=="SQLITE_INSERT" && $arg1=="t2"} {
1.280 + return SQLITE_IGNORE
1.281 + }
1.282 + return SQLITE_OK
1.283 + }
1.284 + catchsql {INSERT INTO t2 VALUES(1,2,3)}
1.285 +} {0 {}}
1.286 +do_test auth-1.32 {
1.287 + execsql {SELECT * FROM t2}
1.288 +} {}
1.289 +do_test auth-1.33 {
1.290 + proc auth {code arg1 arg2 arg3 arg4} {
1.291 + if {$code=="SQLITE_INSERT" && $arg1=="t1"} {
1.292 + return SQLITE_IGNORE
1.293 + }
1.294 + return SQLITE_OK
1.295 + }
1.296 + catchsql {INSERT INTO t2 VALUES(1,2,3)}
1.297 +} {0 {}}
1.298 +do_test auth-1.34 {
1.299 + execsql {SELECT * FROM t2}
1.300 +} {1 2 3}
1.301 +
1.302 +do_test auth-1.35.1 {
1.303 + proc auth {code arg1 arg2 arg3 arg4} {
1.304 + if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
1.305 + return SQLITE_DENY
1.306 + }
1.307 + return SQLITE_OK
1.308 + }
1.309 + catchsql {SELECT * FROM t2}
1.310 +} {1 {access to t2.b is prohibited}}
1.311 +ifcapable attach {
1.312 + do_test auth-1.35.2 {
1.313 + execsql {ATTACH DATABASE 'test.db' AS two}
1.314 + catchsql {SELECT * FROM two.t2}
1.315 + } {1 {access to two.t2.b is prohibited}}
1.316 + execsql {DETACH DATABASE two}
1.317 +}
1.318 +do_test auth-1.36 {
1.319 + proc auth {code arg1 arg2 arg3 arg4} {
1.320 + if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
1.321 + return SQLITE_IGNORE
1.322 + }
1.323 + return SQLITE_OK
1.324 + }
1.325 + catchsql {SELECT * FROM t2}
1.326 +} {0 {1 {} 3}}
1.327 +do_test auth-1.37 {
1.328 + proc auth {code arg1 arg2 arg3 arg4} {
1.329 + if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
1.330 + return SQLITE_IGNORE
1.331 + }
1.332 + return SQLITE_OK
1.333 + }
1.334 + catchsql {SELECT * FROM t2 WHERE b=2}
1.335 +} {0 {}}
1.336 +do_test auth-1.38 {
1.337 + proc auth {code arg1 arg2 arg3 arg4} {
1.338 + if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} {
1.339 + return SQLITE_IGNORE
1.340 + }
1.341 + return SQLITE_OK
1.342 + }
1.343 + catchsql {SELECT * FROM t2 WHERE b=2}
1.344 +} {0 {{} 2 3}}
1.345 +do_test auth-1.39 {
1.346 + proc auth {code arg1 arg2 arg3 arg4} {
1.347 + if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
1.348 + return SQLITE_IGNORE
1.349 + }
1.350 + return SQLITE_OK
1.351 + }
1.352 + catchsql {SELECT * FROM t2 WHERE b IS NULL}
1.353 +} {0 {1 {} 3}}
1.354 +do_test auth-1.40 {
1.355 + proc auth {code arg1 arg2 arg3 arg4} {
1.356 + if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
1.357 + return SQLITE_DENY
1.358 + }
1.359 + return SQLITE_OK
1.360 + }
1.361 + catchsql {SELECT a,c FROM t2 WHERE b IS NULL}
1.362 +} {1 {access to t2.b is prohibited}}
1.363 +
1.364 +do_test auth-1.41 {
1.365 + proc auth {code arg1 arg2 arg3 arg4} {
1.366 + if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {
1.367 + return SQLITE_DENY
1.368 + }
1.369 + return SQLITE_OK
1.370 + }
1.371 + catchsql {UPDATE t2 SET a=11}
1.372 +} {0 {}}
1.373 +do_test auth-1.42 {
1.374 + execsql {SELECT * FROM t2}
1.375 +} {11 2 3}
1.376 +do_test auth-1.43 {
1.377 + proc auth {code arg1 arg2 arg3 arg4} {
1.378 + if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {
1.379 + return SQLITE_DENY
1.380 + }
1.381 + return SQLITE_OK
1.382 + }
1.383 + catchsql {UPDATE t2 SET b=22, c=33}
1.384 +} {1 {not authorized}}
1.385 +do_test auth-1.44 {
1.386 + execsql {SELECT * FROM t2}
1.387 +} {11 2 3}
1.388 +do_test auth-1.45 {
1.389 + proc auth {code arg1 arg2 arg3 arg4} {
1.390 + if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {
1.391 + return SQLITE_IGNORE
1.392 + }
1.393 + return SQLITE_OK
1.394 + }
1.395 + catchsql {UPDATE t2 SET b=22, c=33}
1.396 +} {0 {}}
1.397 +do_test auth-1.46 {
1.398 + execsql {SELECT * FROM t2}
1.399 +} {11 2 33}
1.400 +
1.401 +do_test auth-1.47 {
1.402 + proc auth {code arg1 arg2 arg3 arg4} {
1.403 + if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
1.404 + return SQLITE_DENY
1.405 + }
1.406 + return SQLITE_OK
1.407 + }
1.408 + catchsql {DELETE FROM t2 WHERE a=11}
1.409 +} {1 {not authorized}}
1.410 +do_test auth-1.48 {
1.411 + execsql {SELECT * FROM t2}
1.412 +} {11 2 33}
1.413 +do_test auth-1.49 {
1.414 + proc auth {code arg1 arg2 arg3 arg4} {
1.415 + if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
1.416 + return SQLITE_IGNORE
1.417 + }
1.418 + return SQLITE_OK
1.419 + }
1.420 + catchsql {DELETE FROM t2 WHERE a=11}
1.421 +} {0 {}}
1.422 +do_test auth-1.50 {
1.423 + execsql {SELECT * FROM t2}
1.424 +} {11 2 33}
1.425 +
1.426 +do_test auth-1.51 {
1.427 + proc auth {code arg1 arg2 arg3 arg4} {
1.428 + if {$code=="SQLITE_SELECT"} {
1.429 + return SQLITE_DENY
1.430 + }
1.431 + return SQLITE_OK
1.432 + }
1.433 + catchsql {SELECT * FROM t2}
1.434 +} {1 {not authorized}}
1.435 +do_test auth-1.52 {
1.436 + proc auth {code arg1 arg2 arg3 arg4} {
1.437 + if {$code=="SQLITE_SELECT"} {
1.438 + return SQLITE_IGNORE
1.439 + }
1.440 + return SQLITE_OK
1.441 + }
1.442 + catchsql {SELECT * FROM t2}
1.443 +} {0 {}}
1.444 +do_test auth-1.53 {
1.445 + proc auth {code arg1 arg2 arg3 arg4} {
1.446 + if {$code=="SQLITE_SELECT"} {
1.447 + return SQLITE_OK
1.448 + }
1.449 + return SQLITE_OK
1.450 + }
1.451 + catchsql {SELECT * FROM t2}
1.452 +} {0 {11 2 33}}
1.453 +
1.454 +# Update for version 3: There used to be a handful of test here that
1.455 +# tested the authorisation callback with the COPY command. The following
1.456 +# test makes the same database modifications as they used to.
1.457 +do_test auth-1.54 {
1.458 + execsql {INSERT INTO t2 VALUES(7, 8, 9);}
1.459 +} {}
1.460 +do_test auth-1.55 {
1.461 + execsql {SELECT * FROM t2}
1.462 +} {11 2 33 7 8 9}
1.463 +
1.464 +do_test auth-1.63 {
1.465 + proc auth {code arg1 arg2 arg3 arg4} {
1.466 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1.467 + return SQLITE_DENY
1.468 + }
1.469 + return SQLITE_OK
1.470 + }
1.471 + catchsql {DROP TABLE t2}
1.472 +} {1 {not authorized}}
1.473 +do_test auth-1.64 {
1.474 + execsql {SELECT name FROM sqlite_master}
1.475 +} {t2}
1.476 +do_test auth-1.65 {
1.477 + proc auth {code arg1 arg2 arg3 arg4} {
1.478 + if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
1.479 + return SQLITE_DENY
1.480 + }
1.481 + return SQLITE_OK
1.482 + }
1.483 + catchsql {DROP TABLE t2}
1.484 +} {1 {not authorized}}
1.485 +do_test auth-1.66 {
1.486 + execsql {SELECT name FROM sqlite_master}
1.487 +} {t2}
1.488 +
1.489 +ifcapable tempdb {
1.490 + do_test auth-1.67 {
1.491 + proc auth {code arg1 arg2 arg3 arg4} {
1.492 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1.493 + return SQLITE_DENY
1.494 + }
1.495 + return SQLITE_OK
1.496 + }
1.497 + catchsql {DROP TABLE t1}
1.498 + } {1 {not authorized}}
1.499 + do_test auth-1.68 {
1.500 + execsql {SELECT name FROM sqlite_temp_master}
1.501 + } {t1}
1.502 + do_test auth-1.69 {
1.503 + proc auth {code arg1 arg2 arg3 arg4} {
1.504 + if {$code=="SQLITE_DELETE" && $arg1=="t1"} {
1.505 + return SQLITE_DENY
1.506 + }
1.507 + return SQLITE_OK
1.508 + }
1.509 + catchsql {DROP TABLE t1}
1.510 + } {1 {not authorized}}
1.511 + do_test auth-1.70 {
1.512 + execsql {SELECT name FROM sqlite_temp_master}
1.513 + } {t1}
1.514 +}
1.515 +
1.516 +do_test auth-1.71 {
1.517 + proc auth {code arg1 arg2 arg3 arg4} {
1.518 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1.519 + return SQLITE_IGNORE
1.520 + }
1.521 + return SQLITE_OK
1.522 + }
1.523 + catchsql {DROP TABLE t2}
1.524 +} {0 {}}
1.525 +do_test auth-1.72 {
1.526 + execsql {SELECT name FROM sqlite_master}
1.527 +} {t2}
1.528 +do_test auth-1.73 {
1.529 + proc auth {code arg1 arg2 arg3 arg4} {
1.530 + if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
1.531 + return SQLITE_IGNORE
1.532 + }
1.533 + return SQLITE_OK
1.534 + }
1.535 + catchsql {DROP TABLE t2}
1.536 +} {0 {}}
1.537 +do_test auth-1.74 {
1.538 + execsql {SELECT name FROM sqlite_master}
1.539 +} {t2}
1.540 +
1.541 +ifcapable tempdb {
1.542 + do_test auth-1.75 {
1.543 + proc auth {code arg1 arg2 arg3 arg4} {
1.544 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1.545 + return SQLITE_IGNORE
1.546 + }
1.547 + return SQLITE_OK
1.548 + }
1.549 + catchsql {DROP TABLE t1}
1.550 + } {0 {}}
1.551 + do_test auth-1.76 {
1.552 + execsql {SELECT name FROM sqlite_temp_master}
1.553 + } {t1}
1.554 + do_test auth-1.77 {
1.555 + proc auth {code arg1 arg2 arg3 arg4} {
1.556 + if {$code=="SQLITE_DELETE" && $arg1=="t1"} {
1.557 + return SQLITE_IGNORE
1.558 + }
1.559 + return SQLITE_OK
1.560 + }
1.561 + catchsql {DROP TABLE t1}
1.562 + } {0 {}}
1.563 + do_test auth-1.78 {
1.564 + execsql {SELECT name FROM sqlite_temp_master}
1.565 + } {t1}
1.566 +}
1.567 +
1.568 +# Test cases auth-1.79 to auth-1.124 test creating and dropping views.
1.569 +# Omit these if the library was compiled with views omitted.
1.570 +ifcapable view {
1.571 +do_test auth-1.79 {
1.572 + proc auth {code arg1 arg2 arg3 arg4} {
1.573 + if {$code=="SQLITE_CREATE_VIEW"} {
1.574 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.575 + return SQLITE_DENY
1.576 + }
1.577 + return SQLITE_OK
1.578 + }
1.579 + catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
1.580 +} {1 {not authorized}}
1.581 +do_test auth-1.80 {
1.582 + set ::authargs
1.583 +} {v1 {} main {}}
1.584 +do_test auth-1.81 {
1.585 + execsql {SELECT name FROM sqlite_master}
1.586 +} {t2}
1.587 +do_test auth-1.82 {
1.588 + proc auth {code arg1 arg2 arg3 arg4} {
1.589 + if {$code=="SQLITE_CREATE_VIEW"} {
1.590 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.591 + return SQLITE_IGNORE
1.592 + }
1.593 + return SQLITE_OK
1.594 + }
1.595 + catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
1.596 +} {0 {}}
1.597 +do_test auth-1.83 {
1.598 + set ::authargs
1.599 +} {v1 {} main {}}
1.600 +do_test auth-1.84 {
1.601 + execsql {SELECT name FROM sqlite_master}
1.602 +} {t2}
1.603 +
1.604 +ifcapable tempdb {
1.605 + do_test auth-1.85 {
1.606 + proc auth {code arg1 arg2 arg3 arg4} {
1.607 + if {$code=="SQLITE_CREATE_TEMP_VIEW"} {
1.608 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.609 + return SQLITE_DENY
1.610 + }
1.611 + return SQLITE_OK
1.612 + }
1.613 + catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
1.614 + } {1 {not authorized}}
1.615 + do_test auth-1.86 {
1.616 + set ::authargs
1.617 + } {v1 {} temp {}}
1.618 + do_test auth-1.87 {
1.619 + execsql {SELECT name FROM sqlite_temp_master}
1.620 + } {t1}
1.621 + do_test auth-1.88 {
1.622 + proc auth {code arg1 arg2 arg3 arg4} {
1.623 + if {$code=="SQLITE_CREATE_TEMP_VIEW"} {
1.624 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.625 + return SQLITE_IGNORE
1.626 + }
1.627 + return SQLITE_OK
1.628 + }
1.629 + catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
1.630 + } {0 {}}
1.631 + do_test auth-1.89 {
1.632 + set ::authargs
1.633 + } {v1 {} temp {}}
1.634 + do_test auth-1.90 {
1.635 + execsql {SELECT name FROM sqlite_temp_master}
1.636 + } {t1}
1.637 +}
1.638 +
1.639 +do_test auth-1.91 {
1.640 + proc auth {code arg1 arg2 arg3 arg4} {
1.641 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1.642 + return SQLITE_DENY
1.643 + }
1.644 + return SQLITE_OK
1.645 + }
1.646 + catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
1.647 +} {1 {not authorized}}
1.648 +do_test auth-1.92 {
1.649 + execsql {SELECT name FROM sqlite_master}
1.650 +} {t2}
1.651 +do_test auth-1.93 {
1.652 + proc auth {code arg1 arg2 arg3 arg4} {
1.653 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1.654 + return SQLITE_IGNORE
1.655 + }
1.656 + return SQLITE_OK
1.657 + }
1.658 + catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
1.659 +} {0 {}}
1.660 +do_test auth-1.94 {
1.661 + execsql {SELECT name FROM sqlite_master}
1.662 +} {t2}
1.663 +
1.664 +ifcapable tempdb {
1.665 + do_test auth-1.95 {
1.666 + proc auth {code arg1 arg2 arg3 arg4} {
1.667 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1.668 + return SQLITE_DENY
1.669 + }
1.670 + return SQLITE_OK
1.671 + }
1.672 + catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
1.673 + } {1 {not authorized}}
1.674 + do_test auth-1.96 {
1.675 + execsql {SELECT name FROM sqlite_temp_master}
1.676 + } {t1}
1.677 + do_test auth-1.97 {
1.678 + proc auth {code arg1 arg2 arg3 arg4} {
1.679 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1.680 + return SQLITE_IGNORE
1.681 + }
1.682 + return SQLITE_OK
1.683 + }
1.684 + catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
1.685 + } {0 {}}
1.686 + do_test auth-1.98 {
1.687 + execsql {SELECT name FROM sqlite_temp_master}
1.688 + } {t1}
1.689 +}
1.690 +
1.691 +do_test auth-1.99 {
1.692 + proc auth {code arg1 arg2 arg3 arg4} {
1.693 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1.694 + return SQLITE_DENY
1.695 + }
1.696 + return SQLITE_OK
1.697 + }
1.698 + catchsql {
1.699 + CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2;
1.700 + DROP VIEW v2
1.701 + }
1.702 +} {1 {not authorized}}
1.703 +do_test auth-1.100 {
1.704 + execsql {SELECT name FROM sqlite_master}
1.705 +} {t2 v2}
1.706 +do_test auth-1.101 {
1.707 + proc auth {code arg1 arg2 arg3 arg4} {
1.708 + if {$code=="SQLITE_DROP_VIEW"} {
1.709 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.710 + return SQLITE_DENY
1.711 + }
1.712 + return SQLITE_OK
1.713 + }
1.714 + catchsql {DROP VIEW v2}
1.715 +} {1 {not authorized}}
1.716 +do_test auth-1.102 {
1.717 + set ::authargs
1.718 +} {v2 {} main {}}
1.719 +do_test auth-1.103 {
1.720 + execsql {SELECT name FROM sqlite_master}
1.721 +} {t2 v2}
1.722 +do_test auth-1.104 {
1.723 + proc auth {code arg1 arg2 arg3 arg4} {
1.724 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1.725 + return SQLITE_IGNORE
1.726 + }
1.727 + return SQLITE_OK
1.728 + }
1.729 + catchsql {DROP VIEW v2}
1.730 +} {0 {}}
1.731 +do_test auth-1.105 {
1.732 + execsql {SELECT name FROM sqlite_master}
1.733 +} {t2 v2}
1.734 +do_test auth-1.106 {
1.735 + proc auth {code arg1 arg2 arg3 arg4} {
1.736 + if {$code=="SQLITE_DROP_VIEW"} {
1.737 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.738 + return SQLITE_IGNORE
1.739 + }
1.740 + return SQLITE_OK
1.741 + }
1.742 + catchsql {DROP VIEW v2}
1.743 +} {0 {}}
1.744 +do_test auth-1.107 {
1.745 + set ::authargs
1.746 +} {v2 {} main {}}
1.747 +do_test auth-1.108 {
1.748 + execsql {SELECT name FROM sqlite_master}
1.749 +} {t2 v2}
1.750 +do_test auth-1.109 {
1.751 + proc auth {code arg1 arg2 arg3 arg4} {
1.752 + if {$code=="SQLITE_DROP_VIEW"} {
1.753 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.754 + return SQLITE_OK
1.755 + }
1.756 + return SQLITE_OK
1.757 + }
1.758 + catchsql {DROP VIEW v2}
1.759 +} {0 {}}
1.760 +do_test auth-1.110 {
1.761 + set ::authargs
1.762 +} {v2 {} main {}}
1.763 +do_test auth-1.111 {
1.764 + execsql {SELECT name FROM sqlite_master}
1.765 +} {t2}
1.766 +
1.767 +
1.768 +ifcapable tempdb {
1.769 + do_test auth-1.112 {
1.770 + proc auth {code arg1 arg2 arg3 arg4} {
1.771 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1.772 + return SQLITE_DENY
1.773 + }
1.774 + return SQLITE_OK
1.775 + }
1.776 + catchsql {
1.777 + CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1;
1.778 + DROP VIEW v1
1.779 + }
1.780 + } {1 {not authorized}}
1.781 + do_test auth-1.113 {
1.782 + execsql {SELECT name FROM sqlite_temp_master}
1.783 + } {t1 v1}
1.784 + do_test auth-1.114 {
1.785 + proc auth {code arg1 arg2 arg3 arg4} {
1.786 + if {$code=="SQLITE_DROP_TEMP_VIEW"} {
1.787 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.788 + return SQLITE_DENY
1.789 + }
1.790 + return SQLITE_OK
1.791 + }
1.792 + catchsql {DROP VIEW v1}
1.793 + } {1 {not authorized}}
1.794 + do_test auth-1.115 {
1.795 + set ::authargs
1.796 + } {v1 {} temp {}}
1.797 + do_test auth-1.116 {
1.798 + execsql {SELECT name FROM sqlite_temp_master}
1.799 + } {t1 v1}
1.800 + do_test auth-1.117 {
1.801 + proc auth {code arg1 arg2 arg3 arg4} {
1.802 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1.803 + return SQLITE_IGNORE
1.804 + }
1.805 + return SQLITE_OK
1.806 + }
1.807 + catchsql {DROP VIEW v1}
1.808 + } {0 {}}
1.809 + do_test auth-1.118 {
1.810 + execsql {SELECT name FROM sqlite_temp_master}
1.811 + } {t1 v1}
1.812 + do_test auth-1.119 {
1.813 + proc auth {code arg1 arg2 arg3 arg4} {
1.814 + if {$code=="SQLITE_DROP_TEMP_VIEW"} {
1.815 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.816 + return SQLITE_IGNORE
1.817 + }
1.818 + return SQLITE_OK
1.819 + }
1.820 + catchsql {DROP VIEW v1}
1.821 + } {0 {}}
1.822 + do_test auth-1.120 {
1.823 + set ::authargs
1.824 + } {v1 {} temp {}}
1.825 + do_test auth-1.121 {
1.826 + execsql {SELECT name FROM sqlite_temp_master}
1.827 + } {t1 v1}
1.828 + do_test auth-1.122 {
1.829 + proc auth {code arg1 arg2 arg3 arg4} {
1.830 + if {$code=="SQLITE_DROP_TEMP_VIEW"} {
1.831 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.832 + return SQLITE_OK
1.833 + }
1.834 + return SQLITE_OK
1.835 + }
1.836 + catchsql {DROP VIEW v1}
1.837 + } {0 {}}
1.838 + do_test auth-1.123 {
1.839 + set ::authargs
1.840 + } {v1 {} temp {}}
1.841 + do_test auth-1.124 {
1.842 + execsql {SELECT name FROM sqlite_temp_master}
1.843 + } {t1}
1.844 +}
1.845 +} ;# ifcapable view
1.846 +
1.847 +# Test cases auth-1.125 to auth-1.176 test creating and dropping triggers.
1.848 +# Omit these if the library was compiled with triggers omitted.
1.849 +#
1.850 +ifcapable trigger&&tempdb {
1.851 +do_test auth-1.125 {
1.852 + proc auth {code arg1 arg2 arg3 arg4} {
1.853 + if {$code=="SQLITE_CREATE_TRIGGER"} {
1.854 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.855 + return SQLITE_DENY
1.856 + }
1.857 + return SQLITE_OK
1.858 + }
1.859 + catchsql {
1.860 + CREATE TRIGGER r2 DELETE on t2 BEGIN
1.861 + SELECT NULL;
1.862 + END;
1.863 + }
1.864 +} {1 {not authorized}}
1.865 +do_test auth-1.126 {
1.866 + set ::authargs
1.867 +} {r2 t2 main {}}
1.868 +do_test auth-1.127 {
1.869 + execsql {SELECT name FROM sqlite_master}
1.870 +} {t2}
1.871 +do_test auth-1.128 {
1.872 + proc auth {code arg1 arg2 arg3 arg4} {
1.873 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1.874 + return SQLITE_DENY
1.875 + }
1.876 + return SQLITE_OK
1.877 + }
1.878 + catchsql {
1.879 + CREATE TRIGGER r2 DELETE on t2 BEGIN
1.880 + SELECT NULL;
1.881 + END;
1.882 + }
1.883 +} {1 {not authorized}}
1.884 +do_test auth-1.129 {
1.885 + execsql {SELECT name FROM sqlite_master}
1.886 +} {t2}
1.887 +do_test auth-1.130 {
1.888 + proc auth {code arg1 arg2 arg3 arg4} {
1.889 + if {$code=="SQLITE_CREATE_TRIGGER"} {
1.890 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.891 + return SQLITE_IGNORE
1.892 + }
1.893 + return SQLITE_OK
1.894 + }
1.895 + catchsql {
1.896 + CREATE TRIGGER r2 DELETE on t2 BEGIN
1.897 + SELECT NULL;
1.898 + END;
1.899 + }
1.900 +} {0 {}}
1.901 +do_test auth-1.131 {
1.902 + set ::authargs
1.903 +} {r2 t2 main {}}
1.904 +do_test auth-1.132 {
1.905 + execsql {SELECT name FROM sqlite_master}
1.906 +} {t2}
1.907 +do_test auth-1.133 {
1.908 + proc auth {code arg1 arg2 arg3 arg4} {
1.909 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1.910 + return SQLITE_IGNORE
1.911 + }
1.912 + return SQLITE_OK
1.913 + }
1.914 + catchsql {
1.915 + CREATE TRIGGER r2 DELETE on t2 BEGIN
1.916 + SELECT NULL;
1.917 + END;
1.918 + }
1.919 +} {0 {}}
1.920 +do_test auth-1.134 {
1.921 + execsql {SELECT name FROM sqlite_master}
1.922 +} {t2}
1.923 +do_test auth-1.135 {
1.924 + proc auth {code arg1 arg2 arg3 arg4} {
1.925 + if {$code=="SQLITE_CREATE_TRIGGER"} {
1.926 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.927 + return SQLITE_OK
1.928 + }
1.929 + return SQLITE_OK
1.930 + }
1.931 + catchsql {
1.932 + CREATE TABLE tx(id);
1.933 + CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN
1.934 + INSERT INTO tx VALUES(NEW.rowid);
1.935 + END;
1.936 + }
1.937 +} {0 {}}
1.938 +do_test auth-1.136.1 {
1.939 + set ::authargs
1.940 +} {r2 t2 main {}}
1.941 +do_test auth-1.136.2 {
1.942 + execsql {
1.943 + SELECT name FROM sqlite_master WHERE type='trigger'
1.944 + }
1.945 +} {r2}
1.946 +do_test auth-1.136.3 {
1.947 + proc auth {code arg1 arg2 arg3 arg4} {
1.948 + lappend ::authargs $code $arg1 $arg2 $arg3 $arg4
1.949 + return SQLITE_OK
1.950 + }
1.951 + set ::authargs {}
1.952 + execsql {
1.953 + INSERT INTO t2 VALUES(1,2,3);
1.954 + }
1.955 + set ::authargs
1.956 +} {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2}
1.957 +do_test auth-1.136.4 {
1.958 + execsql {
1.959 + SELECT * FROM tx;
1.960 + }
1.961 +} {3}
1.962 +do_test auth-1.137 {
1.963 + execsql {SELECT name FROM sqlite_master}
1.964 +} {t2 tx r2}
1.965 +do_test auth-1.138 {
1.966 + proc auth {code arg1 arg2 arg3 arg4} {
1.967 + if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {
1.968 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.969 + return SQLITE_DENY
1.970 + }
1.971 + return SQLITE_OK
1.972 + }
1.973 + catchsql {
1.974 + CREATE TRIGGER r1 DELETE on t1 BEGIN
1.975 + SELECT NULL;
1.976 + END;
1.977 + }
1.978 +} {1 {not authorized}}
1.979 +do_test auth-1.139 {
1.980 + set ::authargs
1.981 +} {r1 t1 temp {}}
1.982 +do_test auth-1.140 {
1.983 + execsql {SELECT name FROM sqlite_temp_master}
1.984 +} {t1}
1.985 +do_test auth-1.141 {
1.986 + proc auth {code arg1 arg2 arg3 arg4} {
1.987 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1.988 + return SQLITE_DENY
1.989 + }
1.990 + return SQLITE_OK
1.991 + }
1.992 + catchsql {
1.993 + CREATE TRIGGER r1 DELETE on t1 BEGIN
1.994 + SELECT NULL;
1.995 + END;
1.996 + }
1.997 +} {1 {not authorized}}
1.998 +do_test auth-1.142 {
1.999 + execsql {SELECT name FROM sqlite_temp_master}
1.1000 +} {t1}
1.1001 +do_test auth-1.143 {
1.1002 + proc auth {code arg1 arg2 arg3 arg4} {
1.1003 + if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {
1.1004 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1005 + return SQLITE_IGNORE
1.1006 + }
1.1007 + return SQLITE_OK
1.1008 + }
1.1009 + catchsql {
1.1010 + CREATE TRIGGER r1 DELETE on t1 BEGIN
1.1011 + SELECT NULL;
1.1012 + END;
1.1013 + }
1.1014 +} {0 {}}
1.1015 +do_test auth-1.144 {
1.1016 + set ::authargs
1.1017 +} {r1 t1 temp {}}
1.1018 +do_test auth-1.145 {
1.1019 + execsql {SELECT name FROM sqlite_temp_master}
1.1020 +} {t1}
1.1021 +do_test auth-1.146 {
1.1022 + proc auth {code arg1 arg2 arg3 arg4} {
1.1023 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1.1024 + return SQLITE_IGNORE
1.1025 + }
1.1026 + return SQLITE_OK
1.1027 + }
1.1028 + catchsql {
1.1029 + CREATE TRIGGER r1 DELETE on t1 BEGIN
1.1030 + SELECT NULL;
1.1031 + END;
1.1032 + }
1.1033 +} {0 {}}
1.1034 +do_test auth-1.147 {
1.1035 + execsql {SELECT name FROM sqlite_temp_master}
1.1036 +} {t1}
1.1037 +do_test auth-1.148 {
1.1038 + proc auth {code arg1 arg2 arg3 arg4} {
1.1039 + if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {
1.1040 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1041 + return SQLITE_OK
1.1042 + }
1.1043 + return SQLITE_OK
1.1044 + }
1.1045 + catchsql {
1.1046 + CREATE TRIGGER r1 DELETE on t1 BEGIN
1.1047 + SELECT NULL;
1.1048 + END;
1.1049 + }
1.1050 +} {0 {}}
1.1051 +do_test auth-1.149 {
1.1052 + set ::authargs
1.1053 +} {r1 t1 temp {}}
1.1054 +do_test auth-1.150 {
1.1055 + execsql {SELECT name FROM sqlite_temp_master}
1.1056 +} {t1 r1}
1.1057 +
1.1058 +do_test auth-1.151 {
1.1059 + proc auth {code arg1 arg2 arg3 arg4} {
1.1060 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1.1061 + return SQLITE_DENY
1.1062 + }
1.1063 + return SQLITE_OK
1.1064 + }
1.1065 + catchsql {DROP TRIGGER r2}
1.1066 +} {1 {not authorized}}
1.1067 +do_test auth-1.152 {
1.1068 + execsql {SELECT name FROM sqlite_master}
1.1069 +} {t2 tx r2}
1.1070 +do_test auth-1.153 {
1.1071 + proc auth {code arg1 arg2 arg3 arg4} {
1.1072 + if {$code=="SQLITE_DROP_TRIGGER"} {
1.1073 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1074 + return SQLITE_DENY
1.1075 + }
1.1076 + return SQLITE_OK
1.1077 + }
1.1078 + catchsql {DROP TRIGGER r2}
1.1079 +} {1 {not authorized}}
1.1080 +do_test auth-1.154 {
1.1081 + set ::authargs
1.1082 +} {r2 t2 main {}}
1.1083 +do_test auth-1.155 {
1.1084 + execsql {SELECT name FROM sqlite_master}
1.1085 +} {t2 tx r2}
1.1086 +do_test auth-1.156 {
1.1087 + proc auth {code arg1 arg2 arg3 arg4} {
1.1088 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1.1089 + return SQLITE_IGNORE
1.1090 + }
1.1091 + return SQLITE_OK
1.1092 + }
1.1093 + catchsql {DROP TRIGGER r2}
1.1094 +} {0 {}}
1.1095 +do_test auth-1.157 {
1.1096 + execsql {SELECT name FROM sqlite_master}
1.1097 +} {t2 tx r2}
1.1098 +do_test auth-1.158 {
1.1099 + proc auth {code arg1 arg2 arg3 arg4} {
1.1100 + if {$code=="SQLITE_DROP_TRIGGER"} {
1.1101 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1102 + return SQLITE_IGNORE
1.1103 + }
1.1104 + return SQLITE_OK
1.1105 + }
1.1106 + catchsql {DROP TRIGGER r2}
1.1107 +} {0 {}}
1.1108 +do_test auth-1.159 {
1.1109 + set ::authargs
1.1110 +} {r2 t2 main {}}
1.1111 +do_test auth-1.160 {
1.1112 + execsql {SELECT name FROM sqlite_master}
1.1113 +} {t2 tx r2}
1.1114 +do_test auth-1.161 {
1.1115 + proc auth {code arg1 arg2 arg3 arg4} {
1.1116 + if {$code=="SQLITE_DROP_TRIGGER"} {
1.1117 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1118 + return SQLITE_OK
1.1119 + }
1.1120 + return SQLITE_OK
1.1121 + }
1.1122 + catchsql {DROP TRIGGER r2}
1.1123 +} {0 {}}
1.1124 +do_test auth-1.162 {
1.1125 + set ::authargs
1.1126 +} {r2 t2 main {}}
1.1127 +do_test auth-1.163 {
1.1128 + execsql {
1.1129 + DROP TABLE tx;
1.1130 + DELETE FROM t2 WHERE a=1 AND b=2 AND c=3;
1.1131 + SELECT name FROM sqlite_master;
1.1132 + }
1.1133 +} {t2}
1.1134 +
1.1135 +do_test auth-1.164 {
1.1136 + proc auth {code arg1 arg2 arg3 arg4} {
1.1137 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1.1138 + return SQLITE_DENY
1.1139 + }
1.1140 + return SQLITE_OK
1.1141 + }
1.1142 + catchsql {DROP TRIGGER r1}
1.1143 +} {1 {not authorized}}
1.1144 +do_test auth-1.165 {
1.1145 + execsql {SELECT name FROM sqlite_temp_master}
1.1146 +} {t1 r1}
1.1147 +do_test auth-1.166 {
1.1148 + proc auth {code arg1 arg2 arg3 arg4} {
1.1149 + if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {
1.1150 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1151 + return SQLITE_DENY
1.1152 + }
1.1153 + return SQLITE_OK
1.1154 + }
1.1155 + catchsql {DROP TRIGGER r1}
1.1156 +} {1 {not authorized}}
1.1157 +do_test auth-1.167 {
1.1158 + set ::authargs
1.1159 +} {r1 t1 temp {}}
1.1160 +do_test auth-1.168 {
1.1161 + execsql {SELECT name FROM sqlite_temp_master}
1.1162 +} {t1 r1}
1.1163 +do_test auth-1.169 {
1.1164 + proc auth {code arg1 arg2 arg3 arg4} {
1.1165 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1.1166 + return SQLITE_IGNORE
1.1167 + }
1.1168 + return SQLITE_OK
1.1169 + }
1.1170 + catchsql {DROP TRIGGER r1}
1.1171 +} {0 {}}
1.1172 +do_test auth-1.170 {
1.1173 + execsql {SELECT name FROM sqlite_temp_master}
1.1174 +} {t1 r1}
1.1175 +do_test auth-1.171 {
1.1176 + proc auth {code arg1 arg2 arg3 arg4} {
1.1177 + if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {
1.1178 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1179 + return SQLITE_IGNORE
1.1180 + }
1.1181 + return SQLITE_OK
1.1182 + }
1.1183 + catchsql {DROP TRIGGER r1}
1.1184 +} {0 {}}
1.1185 +do_test auth-1.172 {
1.1186 + set ::authargs
1.1187 +} {r1 t1 temp {}}
1.1188 +do_test auth-1.173 {
1.1189 + execsql {SELECT name FROM sqlite_temp_master}
1.1190 +} {t1 r1}
1.1191 +do_test auth-1.174 {
1.1192 + proc auth {code arg1 arg2 arg3 arg4} {
1.1193 + if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {
1.1194 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1195 + return SQLITE_OK
1.1196 + }
1.1197 + return SQLITE_OK
1.1198 + }
1.1199 + catchsql {DROP TRIGGER r1}
1.1200 +} {0 {}}
1.1201 +do_test auth-1.175 {
1.1202 + set ::authargs
1.1203 +} {r1 t1 temp {}}
1.1204 +do_test auth-1.176 {
1.1205 + execsql {SELECT name FROM sqlite_temp_master}
1.1206 +} {t1}
1.1207 +} ;# ifcapable trigger
1.1208 +
1.1209 +do_test auth-1.177 {
1.1210 + proc auth {code arg1 arg2 arg3 arg4} {
1.1211 + if {$code=="SQLITE_CREATE_INDEX"} {
1.1212 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1213 + return SQLITE_DENY
1.1214 + }
1.1215 + return SQLITE_OK
1.1216 + }
1.1217 + catchsql {CREATE INDEX i2 ON t2(a)}
1.1218 +} {1 {not authorized}}
1.1219 +do_test auth-1.178 {
1.1220 + set ::authargs
1.1221 +} {i2 t2 main {}}
1.1222 +do_test auth-1.179 {
1.1223 + execsql {SELECT name FROM sqlite_master}
1.1224 +} {t2}
1.1225 +do_test auth-1.180 {
1.1226 + proc auth {code arg1 arg2 arg3 arg4} {
1.1227 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1.1228 + return SQLITE_DENY
1.1229 + }
1.1230 + return SQLITE_OK
1.1231 + }
1.1232 + catchsql {CREATE INDEX i2 ON t2(a)}
1.1233 +} {1 {not authorized}}
1.1234 +do_test auth-1.181 {
1.1235 + execsql {SELECT name FROM sqlite_master}
1.1236 +} {t2}
1.1237 +do_test auth-1.182 {
1.1238 + proc auth {code arg1 arg2 arg3 arg4} {
1.1239 + if {$code=="SQLITE_CREATE_INDEX"} {
1.1240 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1241 + return SQLITE_IGNORE
1.1242 + }
1.1243 + return SQLITE_OK
1.1244 + }
1.1245 + catchsql {CREATE INDEX i2 ON t2(b)}
1.1246 +} {0 {}}
1.1247 +do_test auth-1.183 {
1.1248 + set ::authargs
1.1249 +} {i2 t2 main {}}
1.1250 +do_test auth-1.184 {
1.1251 + execsql {SELECT name FROM sqlite_master}
1.1252 +} {t2}
1.1253 +do_test auth-1.185 {
1.1254 + proc auth {code arg1 arg2 arg3 arg4} {
1.1255 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1.1256 + return SQLITE_IGNORE
1.1257 + }
1.1258 + return SQLITE_OK
1.1259 + }
1.1260 + catchsql {CREATE INDEX i2 ON t2(b)}
1.1261 +} {0 {}}
1.1262 +do_test auth-1.186 {
1.1263 + execsql {SELECT name FROM sqlite_master}
1.1264 +} {t2}
1.1265 +do_test auth-1.187 {
1.1266 + proc auth {code arg1 arg2 arg3 arg4} {
1.1267 + if {$code=="SQLITE_CREATE_INDEX"} {
1.1268 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1269 + return SQLITE_OK
1.1270 + }
1.1271 + return SQLITE_OK
1.1272 + }
1.1273 + catchsql {CREATE INDEX i2 ON t2(a)}
1.1274 +} {0 {}}
1.1275 +do_test auth-1.188 {
1.1276 + set ::authargs
1.1277 +} {i2 t2 main {}}
1.1278 +do_test auth-1.189 {
1.1279 + execsql {SELECT name FROM sqlite_master}
1.1280 +} {t2 i2}
1.1281 +
1.1282 +ifcapable tempdb {
1.1283 + do_test auth-1.190 {
1.1284 + proc auth {code arg1 arg2 arg3 arg4} {
1.1285 + if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
1.1286 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1287 + return SQLITE_DENY
1.1288 + }
1.1289 + return SQLITE_OK
1.1290 + }
1.1291 + catchsql {CREATE INDEX i1 ON t1(a)}
1.1292 + } {1 {not authorized}}
1.1293 + do_test auth-1.191 {
1.1294 + set ::authargs
1.1295 + } {i1 t1 temp {}}
1.1296 + do_test auth-1.192 {
1.1297 + execsql {SELECT name FROM sqlite_temp_master}
1.1298 + } {t1}
1.1299 + do_test auth-1.193 {
1.1300 + proc auth {code arg1 arg2 arg3 arg4} {
1.1301 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1.1302 + return SQLITE_DENY
1.1303 + }
1.1304 + return SQLITE_OK
1.1305 + }
1.1306 + catchsql {CREATE INDEX i1 ON t1(b)}
1.1307 + } {1 {not authorized}}
1.1308 + do_test auth-1.194 {
1.1309 + execsql {SELECT name FROM sqlite_temp_master}
1.1310 + } {t1}
1.1311 + do_test auth-1.195 {
1.1312 + proc auth {code arg1 arg2 arg3 arg4} {
1.1313 + if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
1.1314 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1315 + return SQLITE_IGNORE
1.1316 + }
1.1317 + return SQLITE_OK
1.1318 + }
1.1319 + catchsql {CREATE INDEX i1 ON t1(b)}
1.1320 + } {0 {}}
1.1321 + do_test auth-1.196 {
1.1322 + set ::authargs
1.1323 + } {i1 t1 temp {}}
1.1324 + do_test auth-1.197 {
1.1325 + execsql {SELECT name FROM sqlite_temp_master}
1.1326 + } {t1}
1.1327 + do_test auth-1.198 {
1.1328 + proc auth {code arg1 arg2 arg3 arg4} {
1.1329 + if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1.1330 + return SQLITE_IGNORE
1.1331 + }
1.1332 + return SQLITE_OK
1.1333 + }
1.1334 + catchsql {CREATE INDEX i1 ON t1(c)}
1.1335 + } {0 {}}
1.1336 + do_test auth-1.199 {
1.1337 + execsql {SELECT name FROM sqlite_temp_master}
1.1338 + } {t1}
1.1339 + do_test auth-1.200 {
1.1340 + proc auth {code arg1 arg2 arg3 arg4} {
1.1341 + if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
1.1342 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1343 + return SQLITE_OK
1.1344 + }
1.1345 + return SQLITE_OK
1.1346 + }
1.1347 + catchsql {CREATE INDEX i1 ON t1(a)}
1.1348 + } {0 {}}
1.1349 + do_test auth-1.201 {
1.1350 + set ::authargs
1.1351 + } {i1 t1 temp {}}
1.1352 + do_test auth-1.202 {
1.1353 + execsql {SELECT name FROM sqlite_temp_master}
1.1354 + } {t1 i1}
1.1355 +}
1.1356 +
1.1357 +do_test auth-1.203 {
1.1358 + proc auth {code arg1 arg2 arg3 arg4} {
1.1359 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1.1360 + return SQLITE_DENY
1.1361 + }
1.1362 + return SQLITE_OK
1.1363 + }
1.1364 + catchsql {DROP INDEX i2}
1.1365 +} {1 {not authorized}}
1.1366 +do_test auth-1.204 {
1.1367 + execsql {SELECT name FROM sqlite_master}
1.1368 +} {t2 i2}
1.1369 +do_test auth-1.205 {
1.1370 + proc auth {code arg1 arg2 arg3 arg4} {
1.1371 + if {$code=="SQLITE_DROP_INDEX"} {
1.1372 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1373 + return SQLITE_DENY
1.1374 + }
1.1375 + return SQLITE_OK
1.1376 + }
1.1377 + catchsql {DROP INDEX i2}
1.1378 +} {1 {not authorized}}
1.1379 +do_test auth-1.206 {
1.1380 + set ::authargs
1.1381 +} {i2 t2 main {}}
1.1382 +do_test auth-1.207 {
1.1383 + execsql {SELECT name FROM sqlite_master}
1.1384 +} {t2 i2}
1.1385 +do_test auth-1.208 {
1.1386 + proc auth {code arg1 arg2 arg3 arg4} {
1.1387 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1.1388 + return SQLITE_IGNORE
1.1389 + }
1.1390 + return SQLITE_OK
1.1391 + }
1.1392 + catchsql {DROP INDEX i2}
1.1393 +} {0 {}}
1.1394 +do_test auth-1.209 {
1.1395 + execsql {SELECT name FROM sqlite_master}
1.1396 +} {t2 i2}
1.1397 +do_test auth-1.210 {
1.1398 + proc auth {code arg1 arg2 arg3 arg4} {
1.1399 + if {$code=="SQLITE_DROP_INDEX"} {
1.1400 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1401 + return SQLITE_IGNORE
1.1402 + }
1.1403 + return SQLITE_OK
1.1404 + }
1.1405 + catchsql {DROP INDEX i2}
1.1406 +} {0 {}}
1.1407 +do_test auth-1.211 {
1.1408 + set ::authargs
1.1409 +} {i2 t2 main {}}
1.1410 +do_test auth-1.212 {
1.1411 + execsql {SELECT name FROM sqlite_master}
1.1412 +} {t2 i2}
1.1413 +do_test auth-1.213 {
1.1414 + proc auth {code arg1 arg2 arg3 arg4} {
1.1415 + if {$code=="SQLITE_DROP_INDEX"} {
1.1416 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1417 + return SQLITE_OK
1.1418 + }
1.1419 + return SQLITE_OK
1.1420 + }
1.1421 + catchsql {DROP INDEX i2}
1.1422 +} {0 {}}
1.1423 +do_test auth-1.214 {
1.1424 + set ::authargs
1.1425 +} {i2 t2 main {}}
1.1426 +do_test auth-1.215 {
1.1427 + execsql {SELECT name FROM sqlite_master}
1.1428 +} {t2}
1.1429 +
1.1430 +ifcapable tempdb {
1.1431 + do_test auth-1.216 {
1.1432 + proc auth {code arg1 arg2 arg3 arg4} {
1.1433 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1.1434 + return SQLITE_DENY
1.1435 + }
1.1436 + return SQLITE_OK
1.1437 + }
1.1438 + catchsql {DROP INDEX i1}
1.1439 + } {1 {not authorized}}
1.1440 + do_test auth-1.217 {
1.1441 + execsql {SELECT name FROM sqlite_temp_master}
1.1442 + } {t1 i1}
1.1443 + do_test auth-1.218 {
1.1444 + proc auth {code arg1 arg2 arg3 arg4} {
1.1445 + if {$code=="SQLITE_DROP_TEMP_INDEX"} {
1.1446 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1447 + return SQLITE_DENY
1.1448 + }
1.1449 + return SQLITE_OK
1.1450 + }
1.1451 + catchsql {DROP INDEX i1}
1.1452 + } {1 {not authorized}}
1.1453 + do_test auth-1.219 {
1.1454 + set ::authargs
1.1455 + } {i1 t1 temp {}}
1.1456 + do_test auth-1.220 {
1.1457 + execsql {SELECT name FROM sqlite_temp_master}
1.1458 + } {t1 i1}
1.1459 + do_test auth-1.221 {
1.1460 + proc auth {code arg1 arg2 arg3 arg4} {
1.1461 + if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1.1462 + return SQLITE_IGNORE
1.1463 + }
1.1464 + return SQLITE_OK
1.1465 + }
1.1466 + catchsql {DROP INDEX i1}
1.1467 + } {0 {}}
1.1468 + do_test auth-1.222 {
1.1469 + execsql {SELECT name FROM sqlite_temp_master}
1.1470 + } {t1 i1}
1.1471 + do_test auth-1.223 {
1.1472 + proc auth {code arg1 arg2 arg3 arg4} {
1.1473 + if {$code=="SQLITE_DROP_TEMP_INDEX"} {
1.1474 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1475 + return SQLITE_IGNORE
1.1476 + }
1.1477 + return SQLITE_OK
1.1478 + }
1.1479 + catchsql {DROP INDEX i1}
1.1480 + } {0 {}}
1.1481 + do_test auth-1.224 {
1.1482 + set ::authargs
1.1483 + } {i1 t1 temp {}}
1.1484 + do_test auth-1.225 {
1.1485 + execsql {SELECT name FROM sqlite_temp_master}
1.1486 + } {t1 i1}
1.1487 + do_test auth-1.226 {
1.1488 + proc auth {code arg1 arg2 arg3 arg4} {
1.1489 + if {$code=="SQLITE_DROP_TEMP_INDEX"} {
1.1490 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1491 + return SQLITE_OK
1.1492 + }
1.1493 + return SQLITE_OK
1.1494 + }
1.1495 + catchsql {DROP INDEX i1}
1.1496 + } {0 {}}
1.1497 + do_test auth-1.227 {
1.1498 + set ::authargs
1.1499 + } {i1 t1 temp {}}
1.1500 + do_test auth-1.228 {
1.1501 + execsql {SELECT name FROM sqlite_temp_master}
1.1502 + } {t1}
1.1503 +}
1.1504 +
1.1505 +do_test auth-1.229 {
1.1506 + proc auth {code arg1 arg2 arg3 arg4} {
1.1507 + if {$code=="SQLITE_PRAGMA"} {
1.1508 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1509 + return SQLITE_DENY
1.1510 + }
1.1511 + return SQLITE_OK
1.1512 + }
1.1513 + catchsql {PRAGMA full_column_names=on}
1.1514 +} {1 {not authorized}}
1.1515 +do_test auth-1.230 {
1.1516 + set ::authargs
1.1517 +} {full_column_names on {} {}}
1.1518 +do_test auth-1.231 {
1.1519 + execsql2 {SELECT a FROM t2}
1.1520 +} {a 11 a 7}
1.1521 +do_test auth-1.232 {
1.1522 + proc auth {code arg1 arg2 arg3 arg4} {
1.1523 + if {$code=="SQLITE_PRAGMA"} {
1.1524 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1525 + return SQLITE_IGNORE
1.1526 + }
1.1527 + return SQLITE_OK
1.1528 + }
1.1529 + catchsql {PRAGMA full_column_names=on}
1.1530 +} {0 {}}
1.1531 +do_test auth-1.233 {
1.1532 + set ::authargs
1.1533 +} {full_column_names on {} {}}
1.1534 +do_test auth-1.234 {
1.1535 + execsql2 {SELECT a FROM t2}
1.1536 +} {a 11 a 7}
1.1537 +do_test auth-1.235 {
1.1538 + proc auth {code arg1 arg2 arg3 arg4} {
1.1539 + if {$code=="SQLITE_PRAGMA"} {
1.1540 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1541 + return SQLITE_OK
1.1542 + }
1.1543 + return SQLITE_OK
1.1544 + }
1.1545 + catchsql {PRAGMA full_column_names=on}
1.1546 +} {0 {}}
1.1547 +do_test auth-1.236 {
1.1548 + execsql2 {SELECT a FROM t2}
1.1549 +} {t2.a 11 t2.a 7}
1.1550 +do_test auth-1.237 {
1.1551 + proc auth {code arg1 arg2 arg3 arg4} {
1.1552 + if {$code=="SQLITE_PRAGMA"} {
1.1553 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1554 + return SQLITE_OK
1.1555 + }
1.1556 + return SQLITE_OK
1.1557 + }
1.1558 + catchsql {PRAGMA full_column_names=OFF}
1.1559 +} {0 {}}
1.1560 +do_test auth-1.238 {
1.1561 + set ::authargs
1.1562 +} {full_column_names OFF {} {}}
1.1563 +do_test auth-1.239 {
1.1564 + execsql2 {SELECT a FROM t2}
1.1565 +} {a 11 a 7}
1.1566 +
1.1567 +do_test auth-1.240 {
1.1568 + proc auth {code arg1 arg2 arg3 arg4} {
1.1569 + if {$code=="SQLITE_TRANSACTION"} {
1.1570 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1571 + return SQLITE_DENY
1.1572 + }
1.1573 + return SQLITE_OK
1.1574 + }
1.1575 + catchsql {BEGIN}
1.1576 +} {1 {not authorized}}
1.1577 +do_test auth-1.241 {
1.1578 + set ::authargs
1.1579 +} {BEGIN {} {} {}}
1.1580 +do_test auth-1.242 {
1.1581 + proc auth {code arg1 arg2 arg3 arg4} {
1.1582 + if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} {
1.1583 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1584 + return SQLITE_DENY
1.1585 + }
1.1586 + return SQLITE_OK
1.1587 + }
1.1588 + catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT}
1.1589 +} {1 {not authorized}}
1.1590 +do_test auth-1.243 {
1.1591 + set ::authargs
1.1592 +} {COMMIT {} {} {}}
1.1593 +do_test auth-1.244 {
1.1594 + execsql {SELECT * FROM t2}
1.1595 +} {11 2 33 7 8 9 44 55 66}
1.1596 +do_test auth-1.245 {
1.1597 + catchsql {ROLLBACK}
1.1598 +} {1 {not authorized}}
1.1599 +do_test auth-1.246 {
1.1600 + set ::authargs
1.1601 +} {ROLLBACK {} {} {}}
1.1602 +do_test auth-1.247 {
1.1603 + catchsql {END TRANSACTION}
1.1604 +} {1 {not authorized}}
1.1605 +do_test auth-1.248 {
1.1606 + set ::authargs
1.1607 +} {COMMIT {} {} {}}
1.1608 +do_test auth-1.249 {
1.1609 + db authorizer {}
1.1610 + catchsql {ROLLBACK}
1.1611 +} {0 {}}
1.1612 +do_test auth-1.250 {
1.1613 + execsql {SELECT * FROM t2}
1.1614 +} {11 2 33 7 8 9}
1.1615 +
1.1616 +# ticket #340 - authorization for ATTACH and DETACH.
1.1617 +#
1.1618 +ifcapable attach {
1.1619 + do_test auth-1.251 {
1.1620 + db authorizer ::auth
1.1621 + proc auth {code arg1 arg2 arg3 arg4} {
1.1622 + if {$code=="SQLITE_ATTACH"} {
1.1623 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1624 + }
1.1625 + return SQLITE_OK
1.1626 + }
1.1627 + catchsql {
1.1628 + ATTACH DATABASE ':memory:' AS test1
1.1629 + }
1.1630 + } {0 {}}
1.1631 + do_test auth-1.252 {
1.1632 + set ::authargs
1.1633 + } {:memory: {} {} {}}
1.1634 + do_test auth-1.253 {
1.1635 + catchsql {DETACH DATABASE test1}
1.1636 + proc auth {code arg1 arg2 arg3 arg4} {
1.1637 + if {$code=="SQLITE_ATTACH"} {
1.1638 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1639 + return SQLITE_DENY
1.1640 + }
1.1641 + return SQLITE_OK
1.1642 + }
1.1643 + catchsql {
1.1644 + ATTACH DATABASE ':memory:' AS test1;
1.1645 + }
1.1646 + } {1 {not authorized}}
1.1647 + do_test auth-1.254 {
1.1648 + lindex [execsql {PRAGMA database_list}] 7
1.1649 + } {}
1.1650 + do_test auth-1.255 {
1.1651 + catchsql {DETACH DATABASE test1}
1.1652 + proc auth {code arg1 arg2 arg3 arg4} {
1.1653 + if {$code=="SQLITE_ATTACH"} {
1.1654 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1655 + return SQLITE_IGNORE
1.1656 + }
1.1657 + return SQLITE_OK
1.1658 + }
1.1659 + catchsql {
1.1660 + ATTACH DATABASE ':memory:' AS test1;
1.1661 + }
1.1662 + } {0 {}}
1.1663 + do_test auth-1.256 {
1.1664 + lindex [execsql {PRAGMA database_list}] 7
1.1665 + } {}
1.1666 + do_test auth-1.257 {
1.1667 + proc auth {code arg1 arg2 arg3 arg4} {
1.1668 + if {$code=="SQLITE_DETACH"} {
1.1669 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1670 + return SQLITE_OK
1.1671 + }
1.1672 + return SQLITE_OK
1.1673 + }
1.1674 + execsql {ATTACH DATABASE ':memory:' AS test1}
1.1675 + catchsql {
1.1676 + DETACH DATABASE test1;
1.1677 + }
1.1678 + } {0 {}}
1.1679 + do_test auth-1.258 {
1.1680 + lindex [execsql {PRAGMA database_list}] 7
1.1681 + } {}
1.1682 + do_test auth-1.259 {
1.1683 + execsql {ATTACH DATABASE ':memory:' AS test1}
1.1684 + proc auth {code arg1 arg2 arg3 arg4} {
1.1685 + if {$code=="SQLITE_DETACH"} {
1.1686 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1687 + return SQLITE_IGNORE
1.1688 + }
1.1689 + return SQLITE_OK
1.1690 + }
1.1691 + catchsql {
1.1692 + DETACH DATABASE test1;
1.1693 + }
1.1694 + } {0 {}}
1.1695 + ifcapable tempdb {
1.1696 + ifcapable schema_pragmas {
1.1697 + do_test auth-1.260 {
1.1698 + lindex [execsql {PRAGMA database_list}] 7
1.1699 + } {test1}
1.1700 + } ;# ifcapable schema_pragmas
1.1701 + do_test auth-1.261 {
1.1702 + proc auth {code arg1 arg2 arg3 arg4} {
1.1703 + if {$code=="SQLITE_DETACH"} {
1.1704 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1705 + return SQLITE_DENY
1.1706 + }
1.1707 + return SQLITE_OK
1.1708 + }
1.1709 + catchsql {
1.1710 + DETACH DATABASE test1;
1.1711 + }
1.1712 + } {1 {not authorized}}
1.1713 + ifcapable schema_pragmas {
1.1714 + do_test auth-1.262 {
1.1715 + lindex [execsql {PRAGMA database_list}] 7
1.1716 + } {test1}
1.1717 + } ;# ifcapable schema_pragmas
1.1718 + db authorizer {}
1.1719 + execsql {DETACH DATABASE test1}
1.1720 + db authorizer ::auth
1.1721 +
1.1722 + # Authorization for ALTER TABLE. These tests are omitted if the library
1.1723 + # was built without ALTER TABLE support.
1.1724 + ifcapable altertable {
1.1725 +
1.1726 + do_test auth-1.263 {
1.1727 + proc auth {code arg1 arg2 arg3 arg4} {
1.1728 + if {$code=="SQLITE_ALTER_TABLE"} {
1.1729 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1730 + return SQLITE_OK
1.1731 + }
1.1732 + return SQLITE_OK
1.1733 + }
1.1734 + catchsql {
1.1735 + ALTER TABLE t1 RENAME TO t1x
1.1736 + }
1.1737 + } {0 {}}
1.1738 + do_test auth-1.264 {
1.1739 + execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
1.1740 + } {t1x}
1.1741 + do_test auth-1.265 {
1.1742 + set authargs
1.1743 + } {temp t1 {} {}}
1.1744 + do_test auth-1.266 {
1.1745 + proc auth {code arg1 arg2 arg3 arg4} {
1.1746 + if {$code=="SQLITE_ALTER_TABLE"} {
1.1747 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1748 + return SQLITE_IGNORE
1.1749 + }
1.1750 + return SQLITE_OK
1.1751 + }
1.1752 + catchsql {
1.1753 + ALTER TABLE t1x RENAME TO t1
1.1754 + }
1.1755 + } {0 {}}
1.1756 + do_test auth-1.267 {
1.1757 + execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
1.1758 + } {t1x}
1.1759 + do_test auth-1.268 {
1.1760 + set authargs
1.1761 + } {temp t1x {} {}}
1.1762 + do_test auth-1.269 {
1.1763 + proc auth {code arg1 arg2 arg3 arg4} {
1.1764 + if {$code=="SQLITE_ALTER_TABLE"} {
1.1765 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1766 + return SQLITE_DENY
1.1767 + }
1.1768 + return SQLITE_OK
1.1769 + }
1.1770 + catchsql {
1.1771 + ALTER TABLE t1x RENAME TO t1
1.1772 + }
1.1773 + } {1 {not authorized}}
1.1774 + do_test auth-1.270 {
1.1775 + execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
1.1776 + } {t1x}
1.1777 +
1.1778 + do_test auth-1.271 {
1.1779 + set authargs
1.1780 + } {temp t1x {} {}}
1.1781 + } ;# ifcapable altertable
1.1782 +
1.1783 + } else {
1.1784 + db authorizer {}
1.1785 + db eval {
1.1786 + DETACH DATABASE test1;
1.1787 + }
1.1788 + }
1.1789 +}
1.1790 +
1.1791 +ifcapable altertable {
1.1792 +db authorizer {}
1.1793 +catchsql {ALTER TABLE t1x RENAME TO t1}
1.1794 +db authorizer ::auth
1.1795 +do_test auth-1.272 {
1.1796 + proc auth {code arg1 arg2 arg3 arg4} {
1.1797 + if {$code=="SQLITE_ALTER_TABLE"} {
1.1798 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1799 + return SQLITE_OK
1.1800 + }
1.1801 + return SQLITE_OK
1.1802 + }
1.1803 + catchsql {
1.1804 + ALTER TABLE t2 RENAME TO t2x
1.1805 + }
1.1806 +} {0 {}}
1.1807 +do_test auth-1.273 {
1.1808 + execsql {SELECT name FROM sqlite_master WHERE type='table'}
1.1809 +} {t2x}
1.1810 +do_test auth-1.274 {
1.1811 + set authargs
1.1812 +} {main t2 {} {}}
1.1813 +do_test auth-1.275 {
1.1814 + proc auth {code arg1 arg2 arg3 arg4} {
1.1815 + if {$code=="SQLITE_ALTER_TABLE"} {
1.1816 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1817 + return SQLITE_IGNORE
1.1818 + }
1.1819 + return SQLITE_OK
1.1820 + }
1.1821 + catchsql {
1.1822 + ALTER TABLE t2x RENAME TO t2
1.1823 + }
1.1824 +} {0 {}}
1.1825 +do_test auth-1.276 {
1.1826 + execsql {SELECT name FROM sqlite_master WHERE type='table'}
1.1827 +} {t2x}
1.1828 +do_test auth-1.277 {
1.1829 + set authargs
1.1830 +} {main t2x {} {}}
1.1831 +do_test auth-1.278 {
1.1832 + proc auth {code arg1 arg2 arg3 arg4} {
1.1833 + if {$code=="SQLITE_ALTER_TABLE"} {
1.1834 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.1835 + return SQLITE_DENY
1.1836 + }
1.1837 + return SQLITE_OK
1.1838 + }
1.1839 + catchsql {
1.1840 + ALTER TABLE t2x RENAME TO t2
1.1841 + }
1.1842 +} {1 {not authorized}}
1.1843 +do_test auth-1.279 {
1.1844 + execsql {SELECT name FROM sqlite_master WHERE type='table'}
1.1845 +} {t2x}
1.1846 +do_test auth-1.280 {
1.1847 + set authargs
1.1848 +} {main t2x {} {}}
1.1849 +db authorizer {}
1.1850 +catchsql {ALTER TABLE t2x RENAME TO t2}
1.1851 +
1.1852 +} ;# ifcapable altertable
1.1853 +
1.1854 +# Test the authorization callbacks for the REINDEX command.
1.1855 +ifcapable reindex {
1.1856 +
1.1857 +proc auth {code args} {
1.1858 + if {$code=="SQLITE_REINDEX"} {
1.1859 + set ::authargs [concat $::authargs $args]
1.1860 + }
1.1861 + return SQLITE_OK
1.1862 +}
1.1863 +db authorizer auth
1.1864 +do_test auth-1.281 {
1.1865 + execsql {
1.1866 + CREATE TABLE t3(a PRIMARY KEY, b, c);
1.1867 + CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);
1.1868 + CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);
1.1869 + }
1.1870 +} {}
1.1871 +do_test auth-1.282 {
1.1872 + set ::authargs {}
1.1873 + execsql {
1.1874 + REINDEX t3_idx1;
1.1875 + }
1.1876 + set ::authargs
1.1877 +} {t3_idx1 {} main {}}
1.1878 +do_test auth-1.283 {
1.1879 + set ::authargs {}
1.1880 + execsql {
1.1881 + REINDEX BINARY;
1.1882 + }
1.1883 + set ::authargs
1.1884 +} {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}
1.1885 +do_test auth-1.284 {
1.1886 + set ::authargs {}
1.1887 + execsql {
1.1888 + REINDEX NOCASE;
1.1889 + }
1.1890 + set ::authargs
1.1891 +} {t3_idx2 {} main {}}
1.1892 +do_test auth-1.285 {
1.1893 + set ::authargs {}
1.1894 + execsql {
1.1895 + REINDEX t3;
1.1896 + }
1.1897 + set ::authargs
1.1898 +} {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}
1.1899 +do_test auth-1.286 {
1.1900 + execsql {
1.1901 + DROP TABLE t3;
1.1902 + }
1.1903 +} {}
1.1904 +ifcapable tempdb {
1.1905 + do_test auth-1.287 {
1.1906 + execsql {
1.1907 + CREATE TEMP TABLE t3(a PRIMARY KEY, b, c);
1.1908 + CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);
1.1909 + CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);
1.1910 + }
1.1911 + } {}
1.1912 + do_test auth-1.288 {
1.1913 + set ::authargs {}
1.1914 + execsql {
1.1915 + REINDEX temp.t3_idx1;
1.1916 + }
1.1917 + set ::authargs
1.1918 + } {t3_idx1 {} temp {}}
1.1919 + do_test auth-1.289 {
1.1920 + set ::authargs {}
1.1921 + execsql {
1.1922 + REINDEX BINARY;
1.1923 + }
1.1924 + set ::authargs
1.1925 + } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
1.1926 + do_test auth-1.290 {
1.1927 + set ::authargs {}
1.1928 + execsql {
1.1929 + REINDEX NOCASE;
1.1930 + }
1.1931 + set ::authargs
1.1932 + } {t3_idx2 {} temp {}}
1.1933 + do_test auth-1.291 {
1.1934 + set ::authargs {}
1.1935 + execsql {
1.1936 + REINDEX temp.t3;
1.1937 + }
1.1938 + set ::authargs
1.1939 + } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
1.1940 + proc auth {code args} {
1.1941 + if {$code=="SQLITE_REINDEX"} {
1.1942 + set ::authargs [concat $::authargs $args]
1.1943 + return SQLITE_DENY
1.1944 + }
1.1945 + return SQLITE_OK
1.1946 + }
1.1947 + do_test auth-1.292 {
1.1948 + set ::authargs {}
1.1949 + catchsql {
1.1950 + REINDEX temp.t3;
1.1951 + }
1.1952 + } {1 {not authorized}}
1.1953 + do_test auth-1.293 {
1.1954 + execsql {
1.1955 + DROP TABLE t3;
1.1956 + }
1.1957 + } {}
1.1958 +}
1.1959 +
1.1960 +} ;# ifcapable reindex
1.1961 +
1.1962 +ifcapable analyze {
1.1963 + proc auth {code args} {
1.1964 + if {$code=="SQLITE_ANALYZE"} {
1.1965 + set ::authargs [concat $::authargs $args]
1.1966 + }
1.1967 + return SQLITE_OK
1.1968 + }
1.1969 + do_test auth-1.294 {
1.1970 + set ::authargs {}
1.1971 + execsql {
1.1972 + CREATE TABLE t4(a,b,c);
1.1973 + CREATE INDEX t4i1 ON t4(a);
1.1974 + CREATE INDEX t4i2 ON t4(b,a,c);
1.1975 + INSERT INTO t4 VALUES(1,2,3);
1.1976 + ANALYZE;
1.1977 + }
1.1978 + set ::authargs
1.1979 + } {t4 {} main {}}
1.1980 + do_test auth-1.295 {
1.1981 + execsql {
1.1982 + SELECT count(*) FROM sqlite_stat1;
1.1983 + }
1.1984 + } 2
1.1985 + proc auth {code args} {
1.1986 + if {$code=="SQLITE_ANALYZE"} {
1.1987 + set ::authargs [concat $::authargs $args]
1.1988 + return SQLITE_DENY
1.1989 + }
1.1990 + return SQLITE_OK
1.1991 + }
1.1992 + do_test auth-1.296 {
1.1993 + set ::authargs {}
1.1994 + catchsql {
1.1995 + ANALYZE;
1.1996 + }
1.1997 + } {1 {not authorized}}
1.1998 + do_test auth-1.297 {
1.1999 + execsql {
1.2000 + SELECT count(*) FROM sqlite_stat1;
1.2001 + }
1.2002 + } 2
1.2003 +} ;# ifcapable analyze
1.2004 +
1.2005 +
1.2006 +# Authorization for ALTER TABLE ADD COLUMN.
1.2007 +# These tests are omitted if the library
1.2008 +# was built without ALTER TABLE support.
1.2009 +ifcapable {altertable} {
1.2010 + do_test auth-1.300 {
1.2011 + execsql {CREATE TABLE t5(x)}
1.2012 + proc auth {code arg1 arg2 arg3 arg4} {
1.2013 + if {$code=="SQLITE_ALTER_TABLE"} {
1.2014 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.2015 + return SQLITE_OK
1.2016 + }
1.2017 + return SQLITE_OK
1.2018 + }
1.2019 + catchsql {
1.2020 + ALTER TABLE t5 ADD COLUMN new_col_1;
1.2021 + }
1.2022 + } {0 {}}
1.2023 + do_test auth-1.301 {
1.2024 + set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]
1.2025 + regexp new_col_1 $x
1.2026 + } {1}
1.2027 + do_test auth-1.302 {
1.2028 + set authargs
1.2029 + } {main t5 {} {}}
1.2030 + do_test auth-1.303 {
1.2031 + proc auth {code arg1 arg2 arg3 arg4} {
1.2032 + if {$code=="SQLITE_ALTER_TABLE"} {
1.2033 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.2034 + return SQLITE_IGNORE
1.2035 + }
1.2036 + return SQLITE_OK
1.2037 + }
1.2038 + catchsql {
1.2039 + ALTER TABLE t5 ADD COLUMN new_col_2;
1.2040 + }
1.2041 + } {0 {}}
1.2042 + do_test auth-1.304 {
1.2043 + set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]
1.2044 + regexp new_col_2 $x
1.2045 + } {0}
1.2046 + do_test auth-1.305 {
1.2047 + set authargs
1.2048 + } {main t5 {} {}}
1.2049 + do_test auth-1.306 {
1.2050 + proc auth {code arg1 arg2 arg3 arg4} {
1.2051 + if {$code=="SQLITE_ALTER_TABLE"} {
1.2052 + set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1.2053 + return SQLITE_DENY
1.2054 + }
1.2055 + return SQLITE_OK
1.2056 + }
1.2057 + catchsql {
1.2058 + ALTER TABLE t5 ADD COLUMN new_col_3
1.2059 + }
1.2060 + } {1 {not authorized}}
1.2061 + do_test auth-1.307 {
1.2062 + set x [execsql {SELECT sql FROM sqlite_temp_master WHERE type='t5'}]
1.2063 + regexp new_col_3 $x
1.2064 + } {0}
1.2065 +
1.2066 + do_test auth-1.308 {
1.2067 + set authargs
1.2068 + } {main t5 {} {}}
1.2069 + execsql {DROP TABLE t5}
1.2070 +} ;# ifcapable altertable
1.2071 +
1.2072 +do_test auth-2.1 {
1.2073 + proc auth {code arg1 arg2 arg3 arg4} {
1.2074 + if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
1.2075 + return SQLITE_DENY
1.2076 + }
1.2077 + return SQLITE_OK
1.2078 + }
1.2079 + db authorizer ::auth
1.2080 + execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)}
1.2081 + catchsql {SELECT * FROM t3}
1.2082 +} {1 {access to t3.x is prohibited}}
1.2083 +do_test auth-2.1 {
1.2084 + catchsql {SELECT y,z FROM t3}
1.2085 +} {0 {}}
1.2086 +do_test auth-2.2 {
1.2087 + catchsql {SELECT ROWID,y,z FROM t3}
1.2088 +} {1 {access to t3.x is prohibited}}
1.2089 +do_test auth-2.3 {
1.2090 + catchsql {SELECT OID,y,z FROM t3}
1.2091 +} {1 {access to t3.x is prohibited}}
1.2092 +do_test auth-2.4 {
1.2093 + proc auth {code arg1 arg2 arg3 arg4} {
1.2094 + if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
1.2095 + return SQLITE_IGNORE
1.2096 + }
1.2097 + return SQLITE_OK
1.2098 + }
1.2099 + execsql {INSERT INTO t3 VALUES(44,55,66)}
1.2100 + catchsql {SELECT * FROM t3}
1.2101 +} {0 {{} 55 66}}
1.2102 +do_test auth-2.5 {
1.2103 + catchsql {SELECT rowid,y,z FROM t3}
1.2104 +} {0 {{} 55 66}}
1.2105 +do_test auth-2.6 {
1.2106 + proc auth {code arg1 arg2 arg3 arg4} {
1.2107 + if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} {
1.2108 + return SQLITE_IGNORE
1.2109 + }
1.2110 + return SQLITE_OK
1.2111 + }
1.2112 + catchsql {SELECT * FROM t3}
1.2113 +} {0 {44 55 66}}
1.2114 +do_test auth-2.7 {
1.2115 + catchsql {SELECT ROWID,y,z FROM t3}
1.2116 +} {0 {44 55 66}}
1.2117 +do_test auth-2.8 {
1.2118 + proc auth {code arg1 arg2 arg3 arg4} {
1.2119 + if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
1.2120 + return SQLITE_IGNORE
1.2121 + }
1.2122 + return SQLITE_OK
1.2123 + }
1.2124 + catchsql {SELECT ROWID,b,c FROM t2}
1.2125 +} {0 {{} 2 33 {} 8 9}}
1.2126 +do_test auth-2.9.1 {
1.2127 + # We have to flush the cache here in case the Tcl interface tries to
1.2128 + # reuse a statement compiled with sqlite3_prepare_v2(). In this case,
1.2129 + # the first error encountered is an SQLITE_SCHEMA error. Then, when
1.2130 + # trying to recompile the statement, the authorization error is encountered.
1.2131 + # If we do not flush the cache, the correct error message is returned, but
1.2132 + # the error code is SQLITE_SCHEMA, not SQLITE_ERROR as required by the test
1.2133 + # case after this one.
1.2134 + #
1.2135 + db cache flush
1.2136 +
1.2137 + proc auth {code arg1 arg2 arg3 arg4} {
1.2138 + if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
1.2139 + return bogus
1.2140 + }
1.2141 + return SQLITE_OK
1.2142 + }
1.2143 + catchsql {SELECT ROWID,b,c FROM t2}
1.2144 +} {1 {illegal return value (999) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}
1.2145 +do_test auth-2.9.2 {
1.2146 + db errorcode
1.2147 +} {1}
1.2148 +do_test auth-2.10 {
1.2149 + proc auth {code arg1 arg2 arg3 arg4} {
1.2150 + if {$code=="SQLITE_SELECT"} {
1.2151 + return bogus
1.2152 + }
1.2153 + return SQLITE_OK
1.2154 + }
1.2155 + catchsql {SELECT ROWID,b,c FROM t2}
1.2156 +} {1 {illegal return value (1) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}
1.2157 +do_test auth-2.11.1 {
1.2158 + proc auth {code arg1 arg2 arg3 arg4} {
1.2159 + if {$code=="SQLITE_READ" && $arg2=="a"} {
1.2160 + return SQLITE_IGNORE
1.2161 + }
1.2162 + return SQLITE_OK
1.2163 + }
1.2164 + catchsql {SELECT * FROM t2, t3}
1.2165 +} {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}}
1.2166 +do_test auth-2.11.2 {
1.2167 + proc auth {code arg1 arg2 arg3 arg4} {
1.2168 + if {$code=="SQLITE_READ" && $arg2=="x"} {
1.2169 + return SQLITE_IGNORE
1.2170 + }
1.2171 + return SQLITE_OK
1.2172 + }
1.2173 + catchsql {SELECT * FROM t2, t3}
1.2174 +} {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}}
1.2175 +
1.2176 +# Make sure the OLD and NEW pseudo-tables of a trigger get authorized.
1.2177 +#
1.2178 +ifcapable trigger {
1.2179 + do_test auth-3.1 {
1.2180 + proc auth {code arg1 arg2 arg3 arg4} {
1.2181 + return SQLITE_OK
1.2182 + }
1.2183 + execsql {
1.2184 + CREATE TABLE tx(a1,a2,b1,b2,c1,c2);
1.2185 + CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN
1.2186 + INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c);
1.2187 + END;
1.2188 + UPDATE t2 SET a=a+1;
1.2189 + SELECT * FROM tx;
1.2190 + }
1.2191 + } {11 12 2 2 33 33 7 8 8 8 9 9}
1.2192 + do_test auth-3.2 {
1.2193 + proc auth {code arg1 arg2 arg3 arg4} {
1.2194 + if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} {
1.2195 + return SQLITE_IGNORE
1.2196 + }
1.2197 + return SQLITE_OK
1.2198 + }
1.2199 + execsql {
1.2200 + DELETE FROM tx;
1.2201 + UPDATE t2 SET a=a+100;
1.2202 + SELECT * FROM tx;
1.2203 + }
1.2204 + } {12 112 2 2 {} {} 8 108 8 8 {} {}}
1.2205 +} ;# ifcapable trigger
1.2206 +
1.2207 +# Make sure the names of views and triggers are passed on on arg4.
1.2208 +#
1.2209 +ifcapable trigger {
1.2210 +do_test auth-4.1 {
1.2211 + proc auth {code arg1 arg2 arg3 arg4} {
1.2212 + lappend ::authargs $code $arg1 $arg2 $arg3 $arg4
1.2213 + return SQLITE_OK
1.2214 + }
1.2215 + set authargs {}
1.2216 + execsql {
1.2217 + UPDATE t2 SET a=a+1;
1.2218 + }
1.2219 + set authargs
1.2220 +} [list \
1.2221 + SQLITE_READ t2 a main {} \
1.2222 + SQLITE_UPDATE t2 a main {} \
1.2223 + SQLITE_INSERT tx {} main r1 \
1.2224 + SQLITE_READ t2 a main r1 \
1.2225 + SQLITE_READ t2 a main r1 \
1.2226 + SQLITE_READ t2 b main r1 \
1.2227 + SQLITE_READ t2 b main r1 \
1.2228 + SQLITE_READ t2 c main r1 \
1.2229 + SQLITE_READ t2 c main r1]
1.2230 +}
1.2231 +
1.2232 +ifcapable {view && trigger} {
1.2233 +do_test auth-4.2 {
1.2234 + execsql {
1.2235 + CREATE VIEW v1 AS SELECT a+b AS x FROM t2;
1.2236 + CREATE TABLE v1chng(x1,x2);
1.2237 + CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN
1.2238 + INSERT INTO v1chng VALUES(OLD.x,NEW.x);
1.2239 + END;
1.2240 + SELECT * FROM v1;
1.2241 + }
1.2242 +} {115 117}
1.2243 +do_test auth-4.3 {
1.2244 + set authargs {}
1.2245 + execsql {
1.2246 + UPDATE v1 SET x=1 WHERE x=117
1.2247 + }
1.2248 + set authargs
1.2249 +} [list \
1.2250 + SQLITE_UPDATE v1 x main {} \
1.2251 + SQLITE_INSERT v1chng {} main r2 \
1.2252 + SQLITE_READ v1 x main r2 \
1.2253 + SQLITE_READ v1 x main r2 \
1.2254 + SQLITE_SELECT {} {} {} v1 \
1.2255 + SQLITE_READ t2 a main v1 \
1.2256 + SQLITE_READ t2 b main v1 \
1.2257 + SQLITE_SELECT {} {} {} {} \
1.2258 + SQLITE_READ v1 x main v1 \
1.2259 +]
1.2260 +do_test auth-4.4 {
1.2261 + execsql {
1.2262 + CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
1.2263 + INSERT INTO v1chng VALUES(OLD.x,NULL);
1.2264 + END;
1.2265 + SELECT * FROM v1;
1.2266 + }
1.2267 +} {115 117}
1.2268 +do_test auth-4.5 {
1.2269 + set authargs {}
1.2270 + execsql {
1.2271 + DELETE FROM v1 WHERE x=117
1.2272 + }
1.2273 + set authargs
1.2274 +} [list \
1.2275 + SQLITE_DELETE v1 {} main {} \
1.2276 + SQLITE_INSERT v1chng {} main r3 \
1.2277 + SQLITE_READ v1 x main r3 \
1.2278 + SQLITE_SELECT {} {} {} v1 \
1.2279 + SQLITE_READ t2 a main v1 \
1.2280 + SQLITE_READ t2 b main v1 \
1.2281 + SQLITE_SELECT {} {} {} {} \
1.2282 + SQLITE_READ v1 x main v1 \
1.2283 +]
1.2284 +
1.2285 +} ;# ifcapable view && trigger
1.2286 +
1.2287 +# Ticket #1338: Make sure authentication works in the presence of an AS
1.2288 +# clause.
1.2289 +#
1.2290 +do_test auth-5.1 {
1.2291 + proc auth {code arg1 arg2 arg3 arg4} {
1.2292 + return SQLITE_OK
1.2293 + }
1.2294 + execsql {
1.2295 + SELECT count(a) AS cnt FROM t4 ORDER BY cnt
1.2296 + }
1.2297 +} {1}
1.2298 +
1.2299 +# Ticket #1607
1.2300 +#
1.2301 +ifcapable compound&&subquery {
1.2302 + ifcapable trigger {
1.2303 + execsql {
1.2304 + DROP TABLE tx;
1.2305 + }
1.2306 + ifcapable view {
1.2307 + execsql {
1.2308 + DROP TABLE v1chng;
1.2309 + }
1.2310 + }
1.2311 + }
1.2312 + do_test auth-5.2 {
1.2313 + execsql {
1.2314 + SELECT name FROM (
1.2315 + SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)
1.2316 + WHERE type='table'
1.2317 + ORDER BY name
1.2318 + }
1.2319 + } {sqlite_stat1 t1 t2 t3 t4}
1.2320 +}
1.2321 +
1.2322 +
1.2323 +rename proc {}
1.2324 +rename proc_real proc
1.2325 +
1.2326 +
1.2327 +finish_test