auth.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

    do_test auth-1.264 {
      execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
    } {t1x}
    do_test auth-1.265 {
      set authargs
    } {temp t1 {} {}}
    do_test auth-1.266 {
      proc auth {code arg1 arg2 arg3 arg4} {
        if {$code=="SQLITE_ALTER_TABLE"} {
          set ::authargs [list $arg1 $arg2 $arg3 $arg4]
          return SQLITE_IGNORE
        }
        return SQLITE_OK
      }
      catchsql {
        ALTER TABLE t1x RENAME TO t1
      }
    } {0 {}}
    do_test auth-1.267 {
      execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
    } {t1x}
    do_test auth-1.268 {
      set authargs
    } {temp t1x {} {}}
    do_test auth-1.269 {
      proc auth {code arg1 arg2 arg3 arg4} {
        if {$code=="SQLITE_ALTER_TABLE"} {
          set ::authargs [list $arg1 $arg2 $arg3 $arg4]
          return SQLITE_DENY
        }
        return SQLITE_OK
      }
      catchsql {
        ALTER TABLE t1x RENAME TO t1
      }
    } {1 {not authorized}}
    do_test auth-1.270 {
      execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
    } {t1x}

    do_test auth-1.271 {
      set authargs
    } {temp t1x {} {}}
  } ;# ifcapable altertable

} else {
  db authorizer {}
  db eval {
    DETACH DATABASE test1;
  }
}

ifcapable  altertable {
db authorizer {}
catchsql {ALTER TABLE t1x RENAME TO t1}
db authorizer ::auth
do_test auth-1.272 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_ALTER_TABLE"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_OK
    }
    return SQLITE_OK
  }
  catchsql {
    ALTER TABLE t2 RENAME TO t2x
  }
} {0 {}}
do_test auth-1.273 {
  execsql {SELECT name FROM sqlite_master WHERE type='table'}
} {t2x}
do_test auth-1.274 {
  set authargs
} {main t2 {} {}}
do_test auth-1.275 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_ALTER_TABLE"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {
    ALTER TABLE t2x RENAME TO t2
  }
} {0 {}}
do_test auth-1.276 {
  execsql {SELECT name FROM sqlite_master WHERE type='table'}
} {t2x}
do_test auth-1.277 {
  set authargs
} {main t2x {} {}}
do_test auth-1.278 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_ALTER_TABLE"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {
    ALTER TABLE t2x RENAME TO t2
  }
} {1 {not authorized}}
do_test auth-1.279 {
  execsql {SELECT name FROM sqlite_master WHERE type='table'}
} {t2x}
do_test auth-1.280 {
  set authargs
} {main t2x {} {}}
db authorizer {}
catchsql {ALTER TABLE t2x RENAME TO t2}

} ;# ifcapable altertable

# Test the authorization callbacks for the REINDEX command.
ifcapable reindex {

proc auth {code args} {
  if {$code=="SQLITE_REINDEX"} {
    set ::authargs [concat $::authargs $args]
  }
  return SQLITE_OK
}
db authorizer auth
do_test auth-1.281 {
  execsql {
    CREATE TABLE t3(a PRIMARY KEY, b, c);
    CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);
    CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);
  }
} {}
do_test auth-1.282 {
  set ::authargs {}
  execsql {
    REINDEX t3_idx1;
  }
  set ::authargs
} {t3_idx1 {} main {}}
do_test auth-1.283 {
  set ::authargs {}
  execsql {
    REINDEX BINARY;
  }
  set ::authargs
} {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}
do_test auth-1.284 {
  set ::authargs {}
  execsql {
    REINDEX NOCASE;
  }
  set ::authargs
} {t3_idx2 {} main {}}
do_test auth-1.285 {
  set ::authargs {}
  execsql {
    REINDEX t3;
  }
  set ::authargs
} {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}
do_test auth-1.286 {
  execsql {
    DROP TABLE t3;
  }
} {}
ifcapable tempdb {
  do_test auth-1.287 {
    execsql {
      CREATE TEMP TABLE t3(a PRIMARY KEY, b, c);
      CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);
      CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);
    }
  } {}
  do_test auth-1.288 {
    set ::authargs {}
    execsql {
      REINDEX temp.t3_idx1;
    }
    set ::authargs
  } {t3_idx1 {} temp {}}
  do_test auth-1.289 {
    set ::authargs {}
    execsql {
      REINDEX BINARY;
    }
    set ::authargs
  } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
  do_test auth-1.290 {
    set ::authargs {}
    execsql {
      REINDEX NOCASE;
    }
    set ::authargs
  } {t3_idx2 {} temp {}}
  do_test auth-1.291 {
    set ::authargs {}
    execsql {
      REINDEX temp.t3;
    }
    set ::authargs
  } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
  proc auth {code args} {
    if {$code=="SQLITE_REINDEX"} {
      set ::authargs [concat $::authargs $args]
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  do_test auth-1.292 {
    set ::authargs {}
    catchsql {
      REINDEX temp.t3;
    }
  } {1 {not authorized}}
  do_test auth-1.293 {
    execsql {
      DROP TABLE t3;
    }
  } {}
}

} ;# ifcapable reindex 

ifcapable analyze {
  proc auth {code args} {
    if {$code=="SQLITE_ANALYZE"} {
      set ::authargs [concat $::authargs $args]
    }
    return SQLITE_OK
  }
  do_test auth-1.294 {
    set ::authargs {}
    execsql {
      CREATE TABLE t4(a,b,c);
      CREATE INDEX t4i1 ON t4(a);
      CREATE INDEX t4i2 ON t4(b,a,c);
      INSERT INTO t4 VALUES(1,2,3);
      ANALYZE;
    }
    set ::authargs
  } {t4 {} main {}}
  do_test auth-1.295 {
    execsql {
      SELECT count(*) FROM sqlite_stat1;
    }
  } 2
  proc auth {code args} {
    if {$code=="SQLITE_ANALYZE"} {
      set ::authargs [concat $::authargs $args]
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  do_test auth-1.296 {
    set ::authargs {}
    catchsql {
      ANALYZE;
    }
  } {1 {not authorized}}
  do_test auth-1.297 {
    execsql {
      SELECT count(*) FROM sqlite_stat1;
    }
  } 2
} ;# ifcapable analyze


# Authorization for ALTER TABLE ADD COLUMN.
# These tests are omitted if the library
# was built without ALTER TABLE support.
ifcapable {altertable} {
  do_test auth-1.300 {
    execsql {CREATE TABLE t5(x)}
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_ALTER_TABLE"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_OK
      }
      return SQLITE_OK
    }
    catchsql {
      ALTER TABLE t5 ADD COLUMN new_col_1;
    }
  } {0 {}}
  do_test auth-1.301 {
    set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]
    regexp new_col_1 $x
  } {1}
  do_test auth-1.302 {
    set authargs
  } {main t5 {} {}}
  do_test auth-1.303 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_ALTER_TABLE"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {
      ALTER TABLE t5 ADD COLUMN new_col_2;
    }
  } {0 {}}
  do_test auth-1.304 {
    set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]
    regexp new_col_2 $x
  } {0}
  do_test auth-1.305 {
    set authargs
  } {main t5 {} {}}
  do_test auth-1.306 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_ALTER_TABLE"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {
      ALTER TABLE t5 ADD COLUMN new_col_3
    }
  } {1 {not authorized}}
  do_test auth-1.307 {
    set x [execsql {SELECT sql FROM sqlite_temp_master WHERE type='t5'}]
    regexp new_col_3 $x
  } {0}

  do_test auth-1.308 {
    set authargs
  } {main t5 {} {}}
  execsql {DROP TABLE t5}
} ;# ifcapable altertable

do_test auth-2.1 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  db authorizer ::auth
  execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)}
  catchsql {SELECT * FROM t3}
} {1 {access to t3.x is prohibited}}
do_test auth-2.1 {
  catchsql {SELECT y,z FROM t3}
} {0 {}}
do_test auth-2.2 {
  catchsql {SELECT ROWID,y,z FROM t3}
} {1 {access to t3.x is prohibited}}
do_test auth-2.3 {
  catchsql {SELECT OID,y,z FROM t3}
} {1 {access to t3.x is prohibited}}
do_test auth-2.4 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  execsql {INSERT INTO t3 VALUES(44,55,66)}
  catchsql {SELECT * FROM t3}
} {0 {{} 55 66}}
do_test auth-2.5 {
  catchsql {SELECT rowid,y,z FROM t3}
} {0 {{} 55 66}}
do_test auth-2.6 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t3}
} {0 {44 55 66}}
do_test auth-2.7 {
  catchsql {SELECT ROWID,y,z FROM t3}
} {0 {44 55 66}}
do_test auth-2.8 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {SELECT ROWID,b,c FROM t2}
} {0 {{} 2 33 {} 8 9}}
do_test auth-2.9.1 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
      return bogus
    }
    return SQLITE_OK
  }
  catchsql {SELECT ROWID,b,c FROM t2}
} {1 {illegal return value (999) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}
do_test auth-2.9.2 {
  db errorcode
} {1}
do_test auth-2.10 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_SELECT"} {
      return bogus
    }
    return SQLITE_OK
  }
  catchsql {SELECT ROWID,b,c FROM t2}
} {1 {illegal return value (1) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}
do_test auth-2.11.1 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_READ" && $arg2=="a"} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t2, t3}
} {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}}
do_test auth-2.11.2 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_READ" && $arg2=="x"} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t2, t3}
} {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}}

# Make sure the OLD and NEW pseudo-tables of a trigger get authorized.
#
ifcapable trigger {
  do_test auth-3.1 {
    proc auth {code arg1 arg2 arg3 arg4} {
      return SQLITE_OK
    }
    execsql {
      CREATE TABLE tx(a1,a2,b1,b2,c1,c2);
      CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN
        INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c);
      END;
      UPDATE t2 SET a=a+1;
      SELECT * FROM tx;
    }
  } {11 12 2 2 33 33 7 8 8 8 9 9}
  do_test auth-3.2 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} {
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    execsql {
      DELETE FROM tx;
      UPDATE t2 SET a=a+100;
      SELECT * FROM tx;
    }
  } {12 112 2 2 {} {} 8 108 8 8 {} {}}
} ;# ifcapable trigger

# Make sure the names of views and triggers are passed on on arg4.
#
ifcapable trigger {
do_test auth-4.1 {
  proc auth {code arg1 arg2 arg3 arg4} {
    lappend ::authargs $code $arg1 $arg2 $arg3 $arg4
    return SQLITE_OK
  }
  set authargs {}
  execsql {
    UPDATE t2 SET a=a+1;
  }
  set authargs
} [list \
  SQLITE_READ   t2 a  main {} \
  SQLITE_UPDATE t2 a  main {} \
  SQLITE_INSERT tx {} main r1 \
  SQLITE_READ   t2 a  main r1 \
  SQLITE_READ   t2 a  main r1 \
  SQLITE_READ   t2 b  main r1 \
  SQLITE_READ   t2 b  main r1 \
  SQLITE_READ   t2 c  main r1 \
  SQLITE_READ   t2 c  main r1]
}

ifcapable {view && trigger} {
do_test auth-4.2 {
  execsql {
    CREATE VIEW v1 AS SELECT a+b AS x FROM t2;
    CREATE TABLE v1chng(x1,x2);
    CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN
      INSERT INTO v1chng VALUES(OLD.x,NEW.x);
    END;
    SELECT * FROM v1;
  }
} {115 117}
do_test auth-4.3 {
  set authargs {}
  execsql {
    UPDATE v1 SET x=1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_UPDATE v1     x  main {} \
  SQLITE_READ   v1     x  main {} \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_INSERT v1chng {} main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_READ   v1     x  main r2]
do_test auth-4.4 {
  execsql {
    CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
      INSERT INTO v1chng VALUES(OLD.x,NULL);
    END;
    SELECT * FROM v1;
  }
} {115 117}
do_test auth-4.5 {
  set authargs {}
  execsql {
    DELETE FROM v1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_DELETE v1     {} main {} \
  SQLITE_READ   v1     x  main {} \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_INSERT v1chng {} main r3 \
  SQLITE_READ   v1     x  main r3]

} ;# ifcapable view && trigger

# Ticket #1338:  Make sure authentication works in the presence of an AS
# clause.
#
do_test auth-5.1 {
  proc auth {code arg1 arg2 arg3 arg4} {
    return SQLITE_OK
  }
  execsql {
    SELECT count(a) AS cnt FROM t4 ORDER BY cnt
  }
} {1}

# Ticket #1607
#
ifcapable compound&&subquery {
  ifcapable trigger {
    execsql {
      DROP TABLE tx;
    }
    ifcapable view {
      execsql {
        DROP TABLE v1chng;
      }
    }
  }
  do_test auth-5.2 {
    execsql {
      SELECT name FROM (
        SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)
      WHERE type='table'
      ORDER BY name
    }
  } {sqlite_stat1 t1 t2 t3 t4}
}


rename proc {}
rename proc_real proc


finish_test