# 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 sqlite3_set_authorizer() API

# and related functionality.

#

# $Id: auth.test,v 1.43 2008/07/02 13:13:52 danielk1977 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_real

proc_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}}

ifcapable attach {

  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