Symaptic: os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/auth.test@bde4ae8d615e

     1 # 2003 April 4

2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.  The

    12 # focus of this script is testing the sqlite3_set_authorizer() API

    13 # and related functionality.

    14 #

    15 # $Id: auth.test,v 1.43 2008/07/02 13:13:52 danielk1977 Exp $

    16 #

    18 set testdir [file dirname $argv0]

    19 source $testdir/tester.tcl

    21 # disable this test if the SQLITE_OMIT_AUTHORIZATION macro is

    22 # defined during compilation.

    23 if {[catch {db auth {}} msg]} {

    24   finish_test

    25   return

    26 }

    28 rename proc proc_real

    29 proc_real proc {name arguments script} {

    30   proc_real $name $arguments $script

    31   if {$name=="auth"} {

    32     db authorizer ::auth

    33   }

    34 }

    36 do_test auth-1.1.1 {

    37   db close

    38   set ::DB [sqlite3 db test.db]

    39   proc auth {code arg1 arg2 arg3 arg4} {

    40     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {

    41       return SQLITE_DENY

    42     }

    43     return SQLITE_OK

    44   }

    45   db authorizer ::auth

    46   catchsql {CREATE TABLE t1(a,b,c)}

    47 } {1 {not authorized}}

    48 do_test auth-1.1.2 {

    49   db errorcode

    50 } {23}

    51 do_test auth-1.1.3 {

    52   db authorizer

    53 } {::auth}

    54 do_test auth-1.1.4 {

    55   # Ticket #896.

    56   catchsql {

    57     SELECT x;

    58   }

    59 } {1 {no such column: x}}

    60 do_test auth-1.2 {

    61   execsql {SELECT name FROM sqlite_master}

    62 } {}

    63 do_test auth-1.3.1 {

    64   proc auth {code arg1 arg2 arg3 arg4} {

    65     if {$code=="SQLITE_CREATE_TABLE"} {

    66       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

    67       return SQLITE_DENY

    68     }

    69     return SQLITE_OK

    70   }

    71   catchsql {CREATE TABLE t1(a,b,c)}

    72 } {1 {not authorized}}

    73 do_test auth-1.3.2 {

    74   db errorcode

    75 } {23}

    76 do_test auth-1.3.3 {

    77   set ::authargs

    78 } {t1 {} main {}}

    79 do_test auth-1.4 {

    80   execsql {SELECT name FROM sqlite_master}

    81 } {}

    83 ifcapable tempdb {

    84   do_test auth-1.5 {

    85     proc auth {code arg1 arg2 arg3 arg4} {

    86       if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {

    87         return SQLITE_DENY

    88       }

    89       return SQLITE_OK

    90     }

    91     catchsql {CREATE TEMP TABLE t1(a,b,c)}

    92   } {1 {not authorized}}

    93   do_test auth-1.6 {

    94     execsql {SELECT name FROM sqlite_temp_master}

    95   } {}

    96   do_test auth-1.7.1 {

    97     proc auth {code arg1 arg2 arg3 arg4} {

    98       if {$code=="SQLITE_CREATE_TEMP_TABLE"} {

    99         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   100         return SQLITE_DENY

   101       }

   102       return SQLITE_OK

   103     }

   104     catchsql {CREATE TEMP TABLE t1(a,b,c)}

   105   } {1 {not authorized}}

   106   do_test auth-1.7.2 {

   107      set ::authargs

   108   } {t1 {} temp {}}

   109   do_test auth-1.8 {

   110     execsql {SELECT name FROM sqlite_temp_master}

   111   } {}

   112 }

   114 do_test auth-1.9 {

   115   proc auth {code arg1 arg2 arg3 arg4} {

   116     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {

   117       return SQLITE_IGNORE

   118     }

   119     return SQLITE_OK

   120   }

   121   catchsql {CREATE TABLE t1(a,b,c)}

   122 } {0 {}}

   123 do_test auth-1.10 {

   124   execsql {SELECT name FROM sqlite_master}

   125 } {}

   126 do_test auth-1.11 {

   127   proc auth {code arg1 arg2 arg3 arg4} {

   128     if {$code=="SQLITE_CREATE_TABLE"} {

   129       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   130       return SQLITE_IGNORE

   131     }

   132     return SQLITE_OK

   133   }

   134   catchsql {CREATE TABLE t1(a,b,c)}

   135 } {0 {}}

   136 do_test auth-1.12 {

   137   execsql {SELECT name FROM sqlite_master}

   138 } {}

   140 ifcapable tempdb {

   141   do_test auth-1.13 {

   142     proc auth {code arg1 arg2 arg3 arg4} {

   143       if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {

   144         return SQLITE_IGNORE

   145       }

   146       return SQLITE_OK

   147     }

   148     catchsql {CREATE TEMP TABLE t1(a,b,c)}

   149   } {0 {}}

   150   do_test auth-1.14 {

   151     execsql {SELECT name FROM sqlite_temp_master}

   152   } {}

   153   do_test auth-1.15 {

   154     proc auth {code arg1 arg2 arg3 arg4} {

   155       if {$code=="SQLITE_CREATE_TEMP_TABLE"} {

   156         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   157         return SQLITE_IGNORE

   158       }

   159       return SQLITE_OK

   160     }

   161     catchsql {CREATE TEMP TABLE t1(a,b,c)}

   162   } {0 {}}

   163   do_test auth-1.16 {

   164     execsql {SELECT name FROM sqlite_temp_master}

   165   } {}

   167   do_test auth-1.17 {

   168     proc auth {code arg1 arg2 arg3 arg4} {

   169       if {$code=="SQLITE_CREATE_TABLE"} {

   170         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   171         return SQLITE_DENY

   172       }

   173       return SQLITE_OK

   174     }

   175     catchsql {CREATE TEMP TABLE t1(a,b,c)}

   176   } {0 {}}

   177   do_test auth-1.18 {

   178     execsql {SELECT name FROM sqlite_temp_master}

   179   } {t1}

   180 }

   182 do_test auth-1.19.1 {

   183   set ::authargs {}

   184   proc auth {code arg1 arg2 arg3 arg4} {

   185     if {$code=="SQLITE_CREATE_TEMP_TABLE"} {

   186       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   187       return SQLITE_DENY

   188     }

   189     return SQLITE_OK

   190   }

   191   catchsql {CREATE TABLE t2(a,b,c)}

   192 } {0 {}}

   193 do_test auth-1.19.2 {

   194   set ::authargs

   195 } {}

   196 do_test auth-1.20 {

   197   execsql {SELECT name FROM sqlite_master}

   198 } {t2}

   200 do_test auth-1.21.1 {

   201   proc auth {code arg1 arg2 arg3 arg4} {

   202     if {$code=="SQLITE_DROP_TABLE"} {

   203       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   204       return SQLITE_DENY

   205     }

   206     return SQLITE_OK

   207   }

   208   catchsql {DROP TABLE t2}

   209 } {1 {not authorized}}

   210 do_test auth-1.21.2 {

   211   set ::authargs

   212 } {t2 {} main {}}

   213 do_test auth-1.22 {

   214   execsql {SELECT name FROM sqlite_master}

   215 } {t2}

   216 do_test auth-1.23.1 {

   217   proc auth {code arg1 arg2 arg3 arg4} {

   218     if {$code=="SQLITE_DROP_TABLE"} {

   219       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   220       return SQLITE_IGNORE

   221     }

   222     return SQLITE_OK

   223   }

   224   catchsql {DROP TABLE t2}

   225 } {0 {}}

   226 do_test auth-1.23.2 {

   227   set ::authargs

   228 } {t2 {} main {}}

   229 do_test auth-1.24 {

   230   execsql {SELECT name FROM sqlite_master}

   231 } {t2}

   233 ifcapable tempdb {

   234   do_test auth-1.25 {

   235     proc auth {code arg1 arg2 arg3 arg4} {

   236       if {$code=="SQLITE_DROP_TEMP_TABLE"} {

   237         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   238         return SQLITE_DENY

   239       }

   240       return SQLITE_OK

   241     }

   242     catchsql {DROP TABLE t1}

   243   } {1 {not authorized}}

   244   do_test auth-1.26 {

   245     execsql {SELECT name FROM sqlite_temp_master}

   246   } {t1}

   247   do_test auth-1.27 {

   248     proc auth {code arg1 arg2 arg3 arg4} {

   249       if {$code=="SQLITE_DROP_TEMP_TABLE"} {

   250         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   251         return SQLITE_IGNORE

   252       }

   253       return SQLITE_OK

   254     }

   255     catchsql {DROP TABLE t1}

   256   } {0 {}}

   257   do_test auth-1.28 {

   258     execsql {SELECT name FROM sqlite_temp_master}

   259   } {t1}

   260 }

   262 do_test auth-1.29 {

   263   proc auth {code arg1 arg2 arg3 arg4} {

   264     if {$code=="SQLITE_INSERT" && $arg1=="t2"} {

   265       return SQLITE_DENY

   266     }

   267     return SQLITE_OK

   268   }

   269   catchsql {INSERT INTO t2 VALUES(1,2,3)}

   270 } {1 {not authorized}}

   271 do_test auth-1.30 {

   272   execsql {SELECT * FROM t2}

   273 } {}

   274 do_test auth-1.31 {

   275   proc auth {code arg1 arg2 arg3 arg4} {

   276     if {$code=="SQLITE_INSERT" && $arg1=="t2"} {

   277       return SQLITE_IGNORE

   278     }

   279     return SQLITE_OK

   280   }

   281   catchsql {INSERT INTO t2 VALUES(1,2,3)}

   282 } {0 {}}

   283 do_test auth-1.32 {

   284   execsql {SELECT * FROM t2}

   285 } {}

   286 do_test auth-1.33 {

   287   proc auth {code arg1 arg2 arg3 arg4} {

   288     if {$code=="SQLITE_INSERT" && $arg1=="t1"} {

   289       return SQLITE_IGNORE

   290     }

   291     return SQLITE_OK

   292   }

   293   catchsql {INSERT INTO t2 VALUES(1,2,3)}

   294 } {0 {}}

   295 do_test auth-1.34 {

   296   execsql {SELECT * FROM t2}

   297 } {1 2 3}

   299 do_test auth-1.35.1 {

   300   proc auth {code arg1 arg2 arg3 arg4} {

   301     if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {

   302       return SQLITE_DENY

   303     }

   304     return SQLITE_OK

   305   }

   306   catchsql {SELECT * FROM t2}

   307 } {1 {access to t2.b is prohibited}}

   308 ifcapable attach {

   309   do_test auth-1.35.2 {

   310     execsql {ATTACH DATABASE 'test.db' AS two}

   311     catchsql {SELECT * FROM two.t2}

   312   } {1 {access to two.t2.b is prohibited}}

   313   execsql {DETACH DATABASE two}

   314 }

   315 do_test auth-1.36 {

   316   proc auth {code arg1 arg2 arg3 arg4} {

   317     if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {

   318       return SQLITE_IGNORE

   319     }

   320     return SQLITE_OK

   321   }

   322   catchsql {SELECT * FROM t2}

   323 } {0 {1 {} 3}}

   324 do_test auth-1.37 {

   325   proc auth {code arg1 arg2 arg3 arg4} {

   326     if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {

   327       return SQLITE_IGNORE

   328     }

   329     return SQLITE_OK

   330   }

   331   catchsql {SELECT * FROM t2 WHERE b=2}

   332 } {0 {}}

   333 do_test auth-1.38 {

   334   proc auth {code arg1 arg2 arg3 arg4} {

   335     if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} {

   336       return SQLITE_IGNORE

   337     }

   338     return SQLITE_OK

   339   }

   340   catchsql {SELECT * FROM t2 WHERE b=2}

   341 } {0 {{} 2 3}}

   342 do_test auth-1.39 {

   343   proc auth {code arg1 arg2 arg3 arg4} {

   344     if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {

   345       return SQLITE_IGNORE

   346     }

   347     return SQLITE_OK

   348   }

   349   catchsql {SELECT * FROM t2 WHERE b IS NULL}

   350 } {0 {1 {} 3}}

   351 do_test auth-1.40 {

   352   proc auth {code arg1 arg2 arg3 arg4} {

   353     if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {

   354       return SQLITE_DENY

   355     }

   356     return SQLITE_OK

   357   }

   358   catchsql {SELECT a,c FROM t2 WHERE b IS NULL}

   359 } {1 {access to t2.b is prohibited}}

   361 do_test auth-1.41 {

   362   proc auth {code arg1 arg2 arg3 arg4} {

   363     if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {

   364       return SQLITE_DENY

   365     }

   366     return SQLITE_OK

   367   }

   368   catchsql {UPDATE t2 SET a=11}

   369 } {0 {}}

   370 do_test auth-1.42 {

   371   execsql {SELECT * FROM t2}

   372 } {11 2 3}

   373 do_test auth-1.43 {

   374   proc auth {code arg1 arg2 arg3 arg4} {

   375     if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {

   376       return SQLITE_DENY

   377     }

   378     return SQLITE_OK

   379   }

   380   catchsql {UPDATE t2 SET b=22, c=33}

   381 } {1 {not authorized}}

   382 do_test auth-1.44 {

   383   execsql {SELECT * FROM t2}

   384 } {11 2 3}

   385 do_test auth-1.45 {

   386   proc auth {code arg1 arg2 arg3 arg4} {

   387     if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {

   388       return SQLITE_IGNORE

   389     }

   390     return SQLITE_OK

   391   }

   392   catchsql {UPDATE t2 SET b=22, c=33}

   393 } {0 {}}

   394 do_test auth-1.46 {

   395   execsql {SELECT * FROM t2}

   396 } {11 2 33}

   398 do_test auth-1.47 {

   399   proc auth {code arg1 arg2 arg3 arg4} {

   400     if {$code=="SQLITE_DELETE" && $arg1=="t2"} {

   401       return SQLITE_DENY

   402     }

   403     return SQLITE_OK

   404   }

   405   catchsql {DELETE FROM t2 WHERE a=11}

   406 } {1 {not authorized}}

   407 do_test auth-1.48 {

   408   execsql {SELECT * FROM t2}

   409 } {11 2 33}

   410 do_test auth-1.49 {

   411   proc auth {code arg1 arg2 arg3 arg4} {

   412     if {$code=="SQLITE_DELETE" && $arg1=="t2"} {

   413       return SQLITE_IGNORE

   414     }

   415     return SQLITE_OK

   416   }

   417   catchsql {DELETE FROM t2 WHERE a=11}

   418 } {0 {}}

   419 do_test auth-1.50 {

   420   execsql {SELECT * FROM t2}

   421 } {11 2 33}

   423 do_test auth-1.51 {

   424   proc auth {code arg1 arg2 arg3 arg4} {

   425     if {$code=="SQLITE_SELECT"} {

   426       return SQLITE_DENY

   427     }

   428     return SQLITE_OK

   429   }

   430   catchsql {SELECT * FROM t2}

   431 } {1 {not authorized}}

   432 do_test auth-1.52 {

   433   proc auth {code arg1 arg2 arg3 arg4} {

   434     if {$code=="SQLITE_SELECT"} {

   435       return SQLITE_IGNORE

   436     }

   437     return SQLITE_OK

   438   }

   439   catchsql {SELECT * FROM t2}

   440 } {0 {}}

   441 do_test auth-1.53 {

   442   proc auth {code arg1 arg2 arg3 arg4} {

   443     if {$code=="SQLITE_SELECT"} {

   444       return SQLITE_OK

   445     }

   446     return SQLITE_OK

   447   }

   448   catchsql {SELECT * FROM t2}

   449 } {0 {11 2 33}}

   451 # Update for version 3: There used to be a handful of test here that

   452 # tested the authorisation callback with the COPY command. The following

   453 # test makes the same database modifications as they used to.

   454 do_test auth-1.54 {

   455   execsql {INSERT INTO t2 VALUES(7, 8, 9);}

   456 } {}

   457 do_test auth-1.55 {

   458   execsql {SELECT * FROM t2}

   459 } {11 2 33 7 8 9}

   461 do_test auth-1.63 {

   462   proc auth {code arg1 arg2 arg3 arg4} {

   463     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {

   464        return SQLITE_DENY

   465     }

   466     return SQLITE_OK

   467   }

   468   catchsql {DROP TABLE t2}

   469 } {1 {not authorized}}

   470 do_test auth-1.64 {

   471   execsql {SELECT name FROM sqlite_master}

   472 } {t2}

   473 do_test auth-1.65 {

   474   proc auth {code arg1 arg2 arg3 arg4} {

   475     if {$code=="SQLITE_DELETE" && $arg1=="t2"} {

   476        return SQLITE_DENY

   477     }

   478     return SQLITE_OK

   479   }

   480   catchsql {DROP TABLE t2}

   481 } {1 {not authorized}}

   482 do_test auth-1.66 {

   483   execsql {SELECT name FROM sqlite_master}

   484 } {t2}

   486 ifcapable tempdb {

   487   do_test auth-1.67 {

   488     proc auth {code arg1 arg2 arg3 arg4} {

   489       if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {

   490          return SQLITE_DENY

   491       }

   492       return SQLITE_OK

   493     }

   494     catchsql {DROP TABLE t1}

   495   } {1 {not authorized}}

   496   do_test auth-1.68 {

   497     execsql {SELECT name FROM sqlite_temp_master}

   498   } {t1}

   499   do_test auth-1.69 {

   500     proc auth {code arg1 arg2 arg3 arg4} {

   501       if {$code=="SQLITE_DELETE" && $arg1=="t1"} {

   502          return SQLITE_DENY

   503       }

   504       return SQLITE_OK

   505     }

   506     catchsql {DROP TABLE t1}

   507   } {1 {not authorized}}

   508   do_test auth-1.70 {

   509     execsql {SELECT name FROM sqlite_temp_master}

   510   } {t1}

   511 }

   513 do_test auth-1.71 {

   514   proc auth {code arg1 arg2 arg3 arg4} {

   515     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {

   516        return SQLITE_IGNORE

   517     }

   518     return SQLITE_OK

   519   }

   520   catchsql {DROP TABLE t2}

   521 } {0 {}}

   522 do_test auth-1.72 {

   523   execsql {SELECT name FROM sqlite_master}

   524 } {t2}

   525 do_test auth-1.73 {

   526   proc auth {code arg1 arg2 arg3 arg4} {

   527     if {$code=="SQLITE_DELETE" && $arg1=="t2"} {

   528        return SQLITE_IGNORE

   529     }

   530     return SQLITE_OK

   531   }

   532   catchsql {DROP TABLE t2}

   533 } {0 {}}

   534 do_test auth-1.74 {

   535   execsql {SELECT name FROM sqlite_master}

   536 } {t2}

   538 ifcapable tempdb {

   539   do_test auth-1.75 {

   540     proc auth {code arg1 arg2 arg3 arg4} {

   541       if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {

   542          return SQLITE_IGNORE

   543       }

   544       return SQLITE_OK

   545     }

   546     catchsql {DROP TABLE t1}

   547   } {0 {}}

   548   do_test auth-1.76 {

   549     execsql {SELECT name FROM sqlite_temp_master}

   550   } {t1}

   551   do_test auth-1.77 {

   552     proc auth {code arg1 arg2 arg3 arg4} {

   553       if {$code=="SQLITE_DELETE" && $arg1=="t1"} {

   554          return SQLITE_IGNORE

   555       }

   556       return SQLITE_OK

   557     }

   558     catchsql {DROP TABLE t1}

   559   } {0 {}}

   560   do_test auth-1.78 {

   561     execsql {SELECT name FROM sqlite_temp_master}

   562   } {t1}

   563 }

   565 # Test cases auth-1.79 to auth-1.124 test creating and dropping views.

   566 # Omit these if the library was compiled with views omitted.

   567 ifcapable view {

   568 do_test auth-1.79 {

   569   proc auth {code arg1 arg2 arg3 arg4} {

   570     if {$code=="SQLITE_CREATE_VIEW"} {

   571       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   572       return SQLITE_DENY

   573     }

   574     return SQLITE_OK

   575   }

   576   catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}

   577 } {1 {not authorized}}

   578 do_test auth-1.80 {

   579   set ::authargs

   580 } {v1 {} main {}}

   581 do_test auth-1.81 {

   582   execsql {SELECT name FROM sqlite_master}

   583 } {t2}

   584 do_test auth-1.82 {

   585   proc auth {code arg1 arg2 arg3 arg4} {

   586     if {$code=="SQLITE_CREATE_VIEW"} {

   587       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   588       return SQLITE_IGNORE

   589     }

   590     return SQLITE_OK

   591   }

   592   catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}

   593 } {0 {}}

   594 do_test auth-1.83 {

   595   set ::authargs

   596 } {v1 {} main {}}

   597 do_test auth-1.84 {

   598   execsql {SELECT name FROM sqlite_master}

   599 } {t2}

   601 ifcapable tempdb {

   602   do_test auth-1.85 {

   603     proc auth {code arg1 arg2 arg3 arg4} {

   604       if {$code=="SQLITE_CREATE_TEMP_VIEW"} {

   605         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   606         return SQLITE_DENY

   607       }

   608       return SQLITE_OK

   609     }

   610     catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}

   611   } {1 {not authorized}}

   612   do_test auth-1.86 {

   613     set ::authargs

   614   } {v1 {} temp {}}

   615   do_test auth-1.87 {

   616     execsql {SELECT name FROM sqlite_temp_master}

   617   } {t1}

   618   do_test auth-1.88 {

   619     proc auth {code arg1 arg2 arg3 arg4} {

   620       if {$code=="SQLITE_CREATE_TEMP_VIEW"} {

   621         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   622         return SQLITE_IGNORE

   623       }

   624       return SQLITE_OK

   625     }

   626     catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}

   627   } {0 {}}

   628   do_test auth-1.89 {

   629     set ::authargs

   630   } {v1 {} temp {}}

   631   do_test auth-1.90 {

   632     execsql {SELECT name FROM sqlite_temp_master}

   633   } {t1}

   634 }

   636 do_test auth-1.91 {

   637   proc auth {code arg1 arg2 arg3 arg4} {

   638     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {

   639       return SQLITE_DENY

   640     }

   641     return SQLITE_OK

   642   }

   643   catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}

   644 } {1 {not authorized}}

   645 do_test auth-1.92 {

   646   execsql {SELECT name FROM sqlite_master}

   647 } {t2}

   648 do_test auth-1.93 {

   649   proc auth {code arg1 arg2 arg3 arg4} {

   650     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {

   651       return SQLITE_IGNORE

   652     }

   653     return SQLITE_OK

   654   }

   655   catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}

   656 } {0 {}}

   657 do_test auth-1.94 {

   658   execsql {SELECT name FROM sqlite_master}

   659 } {t2}

   661 ifcapable tempdb {

   662   do_test auth-1.95 {

   663     proc auth {code arg1 arg2 arg3 arg4} {

   664       if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {

   665         return SQLITE_DENY

   666       }

   667       return SQLITE_OK

   668     }

   669     catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}

   670   } {1 {not authorized}}

   671   do_test auth-1.96 {

   672     execsql {SELECT name FROM sqlite_temp_master}

   673   } {t1}

   674   do_test auth-1.97 {

   675     proc auth {code arg1 arg2 arg3 arg4} {

   676       if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {

   677         return SQLITE_IGNORE

   678       }

   679       return SQLITE_OK

   680     }

   681     catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}

   682   } {0 {}}

   683   do_test auth-1.98 {

   684     execsql {SELECT name FROM sqlite_temp_master}

   685   } {t1}

   686 }

   688 do_test auth-1.99 {

   689   proc auth {code arg1 arg2 arg3 arg4} {

   690     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {

   691       return SQLITE_DENY

   692     }

   693     return SQLITE_OK

   694   }

   695   catchsql {

   696     CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2;

   697     DROP VIEW v2

   698   }

   699 } {1 {not authorized}}

   700 do_test auth-1.100 {

   701   execsql {SELECT name FROM sqlite_master}

   702 } {t2 v2}

   703 do_test auth-1.101 {

   704   proc auth {code arg1 arg2 arg3 arg4} {

   705     if {$code=="SQLITE_DROP_VIEW"} {

   706       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   707       return SQLITE_DENY

   708     }

   709     return SQLITE_OK

   710   }

   711   catchsql {DROP VIEW v2}

   712 } {1 {not authorized}}

   713 do_test auth-1.102 {

   714   set ::authargs

   715 } {v2 {} main {}}

   716 do_test auth-1.103 {

   717   execsql {SELECT name FROM sqlite_master}

   718 } {t2 v2}

   719 do_test auth-1.104 {

   720   proc auth {code arg1 arg2 arg3 arg4} {

   721     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {

   722       return SQLITE_IGNORE

   723     }

   724     return SQLITE_OK

   725   }

   726   catchsql {DROP VIEW v2}

   727 } {0 {}}

   728 do_test auth-1.105 {

   729   execsql {SELECT name FROM sqlite_master}

   730 } {t2 v2}

   731 do_test auth-1.106 {

   732   proc auth {code arg1 arg2 arg3 arg4} {

   733     if {$code=="SQLITE_DROP_VIEW"} {

   734       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   735       return SQLITE_IGNORE

   736     }

   737     return SQLITE_OK

   738   }

   739   catchsql {DROP VIEW v2}

   740 } {0 {}}

   741 do_test auth-1.107 {

   742   set ::authargs

   743 } {v2 {} main {}}

   744 do_test auth-1.108 {

   745   execsql {SELECT name FROM sqlite_master}

   746 } {t2 v2}

   747 do_test auth-1.109 {

   748   proc auth {code arg1 arg2 arg3 arg4} {

   749     if {$code=="SQLITE_DROP_VIEW"} {

   750       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   751       return SQLITE_OK

   752     }

   753     return SQLITE_OK

   754   }

   755   catchsql {DROP VIEW v2}

   756 } {0 {}}

   757 do_test auth-1.110 {

   758   set ::authargs

   759 } {v2 {} main {}}

   760 do_test auth-1.111 {

   761   execsql {SELECT name FROM sqlite_master}

   762 } {t2}

   765 ifcapable tempdb {

   766   do_test auth-1.112 {

   767     proc auth {code arg1 arg2 arg3 arg4} {

   768       if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {

   769         return SQLITE_DENY

   770       }

   771       return SQLITE_OK

   772     }

   773     catchsql {

   774       CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1;

   775       DROP VIEW v1

   776     }

   777   } {1 {not authorized}}

   778   do_test auth-1.113 {

   779     execsql {SELECT name FROM sqlite_temp_master}

   780   } {t1 v1}

   781   do_test auth-1.114 {

   782     proc auth {code arg1 arg2 arg3 arg4} {

   783       if {$code=="SQLITE_DROP_TEMP_VIEW"} {

   784         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   785         return SQLITE_DENY

   786       }

   787       return SQLITE_OK

   788     }

   789     catchsql {DROP VIEW v1}

   790   } {1 {not authorized}}

   791   do_test auth-1.115 {

   792     set ::authargs

   793   } {v1 {} temp {}}

   794   do_test auth-1.116 {

   795     execsql {SELECT name FROM sqlite_temp_master}

   796   } {t1 v1}

   797   do_test auth-1.117 {

   798     proc auth {code arg1 arg2 arg3 arg4} {

   799       if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {

   800         return SQLITE_IGNORE

   801       }

   802       return SQLITE_OK

   803     }

   804     catchsql {DROP VIEW v1}

   805   } {0 {}}

   806   do_test auth-1.118 {

   807     execsql {SELECT name FROM sqlite_temp_master}

   808   } {t1 v1}

   809   do_test auth-1.119 {

   810     proc auth {code arg1 arg2 arg3 arg4} {

   811       if {$code=="SQLITE_DROP_TEMP_VIEW"} {

   812         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   813         return SQLITE_IGNORE

   814       }

   815       return SQLITE_OK

   816     }

   817     catchsql {DROP VIEW v1}

   818   } {0 {}}

   819   do_test auth-1.120 {

   820     set ::authargs

   821   } {v1 {} temp {}}

   822   do_test auth-1.121 {

   823     execsql {SELECT name FROM sqlite_temp_master}

   824   } {t1 v1}

   825   do_test auth-1.122 {

   826     proc auth {code arg1 arg2 arg3 arg4} {

   827       if {$code=="SQLITE_DROP_TEMP_VIEW"} {

   828         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   829         return SQLITE_OK

   830       }

   831       return SQLITE_OK

   832     }

   833     catchsql {DROP VIEW v1}

   834   } {0 {}}

   835   do_test auth-1.123 {

   836     set ::authargs

   837   } {v1 {} temp {}}

   838   do_test auth-1.124 {

   839     execsql {SELECT name FROM sqlite_temp_master}

   840   } {t1}

   841 }

   842 } ;# ifcapable view

   844 # Test cases auth-1.125 to auth-1.176 test creating and dropping triggers.

   845 # Omit these if the library was compiled with triggers omitted.

   846 #

   847 ifcapable trigger&&tempdb {

   848 do_test auth-1.125 {

   849   proc auth {code arg1 arg2 arg3 arg4} {

   850     if {$code=="SQLITE_CREATE_TRIGGER"} {

   851       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   852       return SQLITE_DENY

   853     }

   854     return SQLITE_OK

   855   }

   856   catchsql {

   857     CREATE TRIGGER r2 DELETE on t2 BEGIN

   858         SELECT NULL;

   859     END;

   860   }

   861 } {1 {not authorized}}

   862 do_test auth-1.126 {

   863   set ::authargs

   864 } {r2 t2 main {}}

   865 do_test auth-1.127 {

   866   execsql {SELECT name FROM sqlite_master}

   867 } {t2}

   868 do_test auth-1.128 {

   869   proc auth {code arg1 arg2 arg3 arg4} {

   870     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {

   871       return SQLITE_DENY

   872     }

   873     return SQLITE_OK

   874   }

   875   catchsql {

   876     CREATE TRIGGER r2 DELETE on t2 BEGIN

   877         SELECT NULL;

   878     END;

   879   }

   880 } {1 {not authorized}}

   881 do_test auth-1.129 {

   882   execsql {SELECT name FROM sqlite_master}

   883 } {t2}

   884 do_test auth-1.130 {

   885   proc auth {code arg1 arg2 arg3 arg4} {

   886     if {$code=="SQLITE_CREATE_TRIGGER"} {

   887       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   888       return SQLITE_IGNORE

   889     }

   890     return SQLITE_OK

   891   }

   892   catchsql {

   893     CREATE TRIGGER r2 DELETE on t2 BEGIN

   894         SELECT NULL;

   895     END;

   896   }

   897 } {0 {}}

   898 do_test auth-1.131 {

   899   set ::authargs

   900 } {r2 t2 main {}}

   901 do_test auth-1.132 {

   902   execsql {SELECT name FROM sqlite_master}

   903 } {t2}

   904 do_test auth-1.133 {

   905   proc auth {code arg1 arg2 arg3 arg4} {

   906     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {

   907       return SQLITE_IGNORE

   908     }

   909     return SQLITE_OK

   910   }

   911   catchsql {

   912     CREATE TRIGGER r2 DELETE on t2 BEGIN

   913         SELECT NULL;

   914     END;

   915   }

   916 } {0 {}}

   917 do_test auth-1.134 {

   918   execsql {SELECT name FROM sqlite_master}

   919 } {t2}

   920 do_test auth-1.135 {

   921   proc auth {code arg1 arg2 arg3 arg4} {

   922     if {$code=="SQLITE_CREATE_TRIGGER"} {

   923       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   924       return SQLITE_OK

   925     }

   926     return SQLITE_OK

   927   }

   928   catchsql {

   929     CREATE TABLE tx(id);

   930     CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN

   931        INSERT INTO tx VALUES(NEW.rowid);

   932     END;

   933   }

   934 } {0 {}}

   935 do_test auth-1.136.1 {

   936   set ::authargs

   937 } {r2 t2 main {}}

   938 do_test auth-1.136.2 {

   939   execsql {

   940     SELECT name FROM sqlite_master WHERE type='trigger'

   941   }

   942 } {r2}

   943 do_test auth-1.136.3 {

   944   proc auth {code arg1 arg2 arg3 arg4} {

   945     lappend ::authargs $code $arg1 $arg2 $arg3 $arg4

   946     return SQLITE_OK

   947   }

   948   set ::authargs {}

   949   execsql {

   950     INSERT INTO t2 VALUES(1,2,3);

   951   }

   952   set ::authargs

   953 } {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2}

   954 do_test auth-1.136.4 {

   955   execsql {

   956     SELECT * FROM tx;

   957   }

   958 } {3}

   959 do_test auth-1.137 {

   960   execsql {SELECT name FROM sqlite_master}

   961 } {t2 tx r2}

   962 do_test auth-1.138 {

   963   proc auth {code arg1 arg2 arg3 arg4} {

   964     if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {

   965       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

   966       return SQLITE_DENY

   967     }

   968     return SQLITE_OK

   969   }

   970   catchsql {

   971     CREATE TRIGGER r1 DELETE on t1 BEGIN

   972         SELECT NULL;

   973     END;

   974   }

   975 } {1 {not authorized}}

   976 do_test auth-1.139 {

   977   set ::authargs

   978 } {r1 t1 temp {}}

   979 do_test auth-1.140 {

   980   execsql {SELECT name FROM sqlite_temp_master}

   981 } {t1}

   982 do_test auth-1.141 {

   983   proc auth {code arg1 arg2 arg3 arg4} {

   984     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {

   985       return SQLITE_DENY

   986     }

   987     return SQLITE_OK

   988   }

   989   catchsql {

   990     CREATE TRIGGER r1 DELETE on t1 BEGIN

   991         SELECT NULL;

   992     END;

   993   }

   994 } {1 {not authorized}}

   995 do_test auth-1.142 {

   996   execsql {SELECT name FROM sqlite_temp_master}

   997 } {t1}

   998 do_test auth-1.143 {

   999   proc auth {code arg1 arg2 arg3 arg4} {

  1000     if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {

  1001       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1002       return SQLITE_IGNORE

  1003     }

  1004     return SQLITE_OK

  1005   }

  1006   catchsql {

  1007     CREATE TRIGGER r1 DELETE on t1 BEGIN

  1008         SELECT NULL;

  1009     END;

  1010   }

  1011 } {0 {}}

  1012 do_test auth-1.144 {

  1013   set ::authargs

  1014 } {r1 t1 temp {}}

  1015 do_test auth-1.145 {

  1016   execsql {SELECT name FROM sqlite_temp_master}

  1017 } {t1}

  1018 do_test auth-1.146 {

  1019   proc auth {code arg1 arg2 arg3 arg4} {

  1020     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {

  1021       return SQLITE_IGNORE

  1022     }

  1023     return SQLITE_OK

  1024   }

  1025   catchsql {

  1026     CREATE TRIGGER r1 DELETE on t1 BEGIN

  1027         SELECT NULL;

  1028     END;

  1029   }

  1030 } {0 {}}

  1031 do_test auth-1.147 {

  1032   execsql {SELECT name FROM sqlite_temp_master}

  1033 } {t1}

  1034 do_test auth-1.148 {

  1035   proc auth {code arg1 arg2 arg3 arg4} {

  1036     if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {

  1037       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1038       return SQLITE_OK

  1039     }

  1040     return SQLITE_OK

  1041   }

  1042   catchsql {

  1043     CREATE TRIGGER r1 DELETE on t1 BEGIN

  1044         SELECT NULL;

  1045     END;

  1046   }

  1047 } {0 {}}

  1048 do_test auth-1.149 {

  1049   set ::authargs

  1050 } {r1 t1 temp {}}

  1051 do_test auth-1.150 {

  1052   execsql {SELECT name FROM sqlite_temp_master}

  1053 } {t1 r1}

  1055 do_test auth-1.151 {

  1056   proc auth {code arg1 arg2 arg3 arg4} {

  1057     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {

  1058       return SQLITE_DENY

  1059     }

  1060     return SQLITE_OK

  1061   }

  1062   catchsql {DROP TRIGGER r2}

  1063 } {1 {not authorized}}

  1064 do_test auth-1.152 {

  1065   execsql {SELECT name FROM sqlite_master}

  1066 } {t2 tx r2}

  1067 do_test auth-1.153 {

  1068   proc auth {code arg1 arg2 arg3 arg4} {

  1069     if {$code=="SQLITE_DROP_TRIGGER"} {

  1070       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1071       return SQLITE_DENY

  1072     }

  1073     return SQLITE_OK

  1074   }

  1075   catchsql {DROP TRIGGER r2}

  1076 } {1 {not authorized}}

  1077 do_test auth-1.154 {

  1078   set ::authargs

  1079 } {r2 t2 main {}}

  1080 do_test auth-1.155 {

  1081   execsql {SELECT name FROM sqlite_master}

  1082 } {t2 tx r2}

  1083 do_test auth-1.156 {

  1084   proc auth {code arg1 arg2 arg3 arg4} {

  1085     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {

  1086       return SQLITE_IGNORE

  1087     }

  1088     return SQLITE_OK

  1089   }

  1090   catchsql {DROP TRIGGER r2}

  1091 } {0 {}}

  1092 do_test auth-1.157 {

  1093   execsql {SELECT name FROM sqlite_master}

  1094 } {t2 tx r2}

  1095 do_test auth-1.158 {

  1096   proc auth {code arg1 arg2 arg3 arg4} {

  1097     if {$code=="SQLITE_DROP_TRIGGER"} {

  1098       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1099       return SQLITE_IGNORE

  1100     }

  1101     return SQLITE_OK

  1102   }

  1103   catchsql {DROP TRIGGER r2}

  1104 } {0 {}}

  1105 do_test auth-1.159 {

  1106   set ::authargs

  1107 } {r2 t2 main {}}

  1108 do_test auth-1.160 {

  1109   execsql {SELECT name FROM sqlite_master}

  1110 } {t2 tx r2}

  1111 do_test auth-1.161 {

  1112   proc auth {code arg1 arg2 arg3 arg4} {

  1113     if {$code=="SQLITE_DROP_TRIGGER"} {

  1114       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1115       return SQLITE_OK

  1116     }

  1117     return SQLITE_OK

  1118   }

  1119   catchsql {DROP TRIGGER r2}

  1120 } {0 {}}

  1121 do_test auth-1.162 {

  1122   set ::authargs

  1123 } {r2 t2 main {}}

  1124 do_test auth-1.163 {

  1125   execsql {

  1126     DROP TABLE tx;

  1127     DELETE FROM t2 WHERE a=1 AND b=2 AND c=3;

  1128     SELECT name FROM sqlite_master;

  1129   }

  1130 } {t2}

  1132 do_test auth-1.164 {

  1133   proc auth {code arg1 arg2 arg3 arg4} {

  1134     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {

  1135       return SQLITE_DENY

  1136     }

  1137     return SQLITE_OK

  1138   }

  1139   catchsql {DROP TRIGGER r1}

  1140 } {1 {not authorized}}

  1141 do_test auth-1.165 {

  1142   execsql {SELECT name FROM sqlite_temp_master}

  1143 } {t1 r1}

  1144 do_test auth-1.166 {

  1145   proc auth {code arg1 arg2 arg3 arg4} {

  1146     if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {

  1147       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1148       return SQLITE_DENY

  1149     }

  1150     return SQLITE_OK

  1151   }

  1152   catchsql {DROP TRIGGER r1}

  1153 } {1 {not authorized}}

  1154 do_test auth-1.167 {

  1155   set ::authargs

  1156 } {r1 t1 temp {}}

  1157 do_test auth-1.168 {

  1158   execsql {SELECT name FROM sqlite_temp_master}

  1159 } {t1 r1}

  1160 do_test auth-1.169 {

  1161   proc auth {code arg1 arg2 arg3 arg4} {

  1162     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {

  1163       return SQLITE_IGNORE

  1164     }

  1165     return SQLITE_OK

  1166   }

  1167   catchsql {DROP TRIGGER r1}

  1168 } {0 {}}

  1169 do_test auth-1.170 {

  1170   execsql {SELECT name FROM sqlite_temp_master}

  1171 } {t1 r1}

  1172 do_test auth-1.171 {

  1173   proc auth {code arg1 arg2 arg3 arg4} {

  1174     if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {

  1175       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1176       return SQLITE_IGNORE

  1177     }

  1178     return SQLITE_OK

  1179   }

  1180   catchsql {DROP TRIGGER r1}

  1181 } {0 {}}

  1182 do_test auth-1.172 {

  1183   set ::authargs

  1184 } {r1 t1 temp {}}

  1185 do_test auth-1.173 {

  1186   execsql {SELECT name FROM sqlite_temp_master}

  1187 } {t1 r1}

  1188 do_test auth-1.174 {

  1189   proc auth {code arg1 arg2 arg3 arg4} {

  1190     if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {

  1191       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1192       return SQLITE_OK

  1193     }

  1194     return SQLITE_OK

  1195   }

  1196   catchsql {DROP TRIGGER r1}

  1197 } {0 {}}

  1198 do_test auth-1.175 {

  1199   set ::authargs

  1200 } {r1 t1 temp {}}

  1201 do_test auth-1.176 {

  1202   execsql {SELECT name FROM sqlite_temp_master}

  1203 } {t1}

  1204 } ;# ifcapable trigger

  1206 do_test auth-1.177 {

  1207   proc auth {code arg1 arg2 arg3 arg4} {

  1208     if {$code=="SQLITE_CREATE_INDEX"} {

  1209       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1210       return SQLITE_DENY

  1211     }

  1212     return SQLITE_OK

  1213   }

  1214   catchsql {CREATE INDEX i2 ON t2(a)}

  1215 } {1 {not authorized}}

  1216 do_test auth-1.178 {

  1217   set ::authargs

  1218 } {i2 t2 main {}}

  1219 do_test auth-1.179 {

  1220   execsql {SELECT name FROM sqlite_master}

  1221 } {t2}

  1222 do_test auth-1.180 {

  1223   proc auth {code arg1 arg2 arg3 arg4} {

  1224     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {

  1225       return SQLITE_DENY

  1226     }

  1227     return SQLITE_OK

  1228   }

  1229   catchsql {CREATE INDEX i2 ON t2(a)}

  1230 } {1 {not authorized}}

  1231 do_test auth-1.181 {

  1232   execsql {SELECT name FROM sqlite_master}

  1233 } {t2}

  1234 do_test auth-1.182 {

  1235   proc auth {code arg1 arg2 arg3 arg4} {

  1236     if {$code=="SQLITE_CREATE_INDEX"} {

  1237       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1238       return SQLITE_IGNORE

  1239     }

  1240     return SQLITE_OK

  1241   }

  1242   catchsql {CREATE INDEX i2 ON t2(b)}

  1243 } {0 {}}

  1244 do_test auth-1.183 {

  1245   set ::authargs

  1246 } {i2 t2 main {}}

  1247 do_test auth-1.184 {

  1248   execsql {SELECT name FROM sqlite_master}

  1249 } {t2}

  1250 do_test auth-1.185 {

  1251   proc auth {code arg1 arg2 arg3 arg4} {

  1252     if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {

  1253       return SQLITE_IGNORE

  1254     }

  1255     return SQLITE_OK

  1256   }

  1257   catchsql {CREATE INDEX i2 ON t2(b)}

  1258 } {0 {}}

  1259 do_test auth-1.186 {

  1260   execsql {SELECT name FROM sqlite_master}

  1261 } {t2}

  1262 do_test auth-1.187 {

  1263   proc auth {code arg1 arg2 arg3 arg4} {

  1264     if {$code=="SQLITE_CREATE_INDEX"} {

  1265       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1266       return SQLITE_OK

  1267     }

  1268     return SQLITE_OK

  1269   }

  1270   catchsql {CREATE INDEX i2 ON t2(a)}

  1271 } {0 {}}

  1272 do_test auth-1.188 {

  1273   set ::authargs

  1274 } {i2 t2 main {}}

  1275 do_test auth-1.189 {

  1276   execsql {SELECT name FROM sqlite_master}

  1277 } {t2 i2}

  1279 ifcapable tempdb {

  1280   do_test auth-1.190 {

  1281     proc auth {code arg1 arg2 arg3 arg4} {

  1282       if {$code=="SQLITE_CREATE_TEMP_INDEX"} {

  1283         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1284         return SQLITE_DENY

  1285       }

  1286       return SQLITE_OK

  1287     }

  1288     catchsql {CREATE INDEX i1 ON t1(a)}

  1289   } {1 {not authorized}}

  1290   do_test auth-1.191 {

  1291     set ::authargs

  1292   } {i1 t1 temp {}}

  1293   do_test auth-1.192 {

  1294     execsql {SELECT name FROM sqlite_temp_master}

  1295   } {t1}

  1296   do_test auth-1.193 {

  1297     proc auth {code arg1 arg2 arg3 arg4} {

  1298       if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {

  1299         return SQLITE_DENY

  1300       }

  1301       return SQLITE_OK

  1302     }

  1303     catchsql {CREATE INDEX i1 ON t1(b)}

  1304   } {1 {not authorized}}

  1305   do_test auth-1.194 {

  1306     execsql {SELECT name FROM sqlite_temp_master}

  1307   } {t1}

  1308   do_test auth-1.195 {

  1309     proc auth {code arg1 arg2 arg3 arg4} {

  1310       if {$code=="SQLITE_CREATE_TEMP_INDEX"} {

  1311         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1312         return SQLITE_IGNORE

  1313       }

  1314       return SQLITE_OK

  1315     }

  1316     catchsql {CREATE INDEX i1 ON t1(b)}

  1317   } {0 {}}

  1318   do_test auth-1.196 {

  1319     set ::authargs

  1320   } {i1 t1 temp {}}

  1321   do_test auth-1.197 {

  1322     execsql {SELECT name FROM sqlite_temp_master}

  1323   } {t1}

  1324   do_test auth-1.198 {

  1325     proc auth {code arg1 arg2 arg3 arg4} {

  1326       if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {

  1327         return SQLITE_IGNORE

  1328       }

  1329       return SQLITE_OK

  1330     }

  1331     catchsql {CREATE INDEX i1 ON t1(c)}

  1332   } {0 {}}

  1333   do_test auth-1.199 {

  1334     execsql {SELECT name FROM sqlite_temp_master}

  1335   } {t1}

  1336   do_test auth-1.200 {

  1337     proc auth {code arg1 arg2 arg3 arg4} {

  1338       if {$code=="SQLITE_CREATE_TEMP_INDEX"} {

  1339         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1340         return SQLITE_OK

  1341       }

  1342       return SQLITE_OK

  1343     }

  1344     catchsql {CREATE INDEX i1 ON t1(a)}

  1345   } {0 {}}

  1346   do_test auth-1.201 {

  1347     set ::authargs

  1348   } {i1 t1 temp {}}

  1349   do_test auth-1.202 {

  1350     execsql {SELECT name FROM sqlite_temp_master}

  1351   } {t1 i1}

  1352 }

  1354 do_test auth-1.203 {

  1355   proc auth {code arg1 arg2 arg3 arg4} {

  1356     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {

  1357       return SQLITE_DENY

  1358     }

  1359     return SQLITE_OK

  1360   }

  1361   catchsql {DROP INDEX i2}

  1362 } {1 {not authorized}}

  1363 do_test auth-1.204 {

  1364   execsql {SELECT name FROM sqlite_master}

  1365 } {t2 i2}

  1366 do_test auth-1.205 {

  1367   proc auth {code arg1 arg2 arg3 arg4} {

  1368     if {$code=="SQLITE_DROP_INDEX"} {

  1369       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1370       return SQLITE_DENY

  1371     }

  1372     return SQLITE_OK

  1373   }

  1374   catchsql {DROP INDEX i2}

  1375 } {1 {not authorized}}

  1376 do_test auth-1.206 {

  1377   set ::authargs

  1378 } {i2 t2 main {}}

  1379 do_test auth-1.207 {

  1380   execsql {SELECT name FROM sqlite_master}

  1381 } {t2 i2}

  1382 do_test auth-1.208 {

  1383   proc auth {code arg1 arg2 arg3 arg4} {

  1384     if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {

  1385       return SQLITE_IGNORE

  1386     }

  1387     return SQLITE_OK

  1388   }

  1389   catchsql {DROP INDEX i2}

  1390 } {0 {}}

  1391 do_test auth-1.209 {

  1392   execsql {SELECT name FROM sqlite_master}

  1393 } {t2 i2}

  1394 do_test auth-1.210 {

  1395   proc auth {code arg1 arg2 arg3 arg4} {

  1396     if {$code=="SQLITE_DROP_INDEX"} {

  1397       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1398       return SQLITE_IGNORE

  1399     }

  1400     return SQLITE_OK

  1401   }

  1402   catchsql {DROP INDEX i2}

  1403 } {0 {}}

  1404 do_test auth-1.211 {

  1405   set ::authargs

  1406 } {i2 t2 main {}}

  1407 do_test auth-1.212 {

  1408   execsql {SELECT name FROM sqlite_master}

  1409 } {t2 i2}

  1410 do_test auth-1.213 {

  1411   proc auth {code arg1 arg2 arg3 arg4} {

  1412     if {$code=="SQLITE_DROP_INDEX"} {

  1413       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1414       return SQLITE_OK

  1415     }

  1416     return SQLITE_OK

  1417   }

  1418   catchsql {DROP INDEX i2}

  1419 } {0 {}}

  1420 do_test auth-1.214 {

  1421   set ::authargs

  1422 } {i2 t2 main {}}

  1423 do_test auth-1.215 {

  1424   execsql {SELECT name FROM sqlite_master}

  1425 } {t2}

  1427 ifcapable tempdb {

  1428   do_test auth-1.216 {

  1429     proc auth {code arg1 arg2 arg3 arg4} {

  1430       if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {

  1431         return SQLITE_DENY

  1432       }

  1433       return SQLITE_OK

  1434     }

  1435     catchsql {DROP INDEX i1}

  1436   } {1 {not authorized}}

  1437   do_test auth-1.217 {

  1438     execsql {SELECT name FROM sqlite_temp_master}

  1439   } {t1 i1}

  1440   do_test auth-1.218 {

  1441     proc auth {code arg1 arg2 arg3 arg4} {

  1442       if {$code=="SQLITE_DROP_TEMP_INDEX"} {

  1443         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1444         return SQLITE_DENY

  1445       }

  1446       return SQLITE_OK

  1447     }

  1448     catchsql {DROP INDEX i1}

  1449   } {1 {not authorized}}

  1450   do_test auth-1.219 {

  1451     set ::authargs

  1452   } {i1 t1 temp {}}

  1453   do_test auth-1.220 {

  1454     execsql {SELECT name FROM sqlite_temp_master}

  1455   } {t1 i1}

  1456   do_test auth-1.221 {

  1457     proc auth {code arg1 arg2 arg3 arg4} {

  1458       if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {

  1459         return SQLITE_IGNORE

  1460       }

  1461       return SQLITE_OK

  1462     }

  1463     catchsql {DROP INDEX i1}

  1464   } {0 {}}

  1465   do_test auth-1.222 {

  1466     execsql {SELECT name FROM sqlite_temp_master}

  1467   } {t1 i1}

  1468   do_test auth-1.223 {

  1469     proc auth {code arg1 arg2 arg3 arg4} {

  1470       if {$code=="SQLITE_DROP_TEMP_INDEX"} {

  1471         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1472         return SQLITE_IGNORE

  1473       }

  1474       return SQLITE_OK

  1475     }

  1476     catchsql {DROP INDEX i1}

  1477   } {0 {}}

  1478   do_test auth-1.224 {

  1479     set ::authargs

  1480   } {i1 t1 temp {}}

  1481   do_test auth-1.225 {

  1482     execsql {SELECT name FROM sqlite_temp_master}

  1483   } {t1 i1}

  1484   do_test auth-1.226 {

  1485     proc auth {code arg1 arg2 arg3 arg4} {

  1486       if {$code=="SQLITE_DROP_TEMP_INDEX"} {

  1487         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1488         return SQLITE_OK

  1489       }

  1490       return SQLITE_OK

  1491     }

  1492     catchsql {DROP INDEX i1}

  1493   } {0 {}}

  1494   do_test auth-1.227 {

  1495     set ::authargs

  1496   } {i1 t1 temp {}}

  1497   do_test auth-1.228 {

  1498     execsql {SELECT name FROM sqlite_temp_master}

  1499   } {t1}

  1500 }

  1502 do_test auth-1.229 {

  1503   proc auth {code arg1 arg2 arg3 arg4} {

  1504     if {$code=="SQLITE_PRAGMA"} {

  1505       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1506       return SQLITE_DENY

  1507     }

  1508     return SQLITE_OK

  1509   }

  1510   catchsql {PRAGMA full_column_names=on}

  1511 } {1 {not authorized}}

  1512 do_test auth-1.230 {

  1513   set ::authargs

  1514 } {full_column_names on {} {}}

  1515 do_test auth-1.231 {

  1516   execsql2 {SELECT a FROM t2}

  1517 } {a 11 a 7}

  1518 do_test auth-1.232 {

  1519   proc auth {code arg1 arg2 arg3 arg4} {

  1520     if {$code=="SQLITE_PRAGMA"} {

  1521       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1522       return SQLITE_IGNORE

  1523     }

  1524     return SQLITE_OK

  1525   }

  1526   catchsql {PRAGMA full_column_names=on}

  1527 } {0 {}}

  1528 do_test auth-1.233 {

  1529   set ::authargs

  1530 } {full_column_names on {} {}}

  1531 do_test auth-1.234 {

  1532   execsql2 {SELECT a FROM t2}

  1533 } {a 11 a 7}

  1534 do_test auth-1.235 {

  1535   proc auth {code arg1 arg2 arg3 arg4} {

  1536     if {$code=="SQLITE_PRAGMA"} {

  1537       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1538       return SQLITE_OK

  1539     }

  1540     return SQLITE_OK

  1541   }

  1542   catchsql {PRAGMA full_column_names=on}

  1543 } {0 {}}

  1544 do_test auth-1.236 {

  1545   execsql2 {SELECT a FROM t2}

  1546 } {t2.a 11 t2.a 7}

  1547 do_test auth-1.237 {

  1548   proc auth {code arg1 arg2 arg3 arg4} {

  1549     if {$code=="SQLITE_PRAGMA"} {

  1550       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1551       return SQLITE_OK

  1552     }

  1553     return SQLITE_OK

  1554   }

  1555   catchsql {PRAGMA full_column_names=OFF}

  1556 } {0 {}}

  1557 do_test auth-1.238 {

  1558   set ::authargs

  1559 } {full_column_names OFF {} {}}

  1560 do_test auth-1.239 {

  1561   execsql2 {SELECT a FROM t2}

  1562 } {a 11 a 7}

  1564 do_test auth-1.240 {

  1565   proc auth {code arg1 arg2 arg3 arg4} {

  1566     if {$code=="SQLITE_TRANSACTION"} {

  1567       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1568       return SQLITE_DENY

  1569     }

  1570     return SQLITE_OK

  1571   }

  1572   catchsql {BEGIN}

  1573 } {1 {not authorized}}

  1574 do_test auth-1.241 {

  1575   set ::authargs

  1576 } {BEGIN {} {} {}}

  1577 do_test auth-1.242 {

  1578   proc auth {code arg1 arg2 arg3 arg4} {

  1579     if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} {

  1580       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1581       return SQLITE_DENY

  1582     }

  1583     return SQLITE_OK

  1584   }

  1585   catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT}

  1586 } {1 {not authorized}}

  1587 do_test auth-1.243 {

  1588   set ::authargs

  1589 } {COMMIT {} {} {}}

  1590 do_test auth-1.244 {

  1591   execsql {SELECT * FROM t2}

  1592 } {11 2 33 7 8 9 44 55 66}

  1593 do_test auth-1.245 {

  1594   catchsql {ROLLBACK}

  1595 } {1 {not authorized}}

  1596 do_test auth-1.246 {

  1597   set ::authargs

  1598 } {ROLLBACK {} {} {}}

  1599 do_test auth-1.247 {

  1600   catchsql {END TRANSACTION}

  1601 } {1 {not authorized}}

  1602 do_test auth-1.248 {

  1603   set ::authargs

  1604 } {COMMIT {} {} {}}

  1605 do_test auth-1.249 {

  1606   db authorizer {}

  1607   catchsql {ROLLBACK}

  1608 } {0 {}}

  1609 do_test auth-1.250 {

  1610   execsql {SELECT * FROM t2}

  1611 } {11 2 33 7 8 9}

  1613 # ticket #340 - authorization for ATTACH and DETACH.

  1614 #

  1615 ifcapable attach {

  1616   do_test auth-1.251 {

  1617     db authorizer ::auth

  1618     proc auth {code arg1 arg2 arg3 arg4} {

  1619       if {$code=="SQLITE_ATTACH"} {

  1620         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1621       }

  1622       return SQLITE_OK

  1623     }

  1624     catchsql {

  1625       ATTACH DATABASE ':memory:' AS test1

  1626     }

  1627   } {0 {}}

  1628   do_test auth-1.252 {

  1629     set ::authargs

  1630   } {:memory: {} {} {}}

  1631   do_test auth-1.253 {

  1632     catchsql {DETACH DATABASE test1}

  1633     proc auth {code arg1 arg2 arg3 arg4} {

  1634       if {$code=="SQLITE_ATTACH"} {

  1635         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1636         return SQLITE_DENY

  1637       }

  1638       return SQLITE_OK

  1639     }

  1640     catchsql {

  1641       ATTACH DATABASE ':memory:' AS test1;

  1642     }

  1643   } {1 {not authorized}}

  1644   do_test auth-1.254 {

  1645     lindex [execsql {PRAGMA database_list}] 7

  1646   } {}

  1647   do_test auth-1.255 {

  1648     catchsql {DETACH DATABASE test1}

  1649     proc auth {code arg1 arg2 arg3 arg4} {

  1650       if {$code=="SQLITE_ATTACH"} {

  1651         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1652         return SQLITE_IGNORE

  1653       }

  1654       return SQLITE_OK

  1655     }

  1656     catchsql {

  1657       ATTACH DATABASE ':memory:' AS test1;

  1658     }

  1659   } {0 {}}

  1660   do_test auth-1.256 {

  1661     lindex [execsql {PRAGMA database_list}] 7

  1662   } {}

  1663   do_test auth-1.257 {

  1664     proc auth {code arg1 arg2 arg3 arg4} {

  1665       if {$code=="SQLITE_DETACH"} {

  1666         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1667         return SQLITE_OK

  1668       }

  1669       return SQLITE_OK

  1670     }

  1671     execsql {ATTACH DATABASE ':memory:' AS test1}

  1672     catchsql {

  1673       DETACH DATABASE test1;

  1674     }

  1675   } {0 {}}

  1676   do_test auth-1.258 {

  1677     lindex [execsql {PRAGMA database_list}] 7

  1678   } {}

  1679   do_test auth-1.259 {

  1680     execsql {ATTACH DATABASE ':memory:' AS test1}

  1681     proc auth {code arg1 arg2 arg3 arg4} {

  1682       if {$code=="SQLITE_DETACH"} {

  1683         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1684         return SQLITE_IGNORE

  1685       }

  1686       return SQLITE_OK

  1687     }

  1688     catchsql {

  1689       DETACH DATABASE test1;

  1690     }

  1691   } {0 {}}

  1692   ifcapable tempdb {

  1693     ifcapable schema_pragmas {

  1694     do_test auth-1.260 {

  1695       lindex [execsql {PRAGMA database_list}] 7

  1696     } {test1}

  1697     } ;# ifcapable schema_pragmas

  1698     do_test auth-1.261 {

  1699       proc auth {code arg1 arg2 arg3 arg4} {

  1700         if {$code=="SQLITE_DETACH"} {

  1701           set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1702           return SQLITE_DENY

  1703         }

  1704         return SQLITE_OK

  1705       }

  1706       catchsql {

  1707         DETACH DATABASE test1;

  1708       }

  1709     } {1 {not authorized}}

  1710     ifcapable schema_pragmas {

  1711     do_test auth-1.262 {

  1712       lindex [execsql {PRAGMA database_list}] 7

  1713     } {test1}

  1714     } ;# ifcapable schema_pragmas

  1715     db authorizer {}

  1716     execsql {DETACH DATABASE test1}

  1717     db authorizer ::auth

  1719     # Authorization for ALTER TABLE. These tests are omitted if the library

  1720     # was built without ALTER TABLE support.

  1721     ifcapable altertable {

  1723       do_test auth-1.263 {

  1724         proc auth {code arg1 arg2 arg3 arg4} {

  1725           if {$code=="SQLITE_ALTER_TABLE"} {

  1726             set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1727             return SQLITE_OK

  1728           }

  1729           return SQLITE_OK

  1730         }

  1731         catchsql {

  1732           ALTER TABLE t1 RENAME TO t1x

  1733         }

  1734       } {0 {}}

  1735       do_test auth-1.264 {

  1736         execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}

  1737       } {t1x}

  1738       do_test auth-1.265 {

  1739         set authargs

  1740       } {temp t1 {} {}}

  1741       do_test auth-1.266 {

  1742         proc auth {code arg1 arg2 arg3 arg4} {

  1743           if {$code=="SQLITE_ALTER_TABLE"} {

  1744             set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1745             return SQLITE_IGNORE

  1746           }

  1747           return SQLITE_OK

  1748         }

  1749         catchsql {

  1750           ALTER TABLE t1x RENAME TO t1

  1751         }

  1752       } {0 {}}

  1753       do_test auth-1.267 {

  1754         execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}

  1755       } {t1x}

  1756       do_test auth-1.268 {

  1757         set authargs

  1758       } {temp t1x {} {}}

  1759       do_test auth-1.269 {

  1760         proc auth {code arg1 arg2 arg3 arg4} {

  1761           if {$code=="SQLITE_ALTER_TABLE"} {

  1762             set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1763             return SQLITE_DENY

  1764           }

  1765           return SQLITE_OK

  1766         }

  1767         catchsql {

  1768           ALTER TABLE t1x RENAME TO t1

  1769         }

  1770       } {1 {not authorized}}

  1771       do_test auth-1.270 {

  1772         execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}

  1773       } {t1x}

  1775       do_test auth-1.271 {

  1776         set authargs

  1777       } {temp t1x {} {}}

  1778     } ;# ifcapable altertable

  1780   } else {

  1781     db authorizer {}

  1782     db eval {

  1783       DETACH DATABASE test1;

  1784     }

  1785   }

  1786 }

  1788 ifcapable  altertable {

  1789 db authorizer {}

  1790 catchsql {ALTER TABLE t1x RENAME TO t1}

  1791 db authorizer ::auth

  1792 do_test auth-1.272 {

  1793   proc auth {code arg1 arg2 arg3 arg4} {

  1794     if {$code=="SQLITE_ALTER_TABLE"} {

  1795       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1796       return SQLITE_OK

  1797     }

  1798     return SQLITE_OK

  1799   }

  1800   catchsql {

  1801     ALTER TABLE t2 RENAME TO t2x

  1802   }

  1803 } {0 {}}

  1804 do_test auth-1.273 {

  1805   execsql {SELECT name FROM sqlite_master WHERE type='table'}

  1806 } {t2x}

  1807 do_test auth-1.274 {

  1808   set authargs

  1809 } {main t2 {} {}}

  1810 do_test auth-1.275 {

  1811   proc auth {code arg1 arg2 arg3 arg4} {

  1812     if {$code=="SQLITE_ALTER_TABLE"} {

  1813       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1814       return SQLITE_IGNORE

  1815     }

  1816     return SQLITE_OK

  1817   }

  1818   catchsql {

  1819     ALTER TABLE t2x RENAME TO t2

  1820   }

  1821 } {0 {}}

  1822 do_test auth-1.276 {

  1823   execsql {SELECT name FROM sqlite_master WHERE type='table'}

  1824 } {t2x}

  1825 do_test auth-1.277 {

  1826   set authargs

  1827 } {main t2x {} {}}

  1828 do_test auth-1.278 {

  1829   proc auth {code arg1 arg2 arg3 arg4} {

  1830     if {$code=="SQLITE_ALTER_TABLE"} {

  1831       set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  1832       return SQLITE_DENY

  1833     }

  1834     return SQLITE_OK

  1835   }

  1836   catchsql {

  1837     ALTER TABLE t2x RENAME TO t2

  1838   }

  1839 } {1 {not authorized}}

  1840 do_test auth-1.279 {

  1841   execsql {SELECT name FROM sqlite_master WHERE type='table'}

  1842 } {t2x}

  1843 do_test auth-1.280 {

  1844   set authargs

  1845 } {main t2x {} {}}

  1846 db authorizer {}

  1847 catchsql {ALTER TABLE t2x RENAME TO t2}

  1849 } ;# ifcapable altertable

  1851 # Test the authorization callbacks for the REINDEX command.

  1852 ifcapable reindex {

  1854 proc auth {code args} {

  1855   if {$code=="SQLITE_REINDEX"} {

  1856     set ::authargs [concat $::authargs $args]

  1857   }

  1858   return SQLITE_OK

  1859 }

  1860 db authorizer auth

  1861 do_test auth-1.281 {

  1862   execsql {

  1863     CREATE TABLE t3(a PRIMARY KEY, b, c);

  1864     CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);

  1865     CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);

  1866   }

  1867 } {}

  1868 do_test auth-1.282 {

  1869   set ::authargs {}

  1870   execsql {

  1871     REINDEX t3_idx1;

  1872   }

  1873   set ::authargs

  1874 } {t3_idx1 {} main {}}

  1875 do_test auth-1.283 {

  1876   set ::authargs {}

  1877   execsql {

  1878     REINDEX BINARY;

  1879   }

  1880   set ::authargs

  1881 } {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}

  1882 do_test auth-1.284 {

  1883   set ::authargs {}

  1884   execsql {

  1885     REINDEX NOCASE;

  1886   }

  1887   set ::authargs

  1888 } {t3_idx2 {} main {}}

  1889 do_test auth-1.285 {

  1890   set ::authargs {}

  1891   execsql {

  1892     REINDEX t3;

  1893   }

  1894   set ::authargs

  1895 } {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}

  1896 do_test auth-1.286 {

  1897   execsql {

  1898     DROP TABLE t3;

  1899   }

  1900 } {}

  1901 ifcapable tempdb {

  1902   do_test auth-1.287 {

  1903     execsql {

  1904       CREATE TEMP TABLE t3(a PRIMARY KEY, b, c);

  1905       CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);

  1906       CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);

  1907     }

  1908   } {}

  1909   do_test auth-1.288 {

  1910     set ::authargs {}

  1911     execsql {

  1912       REINDEX temp.t3_idx1;

  1913     }

  1914     set ::authargs

  1915   } {t3_idx1 {} temp {}}

  1916   do_test auth-1.289 {

  1917     set ::authargs {}

  1918     execsql {

  1919       REINDEX BINARY;

  1920     }

  1921     set ::authargs

  1922   } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}

  1923   do_test auth-1.290 {

  1924     set ::authargs {}

  1925     execsql {

  1926       REINDEX NOCASE;

  1927     }

  1928     set ::authargs

  1929   } {t3_idx2 {} temp {}}

  1930   do_test auth-1.291 {

  1931     set ::authargs {}

  1932     execsql {

  1933       REINDEX temp.t3;

  1934     }

  1935     set ::authargs

  1936   } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}

  1937   proc auth {code args} {

  1938     if {$code=="SQLITE_REINDEX"} {

  1939       set ::authargs [concat $::authargs $args]

  1940       return SQLITE_DENY

  1941     }

  1942     return SQLITE_OK

  1943   }

  1944   do_test auth-1.292 {

  1945     set ::authargs {}

  1946     catchsql {

  1947       REINDEX temp.t3;

  1948     }

  1949   } {1 {not authorized}}

  1950   do_test auth-1.293 {

  1951     execsql {

  1952       DROP TABLE t3;

  1953     }

  1954   } {}

  1955 }

  1957 } ;# ifcapable reindex

  1959 ifcapable analyze {

  1960   proc auth {code args} {

  1961     if {$code=="SQLITE_ANALYZE"} {

  1962       set ::authargs [concat $::authargs $args]

  1963     }

  1964     return SQLITE_OK

  1965   }

  1966   do_test auth-1.294 {

  1967     set ::authargs {}

  1968     execsql {

  1969       CREATE TABLE t4(a,b,c);

  1970       CREATE INDEX t4i1 ON t4(a);

  1971       CREATE INDEX t4i2 ON t4(b,a,c);

  1972       INSERT INTO t4 VALUES(1,2,3);

  1973       ANALYZE;

  1974     }

  1975     set ::authargs

  1976   } {t4 {} main {}}

  1977   do_test auth-1.295 {

  1978     execsql {

  1979       SELECT count(*) FROM sqlite_stat1;

  1980     }

  1981   } 2

  1982   proc auth {code args} {

  1983     if {$code=="SQLITE_ANALYZE"} {

  1984       set ::authargs [concat $::authargs $args]

  1985       return SQLITE_DENY

  1986     }

  1987     return SQLITE_OK

  1988   }

  1989   do_test auth-1.296 {

  1990     set ::authargs {}

  1991     catchsql {

  1992       ANALYZE;

  1993     }

  1994   } {1 {not authorized}}

  1995   do_test auth-1.297 {

  1996     execsql {

  1997       SELECT count(*) FROM sqlite_stat1;

  1998     }

  1999   } 2

  2000 } ;# ifcapable analyze

  2003 # Authorization for ALTER TABLE ADD COLUMN.

  2004 # These tests are omitted if the library

  2005 # was built without ALTER TABLE support.

  2006 ifcapable {altertable} {

  2007   do_test auth-1.300 {

  2008     execsql {CREATE TABLE t5(x)}

  2009     proc auth {code arg1 arg2 arg3 arg4} {

  2010       if {$code=="SQLITE_ALTER_TABLE"} {

  2011         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  2012         return SQLITE_OK

  2013       }

  2014       return SQLITE_OK

  2015     }

  2016     catchsql {

  2017       ALTER TABLE t5 ADD COLUMN new_col_1;

  2018     }

  2019   } {0 {}}

  2020   do_test auth-1.301 {

  2021     set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]

  2022     regexp new_col_1 $x

  2023   } {1}

  2024   do_test auth-1.302 {

  2025     set authargs

  2026   } {main t5 {} {}}

  2027   do_test auth-1.303 {

  2028     proc auth {code arg1 arg2 arg3 arg4} {

  2029       if {$code=="SQLITE_ALTER_TABLE"} {

  2030         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  2031         return SQLITE_IGNORE

  2032       }

  2033       return SQLITE_OK

  2034     }

  2035     catchsql {

  2036       ALTER TABLE t5 ADD COLUMN new_col_2;

  2037     }

  2038   } {0 {}}

  2039   do_test auth-1.304 {

  2040     set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]

  2041     regexp new_col_2 $x

  2042   } {0}

  2043   do_test auth-1.305 {

  2044     set authargs

  2045   } {main t5 {} {}}

  2046   do_test auth-1.306 {

  2047     proc auth {code arg1 arg2 arg3 arg4} {

  2048       if {$code=="SQLITE_ALTER_TABLE"} {

  2049         set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  2050         return SQLITE_DENY

  2051       }

  2052       return SQLITE_OK

  2053     }

  2054     catchsql {

  2055       ALTER TABLE t5 ADD COLUMN new_col_3

  2056     }

  2057   } {1 {not authorized}}

  2058   do_test auth-1.307 {

  2059     set x [execsql {SELECT sql FROM sqlite_temp_master WHERE type='t5'}]

  2060     regexp new_col_3 $x

  2061   } {0}

  2063   do_test auth-1.308 {

  2064     set authargs

  2065   } {main t5 {} {}}

  2066   execsql {DROP TABLE t5}

  2067 } ;# ifcapable altertable

  2069 do_test auth-2.1 {

  2070   proc auth {code arg1 arg2 arg3 arg4} {

  2071     if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {

  2072       return SQLITE_DENY

  2073     }

  2074     return SQLITE_OK

  2075   }

  2076   db authorizer ::auth

  2077   execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)}

  2078   catchsql {SELECT * FROM t3}

  2079 } {1 {access to t3.x is prohibited}}

  2080 do_test auth-2.1 {

  2081   catchsql {SELECT y,z FROM t3}

  2082 } {0 {}}

  2083 do_test auth-2.2 {

  2084   catchsql {SELECT ROWID,y,z FROM t3}

  2085 } {1 {access to t3.x is prohibited}}

  2086 do_test auth-2.3 {

  2087   catchsql {SELECT OID,y,z FROM t3}

  2088 } {1 {access to t3.x is prohibited}}

  2089 do_test auth-2.4 {

  2090   proc auth {code arg1 arg2 arg3 arg4} {

  2091     if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {

  2092       return SQLITE_IGNORE

  2093     }

  2094     return SQLITE_OK

  2095   }

  2096   execsql {INSERT INTO t3 VALUES(44,55,66)}

  2097   catchsql {SELECT * FROM t3}

  2098 } {0 {{} 55 66}}

  2099 do_test auth-2.5 {

  2100   catchsql {SELECT rowid,y,z FROM t3}

  2101 } {0 {{} 55 66}}

  2102 do_test auth-2.6 {

  2103   proc auth {code arg1 arg2 arg3 arg4} {

  2104     if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} {

  2105       return SQLITE_IGNORE

  2106     }

  2107     return SQLITE_OK

  2108   }

  2109   catchsql {SELECT * FROM t3}

  2110 } {0 {44 55 66}}

  2111 do_test auth-2.7 {

  2112   catchsql {SELECT ROWID,y,z FROM t3}

  2113 } {0 {44 55 66}}

  2114 do_test auth-2.8 {

  2115   proc auth {code arg1 arg2 arg3 arg4} {

  2116     if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {

  2117       return SQLITE_IGNORE

  2118     }

  2119     return SQLITE_OK

  2120   }

  2121   catchsql {SELECT ROWID,b,c FROM t2}

  2122 } {0 {{} 2 33 {} 8 9}}

  2123 do_test auth-2.9.1 {

  2124   # We have to flush the cache here in case the Tcl interface tries to

  2125   # reuse a statement compiled with sqlite3_prepare_v2(). In this case,

  2126   # the first error encountered is an SQLITE_SCHEMA error. Then, when

  2127   # trying to recompile the statement, the authorization error is encountered.

  2128   # If we do not flush the cache, the correct error message is returned, but

  2129   # the error code is SQLITE_SCHEMA, not SQLITE_ERROR as required by the test

  2130   # case after this one.

  2131   #

  2132   db cache flush

  2134   proc auth {code arg1 arg2 arg3 arg4} {

  2135     if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {

  2136       return bogus

  2137     }

  2138     return SQLITE_OK

  2139   }

  2140   catchsql {SELECT ROWID,b,c FROM t2}

  2141 } {1 {illegal return value (999) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}

  2142 do_test auth-2.9.2 {

  2143   db errorcode

  2144 } {1}

  2145 do_test auth-2.10 {

  2146   proc auth {code arg1 arg2 arg3 arg4} {

  2147     if {$code=="SQLITE_SELECT"} {

  2148       return bogus

  2149     }

  2150     return SQLITE_OK

  2151   }

  2152   catchsql {SELECT ROWID,b,c FROM t2}

  2153 } {1 {illegal return value (1) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}

  2154 do_test auth-2.11.1 {

  2155   proc auth {code arg1 arg2 arg3 arg4} {

  2156     if {$code=="SQLITE_READ" && $arg2=="a"} {

  2157       return SQLITE_IGNORE

  2158     }

  2159     return SQLITE_OK

  2160   }

  2161   catchsql {SELECT * FROM t2, t3}

  2162 } {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}}

  2163 do_test auth-2.11.2 {

  2164   proc auth {code arg1 arg2 arg3 arg4} {

  2165     if {$code=="SQLITE_READ" && $arg2=="x"} {

  2166       return SQLITE_IGNORE

  2167     }

  2168     return SQLITE_OK

  2169   }

  2170   catchsql {SELECT * FROM t2, t3}

  2171 } {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}}

  2173 # Make sure the OLD and NEW pseudo-tables of a trigger get authorized.

  2174 #

  2175 ifcapable trigger {

  2176   do_test auth-3.1 {

  2177     proc auth {code arg1 arg2 arg3 arg4} {

  2178       return SQLITE_OK

  2179     }

  2180     execsql {

  2181       CREATE TABLE tx(a1,a2,b1,b2,c1,c2);

  2182       CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN

  2183         INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c);

  2184       END;

  2185       UPDATE t2 SET a=a+1;

  2186       SELECT * FROM tx;

  2187     }

  2188   } {11 12 2 2 33 33 7 8 8 8 9 9}

  2189   do_test auth-3.2 {

  2190     proc auth {code arg1 arg2 arg3 arg4} {

  2191       if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} {

  2192         return SQLITE_IGNORE

  2193       }

  2194       return SQLITE_OK

  2195     }

  2196     execsql {

  2197       DELETE FROM tx;

  2198       UPDATE t2 SET a=a+100;

  2199       SELECT * FROM tx;

  2200     }

  2201   } {12 112 2 2 {} {} 8 108 8 8 {} {}}

  2202 } ;# ifcapable trigger

  2204 # Make sure the names of views and triggers are passed on on arg4.

  2205 #

  2206 ifcapable trigger {

  2207 do_test auth-4.1 {

  2208   proc auth {code arg1 arg2 arg3 arg4} {

  2209     lappend ::authargs $code $arg1 $arg2 $arg3 $arg4

  2210     return SQLITE_OK

  2211   }

  2212   set authargs {}

  2213   execsql {

  2214     UPDATE t2 SET a=a+1;

  2215   }

  2216   set authargs

  2217 } [list \

  2218   SQLITE_READ   t2 a  main {} \

  2219   SQLITE_UPDATE t2 a  main {} \

  2220   SQLITE_INSERT tx {} main r1 \

  2221   SQLITE_READ   t2 a  main r1 \

  2222   SQLITE_READ   t2 a  main r1 \

  2223   SQLITE_READ   t2 b  main r1 \

  2224   SQLITE_READ   t2 b  main r1 \

  2225   SQLITE_READ   t2 c  main r1 \

  2226   SQLITE_READ   t2 c  main r1]

  2227 }

  2229 ifcapable {view && trigger} {

  2230 do_test auth-4.2 {

  2231   execsql {

  2232     CREATE VIEW v1 AS SELECT a+b AS x FROM t2;

  2233     CREATE TABLE v1chng(x1,x2);

  2234     CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN

  2235       INSERT INTO v1chng VALUES(OLD.x,NEW.x);

  2236     END;

  2237     SELECT * FROM v1;

  2238   }

  2239 } {115 117}

  2240 do_test auth-4.3 {

  2241   set authargs {}

  2242   execsql {

  2243     UPDATE v1 SET x=1 WHERE x=117

  2244   }

  2245   set authargs

  2246 } [list \

  2247   SQLITE_UPDATE v1     x  main {} \

  2248   SQLITE_INSERT v1chng {} main r2 \

  2249   SQLITE_READ   v1     x  main r2 \

  2250   SQLITE_READ   v1     x  main r2 \

  2251   SQLITE_SELECT {}     {} {}   v1 \

  2252   SQLITE_READ   t2     a  main v1 \

  2253   SQLITE_READ   t2     b  main v1 \

  2254   SQLITE_SELECT {}     {} {}   {} \

  2255   SQLITE_READ   v1     x  main v1 \

  2256 ]

  2257 do_test auth-4.4 {

  2258   execsql {

  2259     CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN

  2260       INSERT INTO v1chng VALUES(OLD.x,NULL);

  2261     END;

  2262     SELECT * FROM v1;

  2263   }

  2264 } {115 117}

  2265 do_test auth-4.5 {

  2266   set authargs {}

  2267   execsql {

  2268     DELETE FROM v1 WHERE x=117

  2269   }

  2270   set authargs

  2271 } [list \

  2272   SQLITE_DELETE v1     {} main {} \

  2273   SQLITE_INSERT v1chng {} main r3 \

  2274   SQLITE_READ   v1     x  main r3 \

  2275   SQLITE_SELECT {}     {} {}   v1 \

  2276   SQLITE_READ   t2     a  main v1 \

  2277   SQLITE_READ   t2     b  main v1 \

  2278   SQLITE_SELECT {}     {} {}   {} \

  2279   SQLITE_READ   v1     x  main v1 \

  2280 ]

  2282 } ;# ifcapable view && trigger

  2284 # Ticket #1338:  Make sure authentication works in the presence of an AS

  2285 # clause.

  2286 #

  2287 do_test auth-5.1 {

  2288   proc auth {code arg1 arg2 arg3 arg4} {

  2289     return SQLITE_OK

  2290   }

  2291   execsql {

  2292     SELECT count(a) AS cnt FROM t4 ORDER BY cnt

  2293   }

  2294 } {1}

  2296 # Ticket #1607

  2297 #

  2298 ifcapable compound&&subquery {

  2299   ifcapable trigger {

  2300     execsql {

  2301       DROP TABLE tx;

  2302     }

  2303     ifcapable view {

  2304       execsql {

  2305         DROP TABLE v1chng;

  2306       }

  2307     }

  2308   }

  2309   do_test auth-5.2 {

  2310     execsql {

  2311       SELECT name FROM (

  2312         SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)

  2313       WHERE type='table'

  2314       ORDER BY name

  2315     }

  2316   } {sqlite_stat1 t1 t2 t3 t4}

  2317 }

  2320 rename proc {}

  2321 rename proc_real proc

  2324 finish_test

author	sl@SLION-WIN7.fritz.box
	Fri, 15 Jun 2012 03:10:57 +0200
changeset 0	bde4ae8d615e
permissions	-rw-r--r--