# 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.46 2009/07/02 18:40:35 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 authx {code arg1 arg2 arg3 arg4 args} {return SQLITE_DENY}

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

db authorizer ::authx

# EVIDENCE-OF: R-03993-24285 Only a single authorizer can be in place on

# a database connection at a time. Each call to sqlite3_set_authorizer

# overrides the previous call.

#

# The authx authorizer above is overridden by the auth authorizer below

# authx is never invoked.

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}

} {}

# EVIDENCE-OF: R-04452-49349 When the callback returns SQLITE_DENY, the

# sqlite3_prepare_v2() or equivalent call that triggered the authorizer

# will fail with an error message explaining that access is denied.

do_test auth-1.3.1 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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 args} {

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 temp.sqlite_master}

} {}

do_test auth-1.7.1 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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 args} {

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 args} {

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 args} {

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 temp.sqlite_master}

} {}

do_test auth-1.15 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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}

}

# EVIDENCE-OF: R-38392-49970 If the action code is SQLITE_READ and the

# callback returns SQLITE_IGNORE then the prepared statement statement

# is constructed to substitute a NULL value in place of the table column

# that would have been read if SQLITE_OK had been returned.

do_test auth-1.36 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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}

} {}

do_test auth-1.50.2 {

execsql {INSERT INTO t2 VALUES(11, 2, 33)}

} {}

do_test auth-1.51 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 args} {

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 temp.sqlite_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 args} {

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

} {v1 {} main {}}

do_test auth-1.81 {

execsql {SELECT name FROM sqlite_master}

} {t2}

do_test auth-1.82 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_CREATE_VIEW"} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

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

} {0 {}}

do_test auth-1.83 {

set ::authargs

} {v1 {} main {}}

do_test auth-1.84 {

execsql {SELECT name FROM sqlite_master}

} {t2}

ifcapable tempdb {

do_test auth-1.85 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_CREATE_TEMP_VIEW"} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

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

} {1 {not authorized}}

do_test auth-1.86 {

set ::authargs

} {v1 {} temp {}}

do_test auth-1.87 {

execsql {SELECT name FROM sqlite_temp_master}

} {t1}

do_test auth-1.88 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_CREATE_TEMP_VIEW"} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

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

} {0 {}}

do_test auth-1.89 {

set ::authargs

} {v1 {} temp {}}

do_test auth-1.90 {

execsql {SELECT name FROM temp.sqlite_master}

} {t1}

}

do_test auth-1.91 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

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.92 {

execsql {SELECT name FROM sqlite_master}

} {t2}

do_test auth-1.93 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

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

} {0 {}}

do_test auth-1.94 {

execsql {SELECT name FROM sqlite_master}

} {t2}

ifcapable tempdb {

do_test auth-1.95 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

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

} {1 {not authorized}}

do_test auth-1.96 {

execsql {SELECT name FROM sqlite_temp_master}

} {t1}

do_test auth-1.97 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

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

} {0 {}}

do_test auth-1.98 {

execsql {SELECT name FROM sqlite_temp_master}

} {t1}

}

do_test auth-1.99 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

catchsql {

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

DROP VIEW v2

}

} {1 {not authorized}}

do_test auth-1.100 {

execsql {SELECT name FROM sqlite_master}

} {t2 v2}

do_test auth-1.101 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_DROP_VIEW"} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

catchsql {DROP VIEW v2}

} {1 {not authorized}}

do_test auth-1.102 {

set ::authargs

} {v2 {} main {}}

do_test auth-1.103 {

execsql {SELECT name FROM sqlite_master}

} {t2 v2}

do_test auth-1.104 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

catchsql {DROP VIEW v2}

} {0 {}}

do_test auth-1.105 {

execsql {SELECT name FROM sqlite_master}

} {t2 v2}

do_test auth-1.106 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_DROP_VIEW"} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

catchsql {DROP VIEW v2}

} {0 {}}

do_test auth-1.107 {

set ::authargs

} {v2 {} main {}}

do_test auth-1.108 {

execsql {SELECT name FROM sqlite_master}

} {t2 v2}

do_test auth-1.109 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_DROP_VIEW"} {

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

return SQLITE_OK

}

return SQLITE_OK

}

catchsql {DROP VIEW v2}

} {0 {}}

do_test auth-1.110 {

set ::authargs

} {v2 {} main {}}

do_test auth-1.111 {

execsql {SELECT name FROM sqlite_master}

} {t2}

ifcapable tempdb {

do_test auth-1.112 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

catchsql {

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

DROP VIEW v1

}

} {1 {not authorized}}

do_test auth-1.113 {

execsql {SELECT name FROM temp.sqlite_master}

} {t1 v1}

do_test auth-1.114 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_DROP_TEMP_VIEW"} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

catchsql {DROP VIEW v1}

} {1 {not authorized}}

do_test auth-1.115 {

set ::authargs

} {v1 {} temp {}}

do_test auth-1.116 {

execsql {SELECT name FROM sqlite_temp_master}

} {t1 v1}

do_test auth-1.117 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

catchsql {DROP VIEW v1}

} {0 {}}

do_test auth-1.118 {

execsql {SELECT name FROM sqlite_temp_master}

} {t1 v1}

do_test auth-1.119 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_DROP_TEMP_VIEW"} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

catchsql {DROP VIEW v1}

} {0 {}}

do_test auth-1.120 {

set ::authargs

} {v1 {} temp {}}

do_test auth-1.121 {

execsql {SELECT name FROM temp.sqlite_master}

} {t1 v1}

do_test auth-1.122 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_DROP_TEMP_VIEW"} {

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

return SQLITE_OK

}

return SQLITE_OK

}

catchsql {DROP VIEW v1}

} {0 {}}

do_test auth-1.123 {

set ::authargs

} {v1 {} temp {}}

do_test auth-1.124 {

execsql {SELECT name FROM sqlite_temp_master}

} {t1}

}

} ;# ifcapable view

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

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

#

ifcapable trigger&&tempdb {

do_test auth-1.125 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_CREATE_TRIGGER"} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

catchsql {

CREATE TRIGGER r2 DELETE on t2 BEGIN

SELECT NULL;

END;

}

} {1 {not authorized}}

do_test auth-1.126 {

set ::authargs

} {r2 t2 main {}}

do_test auth-1.127 {

execsql {SELECT name FROM sqlite_master}

} {t2}

do_test auth-1.128 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

catchsql {

CREATE TRIGGER r2 DELETE on t2 BEGIN

SELECT NULL;

END;

}

} {1 {not authorized}}

do_test auth-1.129 {

execsql {SELECT name FROM sqlite_master}

} {t2}

do_test auth-1.130 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_CREATE_TRIGGER"} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

catchsql {

CREATE TRIGGER r2 DELETE on t2 BEGIN

SELECT NULL;

END;

}

} {0 {}}

do_test auth-1.131 {

set ::authargs

} {r2 t2 main {}}

do_test auth-1.132 {

execsql {SELECT name FROM sqlite_master}

} {t2}

do_test auth-1.133 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_IGNORE

}

return SQLITE_OK

}

catchsql {

CREATE TRIGGER r2 DELETE on t2 BEGIN

SELECT NULL;

END;

}

} {0 {}}

do_test auth-1.134 {

execsql {SELECT name FROM sqlite_master}

} {t2}

do_test auth-1.135 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_CREATE_TRIGGER"} {

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

return SQLITE_OK

}

return SQLITE_OK

}

catchsql {

CREATE TABLE tx(id);

CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN

INSERT INTO tx VALUES(NEW.rowid);

END;

}

} {0 {}}

do_test auth-1.136.1 {

set ::authargs

} {r2 t2 main {}}

do_test auth-1.136.2 {

execsql {

SELECT name FROM sqlite_master WHERE type='trigger'

}

} {r2}

do_test auth-1.136.3 {

proc auth {code arg1 arg2 arg3 arg4 args} {

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

return SQLITE_OK

}

set ::authargs {}

execsql {

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

}

set ::authargs

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

do_test auth-1.136.4 {

execsql {

SELECT * FROM tx;

}

} {3}

do_test auth-1.137 {

execsql {SELECT name FROM sqlite_master}

} {t2 tx r2}

do_test auth-1.138 {

proc auth {code arg1 arg2 arg3 arg4 args} {

if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {

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

return SQLITE_DENY

}

return SQLITE_OK

}

catchsql {

CREATE TRIGGER r1 DELETE on t1 BEGIN

SELECT NULL;

END;

}

} {1 {not authorized}}

do_test auth-1.139 {

set ::authargs

} {r1 t1 temp {}}

do_test auth-1.140 {

execsql {SELECT name FROM temp.sqlite_master}

} {t1}

do_test auth-1.141 {