1

I create an application with pyqt5 which will be used by a few users. These user I want to give different permissions to use the application. I think I will create 3 permission level. (Admin), (supervisor), (user).

To save user data i create a SQLite 3 database where i save user data like username, (hashed)passwort etc. In this usertable I have a column (isAdmin) at the moment. This column is boolean and saves 0 or 1 to check if the user is admin or not. After starting the software you have to login and if you are an admin you get more rights. But while coding this my thought was that everybody could get access to this database by softwares like "SQLite Browser" or "SQLite Studio" and can chance all what he/she want. So this seems not to be the best was to do this what I want.

I need some ideas how do I can solve this problem. I have never did something like User Management system in the past, so I have absolutely no idea.

Me as admin have to be able to create new users, create new tables in database etc. But a normal user shouldnt has these rights. I need some food for thought. I am very grateful for any help.

Robert
  • 159
  • 1
  • 9
  • Does this answer your question? [Password Protect a SQLite DB. Is it possible?](https://stackoverflow.com/questions/1381264/password-protect-a-sqlite-db-is-it-possible) – musicamante Feb 11 '22 at 23:17
  • There is no access control for SQLite, you can only set a "global" password, which means that it has to be hardcoded (so, if users have access to the code and have a minimal knowledge about programming, it's pointless). If the database data and usage are not *that* sensitive, one possibility could be to store the database *file* using basic encryption: decode it before opening, clone it in memory, and then clone it back when closing (or at specific intervals), encoding the file once it's closed. This will only prevent direct opening, though, if users are skilled, they can decode it anyway. – musicamante Feb 11 '22 at 23:25
  • In python i did not found any solution to protect the sqlite database with a password. The setPasswort() method from the C# example is not included in Python. But i like the option to encrypt the whole database and load it in memory after decrypting. Do you have some simple codeexamples for me, of a link where i can read something about it? – Robert Feb 11 '22 at 23:40
  • Oh, sorry, I just found out that it that support is not provided in standard SQLite distributions (including the QtSql plugin). Unfortunately I cannot help you on that, it's just something that I realized as (possibly) feasible. This might be a good starting point: https://stackoverflow.com/questions/3850022/how-to-load-existing-db-file-to-memory-in-python-sqlite3 – musicamante Feb 11 '22 at 23:47
  • You're, in essence, asking how to recreate DRM from first principles. There is no simple solution, and it's made harder since Python code is almost always so easy to look at and modify. – Anon Coward Feb 12 '22 at 00:05
  • Yes, thats good. Thank you. One more Question... If i decrypt the .sqlite file and load it in memory what happens with the file on the drive? This file is decrypted as long as i work with the connection right? So i have to decrypt the file, then load it to memory and then i have to encrypt the file again, right? so i work with the file in memory while the file on the drive is encrypted, right? :-) Sorry, i never did that before. – Robert Feb 12 '22 at 00:10
  • @AnonCoward I just look for a simple way to prevent users to do something what is job for the admin like create new users which uses this software. The admin should be the only one who can create new users. But after login, the software should know which user is an admin. These information i want to safe in the database which i use anyway. But the problem is the easy way to get access to the Database from outer the software. Mhhh not as easy as i thought. The data in the database is not sensitive and no one but me knows something about coding, but i want to do it in the right way. – Robert Feb 12 '22 at 00:21
  • You could decrypt and load the database and operate on it there. You'd lose many of SQLite's safeties if you do that, but it'd work. Or, you could add a field that's the hash of the username + a secret in code and verify it's correct before using a user. Both are equally easy to break, but both prevent casual snooping. Anything more, like using SQLite's official (and not free) encryption support, are more complex, and require careful study beyond a SO post. – Anon Coward Feb 12 '22 at 00:43
  • Thank you very much. Obvisualy i have to deal with it a little more. :-) – Robert Feb 12 '22 at 01:00

0 Answers0