Repository: mattn/go-sqlite3
Pull request #578 (2018-05-30 21:47:51)

Feature/userauth
Issue(s)
* #513 

# ADDED
* User Authentication Implementation
* Rewrite Upgrade tool
* Rename file to conform to fileformat `sqlite3_*_omit.go`
* Updated sqlite3-binding.* with new upgrade tool
* Add: callbackRetNil required for error type return because of adding `RegisterFunc`s directly on the connection. (This requires review)
* Add: TestCreateAuthDatabase

# Upgrade Tool
The upgrade tool can now be build with build tag `upgrade`, CGO must be disabled.
```bash
CGO_ENABLED=0 go build -tags upgrade .
```

# TODO
* Implement Table Exists call to check if the `sqlite_user` table exists, and only run the creation when the table does not exists.
* Documentation

# Nice to Have
* Override implementation of sqlite_crypt with stronger encryption for password.
* Provide multiple password encoders so the user can simple use a `ConnectHook` to select which encoder must be used for the password encoder.

# Tests
The test(s) can be run with the following command:
```go
go test -v -tags sqlite_userauth -run sqlite3_opt_userauth_test.go
```

- [X] Create Auth Database
- [ ] Failed Authentication
- [ ] Add User
- [ ] Delete User
- [ ] Change Password
- [ ] ...
---

@mattn I really need your help with the `Table Exists` I do not know how to do this correctly,  we have to do an sql query directly on the sqlite3_ bindings without the golang sql driver. You probably know how to do this and will probably be peanuts for you.

[See Sqlite3.go Lines: 1371 - 1377 ](https://github.com/mattn/go-sqlite3/blob/feature/userauth/sqlite3.go#L1371-L1377)

[ci skip]


Patch:
diff --git a/.gitignore b/.gitignore
index c219728..fa0e6b5 100644
--- a/.gitignore
+++ b/.gitignore
@@ -5,3 +5,10 @@
 
 # VSCode
 .vscode
+
+# Exclude from upgrade
+upgrade/*.c
+upgrade/*.h
+
+# Exclude upgrade binary
+upgrade/upgrade
diff --git a/callback.go b/callback.go
index 29ece3d..5a735c0 100644
--- a/callback.go
+++ b/callback.go
@@ -331,8 +331,18 @@ func callbackRetText(ctx *C.sqlite3_context, v reflect.Value) error {
 	return nil
 }
 
+func callbackRetNil(ctx *C.sqlite3_context, v reflect.Value) error {
+	return nil
+}
+
 func callbackRet(typ reflect.Type) (callbackRetConverter, error) {
 	switch typ.Kind() {
+	case reflect.Interface:
+		errorInterface := reflect.TypeOf((*error)(nil)).Elem()
+		if typ.Implements(errorInterface) {
+			return callbackRetNil, nil
+		}
+		fallthrough
 	case reflect.Slice:
 		if typ.Elem().Kind() != reflect.Uint8 {
 			return nil, errors.New("the only supported slice type is []byte")
diff --git a/sqlite3-binding.c b/sqlite3-binding.c
index f3d13e7..cf030cf 100644
--- a/sqlite3-binding.c
+++ b/sqlite3-binding.c
@@ -209943,4 +209943,359 @@ SQLITE_API const char *sqlite3_sourceid(void){ return SQLITE_SOURCE_ID; }
 #else // USE_LIBSQLITE3
  // If users really want to link against the system sqlite3 we
 // need to make this file a noop.
- #endif
\ No newline at end of file
+ #endif
+/*
+** 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
+#ifndef SQLITEINT_H
+# include "sqliteInt.h"
+#endif
+
+/*
+** 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;
+  int savedFlags = db->flags;
+
+  va_start(ap, zFormat);
+  zSql = sqlite3_vmprintf(zFormat, ap);
+  va_end(ap);
+  if( zSql==0 ) return 0;
+  db->flags |= SQLITE_WriteSchema;
+  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
+  db->flags = savedFlags;
+  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);
+  if( db->init.busy==0 ){
+    char *zErr = 0;
+    sqlite3Init(db, &zErr);
+    sqlite3DbFree(db, zErr);
+  }
+  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;
+  if( !userTableExists(db, "main") ){
+    *peAuth = UAUTH_Admin;  /* No sqlite_user table.  Everybody is admin. */
+    return SQLITE_OK;
+  }
+  if( db->auth.zAuthUser==0 ){
+    *peAuth = UAUTH_Fail;
+    return SQLITE_OK;
+  }
+  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, db->auth.zAuthPW, db->auth.nAuthPW,SQLITE_STATIC);
+  sqlite3_bind_text(pStmt, 2, db->auth.zAuthUser, -1, SQLITE_STATIC);
+  rc = sqlite3_step(pStmt);
+  if( rc==SQLITE_ROW && sqlite3_column_int(pStmt,0) ){
+    *peAuth = sqlite3_column_int(pStmt, 1) + UAUTH_User;
+  }else{
+    *peAuth = UAUTH_Fail;
+  }
+  return sqlite3_finalize(pStmt);
+}
+int sqlite3UserAuthCheckLogin(
+  sqlite3 *db,               /* The database connection to check */
+  const char *zDb,           /* Name of specific database to check */
+  u8 *peAuth                 /* OUT: One of UAUTH_* constants */
+){
+  int rc;
+  u8 savedAuthLevel;
+  assert( zDb!=0 );
+  assert( peAuth!=0 );
+  savedAuthLevel = db->auth.authLevel;
+  db->auth.authLevel = UAUTH_Admin;
+  rc = userAuthCheckLogin(db, zDb, peAuth);
+  db->auth.authLevel = savedAuthLevel;
+  return rc;
+}
+
+/*
+** If the current authLevel is UAUTH_Unknown, the take actions to figure
+** out what authLevel should be
+*/
+void sqlite3UserAuthInit(sqlite3 *db){
+  if( db->auth.authLevel==UAUTH_Unknown ){
+    u8 authLevel = UAUTH_Fail;
+    sqlite3UserAuthCheckLogin(db, "main", &authLevel);
+    db->auth.authLevel = authLevel;
+    if( authLevel<UAUTH_Admin ) db->flags &= ~SQLITE_WriteSchema;
+  }
+}
+
+/*
+** 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.
+**
+** 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 */
+  const char *zPW,       /* Password or credentials */
+  int nPW                /* Number of bytes in aPW[] */
+){
+  int rc;
+  u8 authLevel = UAUTH_Fail;
+  db->auth.authLevel = UAUTH_Unknown;
+  sqlite3_free(db->auth.zAuthUser);
+  sqlite3_free(db->auth.zAuthPW);
+  memset(&db->auth, 0, sizeof(db->auth));
+  db->auth.zAuthUser = sqlite3_mprintf("%s", zUsername);
+  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 */
+}
+
+/*
+** 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 */
+  const char *aPW,       /* Password or credentials */
+  int nPW,               /* Number of bytes in aPW[] */
+  int isAdmin            /* True to give new user admin privilege */
+){
+  sqlite3_stmt *pStmt;
+  int rc;
+  sqlite3UserAuthInit(db);
+  if( db->auth.authLevel<UAUTH_Admin ) return SQLITE_AUTH;
+  if( !userTableExists(db, "main") ){
+    if( !isAdmin ) return SQLITE_AUTH;
+    pStmt = sqlite3UserAuthPrepare(db, 
+              "CREATE TABLE sqlite_user(\n"
+              "  uname TEXT PRIMARY KEY,\n"
+              "  isAdmin BOOLEAN,\n"
+              "  pw BLOB\n"
+              ") WITHOUT ROWID;");
+    if( pStmt==0 ) return SQLITE_NOMEM;
+    sqlite3_step(pStmt);
+    rc = sqlite3_finalize(pStmt);
+    if( rc ) return rc;
+  }
+  pStmt = sqlite3UserAuthPrepare(db, 
+            "INSERT INTO sqlite_user(uname,isAdmin,pw)"
+            " VALUES(%Q,%d,sqlite_crypt(?1,NULL))",
+            zUsername, isAdmin!=0);
+  if( pStmt==0 ) return SQLITE_NOMEM;
+  sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC);
+  sqlite3_step(pStmt);
+  rc = sqlite3_finalize(pStmt);
+  if( rc ) return rc;
+  if( db->auth.zAuthUser==0 ){
+    assert( isAdmin!=0 );
+    sqlite3_user_authenticate(db, zUsername, aPW, nPW);
+  }
+  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 */
+  const char *aPW,       /* Modified password or credentials */
+  int nPW,               /* Number of bytes in aPW[] */
+  int isAdmin            /* Modified admin privilege for the user */
+){
+  sqlite3_stmt *pStmt;
+  int rc;
+  u8 authLevel;
+
+  authLevel = db->auth.authLevel;
+  if( authLevel<UAUTH_User ){
+    /* Must be logged in to make a change */
+    return SQLITE_AUTH;
+  }
+  if( strcmp(db->auth.zAuthUser, zUsername)!=0 ){
+    if( db->auth.authLevel<UAUTH_Admin ){
+      /* Must be an administrator to change a different user */
+      return SQLITE_AUTH;
+    }
+  }else if( isAdmin!=(authLevel==UAUTH_Admin) ){
+    /* Cannot change the isAdmin setting for self */
+    return SQLITE_AUTH;
+  }
+  db->auth.authLevel = UAUTH_Admin;
+  if( !userTableExists(db, "main") ){
+    /* This routine is a no-op if the user to be modified does not exist */
+  }else{
+    pStmt = sqlite3UserAuthPrepare(db,
+              "UPDATE sqlite_user SET isAdmin=%d, pw=sqlite_crypt(?1,NULL)"
+              " WHERE uname=%Q", isAdmin, zUsername);
+    if( pStmt==0 ){
+      rc = SQLITE_NOMEM;
+    }else{
+      sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC);
+      sqlite3_step(pStmt);
+      rc = sqlite3_finalize(pStmt);
+    }
+  }
+  db->auth.authLevel = authLevel;
+  return rc;
+}
+
+/*
+** 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 */
+){
+  sqlite3_stmt *pStmt;
+  if( db->auth.authLevel<UAUTH_Admin ){
+    /* Must be an administrator to delete a user */
+    return SQLITE_AUTH;
+  }
+  if( strcmp(db->auth.zAuthUser, zUsername)==0 ){
+    /* Cannot delete self */
+    return SQLITE_AUTH;
+  }
+  if( !userTableExists(db, "main") ){
+    /* This routine is a no-op if the user to be deleted does not exist */
+    return SQLITE_OK;
+  }
+  pStmt = sqlite3UserAuthPrepare(db,
+              "DELETE FROM sqlite_user WHERE uname=%Q", zUsername);
+  if( pStmt==0 ) return SQLITE_NOMEM;
+  sqlite3_step(pStmt);
+  return sqlite3_finalize(pStmt);
+}
+
+#endif /* SQLITE_USER_AUTHENTICATION */
diff --git a/sqlite3-binding.h b/sqlite3-binding.h
index 4da4101..702a254 100644
--- a/sqlite3-binding.h
+++ b/sqlite3-binding.h
@@ -11179,4 +11179,100 @@ struct fts5_api {
 #else // USE_LIBSQLITE3
  // If users really want to link against the system sqlite3 we
 // need to make this file a noop.
- #endif
\ No newline at end of file
+ #endif
+/*
+** 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
+
+#ifdef __cplusplus
+extern "C" {
+#endif
+
+/*
+** 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 */
+  const char *aPW,       /* Password or credentials */
+  int nPW                /* Number of bytes in aPW[] */
+);
+
+/*
+** 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 */
+  const char *aPW,       /* Password or credentials */
+  int nPW,               /* Number of bytes in aPW[] */
+  int isAdmin            /* True to give new user admin privilege */
+);
+
+/*
+** 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 */
+  const char *aPW,       /* New password or credentials */
+  int nPW,               /* Number of bytes in aPW[] */
+  int isAdmin            /* Modified admin privilege for the user */
+);
+
+/*
+** 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 */
+);
+
+#ifdef __cplusplus
+}  /* end of the 'extern "C"' block */
+#endif
+
+#endif /* SQLITE_USER_AUTHENTICATION */
diff --git a/sqlite3.go b/sqlite3.go
index 75a5ee8..8a15696 100644
--- a/sqlite3.go
+++ b/sqlite3.go
@@ -891,6 +891,9 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
 
 	// Options
 	var loc *time.Location
+	authCreate := false
+	authUser := ""
+	authPass := ""
 	mutex := C.int(C.SQLITE_OPEN_FULLMUTEX)
 	txlock := "BEGIN"
 
@@ -916,6 +919,17 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
 			return nil, err
 		}
 
+		// Authentication
+		if _, ok := params["_auth"]; ok {
+			authCreate = true
+		}
+		if val := params.Get("_auth_user"); val != "" {
+			authUser = val
+		}
+		if val := params.Get("_auth_pass"); val != "" {
+			authPass = val
+		}
+
 		// _loc
 		if val := params.Get("_loc"); val != "" {
 			switch strings.ToLower(val) {
@@ -1248,7 +1262,93 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
 		return nil
 	}
 
+	// USER AUTHENTICATION
+	//
+	// User Authentication is always performed even when
+	// sqlite_userauth is not compiled in, because without user authentication
+	// the authentication is a no-op.
+	//
+	// Workflow
+	//	- Authenticate
+	//		ON::SUCCESS		=> Continue
+	//		ON::SQLITE_AUTH => Return error and exit Open(...)
+	//
+	//  - Activate User Authentication
+	//		Check if the user wants to activate User Authentication.
+	//		If so then first create a temporary AuthConn to the database
+	//		This is possible because we are already succesfully authenticated.
+	//
+	//	- Check if `sqlite_user`` table exists
+	//		YES				=> Add the provided user from DSN as Admin User and
+	//						   activate user authentication.
+	//		NO				=> Continue
+	//
+
+	// Create connection to SQLite
+	conn := &SQLiteConn{db: db, loc: loc, txlock: txlock}
+
+	// Preform Authentication
+	if err := conn.Authenticate(authUser, authPass); err != nil {
+		return nil, err
+	}
+
+	// Register Authentication Functions into connection
+	//
+	// Register Authentication function
+	// Authenticate will perform an authentication of the provided username
+	// and password against the database.
+	//
+	// If a database contains the SQLITE_USER table, then the
+	// call to Authenticate 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.
+	if err := conn.RegisterFunc("authenticate", conn.Authenticate, true); err != nil {
+		return nil, err
+	}
+	//
+	// Register AuthUserAdd
+	// AuthUserAdd 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 AuthUserAdd only works for the "main" database, not
+	// for any ATTACH-ed databases. Any call to AuthUserAdd by a
+	// non-admin user results in an error.
+	if err := conn.RegisterFunc("auth_user_add", conn.AuthUserAdd, true); err != nil {
+		return nil, err
+	}
+	//
+	// AuthUserChange 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.
+	if err := conn.RegisterFunc("auth_user_change", conn.AuthUserChange, true); err != nil {
+		return nil, err
+	}
+	//
+	// AuthUserDelete 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.
+	if err := conn.RegisterFunc("auth_user_delete", conn.AuthUserDelete, true); err != nil {
+		return nil, err
+	}
+
 	// Auto Vacuum
+	// Moved auto_vacuum command, the user preference for auto_vacuum needs to be implemented directly after
+	// the authentication and before the sqlite_user table gets created if the user
+	// decides to activate User Authentication because
+	// auto_vacuum needs to be set before any tables are created
+	// and activating user authentication creates the internal table `sqlite_user`.
 	if autoVacuum > -1 {
 		if err := exec(fmt.Sprintf("PRAGMA auto_vacuum = %d;", autoVacuum)); err != nil {
 			C.sqlite3_close_v2(db)
@@ -1256,6 +1356,33 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
 		}
 	}
 
+	// Check if user wants to activate User Authentication
+	if authCreate {
+		// Before going any further, we need to check that the user
+		// has provided an username and password within the DSN.
+		// We are not allowed to continue.
+		if len(authUser) < 0 {
+			return nil, fmt.Errorf("Missing '_auth_user' while user authentication was requested with '_auth'")
+		}
+		if len(authPass) < 0 {
+			return nil, fmt.Errorf("Missing '_auth_pass' while user authentication was requested with '_auth'")
+		}
+
+		// TODO: Table exists check for table 'sqlite_user'
+		// replace 'authExists := false' with return value of table exists check
+		//
+		// REPLACE BY RESULT FROM TABLE EXISTS
+		// SELECT count(type) as exists FROM sqlite_master WHERE type='table' AND name='sqlite_user';
+		// Scan result 'exists' and use it instead of boolean below.
+		authExists := false
+
+		if !authExists {
+			if err := conn.AuthUserAdd(authUser, authPass, true); err != nil {
+				return nil, err
+			}
+		}
+	}
+
 	// Case Sensitive LIKE
 	if caseSensitiveLike > -1 {
 		if err := exec(fmt.Sprintf("PRAGMA case_sensitive_like = %d;", caseSensitiveLike)); err != nil {
@@ -1347,8 +1474,6 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
 		}
 	}
 
-	conn := &SQLiteConn{db: db, loc: loc, txlock: txlock}
-
 	if len(d.Extensions) > 0 {
 		if err := conn.loadExtensions(d.Extensions); err != nil {
 			conn.Close()
diff --git a/sqlite3_omit_load_extension.go b/sqlite3_load_extension_omit.go
similarity index 100%
rename from sqlite3_omit_load_extension.go
rename to sqlite3_load_extension_omit.go
diff --git a/sqlite3_opt_userauth.go b/sqlite3_opt_userauth.go
new file mode 100644
index 0000000..3572f87
--- /dev/null
+++ b/sqlite3_opt_userauth.go
@@ -0,0 +1,127 @@
+// Copyright (C) 2018 G.J.R. Timmer <gjr.timmer@gmail.com>.
+//
+// Use of this source code is governed by an MIT-style
+// license that can be found in the LICENSE file.
+
+// +build sqlite_userauth
+
+package sqlite3
+
+/*
+#cgo CFLAGS: -DSQLITE_USER_AUTHENTICATION
+#cgo LDFLAGS: -lm
+#ifndef USE_LIBSQLITE3
+#include <sqlite3-binding.h>
+#else
+#include <sqlite3.h>
+#endif
+#include <stdlib.h>
+
+static int
+_sqlite3_user_authenticate(sqlite3* db, const char* zUsername, const char* aPW, int nPW)
+{
+  return sqlite3_user_authenticate(db, zUsername, aPW, nPW);
+}
+
+static int
+_sqlite3_user_add(sqlite3* db, const char* zUsername, const char* aPW, int nPW, int isAdmin)
+{
+  return sqlite3_user_add(db, zUsername, aPW, nPW, isAdmin);
+}
+
+static int
+_sqlite3_user_change(sqlite3* db, const char* zUsername, const char* aPW, int nPW, int isAdmin)
+{
+  return sqlite3_user_change(db, zUsername, aPW, nPW, isAdmin);
+}
+
+static int
+_sqlite3_user_delete(sqlite3* db, const char* zUsername)
+{
+  return sqlite3_user_delete(db, zUsername);
+}
+*/
+import "C"
+
+const (
+	SQLITE_AUTH = C.SQLITE_AUTH
+)
+
+// Authenticate will perform an authentication of the provided username
+// and password against the database.
+//
+// If a database contains the SQLITE_USER table, then the
+// call to Authenticate 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.
+func (c *SQLiteConn) Authenticate(username, password string) error {
+	rv := C._sqlite3_user_authenticate(c.db, C.CString(username), C.CString(password), C.int(len(password)))
+	if rv != C.SQLITE_OK {
+		return c.lastError()
+	}
+
+	return nil
+}
+
+// AuthUserAdd 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 AuthUserAdd only works for the "main" database, not
+// for any ATTACH-ed databases. Any call to AuthUserAdd by a
+// non-admin user results in an error.
+func (c *SQLiteConn) AuthUserAdd(username, password string, admin bool) error {
+	isAdmin := 0
+	if admin {
+		isAdmin = 1
+	}
+
+	rv := C._sqlite3_user_add(c.db, C.CString(username), C.CString(password), C.int(len(password)), C.int(isAdmin))
+	if rv != C.SQLITE_OK {
+		return c.lastError()
+	}
+
+	return nil
+}
+
+// AuthUserChange 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.
+func (c *SQLiteConn) AuthUserChange(username, password string, admin bool) error {
+	isAdmin := 0
+	if admin {
+		isAdmin = 1
+	}
+
+	rv := C._sqlite3_user_change(c.db, C.CString(username), C.CString(password), C.int(len(password)), C.int(isAdmin))
+	if rv != C.SQLITE_OK {
+		return c.lastError()
+	}
+
+	return nil
+}
+
+// AuthUserDelete 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.
+func (c *SQLiteConn) AuthUserDelete(username string) error {
+	rv := C._sqlite3_user_delete(c.db, C.CString(username))
+	if rv != C.SQLITE_OK {
+		return c.lastError()
+	}
+
+	return nil
+}
+
+// EOF
diff --git a/sqlite3_opt_userauth_omit.go b/sqlite3_opt_userauth_omit.go
new file mode 100644
index 0000000..0ae92da
--- /dev/null
+++ b/sqlite3_opt_userauth_omit.go
@@ -0,0 +1,65 @@
+// Copyright (C) 2018 G.J.R. Timmer <gjr.timmer@gmail.com>.
+//
+// Use of this source code is governed by an MIT-style
+// license that can be found in the LICENSE file.
+
+// +build !sqlite_userauth
+
+package sqlite3
+
+import (
+	"C"
+)
+
+// Authenticate will perform an authentication of the provided username
+// and password against the database.
+//
+// If a database contains the SQLITE_USER table, then the
+// call to Authenticate 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.
+func (c *SQLiteConn) Authenticate(username, password string) error {
+	// NOOP
+	return nil
+}
+
+// AuthUserAdd 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 AuthUserAdd only works for the "main" database, not
+// for any ATTACH-ed databases. Any call to AuthUserAdd by a
+// non-admin user results in an error.
+func (c *SQLiteConn) AuthUserAdd(username, password string, admin bool) error {
+	// NOOP
+	return nil
+}
+
+// AuthUserChange 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.
+func (c *SQLiteConn) AuthUserChange(username, password string, admin bool) error {
+	// NOOP
+	return nil
+}
+
+// AuthUserDelete 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.
+func (c *SQLiteConn) AuthUserDelete(username string) error {
+	// NOOP
+	return nil
+}
+
+// EOF
diff --git a/tool/upgrade.go b/tool/upgrade.go
deleted file mode 100644
index 275f915..0000000
--- a/tool/upgrade.go
+++ /dev/null
@@ -1,111 +0,0 @@
-// +build ignore
-
-package main
-
-import (
-	"archive/zip"
-	"bufio"
-	"bytes"
-	"fmt"
-	"io"
-	"io/ioutil"
-	"log"
-	"net/http"
-	"os"
-	"path"
-	"path/filepath"
-	"strings"
-
-	"github.com/PuerkitoBio/goquery"
-)
-
-func main() {
-	site := "https://www.sqlite.org/download.html"
-	fmt.Printf("scraping %v\n", site)
-	doc, err := goquery.NewDocument(site)
-	if err != nil {
-		log.Fatal(err)
-	}
-	var url string
-	doc.Find("a").Each(func(_ int, s *goquery.Selection) {
-		if url == "" && strings.HasPrefix(s.Text(), "sqlite-amalgamation-") {
-			url = "https://www.sqlite.org/2018/" + s.Text()
-		}
-	})
-	if url == "" {
-		return
-	}
-	fmt.Printf("downloading %v\n", url)
-	resp, err := http.Get(url)
-	if err != nil {
-		log.Fatal(err)
-	}
-
-	b, err := ioutil.ReadAll(resp.Body)
-	if err != nil {
-		resp.Body.Close()
-		log.Fatal(err)
-	}
-
-	fmt.Printf("extracting %v\n", path.Base(url))
-	r, err := zip.NewReader(bytes.NewReader(b), resp.ContentLength)
-	if err != nil {
-		resp.Body.Close()
-		log.Fatal(err)
-	}
-	resp.Body.Close()
-
-	for _, zf := range r.File {
-		var f *os.File
-		switch path.Base(zf.Name) {
-		case "sqlite3.c":
-			f, err = os.Create("sqlite3-binding.c")
-		case "sqlite3.h":
-			f, err = os.Create("sqlite3-binding.h")
-		case "sqlite3ext.h":
-			f, err = os.Create("sqlite3ext.h")
-		default:
-			continue
-		}
-		if err != nil {
-			log.Fatal(err)
-		}
-		zr, err := zf.Open()
-		if err != nil {
-			log.Fatal(err)
-		}
-
-		_, err = io.WriteString(f, "#ifndef USE_LIBSQLITE3\n")
-		if err != nil {
-			zr.Close()
-			f.Close()
-			log.Fatal(err)
-		}
-		scanner := bufio.NewScanner(zr)
-		for scanner.Scan() {
-			text := scanner.Text()
-			if text == `#include "sqlite3.h"` {
-				text = `#include "sqlite3-binding.h"`
-			}
-			_, err = fmt.Fprintln(f, text)
-			if err != nil {
-				break
-			}
-		}
-		err = scanner.Err()
-		if err != nil {
-			zr.Close()
-			f.Close()
-			log.Fatal(err)
-		}
-		_, err = io.WriteString(f, "#else // USE_LIBSQLITE3\n // If users really want to link against the system sqlite3 we\n// need to make this file a noop.\n #endif")
-		if err != nil {
-			zr.Close()
-			f.Close()
-			log.Fatal(err)
-		}
-		zr.Close()
-		f.Close()
-		fmt.Printf("extracted %v\n", filepath.Base(f.Name()))
-	}
-}
diff --git a/upgrade/upgrade.go b/upgrade/upgrade.go
new file mode 100644
index 0000000..d800227
--- /dev/null
+++ b/upgrade/upgrade.go
@@ -0,0 +1,218 @@
+// +build !cgo
+// +build upgrade
+
+package main
+
+import (
+	"archive/zip"
+	"bufio"
+	"bytes"
+	"fmt"
+	"io"
+	"io/ioutil"
+	"log"
+	"net/http"
+	"os"
+	"path"
+	"path/filepath"
+	"strings"
+	"time"
+
+	"github.com/PuerkitoBio/goquery"
+)
+
+func download(prefix string) (url string, content []byte, err error) {
+	year := time.Now().Year()
+
+	site := "https://www.sqlite.org/download.html"
+	//fmt.Printf("scraping %v\n", site)
+	doc, err := goquery.NewDocument(site)
+	if err != nil {
+		log.Fatal(err)
+	}
+
+	doc.Find("a").Each(func(_ int, s *goquery.Selection) {
+		if strings.HasPrefix(s.Text(), prefix) {
+			url = fmt.Sprintf("https://www.sqlite.org/%d/", year) + s.Text()
+		}
+	})
+
+	if url == "" {
+		return "", nil, fmt.Errorf("Unable to find prefix '%s' on sqlite.org", prefix)
+	}
+
+	fmt.Printf("Downloading %v\n", url)
+	resp, err := http.Get(url)
+	if err != nil {
+		log.Fatal(err)
+	}
+
+	// Ready Body Content
+	content, err = ioutil.ReadAll(resp.Body)
+	defer resp.Body.Close()
+	if err != nil {
+		return "", nil, err
+	}
+
+	return url, content, nil
+}
+
+func mergeFile(src string, dst string) error {
+	defer func() error {
+		fmt.Printf("Removing: %s\n", src)
+		err := os.Remove(src)
+
+		if err != nil {
+			return err
+		}
+
+		return nil
+	}()
+
+	// Open destination
+	fdst, err := os.OpenFile(dst, os.O_APPEND|os.O_WRONLY, 0666)
+	if err != nil {
+		return err
+	}
+	defer fdst.Close()
+
+	// Read source content
+	content, err := ioutil.ReadFile(src)
+	if err != nil {
+		return err
+	}
+
+	// Add Additional newline
+	if _, err := fdst.WriteString("\n"); err != nil {
+		return err
+	}
+
+	fmt.Printf("Merging: %s into %s\n", src, dst)
+	if _, err = fdst.Write(content); err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func main() {
+	fmt.Println("Go-SQLite3 Upgrade Tool")
+
+	// Download Amalgamation
+	_, amalgamation, err := download("sqlite-amalgamation-")
+	if err != nil {
+		fmt.Println("Failed to download: sqlite-amalgamation; %s", err)
+	}
+
+	// Download Source
+	_, source, err := download("sqlite-src-")
+	if err != nil {
+		fmt.Println("Failed to download: sqlite-src; %s", err)
+	}
+
+	// Create Amalgamation Zip Reader
+	rAmalgamation, err := zip.NewReader(bytes.NewReader(amalgamation), int64(len(amalgamation)))
+	if err != nil {
+		log.Fatal(err)
+	}
+
+	// Create Source Zip Reader
+	rSource, err := zip.NewReader(bytes.NewReader(source), int64(len(source)))
+	if err != nil {
+		log.Fatal(err)
+	}
+
+	// Extract Amalgamation
+	for _, zf := range rAmalgamation.File {
+		var f *os.File
+		switch path.Base(zf.Name) {
+		case "sqlite3.c":
+			f, err = os.Create("sqlite3-binding.c")
+		case "sqlite3.h":
+			f, err = os.Create("sqlite3-binding.h")
+		case "sqlite3ext.h":
+			f, err = os.Create("sqlite3ext.h")
+		default:
+			continue
+		}
+		if err != nil {
+			log.Fatal(err)
+		}
+		zr, err := zf.Open()
+		if err != nil {
+			log.Fatal(err)
+		}
+
+		_, err = io.WriteString(f, "#ifndef USE_LIBSQLITE3\n")
+		if err != nil {
+			zr.Close()
+			f.Close()
+			log.Fatal(err)
+		}
+		scanner := bufio.NewScanner(zr)
+		for scanner.Scan() {
+			text := scanner.Text()
+			if text == `#include "sqlite3.h"` {
+				text = `#include "sqlite3-binding.h"`
+			}
+			_, err = fmt.Fprintln(f, text)
+			if err != nil {
+				break
+			}
+		}
+		err = scanner.Err()
+		if err != nil {
+			zr.Close()
+			f.Close()
+			log.Fatal(err)
+		}
+		_, err = io.WriteString(f, "#else // USE_LIBSQLITE3\n // If users really want to link against the system sqlite3 we\n// need to make this file a noop.\n #endif")
+		if err != nil {
+			zr.Close()
+			f.Close()
+			log.Fatal(err)
+		}
+		zr.Close()
+		f.Close()
+		fmt.Printf("Extracted: %v\n", filepath.Base(f.Name()))
+	}
+
+	//Extract Source
+	for _, zf := range rSource.File {
+		var f *os.File
+		switch path.Base(zf.Name) {
+		case "userauth.c":
+			f, err = os.Create("userauth.c")
+		case "sqlite3userauth.h":
+			f, err = os.Create("userauth.h")
+		default:
+			continue
+		}
+		if err != nil {
+			log.Fatal(err)
+		}
+		zr, err := zf.Open()
+		if err != nil {
+			log.Fatal(err)
+		}
+
+		_, err = io.Copy(f, zr)
+		if err != nil {
+			log.Fatal(err)
+		}
+
+		zr.Close()
+		f.Close()
+		fmt.Printf("extracted %v\n", filepath.Base(f.Name()))
+	}
+
+	// Merge SQLite User Authentication into amalgamation
+	if err := mergeFile("userauth.c", "sqlite3-binding.c"); err != nil {
+		log.Fatal(err)
+	}
+	if err := mergeFile("userauth.h", "sqlite3-binding.h"); err != nil {
+		log.Fatal(err)
+	}
+
+	os.Exit(0)
+}


Test patch:
diff --git a/sqlite3_opt_userauth_test.go b/sqlite3_opt_userauth_test.go
new file mode 100644
index 0000000..e1bf538
--- /dev/null
+++ b/sqlite3_opt_userauth_test.go
@@ -0,0 +1,39 @@
+// Copyright (C) 2018 G.J.R. Timmer <gjr.timmer@gmail.com>.
+//
+// Use of this source code is governed by an MIT-style
+// license that can be found in the LICENSE file.
+
+// +build sqlite_userauth
+
+package sqlite3
+
+import (
+	"database/sql"
+	"fmt"
+	"testing"
+)
+
+func TestCreateAuthDatabase(t *testing.T) {
+	tempFilename := TempFilename(t)
+	fmt.Println(tempFilename) // debug
+	//defer os.Remove(tempFilename) // Disable for debug
+
+	db, err := sql.Open("sqlite3", "file:"+tempFilename+"?_auth&_auth_user=admin&_auth_pass=admin")
+	if err != nil {
+		t.Fatal("Failed to open database:", err)
+	}
+	defer db.Close()
+
+	var i int64
+	err = db.QueryRow("SELECT count(type) FROM sqlite_master WHERE type='table' AND name='sqlite_user';").Scan(&i)
+	if err != nil {
+		t.Fatal(err)
+	}
+	t.Logf("sqlite_user exists: %d", i)
+
+	_, err = db.Exec("SELECT auth_user_add('test', 'test', false);", nil)
+	if err != nil {
+		t.Fatal(err)
+	}
+
+}