xref: /sqlite-3.40.0/test/auth2.test (revision 5d00d0a8) 
1# 2006 Aug 24
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: auth2.test,v 1.3 2008/07/02 13:13:53 danielk1977 Exp $
16#
17
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
22# defined during compilation.
23if {[catch {db auth {}} msg]} {
24  finish_test
25  return
26}
27
28do_test auth2-1.1 {
29  execsql {
30    CREATE TABLE t1(a,b,c);
31    INSERT INTO t1 VALUES(1,2,3);
32  }
33  set ::flist {}
34  proc auth {code arg1 arg2 arg3 arg4} {
35    if {$code=="SQLITE_FUNCTION"} {
36      lappend ::flist $arg2
37      if {$arg2=="max"} {
38        return SQLITE_DENY
39      } elseif {$arg2=="min"} {
40        return SQLITE_IGNORE
41      } else {
42        return SQLITE_OK
43      }
44    }
45    return SQLITE_OK
46  }
47  db authorizer ::auth
48  catchsql {SELECT max(a,b,c) FROM t1}
49} {1 {not authorized to use function: max}}
50do_test auth2-1.2 {
51  set ::flist
52} max
53do_test auth2-1.3 {
54  set ::flist {}
55  catchsql {SELECT min(a,b,c) FROM t1}
56} {0 {{}}}
57do_test auth2-1.4 {
58  set ::flist
59} min
60do_test auth2-1.5 {
61  set ::flist {}
62  catchsql {SELECT coalesce(min(a,b,c),999) FROM t1}
63} {0 999}
64do_test auth2-1.6 {
65  set ::flist
66} {coalesce min}
67do_test auth2-1.7 {
68  set ::flist {}
69  catchsql {SELECT coalesce(a,b,c) FROM t1}
70} {0 1}
71do_test auth2-1.8 {
72  set ::flist
73} coalesce
74
75# Make sure the authorizer is not called when parsing the schema
76# and when computing the result set of a view.
77#
78db close
79sqlite3 db test.db
80sqlite3 db2 test.db
81proc auth {args} {
82  global authargs
83  append authargs $args\n
84  return SQLITE_OK
85}
86db auth auth
87do_test auth2-2.1 {
88  set ::authargs {}
89  db eval {
90    CREATE TABLE t2(x,y,z);
91  }
92  set ::authargs
93} {SQLITE_INSERT sqlite_master {} main {}
94SQLITE_CREATE_TABLE t2 {} main {}
95SQLITE_UPDATE sqlite_master type main {}
96SQLITE_UPDATE sqlite_master name main {}
97SQLITE_UPDATE sqlite_master tbl_name main {}
98SQLITE_UPDATE sqlite_master rootpage main {}
99SQLITE_UPDATE sqlite_master sql main {}
100SQLITE_READ sqlite_master ROWID main {}
101SQLITE_READ sqlite_master name main {}
102SQLITE_READ sqlite_master rootpage main {}
103SQLITE_READ sqlite_master sql main {}
104SQLITE_READ sqlite_master tbl_name main {}
105}
106do_test auth2-2.2 {
107  set ::authargs {}
108  db eval {
109    CREATE VIEW v2 AS SELECT x+y AS a, y+z AS b from t2;
110  }
111  set ::authargs
112} {SQLITE_INSERT sqlite_master {} main {}
113SQLITE_CREATE_VIEW v2 {} main {}
114SQLITE_UPDATE sqlite_master type main {}
115SQLITE_UPDATE sqlite_master name main {}
116SQLITE_UPDATE sqlite_master tbl_name main {}
117SQLITE_UPDATE sqlite_master rootpage main {}
118SQLITE_UPDATE sqlite_master sql main {}
119SQLITE_READ sqlite_master ROWID main {}
120SQLITE_READ sqlite_master name main {}
121SQLITE_READ sqlite_master rootpage main {}
122SQLITE_READ sqlite_master sql main {}
123SQLITE_READ sqlite_master tbl_name main {}
124}
125do_test auth2-2.3 {
126  set ::authargs {}
127  db eval {
128    SELECT a, b FROM v2;
129  }
130  set ::authargs
131} {SQLITE_SELECT {} {} {} {}
132SQLITE_READ v2 a main {}
133SQLITE_READ v2 b main {}
134SQLITE_READ t2 x main v2
135SQLITE_READ t2 y main v2
136SQLITE_READ t2 y main v2
137SQLITE_READ t2 z main v2
138SQLITE_SELECT {} {} {} v2
139}
140do_test auth2-2.4 {
141  db2 eval {
142    CREATE TABLE t3(p,q,r);
143  }
144  set ::authargs {}
145  db eval {
146    SELECT b, a FROM v2;
147  }
148  set ::authargs
149} {SQLITE_SELECT {} {} {} {}
150SQLITE_READ v2 b main {}
151SQLITE_READ v2 a main {}
152SQLITE_READ t2 x main v2
153SQLITE_READ t2 y main v2
154SQLITE_READ t2 y main v2
155SQLITE_READ t2 z main v2
156SQLITE_SELECT {} {} {} v2
157SQLITE_SELECT {} {} {} {}
158SQLITE_READ v2 b main {}
159SQLITE_READ v2 a main {}
160SQLITE_READ t2 x main v2
161SQLITE_READ t2 y main v2
162SQLITE_READ t2 y main v2
163SQLITE_READ t2 z main v2
164SQLITE_SELECT {} {} {} v2
165}
166db2 close
167
168finish_test
169