/ Check-in [8440f093]
Login

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

Overview
Comment:Non-working preliminary implementation attempts on user authentication.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | user-auth
Files: files | file ages | folders
SHA1: 8440f093bac19a41d44ee352744354eab897fe4e
User & Date: drh 2014-09-09 14:47:53
Context
2014-09-10
17:34
Further ideas on user authentication. Not yet working code. check-in: c8171ecd user: drh tags: user-auth
2014-09-09
14:47
Non-working preliminary implementation attempts on user authentication. check-in: 8440f093 user: drh tags: user-auth
2014-09-06
17:06
Fixes to os_unix.c to support database (and other) files larger than 2GiB on Android. check-in: ad7063aa user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/userauth/user-auth.txt.

            1  +Activate the user authentication logic by compiling SQLite with 
            2  +the -DSQLITE_USER_AUTHENTICATION compile-time option.
            3  +
            4  +The following new APIs are available when user authentication is
            5  +activated:
            6  +
            7  +   int sqlite3_user_authenticate(
            8  +     sqlite3 *db,           /* The database connection */
            9  +     const char *zUsername, /* Username */
           10  +     int nPW,               /* Number of bytes in aPW[] */
           11  +     const void *aPW        /* Password or credentials */
           12  +   );
           13  +   
           14  +   int sqlite3_user_add(
           15  +     sqlite3 *db,           /* Database connection */
           16  +     const char *zUsername, /* Username to be added */
           17  +     int isAdmin,           /* True to give new user admin privilege */
           18  +     int nPW,               /* Number of bytes in aPW[] */
           19  +     const void *aPW        /* Password or credentials */
           20  +   );
           21  +   
           22  +   int sqlite3_user_change(
           23  +     sqlite3 *db,           /* Database connection */
           24  +     const char *zUsername, /* Username to change */
           25  +     int isAdmin,           /* Modified admin privilege for the user */
           26  +     int nPW,               /* Number of bytes in aPW[] */
           27  +     const void *aPW        /* Modified password or credentials */
           28  +   );
           29  +   
           30  +   int sqlite3_user_delete(
           31  +     sqlite3 *db,           /* Database connection */
           32  +     const char *zUsername  /* Username to remove */
           33  +   );
           34  +   
           35  +The sqlite3_open(), sqlite3_open16(), and sqlite3_open_v2() interfaces
           36  +work as before: they open a new database connection.  However, if the
           37  +database being opened requires authentication, then the database
           38  +connection will be unusable until after sqlite3_user_authenticate()
           39  +has been called successfully [1c].  The sqlite3_user_authenticate() call
           40  +will return SQLITE_OK if the authentication credentials are accepted
           41  +and SQLITE_ERROR if not.
           42  +
           43  +Calling sqlite3_user_authenticate() on a no-authentication-required
           44  +database connection is a harmless no-op.  
           45  +
           46  +If the database is encrypted, then sqlite3_key_v2() must be called first,
           47  +with the correct decryption key, prior to invoking sqlite3_user_authenticate().
           48  +
           49  +To recapitulate: When opening an existing unencrypted authentication-
           50  +required database, the call sequence is:
           51  +
           52  +    sqlite3_open_v2()
           53  +    sqlite3_user_authenticate();
           54  +    /* Database is now usable */
           55  +
           56  +To open an existing, encrypted, authentication-required database, the
           57  +call sequence is:
           58  +
           59  +    sqlite3_open_v2();
           60  +    sqlite3_key_v2();
           61  +    sqlite3_user_authenticate();
           62  +    /* Database is now usable */
           63  +
           64  +When opening a no-authentication-required database, the database
           65  +connection is treated as if it was authenticated as an admin user.
           66  +
           67  +When ATTACH-ing new database files to a connection, each newly attached
           68  +database that is an authentication-required database is checked using
           69  +the same username and password as supplied to the main database.  If that
           70  +check fails, then the ATTACH-ed database is unreadable [1g].
           71  +
           72  +The sqlite3_user_add() interface can be used (by an admin user only)
           73  +to create a new user.  When called on a no-authentication-required
           74  +database, this routine converts the database into an authentication-
           75  +required database [3], automatically makes the added user an
           76  +administrator [1b], and logs in the current connection as that user [1a].
           77  +The sqlite3_user_add() interface only works for the "main" database, not
           78  +for any ATTACH-ed databases.  Any call to sqlite3_user_add() by a
           79  +non-admin user results in an error.
           80  +
           81  +Hence, to create a new, unencrypted, authentication-required database,
           82  +the call sequence is:
           83  +
           84  +    sqlite3_open_v2();
           85  +    sqlite3_user_add();
           86  +
           87  +And to create a new, encrypted, authentication-required database, the call
           88  +sequence is:
           89  +
           90  +    sqlite3_open_v2();
           91  +    sqlite3_key_v2();
           92  +    sqlite3_user_add();
           93  +
           94  +The sqlite3_user_delete() interface can be used (by an admin user only)
           95  +to delete a user.  The currently logged-in user cannot be deleted,
           96  +which guarantees that there is always an admin user and hence that
           97  +the database cannot be converted into a no-authentication-required
           98  +database [3].
           99  +
          100  +The sqlite3_user_change() interface can be used to change a users
          101  +login credentials or admin privilege.  Any user can change their own
          102  +login credentials [1b].  Only an admin user can change another users login
          103  +credentials or admin privilege setting.  No user may change their own 
          104  +admin privilege setting.
          105  +
          106  +The sqlite3_set_authorizer() callback is modified to take a 7th parameter
          107  +which is the username of the currently logged in user, or NULL for a
          108  +no-authentication-required database [1d].
          109  +
          110  +-----------------------------------------------------------------------------
          111  +Implementation notes:
          112  +
          113  +An authentication-required database is identified by the presence of a
          114  +new table:
          115  +
          116  +    CREATE TABLE sqlite_user(
          117  +      uname TEXT PRIMARY KEY,
          118  +      isAdmin BOOLEAN,
          119  +      pw BLOB
          120  +    ) WITHOUT ROWID;
          121  +
          122  +This table is inaccessible (unreadable and unwriteable) to non-admin users
          123  +and is read-only for admin users.  However, if the same database file is
          124  +opened by a version of SQLite that omits the -DSQLITE_USER_AUTHENTICATION
          125  +compile-time option, then the sqlite_user table will be readable by
          126  +anybody and writeable by anybody if the "PRAGMA writable_schema=ON"
          127  +statement is run first.
          128  +
          129  +The sqlite_user.pw field is encoded by a built-in SQL function
          130  +"sqlite_crypt(X,Y)".  The two arguments are both BLOBs.  The first argument
          131  +is the plaintext password supplied to the sqlite3_user_authenticate()
          132  +interface.  The second argument is the sqlite_user.pw value and is supplied
          133  +so that the function can extra the "salt" used by the password encoder.
          134  +the result of sqlite_crypt(X,Y) is another blob which is the value that
          135  +ends up being stored in sqlite_user.pw.  To verify credentials X supplied
          136  +by the sqlite3_user_authenticate() routine, SQLite runs:
          137  +
          138  +    sqlite_user.pw == sqlite_crypt(X, sqlite_user.pw)
          139  +
          140  +To compute an appropriate sqlite_user.pw value from a new or modified
          141  +password X, sqlite_crypt(X,NULL) is run.  A new random salt is selected
          142  +when the second argument is NULL.
          143  +
          144  +The built-in version of of sqlite_crypt() uses a simple Ceasar-cypher
          145  +which prevents passwords from being revealed by search the raw database
          146  +for ASCII text, but is otherwise trivally broken.  To truly secure the
          147  +passwords, the database should be encrypted using the SQLite Encryption
          148  +Extension or similar technology.  Or, the application can use the
          149  +sqlite3_create_function() interface to provide an alternative
          150  +implementation of sqlite_crypt() that computes a stronger password hash,
          151  +perhaps using a cryptographic hash function like SHA1.

Added ext/userauth/userauth.c.

            1  +/*
            2  +** 2014-09-08
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +** This file contains the bulk of the implementation of the
           14  +** user-authentication extension feature.  Some parts of the user-
           15  +** authentication code are contained within the SQLite core (in the
           16  +** src/ subdirectory of the main source code tree) but those parts
           17  +** that could reasonable be separated out are moved into this file.
           18  +**
           19  +** To compile with the user-authentication feature, append this file to
           20  +** end of an SQLite amalgamation, then add the SQLITE_USER_AUTHENTICATION
           21  +** compile-time option.  See the user-auth.txt file in the same source
           22  +** directory as this file for additional information.
           23  +*/
           24  +#ifdef SQLITE_USER_AUTHENTICATION
           25  +
           26  +/*
           27  +** Prepare an SQL statement for use by the user authentication logic.
           28  +** Return a pointer to the prepared statement on success.  Return a
           29  +** NULL pointer if there is an error of any kind.
           30  +*/
           31  +static sqlite3_stmt *sqlite3UserAuthPrepare(
           32  +  sqlite3 *db,
           33  +  const char *zFormat,
           34  +  ...
           35  +){
           36  +  sqlite3_stmt *pStmt;
           37  +  char *zSql;
           38  +  int rc;
           39  +  va_list ap;
           40  +
           41  +  va_start(ap, zFormat);
           42  +  zSql = sqlite3_vmprintf(zFormat, ap);
           43  +  va_end(ap);
           44  +  if( zSql==0 ) return 0;
           45  +  savedFlags = db->auth.authFlags;
           46  +  db->auth.authFlags |= UAUTH_Ovrd;
           47  +  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
           48  +  db->auth.authFlags = savedFlags;
           49  +  sqlite3_free(zSql);
           50  +  if( rc ){
           51  +    sqlite3_finalize(pStmt);
           52  +    pStmt = 0;
           53  +  }
           54  +  return pStmt;
           55  +}
           56  +
           57  +/*
           58  +** Check to see if database zDb has a "sqlite_user" table and if it does
           59  +** whether that table can authenticate zUser with nPw,zPw.  
           60  +*/
           61  +static int sqlite3UserAuthCheckLogin(
           62  +  sqlite3 *db,               /* The database connection to check */
           63  +  const char *zDb,           /* Name of specific database to check */
           64  +  const char *zUser,         /* User name */
           65  +  int nPw,                   /* Size of password in bytes */
           66  +  const char *zPw,           /* Password */
           67  +  int *pbOk                  /* OUT: write boolean result here */
           68  +){
           69  +  sqlite3_stmt *pStmt;
           70  +  char *zSql;
           71  +  int rc;
           72  +  int iResult;
           73  +
           74  +  *pbOk = 0;
           75  +  iResult = 0;
           76  +  pStmt = sqlite3UserAuthPrepare(db, 
           77  +              "SELECT 1 FROM \"%w\".sqlite_master "
           78  +              " WHERE name='sqlite_user' AND type='table'", zDb);
           79  +  if( pStmt==0 ) return SQLITE_NOMEM;
           80  +  rc = sqlite3_step(pStmt):
           81  +  sqlite3_finalize(pStmt);
           82  +  if( rc==SQLITE_DONE ){
           83  +    *pbOk = 1;
           84  +    return SQLITE_OK;
           85  +  }
           86  +  if( rc!=SQLITE_OK ){
           87  +    return rc;
           88  +  }
           89  +  pStmt = sqlite3UserAuthPrepare(db,
           90  +            "SELECT pw=sqlite_crypt(?1,pw), isAdmin FROM \"%w\".sqlite_user"
           91  +            " WHERE uname=?2", zDb);
           92  +  if( pStmt==0 ) return SQLITE_NOMEM;
           93  +  sqlite3_bind_blob(pStmt, 1, zPw, nPw, SQLITE_STATIC);
           94  +  sqlite3_bind_text(pStmt, 2, zUser, -1, SQLITE_STATIC);
           95  +  rc = sqlite_step(pStmt);
           96  +  if( rc==SQLITE_ROW && sqlite3_column_int(pStmt,0) ){
           97  +    *pbOk = sqlite3_column_int(pStmt, 1);
           98  +  }
           99  +  sqlite3_finalize(pStmt);
          100  +  return rc;
          101  +}
          102  +
          103  +/*
          104  +** If a database contains the SQLITE_USER table, then the
          105  +** sqlite3_user_authenticate() interface must be invoked with an
          106  +** appropriate username and password prior to enable read and write
          107  +** access to the database.
          108  +**
          109  +** Return SQLITE_OK on success or SQLITE_ERROR if the username/password
          110  +** combination is incorrect or unknown.
          111  +**
          112  +** If the SQLITE_USER table is not present in the database file, then
          113  +** this interface is a harmless no-op returnning SQLITE_OK.
          114  +*/
          115  +int sqlite3_user_authenticate(
          116  +  sqlite3 *db,           /* The database connection */
          117  +  const char *zUsername, /* Username */
          118  +  int nPW,               /* Number of bytes in aPW[] */
          119  +  const void *aPW        /* Password or credentials */
          120  +){
          121  +  int bOk = 0;
          122  +  int rc;
          123  +
          124  +  rc = sqlite3UserAuthCheckLogin(db, zUsername, nPw, zPw, &bOk);
          125  +  if( bOk ){
          126  +    db->auth.authFlags = bOk==2 ? UAUTH_Auth|UAUTH_Admin : UAUTH_Auth;
          127  +    sqlite3_free(db->auth.zAuthUser);
          128  +    db->auth.zAuthUser = sqlite3_malloc("%s", zUsername);
          129  +    sqlite3_free(db->auth.zPw);
          130  +    db->auth.zPw = sqlite3_malloc( nPw+1 );
          131  +    if( db->auth.zPw ){
          132  +      memcpy(db->auth.zPw,zPw,nPw);
          133  +      db->auth.nPw = nPw;
          134  +      rc = SQLITE_OK;
          135  +    }else{
          136  +      rc = SQLITE_NOMEM;
          137  +    }
          138  +  }else{
          139  +    db->auth.authFlags = 0;
          140  +  }
          141  +  return rc;
          142  +}
          143  +
          144  +/*
          145  +** The sqlite3_user_add() interface can be used (by an admin user only)
          146  +** to create a new user.  When called on a no-authentication-required
          147  +** database, this routine converts the database into an authentication-
          148  +** required database, automatically makes the added user an
          149  +** administrator, and logs in the current connection as that user.
          150  +** The sqlite3_user_add() interface only works for the "main" database, not
          151  +** for any ATTACH-ed databases.  Any call to sqlite3_user_add() by a
          152  +** non-admin user results in an error.
          153  +*/
          154  +int sqlite3_user_add(
          155  +  sqlite3 *db,           /* Database connection */
          156  +  const char *zUsername, /* Username to be added */
          157  +  int isAdmin,           /* True to give new user admin privilege */
          158  +  int nPW,               /* Number of bytes in aPW[] */
          159  +  const void *aPW        /* Password or credentials */
          160  +){
          161  +  if( !DbIsAdmin(db) ) return SQLITE_ERROR;
          162  +  
          163  +  return SQLITE_OK;
          164  +}
          165  +
          166  +/*
          167  +** The sqlite3_user_change() interface can be used to change a users
          168  +** login credentials or admin privilege.  Any user can change their own
          169  +** login credentials.  Only an admin user can change another users login
          170  +** credentials or admin privilege setting.  No user may change their own 
          171  +** admin privilege setting.
          172  +*/
          173  +int sqlite3_user_change(
          174  +  sqlite3 *db,           /* Database connection */
          175  +  const char *zUsername, /* Username to change */
          176  +  int isAdmin,           /* Modified admin privilege for the user */
          177  +  int nPW,               /* Number of bytes in aPW[] */
          178  +  const void *aPW        /* Modified password or credentials */
          179  +){
          180  +  return SQLITE_OK;
          181  +}
          182  +
          183  +/*
          184  +** The sqlite3_user_delete() interface can be used (by an admin user only)
          185  +** to delete a user.  The currently logged-in user cannot be deleted,
          186  +** which guarantees that there is always an admin user and hence that
          187  +** the database cannot be converted into a no-authentication-required
          188  +** database.
          189  +*/
          190  +int sqlite3_user_delete(
          191  +  sqlite3 *db,           /* Database connection */
          192  +  const char *zUsername  /* Username to remove */
          193  +){
          194  +  return SQLITE_OK;
          195  +}
          196  +
          197  +#endif /* SQLITE_USER_AUTHENTICATION */

Added ext/userauth/userauth.h.

            1  +/*
            2  +** 2014-09-08
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +** This file contains the application interface definitions for the
           14  +** user-authentication extension feature.
           15  +**
           16  +** To compile with the user-authentication feature, append this file to
           17  +** end of an SQLite amalgamation header file ("sqlite3.h"), then add
           18  +** the SQLITE_USER_AUTHENTICATION compile-time option.  See the
           19  +** user-auth.txt file in the same source directory as this file for
           20  +** additional information.
           21  +*/
           22  +#ifdef SQLITE_USER_AUTHENTICATION
           23  +
           24  +/*
           25  +** If a database contains the SQLITE_USER table, then the
           26  +** sqlite3_user_authenticate() interface must be invoked with an
           27  +** appropriate username and password prior to enable read and write
           28  +** access to the database.
           29  +**
           30  +** Return SQLITE_OK on success or SQLITE_ERROR if the username/password
           31  +** combination is incorrect or unknown.
           32  +**
           33  +** If the SQLITE_USER table is not present in the database file, then
           34  +** this interface is a harmless no-op returnning SQLITE_OK.
           35  +*/
           36  +int sqlite3_user_authenticate(
           37  +  sqlite3 *db,           /* The database connection */
           38  +  const char *zUsername, /* Username */
           39  +  int nPW,               /* Number of bytes in aPW[] */
           40  +  const void *aPW        /* Password or credentials */
           41  +);
           42  +
           43  +/*
           44  +** The sqlite3_user_add() interface can be used (by an admin user only)
           45  +** to create a new user.  When called on a no-authentication-required
           46  +** database, this routine converts the database into an authentication-
           47  +** required database, automatically makes the added user an
           48  +** administrator, and logs in the current connection as that user.
           49  +** The sqlite3_user_add() interface only works for the "main" database, not
           50  +** for any ATTACH-ed databases.  Any call to sqlite3_user_add() by a
           51  +** non-admin user results in an error.
           52  +*/
           53  +int sqlite3_user_add(
           54  +  sqlite3 *db,           /* Database connection */
           55  +  const char *zUsername, /* Username to be added */
           56  +  int isAdmin,           /* True to give new user admin privilege */
           57  +  int nPW,               /* Number of bytes in aPW[] */
           58  +  const void *aPW        /* Password or credentials */
           59  +);
           60  +
           61  +/*
           62  +** The sqlite3_user_change() interface can be used to change a users
           63  +** login credentials or admin privilege.  Any user can change their own
           64  +** login credentials.  Only an admin user can change another users login
           65  +** credentials or admin privilege setting.  No user may change their own 
           66  +** admin privilege setting.
           67  +*/
           68  +int sqlite3_user_change(
           69  +  sqlite3 *db,           /* Database connection */
           70  +  const char *zUsername, /* Username to change */
           71  +  int isAdmin,           /* Modified admin privilege for the user */
           72  +  int nPW,               /* Number of bytes in aPW[] */
           73  +  const void *aPW        /* Modified password or credentials */
           74  +);
           75  +
           76  +/*
           77  +** The sqlite3_user_delete() interface can be used (by an admin user only)
           78  +** to delete a user.  The currently logged-in user cannot be deleted,
           79  +** which guarantees that there is always an admin user and hence that
           80  +** the database cannot be converted into a no-authentication-required
           81  +** database.
           82  +*/
           83  +int sqlite3_user_delete(
           84  +  sqlite3 *db,           /* Database connection */
           85  +  const char *zUsername  /* Username to remove */
           86  +);
           87  +
           88  +#endif /* SQLITE_USER_AUTHENTICATION */

Changes to src/build.c.

   267    267     sqlite3RunParser(pParse, zSql, &zErrMsg);
   268    268     sqlite3DbFree(db, zErrMsg);
   269    269     sqlite3DbFree(db, zSql);
   270    270     memcpy(&pParse->nVar, saveBuf, SAVE_SZ);
   271    271     pParse->nested--;
   272    272   }
   273    273   
          274  +#if SQLITE_USER_AUTHENTICATION
          275  +/*
          276  +** Return TRUE if zTable is the name of the system table that stores the
          277  +** list of users and their access credentials.
          278  +*/
          279  +int sqlite3UserAuthTable(const char *zTable){
          280  +  return sqlite3_stricmp(zTable, "sqlite_user")==0;
          281  +}
          282  +#endif
          283  +
   274    284   /*
   275    285   ** Locate the in-memory structure that describes a particular database
   276    286   ** table given the name of that table and (optionally) the name of the
   277    287   ** database containing the table.  Return NULL if not found.
   278    288   **
   279    289   ** If zDatabase is 0, all databases are searched for the table and the
   280    290   ** first matching table is returned.  (No checking for duplicate table
................................................................................
   285    295   */
   286    296   Table *sqlite3FindTable(sqlite3 *db, const char *zName, const char *zDatabase){
   287    297     Table *p = 0;
   288    298     int i;
   289    299     assert( zName!=0 );
   290    300     /* All mutexes are required for schema access.  Make sure we hold them. */
   291    301     assert( zDatabase!=0 || sqlite3BtreeHoldsAllMutexes(db) );
          302  +#if SQLITE_USER_AUTHENTICATION
          303  +  /* Only the admin user is allowed to know that the sqlite_user table
          304  +  ** exists */
          305  +  if( DbIsAdmin(db)==0 && sqlite3UserAuthTable(zName)!=0 ) return 0;
          306  +#endif
   292    307     for(i=OMIT_TEMPDB; i<db->nDb; i++){
   293    308       int j = (i<2) ? i^1 : i;   /* Search TEMP before MAIN */
   294    309       if( zDatabase!=0 && sqlite3StrICmp(zDatabase, db->aDb[j].zName) ) continue;
   295    310       assert( sqlite3SchemaMutexHeld(db, j, 0) );
   296    311       p = sqlite3HashFind(&db->aDb[j].pSchema->tblHash, zName);
   297    312       if( p ) break;
   298    313     }
................................................................................
  2863   2878       iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  2864   2879     }
  2865   2880     pDb = &db->aDb[iDb];
  2866   2881   
  2867   2882     assert( pTab!=0 );
  2868   2883     assert( pParse->nErr==0 );
  2869   2884     if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 
         2885  +#if SQLITE_USER_AUTHENTICATION
         2886  +       && sqlite3UserAuthTable(pTab->zName)==0
         2887  +#endif
  2870   2888          && sqlite3StrNICmp(&pTab->zName[7],"altertab_",9)!=0 ){
  2871   2889       sqlite3ErrorMsg(pParse, "table %s may not be indexed", pTab->zName);
  2872   2890       goto exit_create_index;
  2873   2891     }
  2874   2892   #ifndef SQLITE_OMIT_VIEW
  2875   2893     if( pTab->pSelect ){
  2876   2894       sqlite3ErrorMsg(pParse, "views may not be indexed");

Changes to src/main.c.

   981    981     }
   982    982     sqlite3HashClear(&db->aModule);
   983    983   #endif
   984    984   
   985    985     sqlite3Error(db, SQLITE_OK); /* Deallocates any cached error strings. */
   986    986     sqlite3ValueFree(db->pErr);
   987    987     sqlite3CloseExtensions(db);
          988  +#if SQLITE_USER_AUTHENTICATION
          989  +  sqlite3_free(db->auth.zAuthUser);
          990  +  sqlite3_free(db->auth.zAuthPW);
          991  +#endif
   988    992   
   989    993     db->magic = SQLITE_MAGIC_ERROR;
   990    994   
   991    995     /* The temp-database schema is allocated differently from the other schema
   992    996     ** objects (using sqliteMalloc() directly, instead of sqlite3BtreeSchema()).
   993    997     ** So it needs to be freed here. Todo: Why not roll the temp schema into
   994    998     ** the same sqliteMalloc() as the one that allocates the database 
................................................................................
  2562   2566       }
  2563   2567       sqlite3Error(db, rc);
  2564   2568       goto opendb_out;
  2565   2569     }
  2566   2570     db->aDb[0].pSchema = sqlite3SchemaGet(db, db->aDb[0].pBt);
  2567   2571     db->aDb[1].pSchema = sqlite3SchemaGet(db, 0);
  2568   2572   
         2573  +#if SQLITE_USER_AUTHENTICATION
         2574  +  db->auth.authFlags = UAUTH_Auth|UAUTH_Admin;
         2575  +#endif
  2569   2576   
  2570   2577     /* The default safety_level for the main database is 'full'; for the temp
  2571   2578     ** database it is 'NONE'. This matches the pager layer defaults.  
  2572   2579     */
  2573   2580     db->aDb[0].zName = "main";
  2574   2581     db->aDb[0].safety_level = 3;
  2575   2582     db->aDb[1].zName = "temp";

Changes to src/pragma.c.

  1393   1393       }else{
  1394   1394         int mask = aPragmaNames[mid].iArg;    /* Mask of bits to set or clear. */
  1395   1395         if( db->autoCommit==0 ){
  1396   1396           /* Foreign key support may not be enabled or disabled while not
  1397   1397           ** in auto-commit mode.  */
  1398   1398           mask &= ~(SQLITE_ForeignKeys);
  1399   1399         }
         1400  +#if SQLITE_USER_AUTHENTICATION
         1401  +      if( !DbIsAdmin(db) ){
         1402  +        /* Do not allow non-admin users to modify the schema arbitrarily */
         1403  +        mask &= ~(SQLITE_WriteSchema);
         1404  +      }
         1405  +#endif
  1400   1406   
  1401   1407         if( sqlite3GetBoolean(zRight, 0) ){
  1402   1408           db->flags |= mask;
  1403   1409         }else{
  1404   1410           db->flags &= ~mask;
  1405   1411           if( mask==SQLITE_DeferFKs ) db->nDeferredImmCons = 0;
  1406   1412         }

Changes to src/prepare.c.

   202    202       rc = initData.rc;
   203    203       goto error_out;
   204    204     }
   205    205     pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName);
   206    206     if( ALWAYS(pTab) ){
   207    207       pTab->tabFlags |= TF_Readonly;
   208    208     }
          209  +#if SQLITE_USER_AUTHENTICATION
          210  +  db->auth.authFlags = UAUTH_Auth|UAUTH_Admin;
          211  +#endif
   209    212   
   210    213     /* Create a cursor to hold the database open
   211    214     */
   212    215     pDb = &db->aDb[iDb];
   213    216     if( pDb->pBt==0 ){
   214    217       if( !OMIT_TEMPDB && ALWAYS(iDb==1) ){
   215    218         DbSetProperty(db, 1, DB_SchemaLoaded);
................................................................................
   357    360       ** of the schema was loaded before the error occurred. The primary
   358    361       ** purpose of this is to allow access to the sqlite_master table
   359    362       ** even when its contents have been corrupted.
   360    363       */
   361    364       DbSetProperty(db, iDb, DB_SchemaLoaded);
   362    365       rc = SQLITE_OK;
   363    366     }
          367  +#if SQLITE_USER_AUTHENTICATION
          368  +  if( rc==SQLITE_OK && iDb!=1 ){
          369  +    if( sqlite3FindTable(db, "sqlite_user", db->aDb[iDb].zName)!=0 ){
          370  +      db->auth.authFlags = UAUTH_AuthReqd;
          371  +    }
          372  +  }
          373  +#endif
          374  +
   364    375   
   365    376     /* Jump here for an error that occurs after successfully allocating
   366    377     ** curMain and calling sqlite3BtreeEnter(). For an error that occurs
   367    378     ** before that point, jump to error_out.
   368    379     */
   369    380   initone_error_out:
   370    381     if( openedTransaction ){
................................................................................
   716    727     sqlite3_mutex_enter(db->mutex);
   717    728     sqlite3BtreeEnterAll(db);
   718    729     rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
   719    730     if( rc==SQLITE_SCHEMA ){
   720    731       sqlite3_finalize(*ppStmt);
   721    732       rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
   722    733     }
          734  +#if SQLITE_USER_AUTHENTICATION
          735  +  assert( rc==SQLITE_OK || *ppStmt==0 );
          736  +printf("rc=%d init=%d auth=%d sql=[%.50s]\n", rc, db->init.busy, db->auth.authFlags, zSql);
          737  +fflush(stdout);
          738  +  if( rc==SQLITE_OK && !DbIsAuth(db) && db->init.busy==0 ){
          739  +    sqlite3_finalize(*ppStmt);
          740  +    *ppStmt = 0;
          741  +    sqlite3ErrorWithMsg(db, SQLITE_ERROR, "user not authenticated");
          742  +    rc = SQLITE_ERROR;
          743  +  }
          744  +#endif
   723    745     sqlite3BtreeLeaveAll(db);
   724    746     sqlite3_mutex_leave(db->mutex);
   725    747     assert( rc==SQLITE_OK || *ppStmt==0 );
   726    748     return rc;
   727    749   }
   728    750   
   729    751   /*

Changes to src/sqliteInt.h.

   983    983   **
   984    984   ** Hash each FuncDef structure into one of the FuncDefHash.a[] slots.
   985    985   ** Collisions are on the FuncDef.pHash chain.
   986    986   */
   987    987   struct FuncDefHash {
   988    988     FuncDef *a[23];       /* Hash table for functions */
   989    989   };
          990  +
          991  +#ifdef SQLITE_USER_AUTHENTICATION
          992  +/*
          993  +** Information held in the "sqlite3" database connection object and used
          994  +** to manage user authentication.
          995  +*/
          996  +typedef struct sqlite3_userauth sqlite3_userauth;
          997  +struct sqlite3_userauth {
          998  +  u8 authFlags;                 /* Status flags for user authentication */
          999  +  int nAuthPW;                  /* Size of the zAuthPW in bytes */
         1000  +  char *zAuthPW;                /* Password used to authenticate */
         1001  +  char *zAuthUser;              /* User name used to authenticate */
         1002  +};
         1003  +
         1004  +/* Allowed values for sqlite3_userauth.authFlags */
         1005  +#define UAUTH_Ovrd        0x01  /* Do not enforce access restrictions */
         1006  +#define UAUTH_Auth        0x02  /* True if the user has authenticated */
         1007  +#define UAUTH_Admin       0x04  /* True if the user is an administrator */
         1008  +#define UAUTH_AuthReqd    0x08  /* True if main has an sqlite_user table */
         1009  +
         1010  +/* Macros for accessing sqlite3.auth.authFlags */
         1011  +#define DbIsAuth(D)       (((D)->auth.authFlags&UAUTH_Auth)!=0)
         1012  +#define DbIsAdmin(D)      (((D)->auth.authFlags&UAUTH_Admin)!=0)
         1013  +
         1014  +/* Functions used only by user authorization logic */
         1015  +int sqlite3UserAuthTable(const char*);
         1016  +
         1017  +#endif /* SQLITE_USER_AUTHENTICATION */
         1018  +
   990   1019   
   991   1020   /*
   992   1021   ** Each database connection is an instance of the following structure.
   993   1022   */
   994   1023   struct sqlite3 {
   995   1024     sqlite3_vfs *pVfs;            /* OS Interface */
   996   1025     struct Vdbe *pVdbe;           /* List of active virtual machines */
................................................................................
  1078   1107     Savepoint *pSavepoint;        /* List of active savepoints */
  1079   1108     int busyTimeout;              /* Busy handler timeout, in msec */
  1080   1109     int nSavepoint;               /* Number of non-transaction savepoints */
  1081   1110     int nStatement;               /* Number of nested statement-transactions  */
  1082   1111     i64 nDeferredCons;            /* Net deferred constraints this transaction. */
  1083   1112     i64 nDeferredImmCons;         /* Net deferred immediate constraints */
  1084   1113     int *pnBytesFreed;            /* If not NULL, increment this in DbFree() */
  1085         -
  1086   1114   #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
  1087   1115     /* The following variables are all protected by the STATIC_MASTER 
  1088   1116     ** mutex, not by sqlite3.mutex. They are used by code in notify.c. 
  1089   1117     **
  1090   1118     ** When X.pUnlockConnection==Y, that means that X is waiting for Y to
  1091   1119     ** unlock so that it can proceed.
  1092   1120     **
................................................................................
  1096   1124     */
  1097   1125     sqlite3 *pBlockingConnection; /* Connection that caused SQLITE_LOCKED */
  1098   1126     sqlite3 *pUnlockConnection;           /* Connection to watch for unlock */
  1099   1127     void *pUnlockArg;                     /* Argument to xUnlockNotify */
  1100   1128     void (*xUnlockNotify)(void **, int);  /* Unlock notify callback */
  1101   1129     sqlite3 *pNextBlocked;        /* Next in list of all blocked connections */
  1102   1130   #endif
         1131  +#ifdef SQLITE_USER_AUTHENTICATION
         1132  +  sqlite3_userauth auth;        /* User authentication information */
         1133  +#endif
  1103   1134   };
  1104   1135   
  1105   1136   /*
  1106   1137   ** A macro to discover the encoding of a database.
  1107   1138   */
  1108   1139   #define ENC(db) ((db)->aDb[0].pSchema->enc)
  1109   1140