I'm finally delving into creating databases and interacting with them via a self-built front end. At the moment, I'm focusing on SQLite since it can run without needing a daemon running all the time.
I want to restrict access to the data contained in the database. This is not a high-security environment, nor are any critical data in jeopardy. This is strictly "let's test this" kinda stuff.
My first goal is to deny easy access to the database by someone using a generic SQlite tool to dump tables or whatever. I plan to accomplish this by encrypting the DB file with a symmetric cipher (cipher TBD, open to suggestions and not married to symmetric ciphers.)
The first issue that comes to mind now is where to store the decryption key. Storing it in plaintext within the executable would render it available to anyone with a string viewer. My current plan is to merely obfuscate the key with a simple replacement cipher hardcoded into the program, but would like a better solution.
Next is how to allow for multiple (non-concurrent) users, with varying levels of access, each with their own username/password combo. Obviously I can't store this data inside the encrypted database since I'd need to authenticate the user before decryption takes place. My idea is to have a separate DB containing the user info that gets loaded at runtime.
This second DB (I'll call it usersDB) has a single table (called users) with three columns: username as VacChar, pwdHash as VarChar, and accessLevel as integer (accesslevel is interpreted by the frontend, which applies the restrictions associated with the accesslevel. Values are 0-5 where 0 is access denied and 5 is SuperDuperDBA; invalid values default to 0.) I'm considering a fourth column that hashes the other three + salt to verify that there haven't been unauthorized alterations to the record.
The usersDB.users.pwdHash column values are generated by MD5'ing a static salt string prepended to the user's actual password.
The pseudocode used to query the usersDB is like this:
var record as RecordSet = userDB.SQLQuery("SELECT * FROM users WHERE (userName LIKE '" + userName + "') AND (pwdHash LIKE '" + MD5(salt+password) + "');") If record.countRecords <> 1 then //GO AWAY Else enumerateUserPermissions(record as RecordSet) decryptMainDB() End If
Any thought/suggestions/critiques so far?