Access Security

[Thales750]

How is Access less secure?

As a front end to a system database like SQL Server or MySQL is Access less secure than .NET or any of the other compiled frontends (in compiled BE mode of course)?

And how to make it that way?

 

[Pat Hartman]

It is less secure for two reasons.
1. Passwords are frequently stored in the connection strings of linked tables. You can open the MSysObjects table and view these passwords in plain text.
2. If you don't store the password in the connection string, people frequently store it in code. Access databases can be opened with text editors and strings are visible. This doesn't make it easy to identify a password embedded in a character string but if you know where to look, you will see it.

So - don't store passwords when you link tables and don't store passwords in code modules. Always prompt the user.

 

[tranchemontaigne]

There are many utilities (commercial and free) that crack MS Access passwords. Generally speaking, I highly recommend performing a Google search for [insert technology name] plus "crack" before attempting to use any kind of technology. Though I have had mixed success when trying, you can decompile MS Access source code. If I recall correctly there are even posts within this site on how to decompile, edit, and recompile MS Access modules.

 

[Pat Hartman]

I'm going to expand my answer. I read the question originally as a learning exercise but I see it differently this time. Many people talk about "Access" beeing a poor database and being unsecure but what they really are talking about is Jet/ACE but they don't understand the difference. Jet (A2003 and earlier) and ACE (A2007 and newer) are the database engines used by Access to store the objects you develop. Both are also used to store data, not only by "Access" but by other applications developed with other tools. Earlier versions of Outlook relied on Jet for data storage as did the Windows registry. "Access" (really Jet/ACE) can be used by any application that can use ODBC to access a data store.

So in reviewing your question, I see it as questioning Jet/ACE rathe than Access. Access as a FE is no less secure than any of the .net options except as I stated earlier with the password being plain text in linked tables if you save it there. The real issue is whether you need a browser based FE or whether you need a client/server FE. If you will have strangers that need to access your app from outside your firewall, the best solution will be a .net browser based app. However, if this is an internal app, your users will be happier with a client/server solution and developing it with Access will be significantly more cost effective than developing with any .net language. The Access runtime engine is free for A2007 and A2010 and can be distributed to any client who does not have a retail version of Access installed.

 

[Thales750]

Thanks Pat,
What we are doing is creating a multi-store POS (point of sale) the local machines will store data locally on MySQL, or SQL Server Express.

They will periodically sync data with a server located across the web to a MySQL database located on a Windows Server.

Passwords will be encrypted, salted and stored in the Local and central databases.

Does this sound secure, or do we need to add some more security?

Most of the real security will be handled by SQL Server or MySQL, it sounds like you are confident that we will not have a security issue there. Do you see any problem with the passwords?

 

[Pat Hartman]

As long as you don't store the passwords in the linked tables, you'll be fine assuming you have a secure connection to the server.

 

[Thales750]

What do you mean by linked tables? Do we need to create a connection for verifiable passwords?

 

[Pat Hartman]

When you link the tables to MySQL or SQL Server, you will be asked to provide a userID and password and you will have the option of saving these with the connection string. Say no.

 

[Thales750]

Oops,
I may need a major over hall of my thinking.
I had intended to allow function or not within the database front end (Access portion) based on employee security levels and storing the passwords in a salted, encrypted record on the main database and also in a local MySQL or SQL Server Express.
Are you saying they will need to login to the frontend and the main databases with two different passwords.

 

[Pat Hartman]

I don't think I said that but I may have been thinking that

Most of the time the DBA wants each user to have a unique identifier so that he can analyze traffic and updates and see who was working in the database. If you link the tables with a static id and password there is no way to determine which user is actually updating the database. You also loose some security since Access stores the user ID and password in plain text in the connection string and that was your original question. How secure is Access as a FE?

I also don't like giving the users IDs and passwords that will get them into SQL Server because some of them are just savvy enough to be able to open SSMS and get to the tables. My solution is to define individual userIDs but use a common password. I give the users their UserID but I don't give them their password. I have them log onto Access with the UserID (and probably a password also), I then take that information and in code, I log into SQL server with the UserID they provided and the password that is hard-coded in the app.

 

[Thales750]

I don't think I said that but I may have been thinking that

Most of the time the DBA wants each user to have a unique identifier so that he can analyze traffic and updates and see who was working in the database. If you link the tables with a static id and password there is no way to determine which user is actually updating the database. You also loose some security since Access stores the user ID and password in plain text in the connection string and that was your original question. How secure is Access as a FE?

I also don't like giving the users IDs and passwords that will get them into SQL Server because some of them are just savvy enough to be able to open SSMS and get to the tables. My solution is to define individual userIDs but use a common password. I give the users their UserID but I don't give them their password. I have them log onto Access with the UserID (and probably a password also), I then take that information and in code, I log into SQL server with the UserID they provided and the password that is hard-coded in the app.

Thanks for continuing this conversation Pat, I got to the end of a multy year development project I did on speculation; and all of a sudden a major waterfall apeared in the river.

We think alike. but what if someone opens the system in word pad and finds the passwords.

What if you developed a password keeper in a DLL created with .NET or something and called it from the Access Database?

 

[Pat Hartman]

If they open the database in notpad, they still have to figure out what text string is a password and what is other stuff. Open a database you don't care about (I think access databases actually break if you open them with word) and look at the gibberish. Make your passwords pass for gibberish.
The problem with the dll solution is you need to distribute it plus you need the correct version of .net on the target computer to use it. It is just one more thing to make installing an application difficult.

 

[Thales750]

Thanks Pat, This has been good.

 

[The_Doc_Man]

This is not always available, and it doesn't close down the loop by any means, but if you have domain-level logins with passwords, there are functions that you can use within Access to ask your terminal who you are. The domain administrators can often help you lock down your terminals so that you can't easily fake the identification. I use this concept to identify my users from the FE file. If the user who comes in to me doesn't use his/her domain account, I get a different answer to "who are you" and toss the person into the "unknown user" file.

The part that gets tricky and I'm still working this issue is that the BE file, when opened by the FE file, either is encrypted or not. (No middle ground.) To leave the BE cleartext requires you to have a very seriously trustworthy group of users and a darned good firewall. To encrypt the BE requires you to get clever with the method of grabbing the password. However, this is NOT different from the problem you have when talking to Jet, ACE, ORACLE, SQL Server, MySQL, etc. It boils down to whether the BE trusts the FE to be secure and whether the users can bypass the FE to get to the BE.

I did some searches on this topic and found that you can do a bit of obscuration to make it work. If you have a BE requiring a password and open a connection to it when your FE starts springing to life, but never close that connection until you close the BE, all subsequent actions in the database use that connection. I.e. as long as you keep one table open with an encrypted channel, you can get ALL tables that way. Therefore, if you can make that first channel action somehow do something very temporary and obscure, you can make the FE-BE connection work even though the BE is encrypted or needs a password in some way.

Please also note that security by obscurity is NOT a recommended method for situations where you are exposed to the world via web connections. Security is ALWAYS a balancing act - cost of securing something vs. cost of recovering from discovery that it wasn't as secure as you hoped - and got hacked or something like that. You always choose the best security you can afford commensurate with the value of the data you wanted to secure.