# 2003 April 4## The author disclaims copyright to this source code.  In place of# a legal notice, here is a blessing:##    May you do good and not evil.#    May you find forgiveness for yourself and forgive others.#    May you share freely, never taking more than you give.##***********************************************************************# This file implements regression tests for SQLite library.  The# focus of this script is testing the ATTACH and DETACH commands# and related functionality.## $Id: auth.test,v 1.34 2006/01/31 14:28:46 drh Exp $#set testdir [file dirname $argv0]source $testdir/tester.tcl# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is# defined during compilation.if {[catch {db auth {}} msg]} {  finish_test  return}rename proc proc_realproc_real proc {name arguments script} {  proc_real $name $arguments $script  if {$name=="auth"} {    db authorizer ::auth  }}do_test auth-1.1.1 {  db close  set ::DB [sqlite3 db test.db]  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {      return SQLITE_DENY    }    return SQLITE_OK  }  db authorizer ::auth  catchsql {CREATE TABLE t1(a,b,c)}} {1 {not authorized}}do_test auth-1.1.2 {  db errorcode} {23}do_test auth-1.1.3 {  db authorizer} {::auth}do_test auth-1.1.4 {  # Ticket #896.  catchsql {    SELECT x;  }} {1 {no such column: x}}do_test auth-1.2 {  execsql {SELECT name FROM sqlite_master}} {}do_test auth-1.3.1 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_CREATE_TABLE"} {      set ::authargs [list $arg1 $arg2 $arg3 $arg4]      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {CREATE TABLE t1(a,b,c)}} {1 {not authorized}}do_test auth-1.3.2 {  db errorcode} {23}do_test auth-1.3.3 {  set ::authargs} {t1 {} main {}}do_test auth-1.4 {  execsql {SELECT name FROM sqlite_master}} {}ifcapable tempdb {  do_test auth-1.5 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {        return SQLITE_DENY      }      return SQLITE_OK    }    catchsql {CREATE TEMP TABLE t1(a,b,c)}  } {1 {not authorized}}  do_test auth-1.6 {    execsql {SELECT name FROM sqlite_temp_master}  } {}  do_test auth-1.7.1 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_CREATE_TEMP_TABLE"} {        set ::authargs [list $arg1 $arg2 $arg3 $arg4]        return SQLITE_DENY      }      return SQLITE_OK    }    catchsql {CREATE TEMP TABLE t1(a,b,c)}  } {1 {not authorized}}  do_test auth-1.7.2 {     set ::authargs  } {t1 {} temp {}}  do_test auth-1.8 {    execsql {SELECT name FROM sqlite_temp_master}  } {}}do_test auth-1.9 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {CREATE TABLE t1(a,b,c)}} {0 {}}do_test auth-1.10 {  execsql {SELECT name FROM sqlite_master}} {}do_test auth-1.11 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_CREATE_TABLE"} {      set ::authargs [list $arg1 $arg2 $arg3 $arg4]      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {CREATE TABLE t1(a,b,c)}} {0 {}}do_test auth-1.12 {  execsql {SELECT name FROM sqlite_master}} {}ifcapable tempdb {  do_test auth-1.13 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {        return SQLITE_IGNORE      }      return SQLITE_OK    }    catchsql {CREATE TEMP TABLE t1(a,b,c)}  } {0 {}}  do_test auth-1.14 {    execsql {SELECT name FROM sqlite_temp_master}  } {}  do_test auth-1.15 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_CREATE_TEMP_TABLE"} {        set ::authargs [list $arg1 $arg2 $arg3 $arg4]        return SQLITE_IGNORE      }      return SQLITE_OK    }    catchsql {CREATE TEMP TABLE t1(a,b,c)}  } {0 {}}  do_test auth-1.16 {    execsql {SELECT name FROM sqlite_temp_master}  } {}    do_test auth-1.17 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_CREATE_TABLE"} {        set ::authargs [list $arg1 $arg2 $arg3 $arg4]        return SQLITE_DENY      }      return SQLITE_OK    }    catchsql {CREATE TEMP TABLE t1(a,b,c)}  } {0 {}}  do_test auth-1.18 {    execsql {SELECT name FROM sqlite_temp_master}  } {t1}}do_test auth-1.19.1 {  set ::authargs {}  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_CREATE_TEMP_TABLE"} {      set ::authargs [list $arg1 $arg2 $arg3 $arg4]      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {CREATE TABLE t2(a,b,c)}} {0 {}}do_test auth-1.19.2 {  set ::authargs} {}do_test auth-1.20 {  execsql {SELECT name FROM sqlite_master}} {t2}do_test auth-1.21.1 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_DROP_TABLE"} {      set ::authargs [list $arg1 $arg2 $arg3 $arg4]      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {DROP TABLE t2}} {1 {not authorized}}do_test auth-1.21.2 {  set ::authargs} {t2 {} main {}}do_test auth-1.22 {  execsql {SELECT name FROM sqlite_master}} {t2}do_test auth-1.23.1 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_DROP_TABLE"} {      set ::authargs [list $arg1 $arg2 $arg3 $arg4]      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {DROP TABLE t2}} {0 {}}do_test auth-1.23.2 {  set ::authargs} {t2 {} main {}}do_test auth-1.24 {  execsql {SELECT name FROM sqlite_master}} {t2}ifcapable tempdb {  do_test auth-1.25 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_DROP_TEMP_TABLE"} {        set ::authargs [list $arg1 $arg2 $arg3 $arg4]        return SQLITE_DENY      }      return SQLITE_OK    }    catchsql {DROP TABLE t1}  } {1 {not authorized}}  do_test auth-1.26 {    execsql {SELECT name FROM sqlite_temp_master}  } {t1}  do_test auth-1.27 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_DROP_TEMP_TABLE"} {        set ::authargs [list $arg1 $arg2 $arg3 $arg4]        return SQLITE_IGNORE      }      return SQLITE_OK    }    catchsql {DROP TABLE t1}  } {0 {}}  do_test auth-1.28 {    execsql {SELECT name FROM sqlite_temp_master}  } {t1}}do_test auth-1.29 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_INSERT" && $arg1=="t2"} {      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {INSERT INTO t2 VALUES(1,2,3)}} {1 {not authorized}}do_test auth-1.30 {  execsql {SELECT * FROM t2}} {}do_test auth-1.31 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_INSERT" && $arg1=="t2"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {INSERT INTO t2 VALUES(1,2,3)}} {0 {}}do_test auth-1.32 {  execsql {SELECT * FROM t2}} {}do_test auth-1.33 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_INSERT" && $arg1=="t1"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {INSERT INTO t2 VALUES(1,2,3)}} {0 {}}do_test auth-1.34 {  execsql {SELECT * FROM t2}} {1 2 3}do_test auth-1.35.1 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {SELECT * FROM t2}} {1 {access to t2.b is prohibited}}do_test auth-1.35.2 {  execsql {ATTACH DATABASE 'test.db' AS two}  catchsql {SELECT * FROM two.t2}} {1 {access to two.t2.b is prohibited}}execsql {DETACH DATABASE two}do_test auth-1.36 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {SELECT * FROM t2}} {0 {1 {} 3}}do_test auth-1.37 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {SELECT * FROM t2 WHERE b=2}} {0 {}}do_test auth-1.38 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {SELECT * FROM t2 WHERE b=2}} {0 {{} 2 3}}do_test auth-1.39 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {SELECT * FROM t2 WHERE b IS NULL}} {0 {1 {} 3}}do_test auth-1.40 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {SELECT a,c FROM t2 WHERE b IS NULL}} {1 {access to t2.b is prohibited}}  do_test auth-1.41 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {UPDATE t2 SET a=11}} {0 {}}do_test auth-1.42 {  execsql {SELECT * FROM t2}} {11 2 3}do_test auth-1.43 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {UPDATE t2 SET b=22, c=33}} {1 {not authorized}}do_test auth-1.44 {  execsql {SELECT * FROM t2}} {11 2 3}do_test auth-1.45 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {UPDATE t2 SET b=22, c=33}} {0 {}}do_test auth-1.46 {  execsql {SELECT * FROM t2}} {11 2 33}do_test auth-1.47 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_DELETE" && $arg1=="t2"} {      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {DELETE FROM t2 WHERE a=11}} {1 {not authorized}}do_test auth-1.48 {  execsql {SELECT * FROM t2}} {11 2 33}do_test auth-1.49 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_DELETE" && $arg1=="t2"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {DELETE FROM t2 WHERE a=11}} {0 {}}do_test auth-1.50 {  execsql {SELECT * FROM t2}} {11 2 33}do_test auth-1.51 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_SELECT"} {      return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {SELECT * FROM t2}} {1 {not authorized}}do_test auth-1.52 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_SELECT"} {      return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {SELECT * FROM t2}} {0 {}}do_test auth-1.53 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_SELECT"} {      return SQLITE_OK    }    return SQLITE_OK  }  catchsql {SELECT * FROM t2}} {0 {11 2 33}}# Update for version 3: There used to be a handful of test here that# tested the authorisation callback with the COPY command. The following# test makes the same database modifications as they used to.do_test auth-1.54 {  execsql {INSERT INTO t2 VALUES(7, 8, 9);}} {}do_test auth-1.55 {  execsql {SELECT * FROM t2}} {11 2 33 7 8 9}do_test auth-1.63 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {       return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {DROP TABLE t2}} {1 {not authorized}}do_test auth-1.64 {  execsql {SELECT name FROM sqlite_master}} {t2}do_test auth-1.65 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_DELETE" && $arg1=="t2"} {       return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {DROP TABLE t2}} {1 {not authorized}}do_test auth-1.66 {  execsql {SELECT name FROM sqlite_master}} {t2}ifcapable tempdb {  do_test auth-1.67 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {         return SQLITE_DENY      }      return SQLITE_OK    }    catchsql {DROP TABLE t1}  } {1 {not authorized}}  do_test auth-1.68 {    execsql {SELECT name FROM sqlite_temp_master}  } {t1}  do_test auth-1.69 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_DELETE" && $arg1=="t1"} {         return SQLITE_DENY      }      return SQLITE_OK    }    catchsql {DROP TABLE t1}  } {1 {not authorized}}  do_test auth-1.70 {    execsql {SELECT name FROM sqlite_temp_master}  } {t1}}do_test auth-1.71 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {       return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {DROP TABLE t2}} {0 {}}do_test auth-1.72 {  execsql {SELECT name FROM sqlite_master}} {t2}do_test auth-1.73 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_DELETE" && $arg1=="t2"} {       return SQLITE_IGNORE    }    return SQLITE_OK  }  catchsql {DROP TABLE t2}} {0 {}}do_test auth-1.74 {  execsql {SELECT name FROM sqlite_master}} {t2}ifcapable tempdb {  do_test auth-1.75 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {         return SQLITE_IGNORE      }      return SQLITE_OK    }    catchsql {DROP TABLE t1}  } {0 {}}  do_test auth-1.76 {    execsql {SELECT name FROM sqlite_temp_master}  } {t1}  do_test auth-1.77 {    proc auth {code arg1 arg2 arg3 arg4} {      if {$code=="SQLITE_DELETE" && $arg1=="t1"} {         return SQLITE_IGNORE      }      return SQLITE_OK    }    catchsql {DROP TABLE t1}  } {0 {}}  do_test auth-1.78 {    execsql {SELECT name FROM sqlite_temp_master}  } {t1}}# Test cases auth-1.79 to auth-1.124 test creating and dropping views.# Omit these if the library was compiled with views omitted.ifcapable view {do_test auth-1.79 {  proc auth {code arg1 arg2 arg3 arg4} {    if {$code=="SQLITE_CREATE_VIEW"} {      set ::authargs [list $arg1 $arg2 $arg3 $arg4]       return SQLITE_DENY    }    return SQLITE_OK  }  catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}} {1 {not authorized}}do_test auth-1.80 {  set ::authargs