/ 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 Unified Diffs Show Whitespace Changes Patch

Added ext/userauth/user-auth.txt.















































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
Activate the user authentication logic by compiling SQLite with 
the -DSQLITE_USER_AUTHENTICATION compile-time option.

The following new APIs are available when user authentication is
activated:

   int sqlite3_user_authenticate(
     sqlite3 *db,           /* The database connection */
     const char *zUsername, /* Username */
     int nPW,               /* Number of bytes in aPW[] */
     const void *aPW        /* Password or credentials */
   );
   
   int sqlite3_user_add(
     sqlite3 *db,           /* Database connection */
     const char *zUsername, /* Username to be added */
     int isAdmin,           /* True to give new user admin privilege */
     int nPW,               /* Number of bytes in aPW[] */
     const void *aPW        /* Password or credentials */
   );
   
   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 void *aPW        /* Modified password or credentials */
   );
   
   int sqlite3_user_delete(
     sqlite3 *db,           /* Database connection */
     const char *zUsername  /* Username to remove */
   );
   
The sqlite3_open(), sqlite3_open16(), and sqlite3_open_v2() interfaces
work as before: they open a new database connection.  However, if the
database being opened requires authentication, then the database
connection will be unusable until after sqlite3_user_authenticate()
has been called successfully [1c].  The sqlite3_user_authenticate() call
will return SQLITE_OK if the authentication credentials are accepted
and SQLITE_ERROR if not.

Calling sqlite3_user_authenticate() on a no-authentication-required
database connection is a harmless no-op.  

If the database is encrypted, then sqlite3_key_v2() must be called first,
with the correct decryption key, prior to invoking sqlite3_user_authenticate().

To recapitulate: When opening an existing unencrypted authentication-
required database, the call sequence is:

    sqlite3_open_v2()
    sqlite3_user_authenticate();
    /* Database is now usable */

To open an existing, encrypted, authentication-required database, the
call sequence is:

    sqlite3_open_v2();
    sqlite3_key_v2();
    sqlite3_user_authenticate();
    /* Database is now usable */

When opening a no-authentication-required database, the database
connection is treated as if it was authenticated as an admin user.

When ATTACH-ing new database files to a connection, each newly attached
database that is an authentication-required database is checked using
the same username and password as supplied to the main database.  If that
check fails, then the ATTACH-ed database is unreadable [1g].

The sqlite3_user_add() interface can be used (by an admin user only)
to create a new user.  When called on a no-authentication-required
database, this routine converts the database into an authentication-
required database [3], automatically makes the added user an
administrator [1b], and logs in the current connection as that user [1a].
The sqlite3_user_add() interface only works for the "main" database, not
for any ATTACH-ed databases.  Any call to sqlite3_user_add() by a
non-admin user results in an error.

Hence, to create a new, unencrypted, authentication-required database,
the call sequence is:

    sqlite3_open_v2();
    sqlite3_user_add();

And to create a new, encrypted, authentication-required database, the call
sequence is:

    sqlite3_open_v2();
    sqlite3_key_v2();
    sqlite3_user_add();

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 [3].

The sqlite3_user_change() interface can be used to change a users
login credentials or admin privilege.  Any user can change their own
login credentials [1b].  Only an admin user can change another users login
credentials or admin privilege setting.  No user may change their own 
admin privilege setting.

The sqlite3_set_authorizer() callback is modified to take a 7th parameter
which is the username of the currently logged in user, or NULL for a
no-authentication-required database [1d].

-----------------------------------------------------------------------------
Implementation notes:

An authentication-required database is identified by the presence of a
new table:

    CREATE TABLE sqlite_user(
      uname TEXT PRIMARY KEY,
      isAdmin BOOLEAN,
      pw BLOB
    ) WITHOUT ROWID;

This table is inaccessible (unreadable and unwriteable) to non-admin users
and is read-only for admin users.  However, if the same database file is
opened by a version of SQLite that omits the -DSQLITE_USER_AUTHENTICATION
compile-time option, then the sqlite_user table will be readable by
anybody and writeable by anybody if the "PRAGMA writable_schema=ON"
statement is run first.

The sqlite_user.pw field is encoded by a built-in SQL function
"sqlite_crypt(X,Y)".  The two arguments are both BLOBs.  The first argument
is the plaintext password supplied to the sqlite3_user_authenticate()
interface.  The second argument is the sqlite_user.pw value and is supplied
so that the function can extra the "salt" used by the password encoder.
the result of sqlite_crypt(X,Y) is another blob which is the value that
ends up being stored in sqlite_user.pw.  To verify credentials X supplied
by the sqlite3_user_authenticate() routine, SQLite runs:

    sqlite_user.pw == sqlite_crypt(X, sqlite_user.pw)

To compute an appropriate sqlite_user.pw value from a new or modified
password X, sqlite_crypt(X,NULL) is run.  A new random salt is selected
when the second argument is NULL.

The built-in version of of sqlite_crypt() uses a simple Ceasar-cypher
which prevents passwords from being revealed by search the raw database
for ASCII text, but is otherwise trivally broken.  To truly secure the
passwords, the database should be encrypted using the SQLite Encryption
Extension or similar technology.  Or, the application can use the
sqlite3_create_function() interface to provide an alternative
implementation of sqlite_crypt() that computes a stronger password hash,
perhaps using a cryptographic hash function like SHA1.

Added ext/userauth/userauth.c.











































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
/*
** 2014-09-08
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
**
** This file contains the bulk of the implementation of the
** user-authentication extension feature.  Some parts of the user-
** authentication code are contained within the SQLite core (in the
** src/ subdirectory of the main source code tree) but those parts
** that could reasonable be separated out are moved into this file.
**
** To compile with the user-authentication feature, append this file to
** end of an SQLite amalgamation, then add the SQLITE_USER_AUTHENTICATION
** compile-time option.  See the user-auth.txt file in the same source
** directory as this file for additional information.
*/
#ifdef SQLITE_USER_AUTHENTICATION

/*
** Prepare an SQL statement for use by the user authentication logic.
** Return a pointer to the prepared statement on success.  Return a
** NULL pointer if there is an error of any kind.
*/
static sqlite3_stmt *sqlite3UserAuthPrepare(
  sqlite3 *db,
  const char *zFormat,
  ...
){
  sqlite3_stmt *pStmt;
  char *zSql;
  int rc;
  va_list ap;

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

/*
** Check to see if database zDb has a "sqlite_user" table and if it does
** whether that table can authenticate zUser with nPw,zPw.  
*/
static int sqlite3UserAuthCheckLogin(
  sqlite3 *db,               /* The database connection to check */
  const char *zDb,           /* Name of specific database to check */
  const char *zUser,         /* User name */
  int nPw,                   /* Size of password in bytes */
  const char *zPw,           /* Password */
  int *pbOk                  /* OUT: write boolean result here */
){
  sqlite3_stmt *pStmt;
  char *zSql;
  int rc;
  int iResult;

  *pbOk = 0;
  iResult = 0;
  pStmt = sqlite3UserAuthPrepare(db, 
              "SELECT 1 FROM \"%w\".sqlite_master "
              " WHERE name='sqlite_user' AND type='table'", zDb);
  if( pStmt==0 ) return SQLITE_NOMEM;
  rc = sqlite3_step(pStmt):
  sqlite3_finalize(pStmt);
  if( rc==SQLITE_DONE ){
    *pbOk = 1;
    return SQLITE_OK;
  }
  if( rc!=SQLITE_OK ){
    return rc;
  }
  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, zPw, nPw, SQLITE_STATIC);
  sqlite3_bind_text(pStmt, 2, zUser, -1, SQLITE_STATIC);
  rc = sqlite_step(pStmt);
  if( rc==SQLITE_ROW && sqlite3_column_int(pStmt,0) ){
    *pbOk = sqlite3_column_int(pStmt, 1);
  }
  sqlite3_finalize(pStmt);
  return rc;
}

/*
** If a database contains the SQLITE_USER table, then the
** sqlite3_user_authenticate() interface must be invoked with an
** appropriate username and password prior to enable read and write
** access to the database.
**
** Return SQLITE_OK on success or SQLITE_ERROR if the username/password
** combination is incorrect or unknown.
**
** If the SQLITE_USER table is not present in the database file, then
** this interface is a harmless no-op returnning SQLITE_OK.
*/
int sqlite3_user_authenticate(
  sqlite3 *db,           /* The database connection */
  const char *zUsername, /* Username */
  int nPW,               /* Number of bytes in aPW[] */
  const void *aPW        /* Password or credentials */
){
  int bOk = 0;
  int rc;

  rc = sqlite3UserAuthCheckLogin(db, zUsername, nPw, zPw, &bOk);
  if( bOk ){
    db->auth.authFlags = bOk==2 ? UAUTH_Auth|UAUTH_Admin : UAUTH_Auth;
    sqlite3_free(db->auth.zAuthUser);
    db->auth.zAuthUser = sqlite3_malloc("%s", zUsername);
    sqlite3_free(db->auth.zPw);
    db->auth.zPw = sqlite3_malloc( nPw+1 );
    if( db->auth.zPw ){
      memcpy(db->auth.zPw,zPw,nPw);
      db->auth.nPw = nPw;
      rc = SQLITE_OK;
    }else{
      rc = SQLITE_NOMEM;
    }
  }else{
    db->auth.authFlags = 0;
  }
  return rc;
}

/*
** The sqlite3_user_add() interface can be used (by an admin user only)
** to create a new user.  When called on a no-authentication-required
** database, this routine converts the database into an authentication-
** required database, automatically makes the added user an
** administrator, and logs in the current connection as that user.
** The sqlite3_user_add() interface only works for the "main" database, not
** for any ATTACH-ed databases.  Any call to sqlite3_user_add() by a
** non-admin user results in an error.
*/
int sqlite3_user_add(
  sqlite3 *db,           /* Database connection */
  const char *zUsername, /* Username to be added */
  int isAdmin,           /* True to give new user admin privilege */
  int nPW,               /* Number of bytes in aPW[] */
  const void *aPW        /* Password or credentials */
){
  if( !DbIsAdmin(db) ) return SQLITE_ERROR;
  
  return SQLITE_OK;
}

/*
** The sqlite3_user_change() interface can be used to change a users
** login credentials or admin privilege.  Any user can change their own
** login credentials.  Only an admin user can change another users login
** credentials or admin privilege setting.  No user may change their own 
** admin privilege setting.
*/
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 void *aPW        /* Modified password or credentials */
){
  return SQLITE_OK;
}

/*
** 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 */
){
  return SQLITE_OK;
}

#endif /* SQLITE_USER_AUTHENTICATION */

Added ext/userauth/userauth.h.

















































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
/*
** 2014-09-08
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
**
** This file contains the application interface definitions for the
** user-authentication extension feature.
**
** To compile with the user-authentication feature, append this file to
** end of an SQLite amalgamation header file ("sqlite3.h"), then add
** the SQLITE_USER_AUTHENTICATION compile-time option.  See the
** user-auth.txt file in the same source directory as this file for
** additional information.
*/
#ifdef SQLITE_USER_AUTHENTICATION

/*
** If a database contains the SQLITE_USER table, then the
** sqlite3_user_authenticate() interface must be invoked with an
** appropriate username and password prior to enable read and write
** access to the database.
**
** Return SQLITE_OK on success or SQLITE_ERROR if the username/password
** combination is incorrect or unknown.
**
** If the SQLITE_USER table is not present in the database file, then
** this interface is a harmless no-op returnning SQLITE_OK.
*/
int sqlite3_user_authenticate(
  sqlite3 *db,           /* The database connection */
  const char *zUsername, /* Username */
  int nPW,               /* Number of bytes in aPW[] */
  const void *aPW        /* Password or credentials */
);

/*
** The sqlite3_user_add() interface can be used (by an admin user only)
** to create a new user.  When called on a no-authentication-required
** database, this routine converts the database into an authentication-
** required database, automatically makes the added user an
** administrator, and logs in the current connection as that user.
** The sqlite3_user_add() interface only works for the "main" database, not
** for any ATTACH-ed databases.  Any call to sqlite3_user_add() by a
** non-admin user results in an error.
*/
int sqlite3_user_add(
  sqlite3 *db,           /* Database connection */
  const char *zUsername, /* Username to be added */
  int isAdmin,           /* True to give new user admin privilege */
  int nPW,               /* Number of bytes in aPW[] */
  const void *aPW        /* Password or credentials */
);

/*
** The sqlite3_user_change() interface can be used to change a users
** login credentials or admin privilege.  Any user can change their own
** login credentials.  Only an admin user can change another users login
** credentials or admin privilege setting.  No user may change their own 
** admin privilege setting.
*/
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 void *aPW        /* Modified password or credentials */
);

/*
** 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 */
);

#endif /* SQLITE_USER_AUTHENTICATION */

Changes to src/build.c.

267
268
269
270
271
272
273










274
275
276
277
278
279
280
...
285
286
287
288
289
290
291





292
293
294
295
296
297
298
....
2863
2864
2865
2866
2867
2868
2869



2870
2871
2872
2873
2874
2875
2876
  sqlite3RunParser(pParse, zSql, &zErrMsg);
  sqlite3DbFree(db, zErrMsg);
  sqlite3DbFree(db, zSql);
  memcpy(&pParse->nVar, saveBuf, SAVE_SZ);
  pParse->nested--;
}











/*
** Locate the in-memory structure that describes a particular database
** table given the name of that table and (optionally) the name of the
** database containing the table.  Return NULL if not found.
**
** If zDatabase is 0, all databases are searched for the table and the
** first matching table is returned.  (No checking for duplicate table
................................................................................
*/
Table *sqlite3FindTable(sqlite3 *db, const char *zName, const char *zDatabase){
  Table *p = 0;
  int i;
  assert( zName!=0 );
  /* All mutexes are required for schema access.  Make sure we hold them. */
  assert( zDatabase!=0 || sqlite3BtreeHoldsAllMutexes(db) );





  for(i=OMIT_TEMPDB; i<db->nDb; i++){
    int j = (i<2) ? i^1 : i;   /* Search TEMP before MAIN */
    if( zDatabase!=0 && sqlite3StrICmp(zDatabase, db->aDb[j].zName) ) continue;
    assert( sqlite3SchemaMutexHeld(db, j, 0) );
    p = sqlite3HashFind(&db->aDb[j].pSchema->tblHash, zName);
    if( p ) break;
  }
................................................................................
    iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  }
  pDb = &db->aDb[iDb];

  assert( pTab!=0 );
  assert( pParse->nErr==0 );
  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 



       && sqlite3StrNICmp(&pTab->zName[7],"altertab_",9)!=0 ){
    sqlite3ErrorMsg(pParse, "table %s may not be indexed", pTab->zName);
    goto exit_create_index;
  }
#ifndef SQLITE_OMIT_VIEW
  if( pTab->pSelect ){
    sqlite3ErrorMsg(pParse, "views may not be indexed");







>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>







 







>
>
>







267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
...
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
....
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
  sqlite3RunParser(pParse, zSql, &zErrMsg);
  sqlite3DbFree(db, zErrMsg);
  sqlite3DbFree(db, zSql);
  memcpy(&pParse->nVar, saveBuf, SAVE_SZ);
  pParse->nested--;
}

#if SQLITE_USER_AUTHENTICATION
/*
** Return TRUE if zTable is the name of the system table that stores the
** list of users and their access credentials.
*/
int sqlite3UserAuthTable(const char *zTable){
  return sqlite3_stricmp(zTable, "sqlite_user")==0;
}
#endif

/*
** Locate the in-memory structure that describes a particular database
** table given the name of that table and (optionally) the name of the
** database containing the table.  Return NULL if not found.
**
** If zDatabase is 0, all databases are searched for the table and the
** first matching table is returned.  (No checking for duplicate table
................................................................................
*/
Table *sqlite3FindTable(sqlite3 *db, const char *zName, const char *zDatabase){
  Table *p = 0;
  int i;
  assert( zName!=0 );
  /* All mutexes are required for schema access.  Make sure we hold them. */
  assert( zDatabase!=0 || sqlite3BtreeHoldsAllMutexes(db) );
#if SQLITE_USER_AUTHENTICATION
  /* Only the admin user is allowed to know that the sqlite_user table
  ** exists */
  if( DbIsAdmin(db)==0 && sqlite3UserAuthTable(zName)!=0 ) return 0;
#endif
  for(i=OMIT_TEMPDB; i<db->nDb; i++){
    int j = (i<2) ? i^1 : i;   /* Search TEMP before MAIN */
    if( zDatabase!=0 && sqlite3StrICmp(zDatabase, db->aDb[j].zName) ) continue;
    assert( sqlite3SchemaMutexHeld(db, j, 0) );
    p = sqlite3HashFind(&db->aDb[j].pSchema->tblHash, zName);
    if( p ) break;
  }
................................................................................
    iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  }
  pDb = &db->aDb[iDb];

  assert( pTab!=0 );
  assert( pParse->nErr==0 );
  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 
#if SQLITE_USER_AUTHENTICATION
       && sqlite3UserAuthTable(pTab->zName)==0
#endif
       && sqlite3StrNICmp(&pTab->zName[7],"altertab_",9)!=0 ){
    sqlite3ErrorMsg(pParse, "table %s may not be indexed", pTab->zName);
    goto exit_create_index;
  }
#ifndef SQLITE_OMIT_VIEW
  if( pTab->pSelect ){
    sqlite3ErrorMsg(pParse, "views may not be indexed");

Changes to src/main.c.

981
982
983
984
985
986
987




988
989
990
991
992
993
994
....
2562
2563
2564
2565
2566
2567
2568



2569
2570
2571
2572
2573
2574
2575
  }
  sqlite3HashClear(&db->aModule);
#endif

  sqlite3Error(db, SQLITE_OK); /* Deallocates any cached error strings. */
  sqlite3ValueFree(db->pErr);
  sqlite3CloseExtensions(db);





  db->magic = SQLITE_MAGIC_ERROR;

  /* The temp-database schema is allocated differently from the other schema
  ** objects (using sqliteMalloc() directly, instead of sqlite3BtreeSchema()).
  ** So it needs to be freed here. Todo: Why not roll the temp schema into
  ** the same sqliteMalloc() as the one that allocates the database 
................................................................................
    }
    sqlite3Error(db, rc);
    goto opendb_out;
  }
  db->aDb[0].pSchema = sqlite3SchemaGet(db, db->aDb[0].pBt);
  db->aDb[1].pSchema = sqlite3SchemaGet(db, 0);





  /* The default safety_level for the main database is 'full'; for the temp
  ** database it is 'NONE'. This matches the pager layer defaults.  
  */
  db->aDb[0].zName = "main";
  db->aDb[0].safety_level = 3;
  db->aDb[1].zName = "temp";







>
>
>
>







 







>
>
>







981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
....
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
  }
  sqlite3HashClear(&db->aModule);
#endif

  sqlite3Error(db, SQLITE_OK); /* Deallocates any cached error strings. */
  sqlite3ValueFree(db->pErr);
  sqlite3CloseExtensions(db);
#if SQLITE_USER_AUTHENTICATION
  sqlite3_free(db->auth.zAuthUser);
  sqlite3_free(db->auth.zAuthPW);
#endif

  db->magic = SQLITE_MAGIC_ERROR;

  /* The temp-database schema is allocated differently from the other schema
  ** objects (using sqliteMalloc() directly, instead of sqlite3BtreeSchema()).
  ** So it needs to be freed here. Todo: Why not roll the temp schema into
  ** the same sqliteMalloc() as the one that allocates the database 
................................................................................
    }
    sqlite3Error(db, rc);
    goto opendb_out;
  }
  db->aDb[0].pSchema = sqlite3SchemaGet(db, db->aDb[0].pBt);
  db->aDb[1].pSchema = sqlite3SchemaGet(db, 0);

#if SQLITE_USER_AUTHENTICATION
  db->auth.authFlags = UAUTH_Auth|UAUTH_Admin;
#endif

  /* The default safety_level for the main database is 'full'; for the temp
  ** database it is 'NONE'. This matches the pager layer defaults.  
  */
  db->aDb[0].zName = "main";
  db->aDb[0].safety_level = 3;
  db->aDb[1].zName = "temp";

Changes to src/pragma.c.

1393
1394
1395
1396
1397
1398
1399






1400
1401
1402
1403
1404
1405
1406
    }else{
      int mask = aPragmaNames[mid].iArg;    /* Mask of bits to set or clear. */
      if( db->autoCommit==0 ){
        /* Foreign key support may not be enabled or disabled while not
        ** in auto-commit mode.  */
        mask &= ~(SQLITE_ForeignKeys);
      }







      if( sqlite3GetBoolean(zRight, 0) ){
        db->flags |= mask;
      }else{
        db->flags &= ~mask;
        if( mask==SQLITE_DeferFKs ) db->nDeferredImmCons = 0;
      }







>
>
>
>
>
>







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

      if( sqlite3GetBoolean(zRight, 0) ){
        db->flags |= mask;
      }else{
        db->flags &= ~mask;
        if( mask==SQLITE_DeferFKs ) db->nDeferredImmCons = 0;
      }

Changes to src/prepare.c.

202
203
204
205
206
207
208



209
210
211
212
213
214
215
...
357
358
359
360
361
362
363








364
365
366
367
368
369
370
...
716
717
718
719
720
721
722











723
724
725
726
727
728
729
    rc = initData.rc;
    goto error_out;
  }
  pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName);
  if( ALWAYS(pTab) ){
    pTab->tabFlags |= TF_Readonly;
  }




  /* Create a cursor to hold the database open
  */
  pDb = &db->aDb[iDb];
  if( pDb->pBt==0 ){
    if( !OMIT_TEMPDB && ALWAYS(iDb==1) ){
      DbSetProperty(db, 1, DB_SchemaLoaded);
................................................................................
    ** of the schema was loaded before the error occurred. The primary
    ** purpose of this is to allow access to the sqlite_master table
    ** even when its contents have been corrupted.
    */
    DbSetProperty(db, iDb, DB_SchemaLoaded);
    rc = SQLITE_OK;
  }









  /* Jump here for an error that occurs after successfully allocating
  ** curMain and calling sqlite3BtreeEnter(). For an error that occurs
  ** before that point, jump to error_out.
  */
initone_error_out:
  if( openedTransaction ){
................................................................................
  sqlite3_mutex_enter(db->mutex);
  sqlite3BtreeEnterAll(db);
  rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
  if( rc==SQLITE_SCHEMA ){
    sqlite3_finalize(*ppStmt);
    rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
  }











  sqlite3BtreeLeaveAll(db);
  sqlite3_mutex_leave(db->mutex);
  assert( rc==SQLITE_OK || *ppStmt==0 );
  return rc;
}

/*







>
>
>







 







>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>







202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
...
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
...
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
    rc = initData.rc;
    goto error_out;
  }
  pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName);
  if( ALWAYS(pTab) ){
    pTab->tabFlags |= TF_Readonly;
  }
#if SQLITE_USER_AUTHENTICATION
  db->auth.authFlags = UAUTH_Auth|UAUTH_Admin;
#endif

  /* Create a cursor to hold the database open
  */
  pDb = &db->aDb[iDb];
  if( pDb->pBt==0 ){
    if( !OMIT_TEMPDB && ALWAYS(iDb==1) ){
      DbSetProperty(db, 1, DB_SchemaLoaded);
................................................................................
    ** of the schema was loaded before the error occurred. The primary
    ** purpose of this is to allow access to the sqlite_master table
    ** even when its contents have been corrupted.
    */
    DbSetProperty(db, iDb, DB_SchemaLoaded);
    rc = SQLITE_OK;
  }
#if SQLITE_USER_AUTHENTICATION
  if( rc==SQLITE_OK && iDb!=1 ){
    if( sqlite3FindTable(db, "sqlite_user", db->aDb[iDb].zName)!=0 ){
      db->auth.authFlags = UAUTH_AuthReqd;
    }
  }
#endif


  /* Jump here for an error that occurs after successfully allocating
  ** curMain and calling sqlite3BtreeEnter(). For an error that occurs
  ** before that point, jump to error_out.
  */
initone_error_out:
  if( openedTransaction ){
................................................................................
  sqlite3_mutex_enter(db->mutex);
  sqlite3BtreeEnterAll(db);
  rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
  if( rc==SQLITE_SCHEMA ){
    sqlite3_finalize(*ppStmt);
    rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
  }
#if SQLITE_USER_AUTHENTICATION
  assert( rc==SQLITE_OK || *ppStmt==0 );
printf("rc=%d init=%d auth=%d sql=[%.50s]\n", rc, db->init.busy, db->auth.authFlags, zSql);
fflush(stdout);
  if( rc==SQLITE_OK && !DbIsAuth(db) && db->init.busy==0 ){
    sqlite3_finalize(*ppStmt);
    *ppStmt = 0;
    sqlite3ErrorWithMsg(db, SQLITE_ERROR, "user not authenticated");
    rc = SQLITE_ERROR;
  }
#endif
  sqlite3BtreeLeaveAll(db);
  sqlite3_mutex_leave(db->mutex);
  assert( rc==SQLITE_OK || *ppStmt==0 );
  return rc;
}

/*

Changes to src/sqliteInt.h.

983
984
985
986
987
988
989





























990
991
992
993
994
995
996
....
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
....
1096
1097
1098
1099
1100
1101
1102



1103
1104
1105
1106
1107
1108
1109
**
** Hash each FuncDef structure into one of the FuncDefHash.a[] slots.
** Collisions are on the FuncDef.pHash chain.
*/
struct FuncDefHash {
  FuncDef *a[23];       /* Hash table for functions */
};






























/*
** Each database connection is an instance of the following structure.
*/
struct sqlite3 {
  sqlite3_vfs *pVfs;            /* OS Interface */
  struct Vdbe *pVdbe;           /* List of active virtual machines */
................................................................................
  Savepoint *pSavepoint;        /* List of active savepoints */
  int busyTimeout;              /* Busy handler timeout, in msec */
  int nSavepoint;               /* Number of non-transaction savepoints */
  int nStatement;               /* Number of nested statement-transactions  */
  i64 nDeferredCons;            /* Net deferred constraints this transaction. */
  i64 nDeferredImmCons;         /* Net deferred immediate constraints */
  int *pnBytesFreed;            /* If not NULL, increment this in DbFree() */

#ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
  /* The following variables are all protected by the STATIC_MASTER 
  ** mutex, not by sqlite3.mutex. They are used by code in notify.c. 
  **
  ** When X.pUnlockConnection==Y, that means that X is waiting for Y to
  ** unlock so that it can proceed.
  **
................................................................................
  */
  sqlite3 *pBlockingConnection; /* Connection that caused SQLITE_LOCKED */
  sqlite3 *pUnlockConnection;           /* Connection to watch for unlock */
  void *pUnlockArg;                     /* Argument to xUnlockNotify */
  void (*xUnlockNotify)(void **, int);  /* Unlock notify callback */
  sqlite3 *pNextBlocked;        /* Next in list of all blocked connections */
#endif



};

/*
** A macro to discover the encoding of a database.
*/
#define ENC(db) ((db)->aDb[0].pSchema->enc)








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







 







<







 







>
>
>







983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
....
1107
1108
1109
1110
1111
1112
1113

1114
1115
1116
1117
1118
1119
1120
....
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
**
** Hash each FuncDef structure into one of the FuncDefHash.a[] slots.
** Collisions are on the FuncDef.pHash chain.
*/
struct FuncDefHash {
  FuncDef *a[23];       /* Hash table for functions */
};

#ifdef SQLITE_USER_AUTHENTICATION
/*
** Information held in the "sqlite3" database connection object and used
** to manage user authentication.
*/
typedef struct sqlite3_userauth sqlite3_userauth;
struct sqlite3_userauth {
  u8 authFlags;                 /* Status flags for user authentication */
  int nAuthPW;                  /* Size of the zAuthPW in bytes */
  char *zAuthPW;                /* Password used to authenticate */
  char *zAuthUser;              /* User name used to authenticate */
};

/* Allowed values for sqlite3_userauth.authFlags */
#define UAUTH_Ovrd        0x01  /* Do not enforce access restrictions */
#define UAUTH_Auth        0x02  /* True if the user has authenticated */
#define UAUTH_Admin       0x04  /* True if the user is an administrator */
#define UAUTH_AuthReqd    0x08  /* True if main has an sqlite_user table */

/* Macros for accessing sqlite3.auth.authFlags */
#define DbIsAuth(D)       (((D)->auth.authFlags&UAUTH_Auth)!=0)
#define DbIsAdmin(D)      (((D)->auth.authFlags&UAUTH_Admin)!=0)

/* Functions used only by user authorization logic */
int sqlite3UserAuthTable(const char*);

#endif /* SQLITE_USER_AUTHENTICATION */


/*
** Each database connection is an instance of the following structure.
*/
struct sqlite3 {
  sqlite3_vfs *pVfs;            /* OS Interface */
  struct Vdbe *pVdbe;           /* List of active virtual machines */
................................................................................
  Savepoint *pSavepoint;        /* List of active savepoints */
  int busyTimeout;              /* Busy handler timeout, in msec */
  int nSavepoint;               /* Number of non-transaction savepoints */
  int nStatement;               /* Number of nested statement-transactions  */
  i64 nDeferredCons;            /* Net deferred constraints this transaction. */
  i64 nDeferredImmCons;         /* Net deferred immediate constraints */
  int *pnBytesFreed;            /* If not NULL, increment this in DbFree() */

#ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
  /* The following variables are all protected by the STATIC_MASTER 
  ** mutex, not by sqlite3.mutex. They are used by code in notify.c. 
  **
  ** When X.pUnlockConnection==Y, that means that X is waiting for Y to
  ** unlock so that it can proceed.
  **
................................................................................
  */
  sqlite3 *pBlockingConnection; /* Connection that caused SQLITE_LOCKED */
  sqlite3 *pUnlockConnection;           /* Connection to watch for unlock */
  void *pUnlockArg;                     /* Argument to xUnlockNotify */
  void (*xUnlockNotify)(void **, int);  /* Unlock notify callback */
  sqlite3 *pNextBlocked;        /* Next in list of all blocked connections */
#endif
#ifdef SQLITE_USER_AUTHENTICATION
  sqlite3_userauth auth;        /* User authentication information */
#endif
};

/*
** A macro to discover the encoding of a database.
*/
#define ENC(db) ((db)->aDb[0].pSchema->enc)