-3

Am currently developing a Delphi app for desktop (Data Base: Access) that needs the multi users feature (each user will have his Permissions, history of transactions).

How can i do that ?

I found this article, they used MySQL and am not familiar with it, explaining the same method in Access will be appreciated.

David Acuna
  • 1
  • This is extremely broad - it doesn't really have to do with the database, but up to you to create your own user structure. – Jerry Dodge Dec 02 '19 at 15:15
  • Jet has workspace, which has groups and users and permissions. But we don't even know what you are using. – Sertac Akyuz Dec 02 '19 at 16:10
  • @SertacAkyuz Jet's workgroup-based security is very much outdated and insecure. See [this comment](https://stackoverflow.com/questions/22148052/access-stuck-using-security-workgroup#comment33608317_22148052) for example. It can be cracked easily. – Erik A Dec 02 '19 at 16:13

1 Answers1

1

Your question is a bit broad, but here are some advises to implement what you expect:

Even if the DB has user authentication, don't rely on it:

  • Authentication is highly DB-specific, so your code won't be easily migrated to another kind of DB;
  • Leaking the DB credentials is always a bad idea, for security reasons: anyone able to connect to the DB would be able to modify it with raw SQL...

So, in a perfect world, I would use a n-Tier architecture, and keep the Authentication on the server side. But it won't apply to a RAD application.

A typical way is to define some User table, with an ID, and authentication using e.g. a hashed password. Then some other UserRight table, with features names as string keys, which I would test in my VCL/UI code: e.g. "ModifyThisKindOfData", "ExportThisKindOfContent"... See for instance how to distinguish Authorization and Authentication.

About security, if you are using Access, you need to secure Authorization and Authentication by using proper hashing of the password (use e.g. PBKDF2/SHA-256 with a secret and a stored salt), and by signing both User and UserRight table rows e.g. with a HMAC-SHA-256. Then validate it in the software to detect if the DB has been tempered. It will avoid most obvious security flaws. But switch to a proper n-Tier design, or a client-server DB will be better.

Arnaud Bouchez
  • 42,305
  • 3
  • 71
  • 159
  • Access is not a client-server database, but a file-based one. Implementing truly secure user-based authentication and roles for it is nigh impossible since Access requires that all users have write access to the database file. Your pointers don't really apply to the question. – Erik A Dec 02 '19 at 15:40
  • @ErikA, well, Access is not a good DB choice. Yet, all said here can be developed even for Access (server tier will access DB tier). – TLama Dec 02 '19 at 15:47
  • 1
    Yup, and the users can just open up the database file directly in Access and edit their permissions/hashes without using your Delphi app, thus you've provided 0 security. Half advice leads to insecure applications. It's worse than no advice imo. – Erik A Dec 02 '19 at 15:51
  • @ErikA For a naive direct password Hash, of course. But it is bad practice. Use rather e.g. BCrypt or HMAC/PBKDF2 over SHA-256 with a secret and a stored salt. And you could apply a similar idea for the User rights fields, and add a digital signature to it. So it is not impossible to make an Access database secure enough for both Authentication and Authorization. Even if anyone could indeed modify its content directly. I add this information to my answer. – Arnaud Bouchez Dec 03 '19 at 11:42
  • @ArnaudBouchez Still very much not real security. You haven't accounted for the database being available for users to directly edit their transaction history, or really, anything that's not the user table (which is a very obvious securty flaw imo), or deleting other users whenever they feel like it. Also, a user will have access to all components needed to re-sign his row after adjusting it (if he doesn't he wouldn't be able to change his password), so that's just an additional hurdle when one wants to escalate his privileges but not real security. – Erik A Dec 03 '19 at 11:53
  • @ErikA, Access has a built-in rights mechanism both Admin/User level against users directly opening and modifying the database: if applied, random users can't directly access the database. I'm not sure if it's broken in latest versions of ms-access because I do remember there were tools to crack the protections. it also has a built-in encryption capabilities. again, not sure how secure it is. but for common users it's fairly strong. – kobik Dec 03 '19 at 12:45
  • With that said, ms-access sucks when it comes to real world database applications, it lacks tons of basic DB features and not truly designed for a multi-user environment. there are many much more superior embedded databases out there. I gave a +1 to the answer because I find it useful in regard to the OP question. – kobik Dec 03 '19 at 12:45
  • @kobik The user-level security is very broken (cracked in minutes by experienced users or anyone who wants to and has access to Google), deprecated, and only supports .mdb files. You can encrypt the entire database securely with a single password. However, that doesn't offer you authorization, just encryption. I've shared a sample database [here](https://stackoverflow.com/a/46196870/7296893) that demonstrates you can encrypt the master password with a user password, but users will still have the master password in memory thus can escalate their privileges. – Erik A Dec 03 '19 at 12:53
  • @ErikA, You don't need to convince me. I've been doing DB applications for all my life and know about access weaknesses, although it served me quit well in the old times. I **would not** recommend to develop a new application based on it. BTW, an experienced user can crack almost any Application/DB if he has access to the right places. – kobik Dec 03 '19 at 13:22
  • @kobik if you either employ proper encryption and/or proper server-side authentication, an experienced user should __NOT__ be able to crack your application, else you're doing something wrong. I've worked on many applications where I'm confident I can't crack them once my access is revoked. We should not advocate poor security because it's common, and instead strive to create properly secure applications, that's why I'm involved here. For embedded databases, however, proper security is hard, that's why you generally shouldn't use them if you require security. – Erik A Dec 03 '19 at 13:31
  • Proud down voter, please give at least a comment why you think it is an inappropriate answer. – Arnaud Bouchez Dec 04 '19 at 08:23