/ Check-in [842c6da8]
Login

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

Overview
Comment:Enhance the sqlite3_user_add() interface to initialize the user authentication logic. Add test cases for the extra argument on the end of the authorizer callback.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | user-auth
Files: files | file ages | folders
SHA1: 842c6da8f1a62bd13a1b4089a98b0835a46a2285
User & Date: drh 2014-09-11 16:19:31
Context
2014-09-11
16:36
Suppress the potential schema error that occurs when a non-user-auth SQLite library tries to parse the sqlite_user table definition in a user-auth database. check-in: cda33c1e user: drh tags: user-auth
16:19
Enhance the sqlite3_user_add() interface to initialize the user authentication logic. Add test cases for the extra argument on the end of the authorizer callback. check-in: 842c6da8 user: drh tags: user-auth
15:25
All interfaces working and tested. check-in: 96ea5c0b user: drh tags: user-auth
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/userauth/userauth.c.

   114    114   int sqlite3UserAuthCheckLogin(
   115    115     sqlite3 *db,               /* The database connection to check */
   116    116     const char *zDb,           /* Name of specific database to check */
   117    117     u8 *peAuth                 /* OUT: One of UAUTH_* constants */
   118    118   ){
   119    119     int rc;
   120    120     u8 savedAuthLevel;
          121  +  assert( zDb!=0 );
          122  +  assert( peAuth!=0 );
   121    123     savedAuthLevel = db->auth.authLevel;
   122    124     db->auth.authLevel = UAUTH_Admin;
   123    125     rc = userAuthCheckLogin(db, zDb, peAuth);
   124    126     db->auth.authLevel = savedAuthLevel;
   125    127     return rc;
   126    128   }
          129  +
          130  +/*
          131  +** If the current authLevel is UAUTH_Unknown, the take actions to figure
          132  +** out what authLevel should be
          133  +*/
          134  +void sqlite3UserAuthInit(sqlite3 *db){
          135  +  if( db->auth.authLevel==UAUTH_Unknown ){
          136  +    u8 authLevel = UAUTH_Fail;
          137  +    sqlite3UserAuthCheckLogin(db, "main", &authLevel);
          138  +    db->auth.authLevel = authLevel;
          139  +    if( authLevel<UAUTH_Admin ) db->flags &= ~SQLITE_WriteSchema;
          140  +  }
          141  +}
   127    142   
   128    143   /*
   129    144   ** Implementation of the sqlite_crypt(X,Y) function.
   130    145   **
   131    146   ** If Y is NULL then generate a new hash for password X and return that
   132    147   ** hash.  If Y is not null, then generate a hash for password X using the
   133    148   ** same salt as the previous hash Y and return the new hash.
................................................................................
   219    234     const char *zUsername, /* Username to be added */
   220    235     const char *aPW,       /* Password or credentials */
   221    236     int nPW,               /* Number of bytes in aPW[] */
   222    237     int isAdmin            /* True to give new user admin privilege */
   223    238   ){
   224    239     sqlite3_stmt *pStmt;
   225    240     int rc;
          241  +  sqlite3UserAuthInit(db);
   226    242     if( db->auth.authLevel<UAUTH_Admin ) return SQLITE_AUTH;
   227    243     if( !userTableExists(db, "main") ){
   228    244       if( !isAdmin ) return SQLITE_AUTH;
   229    245       pStmt = sqlite3UserAuthPrepare(db, 
   230    246                 "CREATE TABLE sqlite_user(\n"
   231    247                 "  uname TEXT PRIMARY KEY,\n"
   232    248                 "  isAdmin BOOLEAN,\n"

Changes to src/build.c.

   154    154          || sqlite3VdbeAssertMayAbort(v, pParse->mayAbort));
   155    155     if( v ){
   156    156       while( sqlite3VdbeDeletePriorOpcode(v, OP_Close) ){}
   157    157       sqlite3VdbeAddOp0(v, OP_Halt);
   158    158   
   159    159   #if SQLITE_USER_AUTHENTICATION
   160    160       if( pParse->nTableLock>0 && db->init.busy==0 ){
          161  +      sqlite3UserAuthInit(db);
   161    162         if( db->auth.authLevel<UAUTH_User ){
   162         -        if( db->auth.authLevel==UAUTH_Unknown ){
   163         -          u8 authLevel = UAUTH_Fail;
   164         -          sqlite3UserAuthCheckLogin(db, "main", &authLevel);
   165         -          db->auth.authLevel = authLevel;
   166         -          if( authLevel<UAUTH_Admin ) db->flags &= ~SQLITE_WriteSchema;
   167         -        }
   168         -        if( db->auth.authLevel<UAUTH_User ){
   169         -          pParse->rc = SQLITE_AUTH_USER;
   170         -          sqlite3ErrorMsg(pParse, "user not authenticated");
   171         -          return;
   172         -        }
          163  +        pParse->rc = SQLITE_AUTH_USER;
          164  +        sqlite3ErrorMsg(pParse, "user not authenticated");
          165  +        return;
   173    166         }
   174    167       }
   175    168   #endif
   176    169   
   177    170       /* The cookie mask contains one bit for each database file open.
   178    171       ** (Bit 0 is for main, bit 1 is for temp, and so forth.)  Bits are
   179    172       ** set for each database that is used.  Generate code to start a

Changes to src/shell.c.

  3441   3441   #if SQLITE_USER_AUTHENTICATION
  3442   3442     if( c=='u' && strncmp(azArg[0], "user", n)==0 ){
  3443   3443       if( nArg<2 ){
  3444   3444         fprintf(stderr, "Usage: .user SUBCOMMAND ...\n");
  3445   3445         rc = 1;
  3446   3446         goto meta_command_exit;
  3447   3447       }
         3448  +    open_db(p, 0);
  3448   3449       if( strcmp(azArg[1],"login")==0 ){
  3449   3450         if( nArg!=4 ){
  3450   3451           fprintf(stderr, "Usage: .user login USER PASSWORD\n");
  3451   3452           rc = 1;
  3452   3453           goto meta_command_exit;
  3453   3454         }
  3454   3455         rc = sqlite3_user_authenticate(p->db, azArg[2], azArg[3],

Changes to src/sqliteInt.h.

  1006   1006   #define UAUTH_Fail        1     /* User authentication failed */
  1007   1007   #define UAUTH_User        2     /* Authenticated as a normal user */
  1008   1008   #define UAUTH_Admin       3     /* Authenticated as an administrator */
  1009   1009   
  1010   1010   /* Functions used only by user authorization logic */
  1011   1011   int sqlite3UserAuthTable(const char*);
  1012   1012   int sqlite3UserAuthCheckLogin(sqlite3*,const char*,u8*);
         1013  +void sqlite3UserAuthInit(sqlite3*);
  1013   1014   void sqlite3CryptFunc(sqlite3_context*,int,sqlite3_value**);
  1014   1015   
  1015   1016   #endif /* SQLITE_USER_AUTHENTICATION */
  1016   1017   
  1017   1018   /*
  1018   1019   ** typedef for the authorization callback function.
  1019   1020   */

Changes to test/userauth01.test.

   205    205   # database that is an authentication-required database is checked using
   206    206   # the same username and password as supplied to the main database.  If that
   207    207   # check fails, then the ATTACH command fails with an SQLITE_AUTH error.
   208    208   #
   209    209   do_test userauth01-1.60 {
   210    210     forcedelete test3.db
   211    211     sqlite3 db3 test3.db
          212  +  sqlite3_user_add db3 alice xyzzy-alice 1
          213  +} {SQLITE_OK}
          214  +do_test userauth01-1.61 {
   212    215     db3 eval {
   213    216       CREATE TABLE t3(a,b,c); INSERT INTO t3 VALUES(1,2,3);
   214    217       SELECT * FROM t3;
   215    218     }
   216    219   } {1 2 3}
   217         -do_test userauth01-1.61 {
   218         -  sqlite3_user_add db3 alice xyzzy-alice 1
   219         -} {SQLITE_OK}
   220    220   do_test userauth01-1.62 {
   221    221     db eval {
   222    222       ATTACH 'test3.db' AS aux;
   223    223       SELECT * FROM t1, t3 ORDER BY x LIMIT 1;
   224    224       DETACH aux;
   225    225     }
   226    226   } {{} 1 2 3}
................................................................................
   233    233   } {1 {unable to open database: test3.db}}
   234    234   do_test userauth01-1.64 {
   235    235     sqlite3_extended_errcode db
   236    236   } {SQLITE_AUTH}
   237    237   do_test userauth01-1.65 {
   238    238     db eval {PRAGMA database_list}
   239    239   } {~/test3.db/}
          240  +
          241  +# The sqlite3_set_authorizer() callback is modified to take a 7th parameter
          242  +# which is the username of the currently logged in user, or NULL for a
          243  +# no-authentication-required database.
          244  +#
          245  +proc auth {args} {
          246  +  lappend ::authargs $args
          247  +  return SQLITE_OK
          248  +}
          249  +do_test authuser01-2.1 {
          250  +  unset -nocomplain ::authargs
          251  +  db auth auth
          252  +  db eval {SELECT x FROM t1}
          253  +  set ::authargs
          254  +} {/SQLITE_SELECT {} {} {} {} alice/}  
          255  +
   240    256   
   241    257   finish_test