Using sqlite database

Question

I am working on an application where users go on and offline. I want the data entered by users to be synced with central db.

The application is in Swing and using web services to update data in central DB.

I am thinking SQLite as a solution but I am concerned about security. The concern in each client system will have more than one user and every user will have his specific data. Does SQLite support this?

Are there any other alternatives for SQLite in this scenario?

I have Oracle 10g as my Central DB. The role of SQLite is a local DB that stores data till user goes online

EDIT:: I am here concerned about the security of SQLite file. Through my initial analysis I found there are no features like authentication while accessing sqlite file. Hope I am clear now.

• Hot Licks: You're question is unclear. Are you asking about SQLite as a local DB that would be updated via the web, or are you asking about the DB to be used on the web server? If the former, it's perfectly reasonable for each user to have his own SQLite DB file, so it's as secure as the file system.
• StanislavL: db.apache.org/derby ?
• Mihai8: A related question is stackoverflow.com/questions/8107141/…

Answer 1

SQLite does not support per-user authentication (you can use a password, but it's the same for the whole database file). Per-user authentication wouldn't be efficient anyway, because a user who has read-access to the database file could just read the file directly. You could encrypt the data, but you would have to store the decryption key and algorithm in the executable, where the user can access it.

When you want to protect the local data of each user, I would thus recommend to delegate this responsibility to the operating system. Just store an individual database file for each user in the private user directory of the current user (%appdata% on windows, ~/ on unix). As long as users do not have admin/root rights, the operating system should prevent them from accessing data in each others user directories.

This, of course, only works when each user of your application also has an individual account on the operating system. When that's not the case, you could still use individual databases for each user stored in a public location, but encrypt each database file with a key which is derived from the password of each user.