I am an independent web developer/designer, trying to create a desktop AIR app with an encrypted local database. In reading some of Adobe's support documents, though, I've come to what I think might be a big problem, given my goals for the app, and I'd love if someone with more experience/knowledge could let me know if I'm right (that it's a problem), and potentially offer solutions (if they exist), or point me in the right direction!
I also tend to write waaaay more than necessary, so please feel free to skip down to the red highlighted text!
My skill level
I'm not sure how to classify my skill/knowledge level, as I'm a learn-as-I-go-as-needed kind of guy - so while I know how to do certain things really well, I don't have a solid foundational understanding of programming, and there are big gaps in my knowledge. I work mostly in code, but do use things like Dreamweaver's built-in server behaviours for stuff I'd have no idea how to code from scratch (and then I tinker with the generated code, as necessary).
What I'm using
To create ExampleApp.air, which uses an encrypted local database, with one Admin-style user, and multiple read-only only users. All users would access the program from the same computer, and from the same computer account - so no concurrent users, if that's relevant. Each of the read-only users would have access to view different parts (tables, fields, & records) of the db, depending on how the Admin-style user set up their account. For example:
MasterUser can enter, edit, and delete most data with the App (some, of course, is reserved for the functioning of the App, and would need to be restricted to app-level use only).
MasterUser can also create, edit, and delete other User accounts, and select which tables, fields, and records each User account can view, such that:
User1 can view all records and fields in Tables 1, 2, & 4
User2 can view Table 3, but only fields X & Z
User3 can view all tables and fields, but only records where Field Y = "monkey"
I hope that example made things clearer!
From the documentation I've found for using encrypted dbs in AIR with SQLite, if I understand correctly (which I certainly don't take as given!), there is just one password (I'll call it the 'DB Password') to decrypt the database, which any user would need to enter to run the program.
Now I know that within the app & db, I can set as many passwords and access levels as I like, just as I would if I were making this for the web. So, for example, every user would need the 'DB Password' to run the app, but would then be greeted with a login screen, where they would have to enter their individual usernames and passwords to determine their level of access within the app.
But since the db is a local file, does that mean that anyone with access to the computer and the 'DB Password' (ie - one of the non-Admin-type users) could potentially use some other database administration software (like Navicat or SQLite Administrator) to access the db file and gain full privileges over the data?? If so, is there any way around this?
Thanks in advance for any help you can offer!!