How Is User Level Security With Accdb File Possible?, Access 2010
Feb 16 2017, 07:10 PM
Joined: 18-January 17
We have an Access based application at work that runs on a Windows 7 PC using the Microsoft Access 2010 Runtime. No server based software is used.
To run the software you run a small exe file (175kB) to login and have to enter a username and password. If you try to open the accdb or accde file in Access 2010 it asks for a password and none of the user passwords or the administrator level password open them.
The executable to login has an associated ini file:
Runtime=C:\Program Files\Microsoft Office\OFFICE14\msAccess.EXE
I understood accdb databases don't support user-level security but this is apparently not true.
Can anyone suggest how this might have been achieved.
This post has been edited by Bubsterdoofus: Feb 16 2017, 07:18 PM
Feb 16 2017, 07:45 PM
Joined: 20-June 02
From: Newcastle, WA
Actually, mdb files do support ULS, and they do run under Access 2010. Is it possible that someone renamed these files from mde and mdb to accde and accdb? I don't think that would work, but it's the only thing that comes to mind at first look.
Feb 17 2017, 01:37 AM
Joined: 26-January 14
From: London, UK
Access in any version never did have "user-level" security (AKA role-based security). It still does not. There was a "feature" that used to be called ULS but even Microsoft admit it was not a security feature. The name was incredibly misleading.
Practically speaking you cannot allow multiple users to access your Access database and expect to have control over what different users can do with it. If you need role-based security then use a SQL DBMS (like SQL Server, Oracle, etc).
Feb 17 2017, 02:43 AM
Joined: 5-June 07
Access security used to work by modifying the .mdw file (like a "rights" file). The standard was called something like "normal.mdw" - I forget now, and you never changed that, but always created a new one, and secured your dbs against that. If this dbs used a .mdw file, and you do not know the credentials for that file, I suspect you have little chance of fixing it. You can't even create a new file with the same name, as when you create it, you needed to enter some unique security data. Even in those days, it was easy to lock yourself out of your dbs without meaning to, and MS gave you lots of warnings to this effect.
this is the one
you need to get inside the .exe and/or you need to know how to use this file.
Do you know who built this exe?
(Gemma was my dog)
Feb 17 2017, 04:43 AM
Joined: 18-January 17
I understand that proper User-Level security in Access in terms of controlling what specific users can see or do requires a SQL DBMS, but what is interesting/puzzling to me is that this Access application allows a user to run it using their allocated username/password without revealing the accdb/accde file passwords needed to view the tables,code etc. or make any changes.
If I open the MDW file, there are various tables such as MSysAccounts, MSysGroups, MSysObjects etc. The MsysAccounts table lists different user types such as admin, Admins, Creator, Engine, Users, Full Data Users, Full Permissions and each has a password field (mostly blank) and an SID field which appears to be encrypted as it has at least 15 characters which are either symbols or what look like japanese characters.
To re-phrase my initial question I am curious as to how you can provide access to an encrypted accde Access application without revealing its password and therefore making it secure?
This post has been edited by Bubsterdoofus: Feb 17 2017, 04:44 AM
Feb 17 2017, 07:53 AM
Joined: 8-November 07
From: South coast, England
I am curious as to how you can provide access to an encrypted accde Access application without revealing its password and therefore making it secure?
The method I use, even though 'secure' is rather an exaggeration of Access' security capabilities, is to encrypt the Back End (BE) Access and hold the BE password in a VBA module in the Front End (FE). This password could also be encrypted or hidden in some form provided the FE is able to de-crypt/unhide it. The FE is only ever distributed as an accde making it more difficult for the BE password to be found from the FE.
On opening the FE user are required to enter a UserID and password, The FE then opens the BE using the BE password, checks the users credentials held in a user table in the BE, and, if correct, opens a persistent recordset allowing the FE to access the BE.
Using this method users have to go through an Autoexec start up routine that prevents BE tables being accessed directly through the FE. Also, to prevent users being able to read users passwords by hacking the BE, the users password are also encrypted using a one way hash (e.g. SHA 256).
For an Access database this is probably 'over the top', as Access is not known for its security. If you really do need that level of security, then a different RDBMS BE, e.g. SQL Server etc, as already mentioned, is your probably you best option.
Feb 17 2017, 08:41 AM
Joined: 24-May 10
From: Downeast Maine
ULS provided a measure of security, but was not especially secure, and was clunky to set up. The standard file was System.mdw. It listed the users, groups, and passwords. Every time you opened Access you would join the System.mdw workgroup unless you specified otherwise. System.mdw had a few standard groups, and an Admin user. If it wasn't specified otherwise, everybody who opened Access was an Admin user. The Admin user was a member of the Users and Admins groups. Unless you specified otherwise in the database (mdb) file, the Admin user had permissions to everything. Neither the Users nor Admins groups could be deleted, and had to contain at least one member.
The trick was to log on to Access with a command line that included the instruction to join a specific workgroup (mdw) file other than System.mdw. Once there you would go into the ULS manager or whatever it was called, and:
Create groups (Data Entry users, Supervisors, Developer, etc. as made sense for the situation)
Create users. Everybody could be a separate user, or you could just have a handful of users so all Data Entry users would log on as the same user, for instance.
Place yourself into the Admins group
Move the Admin user to the Users group
Remove all permissions for the Users group
Create permissions for the various groups. For instance, the Date Entry users could be set up to view certain forms and change data in certain tables, but not delete records. The Supervisors group could be set up to view more forms and delete records from some tables, and so forth for as many groups as made sense. The members of the Admins group were the only ones who could set up permissions, and that group had to contain at least one user. If I remember correctly, everybody was a member of the Users group be default, so the best thing generally was for that group to have no permissions.
ULS was NOT the database password. That was entirely different.
That's the flyover view. I will have to disagree courteously with nvogel that ULS was no security at all. If used properly it was effective at managing permissions for everyday users, and could prevent routine snooping, but could not withstand determined hacking by knowledgeable users. The problem, and the reason it was often very insecure in its implementation, is that it was difficult to set up properly, and clunky to administer. It could easily take a half hour to set up basic permissions in a simple database, and it was necessary to use a third party tool to transfer permissions. If you imported your objects into a new mdb file you had to start from the beginning unless you had one of those tools. Sandra Daigle (I think) had a wonderful free utility to manage that task.
CurrentUser could be used to identify the person who logged in to the database. I made good use of that so that people could "sign" their data, or otherwise to identify the user. I think in an accdb database CurrentUser can only return Admin, and would return Admin if ULS is not set up in an mdb database, but at least it won't give you an error.
I believe ULS can still be implemented in a mdb database, and I suppose it would be necessary to continue administering it in a legacy application that uses ULS. You would need to customize the ribbon to add User and Group Permissions and User and Group Accounts at a minimum, because that's how ULS is managed. To implement it in an mdb database that does not have it you would need to create a new mdw file. When opening Access, you would use a command line that includes the full path to MSACCESS.exe, the full path to the mdb application, a "user" switch to identify the user, and a "wrkgrp" switch consisting of the full path to the custom mdw file.
I don't miss ULS, but I did have to create my own system to manage light security, identify the logged on user, and so forth. I doubt it is very secure, but there is no sensitive information in the databases, and it is hard enough to get people to manage the records for which they are responsible. There is little worry that somebody would break in to do extra work. Mostly it is used to show/hide command buttons, allow/disallow edits, and so forth.
Feb 17 2017, 10:09 AM
Joined: 5-June 07
but the underlying issue, is that if you do not have the code to the .exe file, and you do not have the key to the .mdw file - then you will find it difficult.
You could try connecting to the database, and THEN trying to see if a new database could read files from your open database. That might let you "daisy chain" to the data tables.
You could open the database normally, and then kill the session in task manager. That might also let you get to the data tables.
Whether you can get the normal application code is a different matter.
Was this solution proprietary? If not, then maybe some support services could recover the database for you.
EverythingAccess does this sort of stuff.
(Gemma was my dog)
Feb 17 2017, 10:33 AM
Joined: 24-May 10
From: Downeast Maine
I got into a digression, and overlooked the final sentence of the OP's second posting. Now that I look at it carefully I am uncertain what is being asked. I am especially puzzled by "and therefore making it secure." If it had been "insecure" I would have understood the concern to be that revealing the password would have made it insecure. Also, I am not sure how much would be revealed in an accde file anyhow. For an accdb file there would have been more reason for concern.
With that uncertainty in mind, one thing to consider is that when an accdb (or accde, I expect) is opened by a command line there is a cmd switch that functions rather like OpenArgs when opening a form, except it applies to an entire database. The Command function returns this value in the database that was opened.
You could have the cmd switch be the Windows logon name. This could be managed via a small "bootstrap" Access database, where an API (or the Environ function) could retrieve the logon name. In the database being opened, a table (linked) that assigns security groups (Admins, Data Entry, Supervisors, etc.) to various users (Windows logon names). If the logged on user is in the Data Entry group, show certain forms and controls. Maybe an expanded set of forms and controls for Supervisors, and so forth.
Other people commenting in this thread clearly know more than I do about encrypting the data files and so forth, so I won't try to add anything else. I just wanted to point out the cmd switch, which can be quite useful, and may have a role to play here.
Feb 17 2017, 12:53 PM
Joined: 18-January 17
Many thanks for the various inputs and points of view guys. I will look into the points raised, and as is inevitably the case with Access do some further research then possibly raise one or two further questions in due course.
I find it an interesting area for discussion as there seems to be many more than one way of skinning this particular cat.
|Search Top Lo-Fi||23rd May 2017 - 09:14 PM|