# 2003 January 12
#
# 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 testing the sqlite_set_authorizer() API.
#
# $Id: auth.test,v 1.1 2003/01/12 19:33:54 drh Exp $
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
if {[info command sqlite_set_authorizer]!=""} {
do_test auth-1.1 {
db close
set ::DB [sqlite db test.db]
proc auth {code arg1 arg2} {
if {$code=="SQLITE_INSERT_ROW"
&& [string compare -nocase $arg1 sqlite_master]==0} {
return SQLITE_DENY
}
return SQLITE_OK
}
sqlite_set_authorizer $::DB ::auth
catchsql {CREATE TABLE t1(a,b,c)}
} {1 {insertion into table sqlite_master is prohibited}}
do_test auth-1.2 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_INSERT_ROW"
&& [string compare -nocase $arg1 sqlite_master]==0} {
return SQLITE_IGNORE
}
return SQLITE_OK
}
catchsql {CREATE TABLE t1(a,b,c)}
} {1 {insertion into table sqlite_master is prohibited}}
do_test auth-1.3 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_INSERT_ROW"
&& [string compare -nocase $arg1 sqlite_master]==0} {
return SQLITE_OK
}
return SQLITE_OK
}
catchsql {CREATE TABLE t1(a,b,c)}
} {0 {}}
do_test auth-1.4 {
execsql {SELECT name FROM sqlite_master}
} {t1}
do_test auth-1.5 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_INSERT_ROW"
&& [string compare -nocase $arg1 sqlite_master]==0} {
return BOGUS
}
return SQLITE_OK
}
catchsql {CREATE TABLE t2(a,b,c)}
} {1 {illegal return value (1) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}
do_test auth-1.6 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_DELETE_ROW"
&& [string compare -nocase $arg1 sqlite_master]==0} {
return SQLITE_DENY
}
return SQLITE_OK
}
catchsql {DROP TABLE t1}
} {1 {deletion from table sqlite_master is prohibited}}
do_test auth-1.7 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_DELETE_ROW"
&& [string compare -nocase $arg1 sqlite_master]==0} {
return SQLITE_IGNORE
}
return SQLITE_OK
}
catchsql {DROP TABLE t1}
} {1 {deletion from table sqlite_master is prohibited}}
do_test auth-1.8 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_INSERT_ROW"
&& [string compare -nocase $arg1 t1]==0} {
return SQLITE_DENY
}
return SQLITE_OK
}
catchsql {INSERT INTO t1 VALUES(1,2,3)}
} {1 {insertion into table t1 is prohibited}}
do_test auth-1.9 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_INSERT_ROW"
&& [string compare -nocase $arg1 t1]==0} {
return SQLITE_IGNORE
}
return SQLITE_OK
}
catchsql {INSERT INTO t1 VALUES(1,2,3)}
} {0 {}}
do_test auth-1.10 {
execsql {SELECT * FROM t1}
} {}
do_test auth-1.11 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_INSERT_ROW"
&& [string compare -nocase $arg1 t1]==0} {
return SQLITE_OK
}
return SQLITE_OK
}
catchsql {INSERT INTO t1 VALUES(1,2,3)}
} {0 {}}
do_test auth-1.12 {
execsql {SELECT * FROM t1}
} {1 2 3}
do_test auth-1.13 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_DELETE_ROW"
&& [string compare -nocase $arg1 t1]==0} {
return SQLITE_DENY
}
return SQLITE_OK
}
catchsql {DELETE FROM t1 WHERE a=1}
} {1 {deletion from table t1 is prohibited}}
do_test auth-1.14 {
execsql {SELECT * FROM t1}
} {1 2 3}
do_test auth-1.15 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_DELETE_ROW"
&& [string compare -nocase $arg1 t1]==0} {
return SQLITE_IGNORE
}
return SQLITE_OK
}
catchsql {DELETE FROM t1 WHERE a=1}
} {0 {}}
do_test auth-1.16 {
execsql {SELECT * FROM t1}
} {1 2 3}
do_test auth-1.17 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_READ_COLUMN"
&& [string compare -nocase $arg1 t1]==0
&& [string compare -nocase $arg2 a]==0} {
return SQLITE_DENY
}
return SQLITE_OK
}
catchsql {SELECT * FROM t1}
} {1 {access to t1.a is prohibited}}
do_test auth-1.18 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_READ_COLUMN"
&& [string compare -nocase $arg1 t1]==0
&& [string compare -nocase $arg2 a]==0} {
return SQLITE_IGNORE
}
return SQLITE_OK
}
catchsql {SELECT * FROM t1}
} {0 {{} 2 3}}
do_test auth-1.19 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_WRITE_COLUMN"
&& [string compare -nocase $arg1 t1]==0
&& [string compare -nocase $arg2 a]==0} {
return SQLITE_DENY
}
return SQLITE_OK
}
catchsql {UPDATE t1 SET a=11 WHERE a=1}
} {1 {changes to t1.a are prohibited}}
do_test auth-1.20 {
execsql {SELECT * FROM t1}
} {1 2 3}
do_test auth-1.21 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_WRITE_COLUMN"
&& [string compare -nocase $arg1 t1]==0
&& [string compare -nocase $arg2 a]==0} {
return SQLITE_DENY
}
return SQLITE_OK
}
catchsql {UPDATE t1 SET b=12 WHERE a=1}
} {0 {}}
do_test auth-1.22 {
execsql {SELECT * FROM t1}
} {1 12 3}
do_test auth-1.23 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_WRITE_COLUMN"
&& [string compare -nocase $arg1 t1]==0
&& [string compare -nocase $arg2 a]==0} {
return SQLITE_IGNORE
}
return SQLITE_OK
}
catchsql {UPDATE t1 SET a=11, b=22 WHERE a=1}
} {0 {}}
do_test auth-1.24 {
execsql {SELECT * FROM t1}
} {1 22 3}
do_test auth-1.25 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_WRITE_COLUMN"
&& [string compare -nocase $arg1 t1]==0
&& [string compare -nocase $arg2 a]==0} {
return SQLITE_DENY
}
return SQLITE_OK
}
catchsql {UPDATE t1 SET a=11, b=33 WHERE a=1}
} {1 {changes to t1.a are prohibited}}
do_test auth-1.26 {
execsql {SELECT * FROM t1}
} {1 22 3}
do_test auth-1.27 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_READ_COLUMN"
&& [string compare -nocase $arg1 t1]==0
&& [string compare -nocase $arg2 a]==0} {
return SQLITE_DENY
}
return SQLITE_OK
}
catchsql {UPDATE t1 SET b=33, c=44 WHERE a=1}
} {1 {access to t1.a is prohibited}}
do_test auth-1.28 {
execsql {SELECT b, c FROM t1}
} {22 3}
do_test auth-1.29 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_READ_COLUMN"
&& [string compare -nocase $arg1 t1]==0
&& [string compare -nocase $arg2 a]==0} {
return SQLITE_IGNORE
}
return SQLITE_OK
}
catchsql {UPDATE t1 SET b=33, c=44 WHERE a=1}
} {0 {}}
do_test auth-1.30 {
execsql {SELECT b, c FROM t1}
} {22 3}
do_test auth-1.31 {
proc auth {code arg1 arg2} {
if {$code=="SQLITE_READ_COLUMN"
&& [string compare -nocase $arg1 t1]==0
&& [string compare -nocase $arg2 a]==0} {
return SQLITE_IGNORE
}
return SQLITE_OK
}
catchsql {UPDATE t1 SET b=33, c=44 WHERE a IS NULL}
} {0 {}}
do_test auth-1.32 {
execsql {SELECT b, c FROM t1}
} {33 44}
} ;# End of the "if( db command exists )"
finish_test