SQLite

Check-in [4eaaa7fa87]
Login

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

Overview
Comment:Complete the implementation of the various APIs. Fix several problems. This is another incremental check-in that does not completely work.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | user-auth
Files: files | file ages | folders
SHA1: 4eaaa7fa87aa912d24f8b35440ab60310dc08310
User & Date: drh 2014-09-10 22:46:46.526
Context
2014-09-11
00:27
Reorder parameters on the sqlite3_user_*() interfaces for consistency. Add the first TCL test cases. (check-in: 2f6d8f32ee user: drh tags: user-auth)
2014-09-10
22:46
Complete the implementation of the various APIs. Fix several problems. This is another incremental check-in that does not completely work. (check-in: 4eaaa7fa87 user: drh tags: user-auth)
19:01
Add the ".user" shell command and implement the sqlite3_user_add() routine. Incremental check-in. The code compiles but does not work. (check-in: a0455f9deb user: drh tags: user-auth)
Changes
Side-by-Side Diff Show Whitespace Changes Patch
Changes to ext/userauth/userauth.c.
︙
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
60
61
62





63
64
65
66
67
68
69

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77








+





+

+















+
+
+
+
+








  const char *zFormat,
  ...
){
  sqlite3_stmt *pStmt;
  char *zSql;
  int rc;
  va_list ap;
  int savedFlags = db->flags;

  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);
  if( zSql==0 ) return 0;
  db->flags |= SQLITE_WriteSchema;
  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  db->flags = savedFlags;
  sqlite3_free(zSql);
  if( rc ){
    sqlite3_finalize(pStmt);
    pStmt = 0;
  }
  return pStmt;
}

/*
** Check to see if the sqlite_user table exists in database zDb.
*/
static int userTableExists(sqlite3 *db, const char *zDb){
  int rc;
  sqlite3_mutex_enter(db->mutex);
  sqlite3BtreeEnterAll(db);
  if( db->init.busy==0 ){
    char *zErr = 0;
    sqlite3Init(db, &zErr);
    sqlite3DbFree(db, zErr);
  }
  rc = sqlite3FindTable(db, "sqlite_user", zDb)!=0;
  sqlite3BtreeLeaveAll(db);
  sqlite3_mutex_leave(db->mutex);
  return rc;
}

/*
︙
79
80
81
82
83
84
85

86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103

104
105
106
107
108
109
110
111

87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111

112

113
114
115
116
117
118
119








+

















-
+
-








){
  sqlite3_stmt *pStmt;
  int rc;

  *peAuth = UAUTH_Unknown;
  if( !userTableExists(db, "main") ){
    *peAuth = UAUTH_Admin;  /* No sqlite_user table.  Everybody is admin. */
    return SQLITE_OK;
  }
  if( db->auth.zAuthUser==0 ){
    *peAuth = UAUTH_Fail;
    return SQLITE_OK;
  }
  pStmt = sqlite3UserAuthPrepare(db,
            "SELECT pw=sqlite_crypt(?1,pw), isAdmin FROM \"%w\".sqlite_user"
            " WHERE uname=?2", zDb);
  if( pStmt==0 ) return SQLITE_NOMEM;
  sqlite3_bind_blob(pStmt, 1, db->auth.zAuthPW, db->auth.nAuthPW,SQLITE_STATIC);
  sqlite3_bind_text(pStmt, 2, db->auth.zAuthUser, -1, SQLITE_STATIC);
  rc = sqlite3_step(pStmt);
  if( rc==SQLITE_ROW && sqlite3_column_int(pStmt,0) ){
    *peAuth = sqlite3_column_int(pStmt, 1) + UAUTH_User;
  }else{
    *peAuth = UAUTH_Fail;
  }
  sqlite3_finalize(pStmt);
  return sqlite3_finalize(pStmt);
  return rc;
}
int sqlite3UserAuthCheckLogin(
  sqlite3 *db,               /* The database connection to check */
  const char *zDb,           /* Name of specific database to check */
  u8 *peAuth                 /* OUT: One of UAUTH_* constants */
){
  int rc;
︙
226
227
228
229
230
231
232
233
234


235
236
237
238
239
240
241

234
235
236
237
238
239
240


241
242
243
244
245
246
247
248
249








-
-
+
+








              ") WITHOUT ROWID;");
    if( pStmt==0 ) return SQLITE_NOMEM;
    sqlite3_step(pStmt);
    rc = sqlite3_finalize(pStmt);
    if( rc ) return rc;
  }
  pStmt = sqlite3UserAuthPrepare(db, 
            "INSERT INTO sqlite_user(uname,isAdmin,sqlite_crypt(pw,NULL))"
            " VALUES(%Q,%d,?1)",
            "INSERT INTO sqlite_user(uname,isAdmin,pw)"
            " VALUES(%Q,%d,sqlite_crypt(?1,NULL))",
            zUsername, isAdmin!=0);
  if( pStmt==0 ) return SQLITE_NOMEM;
  sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC);
  sqlite3_step(pStmt);
  rc = sqlite3_finalize(pStmt);
  if( rc ) return rc;
  if( db->auth.zAuthUser==0 ){
︙
255
256
257
258
259
260
261

262
263
264















265








266
267
268
269
270
271
272
273
274
275
276
277
278

279
280










281






282
283
284

263
264
265
266
267
268
269
270



271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308


309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328








+
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+

+
+
+
+
+
+
+
+













+
-
-
+
+
+
+
+
+
+
+
+
+

+
+
+
+
+
+




int sqlite3_user_change(
  sqlite3 *db,           /* Database connection */
  const char *zUsername, /* Username to change */
  int isAdmin,           /* Modified admin privilege for the user */
  int nPW,               /* Number of bytes in aPW[] */
  const char *aPW        /* Modified password or credentials */
){
  sqlite3_stmt *pStmt;
  if( db->auth.authLevel<UAUTH_User ) return SQLITE_AUTH;
  if( strcmp(db->auth.zAuthUser, zUsername)!=0
       && db->auth.authLevel<UAUTH_Admin ) return SQLITE_AUTH;
  if( db->auth.authLevel<UAUTH_User ){
    /* Must be logged in to make a change */
    return SQLITE_AUTH;
  }
  if( strcmp(db->auth.zAuthUser, zUsername)!=0 ){
    if( db->auth.authLevel<UAUTH_Admin ){
      /* Must be an administrator to change a different user */
      return SQLITE_AUTH;
    }
  }else if( isAdmin!=(db->auth.authLevel==UAUTH_Admin) ){
    /* Cannot change the isAdmin setting for self */
    return SQLITE_AUTH;
  }
  if( !userTableExists(db, "main") ){
    /* This routine is a no-op if the user to be modified does not exist */
  return SQLITE_OK;
  }
  pStmt = sqlite3UserAuthPrepare(db,
            "UPDATE sqlite_user SET isAdmin=%d, pw=sqlite_crypt(?1,NULL)"
            " WHERE uname=%Q", isAdmin, zUsername);
  if( pStmt==0 ) return SQLITE_NOMEM;
  sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC);
  sqlite3_step(pStmt);
  return sqlite3_finalize(pStmt);
}

/*
** The sqlite3_user_delete() interface can be used (by an admin user only)
** to delete a user.  The currently logged-in user cannot be deleted,
** which guarantees that there is always an admin user and hence that
** the database cannot be converted into a no-authentication-required
** database.
*/
int sqlite3_user_delete(
  sqlite3 *db,           /* Database connection */
  const char *zUsername  /* Username to remove */
){
  sqlite3_stmt *pStmt;
  if( db->auth.authLevel<UAUTH_Admin ) return SQLITE_AUTH;
  if( strcmp(db->auth.zAuthUser, zUsername)==0 ) return SQLITE_AUTH;
  if( db->auth.authLevel<UAUTH_Admin ){
    /* Must be an administrator to delete a user */
    return SQLITE_AUTH;
  }
  if( strcmp(db->auth.zAuthUser, zUsername)==0 ){
    /* Cannot delete self */
    return SQLITE_AUTH;
  }
  if( !userTableExists(db, "main") ){
    /* This routine is a no-op if the user to be deleted does not exist */
  return SQLITE_OK;
  }
  pStmt = sqlite3UserAuthPrepare(db,
              "SELECT FROM sqlite_user WHERE uname=%Q", zUsername);
  if( pStmt==0 ) return SQLITE_NOMEM;
  sqlite3_step(pStmt);
  return sqlite3_finalize(pStmt);
}

#endif /* SQLITE_USER_AUTHENTICATION */
Changes to src/ctime.c.
︙
363
364
365
366
367
368
369



370
371
372
373
374
375
376

363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379








+
+
+








  "TEST",
#endif
#if defined(SQLITE_THREADSAFE)
  "THREADSAFE=" CTIMEOPT_VAL(SQLITE_THREADSAFE),
#endif
#ifdef SQLITE_USE_ALLOCA
  "USE_ALLOCA",
#endif
#ifdef SQLITE_USER_AUTHENTICATION
  "USER_AUTHENTICATION",
#endif
#ifdef SQLITE_WIN32_MALLOC
  "WIN32_MALLOC",
#endif
#ifdef SQLITE_ZERO_MALLOC
  "ZERO_MALLOC"
#endif
︙
Changes to src/legacy.c.
︙
121
122
123
124
125
126
127
128

129
130
131
132
133
134
135

121
122
123
124
125
126
127

128
129
130
131
132
133
134
135








-
+








  }

exec_out:
  if( pStmt ) sqlite3VdbeFinalize((Vdbe *)pStmt);
  sqlite3DbFree(db, azCols);

  rc = sqlite3ApiExit(db, rc);
  if( rc!=SQLITE_OK && ALWAYS(rc==sqlite3_errcode(db)) && pzErrMsg ){
  if( rc!=SQLITE_OK && pzErrMsg ){
    int nErrMsg = 1 + sqlite3Strlen30(sqlite3_errmsg(db));
    *pzErrMsg = sqlite3Malloc(nErrMsg);
    if( *pzErrMsg ){
      memcpy(*pzErrMsg, sqlite3_errmsg(db), nErrMsg);
    }else{
      rc = SQLITE_NOMEM;
      sqlite3Error(db, SQLITE_NOMEM);
︙
Changes to src/prepare.c.
︙
390
391
392
393
394
395
396

397
398
399
400
401
402
403

390
391
392
393
394
395
396
397
398
399
400
401
402
403
404








+








** file was of zero-length, then the DB_Empty flag is also set.
*/
int sqlite3Init(sqlite3 *db, char **pzErrMsg){
  int i, rc;
  int commit_internal = !(db->flags&SQLITE_InternChanges);
  
  assert( sqlite3_mutex_held(db->mutex) );
  assert( db->init.busy==0 );
  rc = SQLITE_OK;
  db->init.busy = 1;
  for(i=0; rc==SQLITE_OK && i<db->nDb; i++){
    if( DbHasProperty(db, i, DB_SchemaLoaded) || i==1 ) continue;
    rc = sqlite3InitOne(db, i, pzErrMsg);
    if( rc ){
      sqlite3ResetOneSchema(db, i);
︙
Changes to src/test_config.c.
︙
598
599
600
601
602
603
604






605
606
607
608
609
610
611

598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617








+
+
+
+
+
+








#endif

#ifdef SQLITE_SECURE_DELETE
  Tcl_SetVar2(interp, "sqlite_options", "secure_delete", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "secure_delete", "0", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_USER_AUTHENTICATION
  Tcl_SetVar2(interp, "sqlite_options", "userauth", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "userauth", "0", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_MULTIPLEX_EXT_OVWR
  Tcl_SetVar2(interp, "sqlite_options", "multiplex_ext_overwrite", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "multiplex_ext_overwrite", "0", TCL_GLOBAL_ONLY);
#endif
︙