/ Check-in [3980ea09]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:New requirements marks and documentation for the authorizer.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3980ea0911b3ad3f86d7a7bdc6503f233315c274f473e18831e13eda2c238eeb
User & Date: drh 2017-05-11 13:43:57
Context
2017-05-11
18:14
Do not save the state of an fts5 merge operation mid-way through an input term, even if no previous entry for that term has caused any output. Doing so may corrupt the FTS index. check-in: 9a2de4f0 user: dan tags: trunk
15:20
Negative N values in sqlite3_get_auxdata() and sqlite3_set_auxdata() can be used to access an auxiliary data cache over all functions in a single prepared statement. check-in: ff530675 user: drh tags: auxdata-cache
13:43
New requirements marks and documentation for the authorizer. check-in: 3980ea09 user: drh tags: trunk
12:27
Improvements to the sqlite3_set_authorizer() documentation. check-in: 47629b19 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/auth.c.

212
213
214
215
216
217
218












219
220
221
222
223
224
225
  if( db->init.busy || IN_DECLARE_VTAB ){
    return SQLITE_OK;
  }

  if( db->xAuth==0 ){
    return SQLITE_OK;
  }












  rc = db->xAuth(db->pAuthArg, code, zArg1, zArg2, zArg3, pParse->zAuthContext
#ifdef SQLITE_USER_AUTHENTICATION
                 ,db->auth.zAuthUser
#endif
                );
  if( rc==SQLITE_DENY ){
    sqlite3ErrorMsg(pParse, "not authorized");







>
>
>
>
>
>
>
>
>
>
>
>







212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
  if( db->init.busy || IN_DECLARE_VTAB ){
    return SQLITE_OK;
  }

  if( db->xAuth==0 ){
    return SQLITE_OK;
  }

  /* EVIDENCE-OF: R-43249-19882 The third through sixth parameters to the
  ** callback are either NULL pointers or zero-terminated strings that
  ** contain additional details about the action to be authorized.
  **
  ** The following testcase() macros show that any of the 3rd through 6th
  ** parameters can be either NULL or a string. */
  testcase( zArg1==0 );
  testcase( zArg2==0 );
  testcase( zArg3==0 );
  testcase( pParse->zAuthContext==0 );

  rc = db->xAuth(db->pAuthArg, code, zArg1, zArg2, zArg3, pParse->zAuthContext
#ifdef SQLITE_USER_AUTHENTICATION
                 ,db->auth.zAuthUser
#endif
                );
  if( rc==SQLITE_DENY ){
    sqlite3ErrorMsg(pParse, "not authorized");

Changes to src/delete.c.

346
347
348
349
350
351
352
353







354
355
356
357
358
359
360
    sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt);
  }

#ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
  ** this optimization caused the row change count (the value returned by 
  ** API function sqlite3_count_changes) to be set incorrectly.  */







  if( rcauth==SQLITE_OK
   && pWhere==0
   && !bComplex
   && !IsVirtual(pTab)
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
   && db->xPreUpdateCallback==0
#endif







|
>
>
>
>
>
>
>







346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
    sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt);
  }

#ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
  ** this optimization caused the row change count (the value returned by 
  ** API function sqlite3_count_changes) to be set incorrectly.
  **
  ** The "rcauth==SQLITE_OK" terms is the
  ** IMPLEMENATION-OF: R-17228-37124 If the action code is SQLITE_DELETE and
  ** the callback returns SQLITE_IGNORE then the DELETE operation proceeds but
  ** the truncate optimization is disabled and all rows are deleted
  ** individually.
  */
  if( rcauth==SQLITE_OK
   && pWhere==0
   && !bComplex
   && !IsVirtual(pTab)
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
   && db->xPreUpdateCallback==0
#endif

Changes to src/tclsqlite.c.

1029
1030
1031
1032
1033
1034
1035




1036
1037
1038



1039
1040
1041
1042
1043
1044
1045
  ,const char *zArg5
#endif
){
  const char *zCode;
  Tcl_DString str;
  int rc;
  const char *zReply;




  SqliteDb *pDb = (SqliteDb*)pArg;
  if( pDb->disableAuth ) return SQLITE_OK;




  switch( code ){
    case SQLITE_COPY              : zCode="SQLITE_COPY"; break;
    case SQLITE_CREATE_INDEX      : zCode="SQLITE_CREATE_INDEX"; break;
    case SQLITE_CREATE_TABLE      : zCode="SQLITE_CREATE_TABLE"; break;
    case SQLITE_CREATE_TEMP_INDEX : zCode="SQLITE_CREATE_TEMP_INDEX"; break;
    case SQLITE_CREATE_TEMP_TABLE : zCode="SQLITE_CREATE_TEMP_TABLE"; break;
    case SQLITE_CREATE_TEMP_TRIGGER: zCode="SQLITE_CREATE_TEMP_TRIGGER"; break;







>
>
>
>



>
>
>







1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
  ,const char *zArg5
#endif
){
  const char *zCode;
  Tcl_DString str;
  int rc;
  const char *zReply;
  /* EVIDENCE-OF: R-38590-62769 The first parameter to the authorizer
  ** callback is a copy of the third parameter to the
  ** sqlite3_set_authorizer() interface.
  */
  SqliteDb *pDb = (SqliteDb*)pArg;
  if( pDb->disableAuth ) return SQLITE_OK;

  /* EVIDENCE-OF: R-56518-44310 The second parameter to the callback is an
  ** integer action code that specifies the particular action to be
  ** authorized. */
  switch( code ){
    case SQLITE_COPY              : zCode="SQLITE_COPY"; break;
    case SQLITE_CREATE_INDEX      : zCode="SQLITE_CREATE_INDEX"; break;
    case SQLITE_CREATE_TABLE      : zCode="SQLITE_CREATE_TABLE"; break;
    case SQLITE_CREATE_TEMP_INDEX : zCode="SQLITE_CREATE_TEMP_INDEX"; break;
    case SQLITE_CREATE_TEMP_TABLE : zCode="SQLITE_CREATE_TEMP_TABLE"; break;
    case SQLITE_CREATE_TEMP_TRIGGER: zCode="SQLITE_CREATE_TEMP_TRIGGER"; break;

Changes to test/auth.test.

32
33
34
35
36
37
38

39
40
41
42
43
44







45
46
47
48
49
50
51
..
56
57
58
59
60
61
62



63
64
65
66
67
68
69
...
308
309
310
311
312
313
314




315
316
317
318
319
320
321
....
1602
1603
1604
1605
1606
1607
1608


1609
1610
1611
1612
1613
1614
1615
....
2477
2478
2479
2480
2481
2482
2483






2484
2485
2486
2487
2488
2489
2490
    db authorizer ::auth
  }
}

do_test auth-1.1.1 {
  db close
  set ::DB [sqlite3 db test.db]

  proc auth {code arg1 arg2 arg3 arg4 args} {
    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }







  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 {
................................................................................
  catchsql {
    SELECT x;
  }
} {1 {no such column: x}}
do_test auth-1.2 {
  execsql {SELECT name FROM sqlite_master}
} {}



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




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
  }
................................................................................
do_test auth-1.247 {
  catchsql {END TRANSACTION}
} {1 {not authorized}}
do_test auth-1.248 {
  set ::authargs
} {COMMIT {} {} {}}
do_test auth-1.249 {


  db authorizer {}
  catchsql {ROLLBACK}
} {0 {}}
do_test auth-1.250 {
  execsql {SELECT * FROM t2}
} {11 2 33 7 8 9}

................................................................................
  SQLITE_READ t7 a main {}       \
  SQLITE_READ t7 c main {}       \
]

# If a table is referenced but no columns are read from the table,
# that causes a single SQLITE_READ authorization with a NULL column
# name.






#
set ::authargs [list]
do_test auth-8.1 {
  execsql {SELECT count(*) FROM t7}
  set ::authargs
} [list \
  SQLITE_SELECT {} {} {} {}          \







>






>
>
>
>
>
>
>







 







>
>
>







 







>
>
>
>







 







>
>







 







>
>
>
>
>
>







32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
...
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
....
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
....
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
    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 {
................................................................................
  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
................................................................................
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
  }
................................................................................
do_test auth-1.247 {
  catchsql {END TRANSACTION}
} {1 {not authorized}}
do_test auth-1.248 {
  set ::authargs
} {COMMIT {} {} {}}
do_test auth-1.249 {
  # EVIDENCE-OF: R-52112-44167 Disable the authorizer by installing a NULL
  # callback.
  db authorizer {}
  catchsql {ROLLBACK}
} {0 {}}
do_test auth-1.250 {
  execsql {SELECT * FROM t2}
} {11 2 33 7 8 9}

................................................................................
  SQLITE_READ t7 a main {}       \
  SQLITE_READ t7 c main {}       \
]

# If a table is referenced but no columns are read from the table,
# that causes a single SQLITE_READ authorization with a NULL column
# name.
#
# EVIDENCE-OF: R-31520-16302 When a table is referenced by a SELECT but
# no column values are extracted from that table (for example in a query
# like "SELECT count(*) FROM tab") then the SQLITE_READ authorizer
# callback is invoked once for that table with a column name that is an
# empty string.
#
set ::authargs [list]
do_test auth-8.1 {
  execsql {SELECT count(*) FROM t7}
  set ::authargs
} [list \
  SQLITE_SELECT {} {} {} {}          \

Changes to test/auth3.test.

49
50
51
52
53
54
55




56
57
58
59
60
61
62
    INSERT INTO t1 VALUES(4, 5, 6);
  }
} {}
do_test auth3.1.2 {
  set ::authcode SQLITE_DENY
  catchsql { DELETE FROM t1 }
} {1 {not authorized}}




do_test auth3.1.3 {
  set ::authcode SQLITE_INVALID
  catchsql { DELETE FROM t1 }
} {1 {authorizer malfunction}}
do_test auth3.1.4 {
  execsql { SELECT * FROM t1 }
} {1 2 3 4 5 6}







>
>
>
>







49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
    INSERT INTO t1 VALUES(4, 5, 6);
  }
} {}
do_test auth3.1.2 {
  set ::authcode SQLITE_DENY
  catchsql { DELETE FROM t1 }
} {1 {not authorized}}
# EVIDENCE-OF: R-64962-58611 If the authorizer callback returns any
# value other than SQLITE_IGNORE, SQLITE_OK, or SQLITE_DENY then the
# sqlite3_prepare_v2() or equivalent call that triggered the authorizer
# will fail with an error message.
do_test auth3.1.3 {
  set ::authcode SQLITE_INVALID
  catchsql { DELETE FROM t1 }
} {1 {authorizer malfunction}}
do_test auth3.1.4 {
  execsql { SELECT * FROM t1 }
} {1 2 3 4 5 6}