test/auth.test

# 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