Home / server / Questions / 424276
Accepted
Mallow
Asked: 2012-09-05 11:04:26 +0800 CST

How could I make a MsAccess Frontend and Mysql Backend more user aware?

  • 772

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:

Introduction to Access 2010 security (office.microsoft.com)

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.

security

1 Answers

  1. Best Answer

    As I understand it, you're looking to provide a more personalized experience/environment for your end-users within the application ("application users") while also improving security by coupling the user authentication process with the OBDC DSN connection credentials ("database user").

    This customized user environment is typically achieved by having your end-users authenticate themselves with your application. This is usually done by having the users type in a username and password (which is typically stored in a my_app_db.users table) when the application loads.

    Before this application user authentication can happen, the application itself (or machine) needs to establish a connection with the database server. This "database user" is typically achieved with:

    1. A static, pre-defined database user/password that's hard-coded into the application (and/or installer script/binary). The installation script will then typically ask you, the server admin, for database credentials with (among others) the CREATE and GRANT permissions (typically the root user) so that the installer can create the database user it needs.
    2. A user-defined database user that's been pre-created with the appropriate permissions; the installer will then ask you to provide these credentials, which the application will store using it's own method of reversible encryption (or obfuscation) in order to keep these credentials somewhere on the client (a .dat file, registry key, etc.) in a way that can be read before the database connection is established.

    As for security, the Rule of Least Privilege is important here: this database user should only be granted the least amount of privileges required for the application to function correctly (i.e. SELECT, INSERT, UPDATE, and maybe DELETE, and for only the application's database, never the system/master database(s)).

    This in combination with not storing database credentials in plain text on the end-user machines can go along way to protect your application from unauthorized access. Keep in mind that if a malicious user gains local administrator access to a machine where your application has been installed, it's pretty much game over: they could likely pull that connection string out of memory decrypted or even sniff MySQL traffic on the network interface to get the credentials of the database user and at that point, it's up to how restricted you've made that user (well, and hopefully MySQL's getting patched regularly) and how sophisticated and determined the attacker is.

    Keep in mind that alot of database applications will use an application server, essentially a "watch guard" in the middle that brokers connections to/from the database on behalf of the client. This is usually done for performance/scalability reasons, but there's also a security benefit in that the database server can be isolated/restricted to only the application server and no end-user clients can directly access it.

    As for ODBC DSN options and MySQL, I'm not that well-versed in what's available. However, there does seem to be Windows Native authentication support in later versions of MySQL.

    I have had success using the MySQL/NET connector with security certificates in conjunction with MySQL and an ASP.NET application, however, but I don't know if that extends to Microsoft Access at all; you'd probably have to write some .NET glue there.

    And when/if it gets down to specifics, you're probably better off asking over at Stack Overflow at that point.

    Hope this helps.

    • 1