Question about database security (1 Viewer)

[sumdumgai]

I have an application that has a frontend Access database that contains some forms, and a backend Access database that contains tables which the forms update. In the backend, there is a 'user' table that lists user-ids and their security level, either 'admin' or 'user'. The frontend database links to tables in the backend. Upon opening the frontend (shift bypass is disabled), an 'autoexec' macro checks the security level for the user. If the user passes this check, the form opens and the user begins data entry.

My question is this. How do I prevent the user from causing a '2001' error and stopping all macros, thereby gaining access to all database objects, by simply copying the database to an 'untrusted' location?

 

[theDBguy]

Hi. Short answer is "you can't" totally prevent anyone from doing this. What exactly are you trying to protect? If your code, then deploy ACCDE front ends only.

 

[sumdumgai]

Thanks. I don't want a 'user' level person to see anything except a form that loads when they open the frontend. I've disabled ribbon menus, design shortcuts, navigation pane. Everything I want works except for this hole, which allows any user to stop the autoexec code and bypass user level check.

 

[theDBguy]

Thanks. I don't want a 'user' level person to see anything except a form that loads when they open the frontend. I've disabled ribbon menus, design shortcuts, navigation pane. Everything I want works except for this hole, which allows any user to stop the autoexec code and bypass user level check.

Okay, the only way you can do it, I think, is to not have the tables linked to the front end already. Instead, at startup, check if code is allowed to run, and if so, then you can run your login code to check the user level and then link the tables at run time.

 

[sumdumgai]

at startup, check if code is allowed to run

How do you do that outside of using 'autoexec'?

 

[isladogs]

Don't use an autoexec macro.
Move the code from the autoexec macro to the load event of a start-up form.
This could be a hidden form if you wish.

You can also add code that checks the app location and if its not in the specified folder, the app is closed.
Similarly, you can check if the location is trusted if you prefer.

For more info on security issues, see Improve Security in Access databases

 

[theDBguy]

How do you do that outside of using 'autoexec'?

In your Autoexec macro, use an If/Then action and use [CurrentProject].[IsTrusted].

 

[Micron]

How do you do that outside of using 'autoexec'?

I don't get what the concern over autoexec is, unless it is doing something that it shouldn't be. All it should do is run startup code where you validate/verify in the code. While the bypass isn't bullet proof, if disabled and user doesn't know a way around it, they can't stop it from launching so where's the concern over this aspect?


If you are using macros as you indicate, then you will always run the risk of a user getting some sort of look at the db that you don't want. In pursuit of as much security as Access can provide, there should be only one macro in a db, and that's autoexec.

 

[sumdumgai]

I'm just a neophyte at this so bear with me. From what I see, running any kind of user id/level check in autoexec is useless because if macros are disabled in Trust Center (e.g., database is distributed to new user), or the database has been moved and is not in Trusted Location, the autoexec fails, leaving the database fully open for viewing, design, etc.

 

[theDBguy]

I'm just a neophyte at this so bear with me. From what I see, running any kind of user id/level check in autoexec is useless because if macros are disabled in Trust Center (e.g., database is distributed to new user), or the database has been moved and is not in Trusted Location, the autoexec fails, leaving the database fully open for viewing, design, etc.

Hi. I did say that in the first place. You, basically cannot prevent others from snooping if you leave things for them to look at. So, I was saying don't put anything you don't want them to see if you can't run your code. So, to recap, what I was suggesting is you only "put" the things you want in the db by using code. Therefore, if the code doesn't execute, the db is pretty much empty. Otherwise, it works fine. Hope this makes sense...

 

[Micron]

running any kind of user id/level check in autoexec is useless

Exactly. Which is why I said the only thing it should do is call code. What could that code do?
Validate

- user (I prefer getting Windows loginID over password forms where possible)
- db location (if not where specified, close db)
- user level (set some sort of global variable or instantiate custom user object)
- decide if db tables need updating
- fix any broken table links
- validate any project references
- in short, anything you can think of that may prevent a problem or can provide flexibility


By keeping the use of macros to only calling code, you avoid the issue of which you speak. Then there is the form load idea - as long as nothing prevents that from opening

 

[sumdumgai]

Micron, the code in autoexec doesn't execute if macros are disabled. There's an 'If' command and an 'OnError' command in macro build. I tried both but get a 'can't find the macro 'errorHandler' in the macro group 'autoexec'. What I want to incorporate in the autoexec is display a message on error that 'macros are disabled' and the quit Access.

 

[Micron]

Then I guess you go with an opening form, otherwise that makes no sense to me. How could a macro do anything at all if they are not allowed to run?

 

[theDBguy]

Micron, the code in autoexec doesn't execute if macros are disabled. There's an 'If' command and an 'OnError' command in macro build. I tried both but get a 'can't find the macro 'errorHandler' in the macro group 'autoexec'. What I want to incorporate in the autoexec is display a message on error that 'macros are disabled' and the quit Access.

Hi. Just curious... What do you mean exactly by "macros are disabled?" How was it disabled?

 

[The_Doc_Man]

@theDBguy: IF you run an app from an untrusted location, macros are disabled until you explicitly re-trust the app in its new location. Ever since Access dropped userl-level security it has been that way, I think. So the hole is to copy the app somewhere and launch it knowing that the "somewhere" is untrusted.

 

[theDBguy]

@theDBguy: IF you run an app from an untrusted location, macros are disabled until you explicitly re-trust the app in its new location. Ever since Access dropped userl-level security it has been that way, I think. So the hole is to copy the app somewhere and launch it knowing that the "somewhere" is untrusted.

Hi Doc. Do you think that’s what the OP meant? I just wanted to make sure. I already said it twice earlier, what he’s asking to do is not really possible. I also wanted to find out what he was trying to protect. Based on his answers to those two questions of mine, I could then attempt to give an appropriate suggestion.

 

[Micron]

IF you run an app from an untrusted location, macros are disabled until you explicitly re-trust the app in its new location.

That makes sense, but distributing front ends into non trusted locations should only be a one time problem at most - if ever, no? Also, if I haven't forgotten everything about the subject, no code would run in an non-trusted location without at least a warning, which would preclude opening a default form and expecting procedures behind it to run without user intervention.

I might be missing a vital point in with respect to what this thread is really about now because the root problem is looking like a Trusted Locations issue and not macros or code behind default forms.

 

[theDBguy]

That makes sense, but distributing front ends into non trusted locations should only be a one time problem at most - if ever, no? Also, if I haven't forgotten everything about the subject, no code would run in an non-trusted location without at least a warning, which would preclude opening a default form and expecting procedures behind it to run without user intervention.

I might be missing a vital point in with respect to what this thread is really about now because the root problem is looking like a Trusted Locations issue and not macros or code behind default forms.

Just to clarify my position, merely opening a non-trusted Access file does not necessarily disable macros. For example, if you create a blank database and add nothing to it but an Autoexec macro with one safe action in it, like the Message Box, the message will still show when you open the file without using the shift bypass.

 

[Minty]

Use the inbuilt ability (Access 2007 onwards) to load a form at start up, and run the code from there that does everything. No Macro's required.

If the form isn't loaded you haven't connected anything, and if its an accde there is nothing to see.

 

[sumdumgai]

Thanks to everybody for your inputs. To further explain what I want:
The database is split into FE and BE. The BE contains a table that lists user-ids and their respective user-level. If the user is 'Admin' level, then opening either FE or BE should give them full ribbon menus and design capabilities. If user is 'User' level, then opening BE should be denied (unless they know back door password). If they open FE, then the only thing visible should be a form; i.e., no ribbon, navigation pane, or design shortcuts.


The whole thing is out the window if they have disabled macros or copied the database to un-trusted location, in which case the 'Autoexec' (verify user function) is not performed. Now they have access to everything.