I have a distributed MsAccess front-end database which uses a mysql backend.
It uses the Windows System DSN ODBC connections to connect to the server. All my Linked tables refer to that ODBC connection. The thing is though, they all use the same username and password which is hard-coded into each computer.
What would be a better way to implement it so that each user gets it's login.
Since each DSN Connection is "hard-wired" I don't think that rewriting the registry every time the application starts is a secure way since the moment the app crashes the DSN settings will remain.
I am unaware if I can leave the System DSN without a username and password for a prompt, however we connect to four different databases so I wouldn't want the user to enter their info four times as that would just frustrate the user.
I was thinking maybe I could use the System DSN user as a read only to a user table, or maybe preferably a procedure that would validate the user, except once validated I am unsure how I would subsequently connect every table. Can I store a global variable in the ODBC connection string?
What is a better way to make MsAccess more User Aware?
(I looked into the MSACCESS Security settings however it seems that Microsoft is weening off of that and my attempt to establish it locks me out completely and doesn't present any form of login validation. I guess it just uses the widows login as security but that's not a real solution) And there is a disclaimer here:
Access and user-level security
Access does not support user-level security for databases that are created in the new file format (.accdb and .accde files). However, if you open a database from an earlier version of Access in Access 2010 and that database has user-level security applied, those settings will still function.
IMPORTANT Permissions created by using the user-level security feature do not protect your database from users who have malicious intent, and are not intended as a security barrier. It is appropriate to use this feature to improve the usability of a database for trusted users. To help keep your data secure, allow only trusted users to access your database file or associated user-level security files by using Windows file system permissions.
If you convert a database from an earlier version of Access with user-level security to the new file format, Access strips out all security settings automatically, and the rules for securing an .accdb or .accde file apply.
Finally, remember that all users can see all database objects at all times when you open databases that have the new file format.