/ Check-in [4eaaa7fa]
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 | SQL archive
Timelines: family | ancestors | descendants | both | user-auth
Files: files | file ages | folders
SHA1:4eaaa7fa87aa912d24f8b35440ab60310dc08310
User & Date: drh 2014-09-10 22:46:46
Context
2014-09-11
00:27
Reorder parameters on the sqlite3_user_*() interfaces for consistency. Add the first TCL test cases. check-in: 2f6d8f32 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: 4eaaa7fa 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: a0455f9d user: drh tags: user-auth
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/userauth/userauth.c.

    35     35     const char *zFormat,
    36     36     ...
    37     37   ){
    38     38     sqlite3_stmt *pStmt;
    39     39     char *zSql;
    40     40     int rc;
    41     41     va_list ap;
           42  +  int savedFlags = db->flags;
    42     43   
    43     44     va_start(ap, zFormat);
    44     45     zSql = sqlite3_vmprintf(zFormat, ap);
    45     46     va_end(ap);
    46     47     if( zSql==0 ) return 0;
           48  +  db->flags |= SQLITE_WriteSchema;
    47     49     rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
           50  +  db->flags = savedFlags;
    48     51     sqlite3_free(zSql);
    49     52     if( rc ){
    50     53       sqlite3_finalize(pStmt);
    51     54       pStmt = 0;
    52     55     }
    53     56     return pStmt;
    54     57   }
................................................................................
    56     59   /*
    57     60   ** Check to see if the sqlite_user table exists in database zDb.
    58     61   */
    59     62   static int userTableExists(sqlite3 *db, const char *zDb){
    60     63     int rc;
    61     64     sqlite3_mutex_enter(db->mutex);
    62     65     sqlite3BtreeEnterAll(db);
           66  +  if( db->init.busy==0 ){
           67  +    char *zErr = 0;
           68  +    sqlite3Init(db, &zErr);
           69  +    sqlite3DbFree(db, zErr);
           70  +  }
    63     71     rc = sqlite3FindTable(db, "sqlite_user", zDb)!=0;
    64     72     sqlite3BtreeLeaveAll(db);
    65     73     sqlite3_mutex_leave(db->mutex);
    66     74     return rc;
    67     75   }
    68     76   
    69     77   /*
................................................................................
    79     87   ){
    80     88     sqlite3_stmt *pStmt;
    81     89     int rc;
    82     90   
    83     91     *peAuth = UAUTH_Unknown;
    84     92     if( !userTableExists(db, "main") ){
    85     93       *peAuth = UAUTH_Admin;  /* No sqlite_user table.  Everybody is admin. */
           94  +    return SQLITE_OK;
    86     95     }
    87     96     if( db->auth.zAuthUser==0 ){
    88     97       *peAuth = UAUTH_Fail;
    89     98       return SQLITE_OK;
    90     99     }
    91    100     pStmt = sqlite3UserAuthPrepare(db,
    92    101               "SELECT pw=sqlite_crypt(?1,pw), isAdmin FROM \"%w\".sqlite_user"
................................................................................
    96    105     sqlite3_bind_text(pStmt, 2, db->auth.zAuthUser, -1, SQLITE_STATIC);
    97    106     rc = sqlite3_step(pStmt);
    98    107     if( rc==SQLITE_ROW && sqlite3_column_int(pStmt,0) ){
    99    108       *peAuth = sqlite3_column_int(pStmt, 1) + UAUTH_User;
   100    109     }else{
   101    110       *peAuth = UAUTH_Fail;
   102    111     }
   103         -  sqlite3_finalize(pStmt);
   104         -  return rc;
          112  +  return sqlite3_finalize(pStmt);
   105    113   }
   106    114   int sqlite3UserAuthCheckLogin(
   107    115     sqlite3 *db,               /* The database connection to check */
   108    116     const char *zDb,           /* Name of specific database to check */
   109    117     u8 *peAuth                 /* OUT: One of UAUTH_* constants */
   110    118   ){
   111    119     int rc;
................................................................................
   226    234                 ") WITHOUT ROWID;");
   227    235       if( pStmt==0 ) return SQLITE_NOMEM;
   228    236       sqlite3_step(pStmt);
   229    237       rc = sqlite3_finalize(pStmt);
   230    238       if( rc ) return rc;
   231    239     }
   232    240     pStmt = sqlite3UserAuthPrepare(db, 
   233         -            "INSERT INTO sqlite_user(uname,isAdmin,sqlite_crypt(pw,NULL))"
   234         -            " VALUES(%Q,%d,?1)",
          241  +            "INSERT INTO sqlite_user(uname,isAdmin,pw)"
          242  +            " VALUES(%Q,%d,sqlite_crypt(?1,NULL))",
   235    243               zUsername, isAdmin!=0);
   236    244     if( pStmt==0 ) return SQLITE_NOMEM;
   237    245     sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC);
   238    246     sqlite3_step(pStmt);
   239    247     rc = sqlite3_finalize(pStmt);
   240    248     if( rc ) return rc;
   241    249     if( db->auth.zAuthUser==0 ){
................................................................................
   255    263   int sqlite3_user_change(
   256    264     sqlite3 *db,           /* Database connection */
   257    265     const char *zUsername, /* Username to change */
   258    266     int isAdmin,           /* Modified admin privilege for the user */
   259    267     int nPW,               /* Number of bytes in aPW[] */
   260    268     const char *aPW        /* Modified password or credentials */
   261    269   ){
   262         -  if( db->auth.authLevel<UAUTH_User ) return SQLITE_AUTH;
   263         -  if( strcmp(db->auth.zAuthUser, zUsername)!=0
   264         -       && db->auth.authLevel<UAUTH_Admin ) return SQLITE_AUTH;
   265         -  return SQLITE_OK;
          270  +  sqlite3_stmt *pStmt;
          271  +  if( db->auth.authLevel<UAUTH_User ){
          272  +    /* Must be logged in to make a change */
          273  +    return SQLITE_AUTH;
          274  +  }
          275  +  if( strcmp(db->auth.zAuthUser, zUsername)!=0 ){
          276  +    if( db->auth.authLevel<UAUTH_Admin ){
          277  +      /* Must be an administrator to change a different user */
          278  +      return SQLITE_AUTH;
          279  +    }
          280  +  }else if( isAdmin!=(db->auth.authLevel==UAUTH_Admin) ){
          281  +    /* Cannot change the isAdmin setting for self */
          282  +    return SQLITE_AUTH;
          283  +  }
          284  +  if( !userTableExists(db, "main") ){
          285  +    /* This routine is a no-op if the user to be modified does not exist */
          286  +    return SQLITE_OK;
          287  +  }
          288  +  pStmt = sqlite3UserAuthPrepare(db,
          289  +            "UPDATE sqlite_user SET isAdmin=%d, pw=sqlite_crypt(?1,NULL)"
          290  +            " WHERE uname=%Q", isAdmin, zUsername);
          291  +  if( pStmt==0 ) return SQLITE_NOMEM;
          292  +  sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC);
          293  +  sqlite3_step(pStmt);
          294  +  return sqlite3_finalize(pStmt);
   266    295   }
   267    296   
   268    297   /*
   269    298   ** The sqlite3_user_delete() interface can be used (by an admin user only)
   270    299   ** to delete a user.  The currently logged-in user cannot be deleted,
   271    300   ** which guarantees that there is always an admin user and hence that
   272    301   ** the database cannot be converted into a no-authentication-required
   273    302   ** database.
   274    303   */
   275    304   int sqlite3_user_delete(
   276    305     sqlite3 *db,           /* Database connection */
   277    306     const char *zUsername  /* Username to remove */
   278    307   ){
   279         -  if( db->auth.authLevel<UAUTH_Admin ) return SQLITE_AUTH;
   280         -  if( strcmp(db->auth.zAuthUser, zUsername)==0 ) return SQLITE_AUTH;
   281         -  return SQLITE_OK;
          308  +  sqlite3_stmt *pStmt;
          309  +  if( db->auth.authLevel<UAUTH_Admin ){
          310  +    /* Must be an administrator to delete a user */
          311  +    return SQLITE_AUTH;
          312  +  }
          313  +  if( strcmp(db->auth.zAuthUser, zUsername)==0 ){
          314  +    /* Cannot delete self */
          315  +    return SQLITE_AUTH;
          316  +  }
          317  +  if( !userTableExists(db, "main") ){
          318  +    /* This routine is a no-op if the user to be deleted does not exist */
          319  +    return SQLITE_OK;
          320  +  }
          321  +  pStmt = sqlite3UserAuthPrepare(db,
          322  +              "SELECT FROM sqlite_user WHERE uname=%Q", zUsername);
          323  +  if( pStmt==0 ) return SQLITE_NOMEM;
          324  +  sqlite3_step(pStmt);
          325  +  return sqlite3_finalize(pStmt);
   282    326   }
   283    327   
   284    328   #endif /* SQLITE_USER_AUTHENTICATION */

Changes to src/ctime.c.

   363    363     "TEST",
   364    364   #endif
   365    365   #if defined(SQLITE_THREADSAFE)
   366    366     "THREADSAFE=" CTIMEOPT_VAL(SQLITE_THREADSAFE),
   367    367   #endif
   368    368   #ifdef SQLITE_USE_ALLOCA
   369    369     "USE_ALLOCA",
          370  +#endif
          371  +#ifdef SQLITE_USER_AUTHENTICATION
          372  +  "USER_AUTHENTICATION",
   370    373   #endif
   371    374   #ifdef SQLITE_WIN32_MALLOC
   372    375     "WIN32_MALLOC",
   373    376   #endif
   374    377   #ifdef SQLITE_ZERO_MALLOC
   375    378     "ZERO_MALLOC"
   376    379   #endif

Changes to src/legacy.c.

   121    121     }
   122    122   
   123    123   exec_out:
   124    124     if( pStmt ) sqlite3VdbeFinalize((Vdbe *)pStmt);
   125    125     sqlite3DbFree(db, azCols);
   126    126   
   127    127     rc = sqlite3ApiExit(db, rc);
   128         -  if( rc!=SQLITE_OK && ALWAYS(rc==sqlite3_errcode(db)) && pzErrMsg ){
          128  +  if( rc!=SQLITE_OK && pzErrMsg ){
   129    129       int nErrMsg = 1 + sqlite3Strlen30(sqlite3_errmsg(db));
   130    130       *pzErrMsg = sqlite3Malloc(nErrMsg);
   131    131       if( *pzErrMsg ){
   132    132         memcpy(*pzErrMsg, sqlite3_errmsg(db), nErrMsg);
   133    133       }else{
   134    134         rc = SQLITE_NOMEM;
   135    135         sqlite3Error(db, SQLITE_NOMEM);

Changes to src/prepare.c.

   390    390   ** file was of zero-length, then the DB_Empty flag is also set.
   391    391   */
   392    392   int sqlite3Init(sqlite3 *db, char **pzErrMsg){
   393    393     int i, rc;
   394    394     int commit_internal = !(db->flags&SQLITE_InternChanges);
   395    395     
   396    396     assert( sqlite3_mutex_held(db->mutex) );
          397  +  assert( db->init.busy==0 );
   397    398     rc = SQLITE_OK;
   398    399     db->init.busy = 1;
   399    400     for(i=0; rc==SQLITE_OK && i<db->nDb; i++){
   400    401       if( DbHasProperty(db, i, DB_SchemaLoaded) || i==1 ) continue;
   401    402       rc = sqlite3InitOne(db, i, pzErrMsg);
   402    403       if( rc ){
   403    404         sqlite3ResetOneSchema(db, i);

Changes to src/test_config.c.

   598    598   #endif
   599    599   
   600    600   #ifdef SQLITE_SECURE_DELETE
   601    601     Tcl_SetVar2(interp, "sqlite_options", "secure_delete", "1", TCL_GLOBAL_ONLY);
   602    602   #else
   603    603     Tcl_SetVar2(interp, "sqlite_options", "secure_delete", "0", TCL_GLOBAL_ONLY);
   604    604   #endif
          605  +
          606  +#ifdef SQLITE_USER_AUTHENTICATION
          607  +  Tcl_SetVar2(interp, "sqlite_options", "userauth", "1", TCL_GLOBAL_ONLY);
          608  +#else
          609  +  Tcl_SetVar2(interp, "sqlite_options", "userauth", "0", TCL_GLOBAL_ONLY);
          610  +#endif
   605    611   
   606    612   #ifdef SQLITE_MULTIPLEX_EXT_OVWR
   607    613     Tcl_SetVar2(interp, "sqlite_options", "multiplex_ext_overwrite", "1", TCL_GLOBAL_ONLY);
   608    614   #else
   609    615     Tcl_SetVar2(interp, "sqlite_options", "multiplex_ext_overwrite", "0", TCL_GLOBAL_ONLY);
   610    616   #endif
   611    617