Question about passwords in a multiuser environment

[uyuni]

I am planning to build a multiuser access DB. I plan to split the front end and the back end. I understand the idea of creating a table with user IDs (network logons) and their corresponding passwords and privileges. The front end then checks the password against that table and determines the privilege.

The only problem with this approach is that a truly malicious user can just open the back end DB and have access to all the data.

How do I address this issue?

I know VBA well, so any suggestions/code samples are appreciated

-u

 

[Rx_]

How many users? How important is it to protect the back end?
If the back-end is put into a folder with group rights (nobody outside the approved users list can open it) then only the users could get to it. That is a bulk protection method.
If you moved the back end to SQL Server - there is a whole other level of protection available. Not that it couldn't be done with an Acces DB. Any security model has an overhead.
If the front end was distributed on Citrix, that adds a whole new level of security.

http://office.microsoft.com/en-us/access-help/what-happened-to-user-level-security-HA010234561.aspx
It aslo depends on the version of Access. Microsoft wants to sell more sharepoint and SQL Server license. Wonder if that is why they took out features in 2007?

How malicious is your user? Would a password protected DB be enough?
Give a little more information about your version and options.

 

[uyuni]

Thank you for your reply. But let's say I have privileges where UserA can do everything, but UserB cannot modify or delete. UserB can still then open the back end and delete something, correct?

 

[Rx_]

They could, if both of them had full rights to the network folder.
Perhaps set it up for read/write for the average user.
Then make the back-end password protected. Use code to create the linked tables with the password.
Then - compile the front-end. That way users can't get to the design view.

There are others who have this experience. In my case, I use Citrix and SQL Server. The users with an account can only request a run-time front-end connected to a secured sql server. Lots of users and the data is extremely valuable.
So valuable, I log all users activity by form and by major process.

There should be some 3rd party tools for securing the DB as another option.

Here are a couple of ideas along that line to get you started. Was hoping that some others who do this would chime in.


With SQL Server - that option goes away for them.

The owner of the database can remove the password as follows:

1. Open the database in Exclusive mode.
2. Choose Security from the Tools menu and select Unset Database Password.
3. Enter the password.
4. Click OK. (There is probably the same method to secure your database)

You can also password-protect your VBA modules (code) as follows:

1. Choose project Properties from the Tools menu (in the VBE).
2. Click the Protection tab.
3. Check the Lock Project For Viewing option.
4. Fill in the two password fields (using the same password, of course).
5. Click OK.

Password protection is better than no password at all, but it won’t stop someone with the right tools and a little time.

Then the password would be in code. To protect the code:
Access offers a security feature in the guise of a file format: mde and accde (in Access 2007). This format is an execute-only version of the database. That means users don’t have access to the code via the VBE, nor can they make design changes to objects. This format protects the validity of your design (mostly) but it doesn’t protect the data. (Be sure to keep a copy of the original mdb/accdb file for upgrades and other modifications.)

This format has it issues, as you might suspect:

• Use this format for the front end of a split database. Don’t use it to secure the backend or a stand-alone database. If you do, you’ll have to transfer all the data into a new database every time you upgrade the front end.
• This format doesn’t protect tables, queries, macros, relationships, database properties, or startup options. <groan>

To convert a front-end database to the mde or accde format, do the following:

1. In Access XP and earlier, choose Database Utilities from the Tools menu and then select Make MDE File. In Access 2007, click Make ACCDE in the Database Tools group on the Database Tools tab. (You can’t convert Access 2000 format or earlier.)
2. In the resulting dialog box, specify a folder and name for the new database and click Save.

 

[uyuni]

The back end has to be in Access unfortunately. If I use a password for the back end, then this password has to be included in the front end VBA code, and hence it is hardwired, is it not? i.e. the administrator cannot change it?

Thanks again for your help.

-u

 

[Rx_]

Consider a variable. Also consider a custom function to create the password for that variable. Maybe something a little more difficult than 1=A, 2=B, 3=C...

Years ago, on one product we had that was very expensive and used a serial port hardware key, we had a function called "Warp Coil Breach". As QA Manager, it was a real blast to set that up for testing. We later heard rumors of a chain in Mexico that ... Oh well, use some innovation here.
Would bet that if you posted just 'how to set up an encryption function for your DB back-end password' on this forum, there will be some very smart people respond to that one.