Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the ".user" shell command and implement the sqlite3_user_add() routine. Incremental check-in. The code compiles but does not work. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | user-auth |
Files: | files | file ages | folders |
SHA1: |
a0455f9deb603bf91684158d91126962 |
User & Date: | drh 2014-09-10 19:01:14.206 |
Context
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) | |
17:34 | Further ideas on user authentication. Not yet working code. (check-in: c8171ecd0d user: drh tags: user-auth) | |
Changes
Name change from ext/userauth/userauth.h to ext/userauth/sqlite3userauth.h.
︙ | |||
33 34 35 36 37 38 39 | 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 | - + - + - + | ** 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[] */ |
︙ |
Changes to ext/userauth/userauth.c.
︙ | |||
19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | + | ** 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 #include "sqliteInt.h" #include "sqlite3userauth.h" /* ** 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( |
︙ | |||
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | 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 | + + + + + + + + + + + + + - - - - + - - - - - - - - - | 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); rc = sqlite3FindTable(db, "sqlite_user", zDb)!=0; sqlite3BtreeLeaveAll(db); sqlite3_mutex_leave(db->mutex); return rc; } /* ** Check to see if database zDb has a "sqlite_user" table and if it does ** whether that table can authenticate zUser with nPw,zPw. Write one of ** the UAUTH_* user authorization level codes into *peAuth and return a ** result code. */ static int userAuthCheckLogin( sqlite3 *db, /* The database connection to check */ const char *zDb, /* Name of specific database to check */ u8 *peAuth /* OUT: One of UAUTH_* constants */ ){ sqlite3_stmt *pStmt; int rc; *peAuth = UAUTH_Unknown; |
︙ | |||
111 112 113 114 115 116 117 118 119 120 121 122 123 124 | 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 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | savedAuthLevel = db->auth.authLevel; db->auth.authLevel = UAUTH_Admin; rc = userAuthCheckLogin(db, zDb, peAuth); db->auth.authLevel = savedAuthLevel; return rc; } /* ** Implementation of the sqlite_crypt(X,Y) function. ** ** If Y is NULL then generate a new hash for password X and return that ** hash. If Y is not null, then generate a hash for password X using the ** same salt as the previous hash Y and return the new hash. */ void sqlite3CryptFunc( sqlite3_context *context, int NotUsed, sqlite3_value **argv ){ const char *zIn; int nIn, ii; u8 *zOut; char zSalt[8]; zIn = sqlite3_value_blob(argv[0]); nIn = sqlite3_value_bytes(argv[0]); if( sqlite3_value_type(argv[1])==SQLITE_BLOB && sqlite3_value_bytes(argv[1])==nIn+sizeof(zSalt) ){ memcpy(zSalt, sqlite3_value_blob(argv[1]), sizeof(zSalt)); }else{ sqlite3_randomness(sizeof(zSalt), zSalt); } zOut = sqlite3_malloc( nIn+sizeof(zSalt) ); if( zOut==0 ){ sqlite3_result_error_nomem(context); }else{ memcpy(zOut, zSalt, sizeof(zSalt)); for(ii=0; ii<nIn; ii++){ zOut[ii+sizeof(zSalt)] = zIn[ii]^zSalt[ii&0x7]; } sqlite3_result_blob(context, zOut, nIn+sizeof(zSalt), sqlite3_free); } } /* ** 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. ** |
︙ | |||
144 145 146 147 148 149 150 151 152 153 154 155 156 157 | 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 | + | if( db->auth.zAuthUser==0 ) return SQLITE_NOMEM; db->auth.zAuthPW = sqlite3_malloc( nPW+1 ); if( db->auth.zAuthPW==0 ) return SQLITE_NOMEM; memcpy(db->auth.zAuthPW,zPW,nPW); db->auth.nAuthPW = nPW; rc = sqlite3UserAuthCheckLogin(db, "main", &authLevel); db->auth.authLevel = authLevel; sqlite3ExpirePreparedStatements(db); if( rc ){ return rc; /* OOM error, I/O error, etc. */ } if( authLevel<UAUTH_User ){ return SQLITE_AUTH; /* Incorrect username and/or password */ } return SQLITE_OK; /* Successful login */ |
︙ | |||
168 169 170 171 172 173 174 | 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 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 | - + + + - + + + + + + + + + + + + + + + + + + + + + + + + + + + - + - + - + - + + | ** 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[] */ |
Changes to main.mk.
︙ | |||
42 43 44 45 46 47 48 | 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | - + | # build the SQLite library and testing tools. ################################################################################ # This is how we compile # TCCX = $(TCC) $(OPTS) -I. -I$(TOP)/src -I$(TOP) TCCX += -I$(TOP)/ext/rtree -I$(TOP)/ext/icu -I$(TOP)/ext/fts3 |
︙ | |||
212 213 214 215 216 217 218 | 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 | - + | $(TOP)/ext/icu/icu.c SRC += \ $(TOP)/ext/rtree/sqlite3rtree.h \ $(TOP)/ext/rtree/rtree.h \ $(TOP)/ext/rtree/rtree.c SRC += \ $(TOP)/ext/userauth/userauth.c \ |
︙ | |||
376 377 378 379 380 381 382 | 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 | - + | $(TOP)/ext/fts3/fts3_hash.h \ $(TOP)/ext/fts3/fts3_tokenizer.h EXTHDR += \ $(TOP)/ext/rtree/rtree.h EXTHDR += \ $(TOP)/ext/icu/sqliteicu.h EXTHDR += \ |
︙ |
Changes to src/func.c.
︙ | |||
1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 | 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 | + + + | FUNCTION2(likely, 1, 0, 0, noopFunc, SQLITE_FUNC_UNLIKELY), VFUNCTION(random, 0, 0, 0, randomFunc ), VFUNCTION(randomblob, 1, 0, 0, randomBlob ), FUNCTION(nullif, 2, 0, 1, nullifFunc ), FUNCTION(sqlite_version, 0, 0, 0, versionFunc ), FUNCTION(sqlite_source_id, 0, 0, 0, sourceidFunc ), FUNCTION(sqlite_log, 2, 0, 0, errlogFunc ), #if SQLITE_USER_AUTHENTICATION FUNCTION(sqlite_crypt, 2, 0, 0, sqlite3CryptFunc ), #endif #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS FUNCTION(sqlite_compileoption_used,1, 0, 0, compileoptionusedFunc ), FUNCTION(sqlite_compileoption_get, 1, 0, 0, compileoptiongetFunc ), #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ FUNCTION(quote, 1, 0, 0, quoteFunc ), VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid), VFUNCTION(changes, 0, 0, 0, changes ), |
︙ |
Changes to src/prepare.c.
︙ | |||
718 719 720 721 722 723 724 | 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 | - + | if( db->auth.authLevel<UAUTH_User ){ if( db->auth.authLevel==UAUTH_Unknown ){ u8 authLevel = UAUTH_Fail; sqlite3UserAuthCheckLogin(db, "main", &authLevel); db->auth.authLevel = authLevel; } if( db->auth.authLevel<UAUTH_User ){ |
︙ |
Changes to src/shell.c.
︙ | |||
29 30 31 32 33 34 35 36 37 38 39 40 41 42 | 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | + + + | #endif #include <stdlib.h> #include <string.h> #include <stdio.h> #include <assert.h> #include "sqlite3.h" #if SQLITE_USER_AUTHENTICATION # include "sqlite3userauth.h" #endif #include <ctype.h> #include <stdarg.h> #if !defined(_WIN32) && !defined(WIN32) # include <signal.h> # if !defined(__RTP__) && !defined(_WRS_KERNEL) # include <pwd.h> |
︙ | |||
3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444 | 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444 3445 3446 3447 3448 3449 3450 3451 3452 3453 3454 3455 3456 3457 3458 3459 3460 3461 3462 3463 3464 3465 3466 3467 3468 3469 3470 3471 3472 3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507 3508 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | sqlite3_trace(p->db, 0, 0); }else{ sqlite3_trace(p->db, sql_trace_callback, p->traceOut); } #endif }else #if SQLITE_USER_AUTHENTICATION if( c=='u' && strncmp(azArg[0], "user", n)==0 ){ if( nArg<2 ){ fprintf(stderr, "Usage: .user SUBCOMMAND ...\n"); rc = 1; goto meta_command_exit; } if( strcmp(azArg[1],"login")==0 ){ if( nArg!=4 ){ fprintf(stderr, "Usage: .user login USER PASSWORD\n"); rc = 1; goto meta_command_exit; } rc = sqlite3_user_authenticate(p->db, azArg[2], (int)strlen(azArg[3]), azArg[3]); if( rc ){ fprintf(stderr, "Authentication failed for user %s\n", azArg[2]); rc = 1; } }else if( strcmp(azArg[1],"add")==0 ){ if( nArg!=5 ){ fprintf(stderr, "Usage: .user add USER ISADMIN PASSWORD\n"); rc = 1; goto meta_command_exit; } rc = sqlite3_user_add(p->db, azArg[2], booleanValue(azArg[3]), (int)strlen(azArg[4]), azArg[4]); if( rc ){ fprintf(stderr, "User-Add failed: %d\n", rc); rc = 1; } }else if( strcmp(azArg[1],"edit")==0 ){ if( nArg!=5 ){ fprintf(stderr, "Usage: .user edit USER ISADMIN PASSWORD\n"); rc = 1; goto meta_command_exit; } rc = sqlite3_user_change(p->db, azArg[2], booleanValue(azArg[3]), (int)strlen(azArg[4]), azArg[4]); if( rc ){ fprintf(stderr, "User-Edit failed: %d\n", rc); rc = 1; } }else if( strcmp(azArg[1],"delete")==0 ){ if( nArg!=3 ){ fprintf(stderr, "Usage: .user delete USER\n"); rc = 1; goto meta_command_exit; } rc = sqlite3_user_delete(p->db, azArg[2]); if( rc ){ fprintf(stderr, "User-Delete failed: %d\n", rc); rc = 1; } }else{ fprintf(stderr, "Usage: .user login|add|edit|delete ...\n"); rc = 1; goto meta_command_exit; } }else #endif /* SQLITE_USER_AUTHENTICATION */ if( c=='v' && strncmp(azArg[0], "version", n)==0 ){ fprintf(p->out, "SQLite %s %s\n" /*extra-version-info*/, sqlite3_libversion(), sqlite3_sourceid()); }else if( c=='v' && strncmp(azArg[0], "vfsname", n)==0 ){ const char *zDbName = nArg==2 ? azArg[1] : "main"; |
︙ |
Changes to src/sqlite.h.in.
︙ | |||
488 489 490 491 492 493 494 495 496 497 498 499 500 501 | 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 | + | #define SQLITE_CONSTRAINT_TRIGGER (SQLITE_CONSTRAINT | (7<<8)) #define SQLITE_CONSTRAINT_UNIQUE (SQLITE_CONSTRAINT | (8<<8)) #define SQLITE_CONSTRAINT_VTAB (SQLITE_CONSTRAINT | (9<<8)) #define SQLITE_CONSTRAINT_ROWID (SQLITE_CONSTRAINT |(10<<8)) #define SQLITE_NOTICE_RECOVER_WAL (SQLITE_NOTICE | (1<<8)) #define SQLITE_NOTICE_RECOVER_ROLLBACK (SQLITE_NOTICE | (2<<8)) #define SQLITE_WARNING_AUTOINDEX (SQLITE_WARNING | (1<<8)) #define SQLITE_AUTH_USER (SQLITE_AUTH | (1<<8)) /* ** CAPI3REF: Flags For File Open Operations ** ** These bit values are intended for use in the ** 3rd parameter to the [sqlite3_open_v2()] interface and ** in the 4th parameter to the [sqlite3_vfs.xOpen] method. |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 | 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 | + + | #define UAUTH_Fail 1 /* User authentication failed */ #define UAUTH_User 2 /* Authenticated as a normal user */ #define UAUTH_Admin 3 /* Authenticated as an administrator */ /* Functions used only by user authorization logic */ int sqlite3UserAuthTable(const char*); int sqlite3UserAuthCheckLogin(sqlite3*,const char*,u8*); void sqlite3CryptFunc(sqlite3_context*,int,sqlite3_value**); #endif /* SQLITE_USER_AUTHENTICATION */ /* ** Each database connection is an instance of the following structure. */ |
︙ |