sqlite access control?

[TurboRascal]

I'm working on an accounting application project which uses Postgres as the DB backend, however there will probably be several installs where the database will be quite small and accessed from a single machine. So, I thought we might use sqlite instead for such cases to avoid the overhead of installing and running Postgres for local use only. Since we don't plan on using any Postgres-specific SQL extensions, the dataset switching could be easily done even whithin the same binary (fat client).

The problem is, sqlite doesn't have any security features nor access control and that poses two problems. First, the database can be opened and the data read without the application. Second, the program has authentication at start, and it's easy to limit program access if the RDBMS authentication is used and fails; however sqlite doesn't have any authentication.

The first problem could be solved by simple scrambling, even ROT13 would do since the purpose here is not to prevent NSA agents but to prevent someone who casually opens the database to see the plaintext data. However, that solution would need heavy changes in the program itself to encode/decode such data, would interfere with switching to another dataset, and would make using the DB-aware controls much harder; so, this solution doesn't seem right.

The second problem could be solved by including password hashes in a custom database table, but since the sqlite database is unprotected, there is nothing to prevent someone to modify the hashes and gain access (or more privileged access) to the application.

So my question is, is it possible to protect an sqlite database, or is it simply too complicated and insecure, like I've concluded above?

Also unfortunately I don't see any better alternative to sqlite (ok, firebird might do that, but it's not portable)...

[BigChimp]

Hi Arny,

Depends on what you mean with portable. Firebird has an embedded version that works on Windows and people have worked with an embedded version on Linux/OSX http://www.firebirdfaq.org/faq51/ (a bit old and C centric) and http://www.mwasoftware.co.uk/index.php?option=com_content&task=view&id=105 (Linux, FreePascal centric).
Having an embedded Firebird Lazarus app on Linux is still on my nice to have list...

The fundamental point is the same though: the user has access to the database file and could run strings, grep etc over it. So confidentially is out the window, unless, as you say, the program encrypts the data. (Of course, depends also on how the binary format is scrambled. IIRC, Firebird uses RLE compression for at least memo fields, if you're lucky also varchars, so that might help - EDIT: Nope, just ran strings on one of my databases, could see a lot of data in VARCHAR fields...)

Access control can actually be solved at the file level: just store the database file in a user's programdata (can't remember exact name)/my documents folder.
If permissions are correct, other users won't have read/write access to the db file.

Oooh, unless you mean that different people on the same pc must have different access levels on the same database.
You could do something like a password hash computed as SHA1(salt+username+password+access level). The username would be the windows user name.
You can store the "plain text" salt in an innocuous table, or in the user table with a misleading field name. I'd store the access level in the user table.
Then calculate the sha1 hash from the various elements.
Using a Win API call you can retrieve people's user name, so they'd just have to enter their password. This blocks people from trying other user names.
A simplified version could be SHA1(salt+username+access level) - don't use a password.

Of course, when people change their Windows user names, they'll be stuck. Could be a viable tradeoff as I suppose you'll be targeting small businesses. You could have a master/support user as well, or you could of course calculate the hash for such a key in advance and use it during support.

Will not stop a dedicated, knowledgeable person, but you're not looking into keeping the NSA out 

Maybe you could look into always using a (Firebird) server. Easy to set up (you can take an obscure port, use netstat -a to check if it's in use, change Firebird's port to that, show that to the user to note, and write it to a config file).
If you're in small business mode, just let FB listen on 127.0.0.1 only, otherwise on 0.0.0.0 (all addresses).
Result: one database, one way of programming. Slightly more complicated for people to figure out where the database is stored.

<Edited 9 October for clarity re Windows user names>

[BigChimp]

Re encryption: actually, encryption of all data going in & decryption of everything coming out of a db is a common scenario (or at least request).
Might be worthwile to investigate whether it would be possible to hack FPC SQLDB layer to enable encryption, maybe compression as well...

Don't know how feasible that is, but a man can dream 

[TurboRascal]

Thanks for the answer, BigChimp. I believe we're more or less on the same lines here And it seems there is no easy answer, just as I thought from the beginning... I hoped someone would prove me wrong this time

[BigChimp]

True. I thought about my answer - edited it, hope I clarified the point about users & hashes.
Doesn't really change the equation though.

Finally: yes, embedded/client side databases can be accessed using text editors, grep, etc but obviously the same applies to server databases.
I suppose you're counting on a server having more access control? But the admin is likely to be a bit more technical, so has more chance of figuring things out.

I don't really see a way around the file-level confidentiality problem, but the access level problem is of course mitigated a lot in a client/server setup. (An admin could still take a backup copy, restore on a different db server under his control and voila...)

[TurboRascal]

I do count on a server having more access control, and I don't fear of what the admin might do because he/she would have clearance regarding that data anyway, but I know admin would behave; on the contrary the workstations would have quite free access for anyone too stupid or too smart That also means that windows user access control doesn't matter much.

We already have an older DBF based system which is quite insecure, but so far the working environments haven't been such to require anything better. When designing a new solution however I'd like it to be security-conscious from the ground up.

If I decide to implement a somewhat secure solution with sqlite or other local database option, I guess I'll first and foremost use the password hashes in the database, but that doesn't prevent accessing the database outside the application. While some encryption or at least scrambling would be useful to prevent reading of sensitive data, it would be very complicated to do, and reading itself is not what bothers me; I'd like to be sure that database is not modified. I might ensure that by calculating checksums or hashes of some data and encrypting it with the user password, so I can check if the database is compromised on application start.

[BigChimp]

I'd like to be sure that database is not modified. I might ensure that by calculating checksums or hashes of some data and encrypting it with the user password, so I can check if the database is compromised on application start.

Ok, sounds sensible, maybe having a column containing an application-generated hash of (concatenating all the columns in a row, with a fixed value added for obfuscation) after update/insert will be sufficient?

Do I understand correctly that this embedded solution would be used for a single workstation in a client organisation? Presumably, if there are more, a client/server setup would be used...
If so, isn't it really the client's problem whether their users corrupt the data?
I understand that you as the developer always get the blame from the customer if something went titsup, so is that why you want to build this in?
I'd also pay a lot of attention to automated backups, e.g. on app start, but you must have thought of that already.

[Lacak2]

You can look at SQLCipher, SSE etc. which provide transparent encryption of SQLite database file.
But if you plan multiuser access, IMO there still remains some problems ;-(

[TurboRascal]

Ok, sounds sensible, maybe having a column containing an application-generated hash of (concatenating all the columns in a row, with a fixed value added for obfuscation) after update/insert will be sufficient?

Yes, that might do, but it's "security through obscurity", a concept I don't like much; I prefer security through... well, security For read protection I was willing to use a simple scrambling though, because I don't need actual security, just making sure that some idiot can't grep it For ensuring database integrity, I'd prefer something at least somewhat cryptographically secure...

Do I understand correctly that this embedded solution would be used for a single workstation in a client organisation? Presumably, if there are more, a client/server setup would be used...

Yes, that is exactly so. That would be used in case of deployment to a single workstation.

If so, isn't it really the client's problem whether their users corrupt the data?
I understand that you as the developer always get the blame from the customer if something went titsup, so is that why you want to build this in?
I'd also pay a lot of attention to automated backups, e.g. on app start, but you must have thought of that already.

Unfortunately, it is exactly so... Over here it's always the developer's fault and developer's problem if something goes titsup.  Therefore we like to throw in as many checks, locks and idiot-traps as possible

You can look at SQLCipher, SSE etc. which provide transparent encryption of SQLite database file.
But if you plan multiuser access, IMO there still remains some problems ;-(

The transparent encryption is exactly what I'd need here... This sounds interesting, where can I find that (ok, I'll google "SQLCipher", but "SSE" would be impossible to find that way). Multiuser access doesn't present a problem at all, in multiuser deployment we switch the backend to a RDBMS

[BigChimp]

Ok, sounds sensible, maybe having a column containing an application-generated hash of (concatenating all the columns in a row, with a fixed value added for obfuscation) after update/insert will be sufficient?

Yes, that might do, but it's "security through obscurity", a concept I don't like much; I prefer security through... well, security

Don't take this the wrong way, but... explain 
Hmmm. The "fixed value" would emanate from your application code, sure it's obscurity in that way, but one can always dissassemble the code etc.
For my eduction (and I agree I should still be educated - a lot  ), can you tell me what needs to improve/where the error lies?
Adding user name/password to the hash will lead to problems whenever the password or username is changed. It will add complexity to the hash, will it really add to the security?

I'd think users would have to be pretty smart to go to these lengths to modify their own data in the database outside of your application, but maybe I'm not paranoid enough.

[TurboRascal]

Don't take this the wrong way, but... explain 
Hmmm. The "fixed value" would emanate from your application code, sure it's obscurity in that way, but one can always dissassemble the code etc.
For my eduction (and I agree I should still be educated - a lot  ), can you tell me what needs to improve/where the error lies?
Adding user name/password to the hash will lead to problems whenever the password or username is changed. It will add complexity to the hash, will it really add to the security?

I'd think users would have to be pretty smart to go to these lengths to modify their own data in the database outside of your application, but maybe I'm not paranoid enough.

No wrong way, it's a valid question There is a big difference if something can be broken by disassembling the code or by crypto-cracking. I'm not paranoid, but I like to think ahead, I wouldn't assume anything anymore. There is another bonus in using cryptography, if something somewhat gets to the court of law, a cryptographically secure check can be claimed as evidence, while a simply obfuscated one may be disproven. Why would someone try tampering with the data? How about fraud? After all, it's all about the money I'd like to take all that as seriously as possible...

The problem with pass change doesn't exist because those encrypted hashes/checksums (signatures) are invalid once the application starts an begins modifying data. If it crashes, it won't be created anyway, and it would be recreated on application's clean shutdown, using the changed password. Unfortunately this crash handling also presents a security hole...

[BigChimp]

Ok, you're saying you'll rehash all the hashes whenever a password etc changes - even those for already existing data....

NB: my method would still be crypto - it just uses less variables than yours 
Re your solution: if people disassemble far enough, they'll know you use username and password as components in the hash

Well, that's all, thanks for the explanation.

[TurboRascal]

Ok, you're saying you'll rehash all the hashes whenever a password etc changes - even those for already existing data....

Actually, I'd need to rehash all changed data every session, or hashes would become invalid...

Re your solution: if people disassemble far enough, they'll know you use username and password as components in the hash

That's not much of a problem, unix and windows passwords are protected the same way, but you can't get them so easily even if you get them (actually now you can, using rainbow tables). I doubt someone would have prepared rainbow tables for my hashes so he'd need to brute force crack them which isn't quite trivial, disassembly won't help there (there are also some weaknesses in MD5 and SHA1 which might be exploited in the future if those algorithms are used). However, this might be enough if the data isn't very sensitive. I've planned on using a proper signing methodology though, and hash only the data, then encrypt that using the password based key...

[BigChimp]

Actually, I'd need to rehash all changed data every session, or hashes would become invalid...

Eehrm? Wouldn't you just need to hash only the INSERTS/UPDATES?
But as long as you're aware of the performance implications, fine by me 

Have fun 

[TurboRascal]

Actually, I'd need to rehash all changed data every session, or hashes would become invalid...

Eehrm? Wouldn't you just need to hash only the INSERTS/UPDATES?

Uhm... I thought  that was clear from my post...