2

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.

gravyface
  • 13,947
  • 16
  • 65
  • 100
Mallow
  • 144
  • 2
  • 9
  • 3
    The right answer is to not use MS Access as the front-end to any system. Focus your efforts on changing that instead of worrying about installation setup. – Brent Pabst Sep 04 '12 at 19:17
  • 1
    Really not sure what you're asking here: more "user aware" -- you could always get the environmental variables to get their current NT user. Pretty sure there's a way to query that from VBA. – gravyface Sep 04 '12 at 19:53
  • @BrentPabst It's a convenient tool that the workplace already offers. Access has some tools already integrated. (Primarily the construction of forms) I've tried to implement my own forms once before using php and it flopped. Unless you have suggestions for alternatives? Regardless of whether I keep Access or not it is nonetheless something I would have to implement in some form or another. – Mallow Sep 04 '12 at 20:19
  • @gravyface I want to avoid getting their NT user since that won't do much. I could very easily get up out of my chair to another coworker's computer, ask to use the computer for five minutes without switching users...etc... The NT users just aren't 100% part of the office culture... yet... – Mallow Sep 04 '12 at 20:21
  • 1
    Mallow, somewhat of a crappy example but there are tools out there that can convert Access web forms. http://dbconvert.com/convert-dbforms-access-to-aspnet.php. However, I fully understand, used to work with an Access 97 MDE a few months ago so I feel the benefit, but mostly pain that comes with it. – Brent Pabst Sep 04 '12 at 20:22
  • 1
    @Mallow still have no idea what you're asking. Typically, with any software, you ask them to authenticate themselves; this is usually achieved with a username and password. If you don't want to ride the Windows logon session that's already active, then ask them for a different username/password and store that in a Users table. – gravyface Sep 04 '12 at 20:35
  • @gravyface I guess then my question would be, if I want to store it in a user table, how would I implement that. I would still have to dynamically change the corresponding information in each DSN Connection string, or else it's not "really" secure, right? For example, the connection password right now, you can see it pretty easily in the registry despite entering it in a password field in the 'ODBC Data Source Administrator' – Mallow Sep 04 '12 at 20:39
  • 1
    @Mallow why change it? There are a lot of apps out there (client/server, web, etc.) that have a static *someappuser* that's shared across all installations and used for client or app server connectivity to a database server. – gravyface Sep 04 '12 at 20:44
  • 1
    as for security, well the privileges of this client connection user should be pretty restricted: SELECT, INSERT, UPDATE, (maybe) DELETE and these are typically only granted to the application's database(s), not system databases (mysql). I have worked with a software vendor that used certificates for authentication (MySQL.NET DB library), but if someone has local admin on the machine, nothing is safe at that point: certificates, registry, etc. doesn't matter. – gravyface Sep 04 '12 at 20:49
  • @gravyface Thanks, I wish you made that an answer, You helped clarify what I need to do. Restrict table rights on the Mysql side to only Select Insert Update and Delete and then I could keep track of changes but just sending off a query setting a user variable or something simple instead of giving myself a headache. :) – Mallow Sep 04 '12 at 21:08
  • 1
    Typing one now. :) – gravyface Sep 04 '12 at 21:08

1 Answers1

1

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.

gravyface
  • 13,947
  • 16
  • 65
  • 100