Solved Locking Down the Database (1 Viewer)

[Weekleyba]

I'm trying to lock down my database after a user went in and changed some table records.
My database is split.
The Back End is encrypted with a password.
The Front End opens to the Main form. All forms are not able to go to Design View.
Also, the FE has the Navigation Pane and the Use Access Special Keys turn off in the Access Options menu.

So this is pretty good for most folks. I'm not really worried about a pro trying to crack in into it.

But, even with this all set up, a user can still access the Options menu through File>Privacy Options and change the start up options.

Is there a way to prevent access to the Options menu?

 

[NauticalGent]

If you are providing your users with a shortcut, add the /runtime switch

 

[bob fitz]

Colin has some interesting stuff here: https://mendipdatasystems.co.uk/improve-security-2/4594571073.html
Point 5 there might be of particular interest to you

 

[bob fitz]

You could hide the ribbon completely with one line of code in the OnOpen event of the form that opens first or in an autoexec macro:

DoCmd.ShowToolbar "Ribbon", acToolbarNo

 

[isladogs]

My mendipdatasystems.co.uk site is still available but not up to date.
All the latest versions of my apps & articles are on my isladogs.co.uk site
Here's the updated link to the article that Bob mentioned

Improve Access Security 2

This article explains how to apply various security measures to make your databases as secure as possible. An example app is used to illustrate the possible methods available

www.isladogs.co.uk

 

[theDBguy]

Not as extensive as Colin's article, but this blog might still be helpful.

Backstage View

A while back, I posted a demo showing how to hide the Backstage View in Access 2010 ( click here to see the original article ). This particu...

thedbguy.blogspot.com

 

[Weekleyba]

This is all very helpful. I'm playing around with it now and I will land on good solution. Thank you all!!

 

[tomasm]

You could hide the ribbon completely with one line of code in the OnOpen event of the form that opens first or in an autoexec macro:

DoCmd.ShowToolbar "Ribbon", acToolbarNo

I keep seeing this as an easy way to hide the ribbon. But if a user copies the database file to a new location, VBA code is disabled until the user clicks "enable content" under the security warning. The hide ribbon code won't run and the user will have full access to the ribbon. How do you prevent this?

 

[theDBguy]

I keep seeing this as an easy way to hide the ribbon. But if a user copies the database file to a new location, VBA code is disabled until the user clicks "enable content" under the security warning. The hide ribbon code won't run and the user will have full access to the ribbon. How do you prevent this?

One way is to use a blank custom ribbon.

 

[tomasm]

The custom ribbon approach provided by Microsoft seems like a better solution (when it works) but I'm still curious how people are using code to secure the database when the code won't run until the user allows it to run.

learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/how-to-hide-the-ribbon-when-access-starts
This worked for about an hour before it stopped working. I can't even get it to work now in my backups so I might need to keep looking for a better solution.

 

[theDBguy]

This worked for about an hour before it stopped working.

Just curious, what made it stop working?

 

[tomasm]

Just curious, what made it stop working?

No idea. I can't figure it out. I added it two hours ago and it was great. Now the ribbon shows every time. Maybe caused by a setting I changed somewhere because it won't work in my backup files either.

 

[isladogs]

I keep seeing this as an easy way to hide the ribbon. But if a user copies the database file to a new location, VBA code is disabled until the user clicks "enable content" under the security warning. The hide ribbon code won't run and the user will have full access to the ribbon. How do you prevent this?

I recommend you go through all the steps in my web article referenced in post #5
That includes an unlocked database which you can use for testing.

Attached is a locked version of the same file. Download & save it to an UNTRUSTED location as in post #8
You shouldn't see the security 'Enable Content' banner nor the ribbon / nav pane etc
Does that work for you?

Note that I have deliberately left a 'back door' open in order to unlock the database again if needed.
However, if you want to study how it works, its much easier to download the unlocked version from my website

 

[theDBguy]

No idea. I can't figure it out. I added it two hours ago and it was great. Now the ribbon shows every time. Maybe caused by a setting I changed somewhere because it won't work in my backup files either.

Do you see the regular ribbon with the attached? If so, then yes, maybe you accidentally changed something in your settings. If so, try it again on another computer just to compare the results.

 

[tomasm]

I recommend you go through all the steps in my web article referenced in post #5
That includes an unlocked database which you can use for testing.

Attached is a locked version of the same file. Download & save it to an UNTRUSTED location as in post #8
You shouldn't see the security 'Enable Content' banner nor the ribbon / nav pane etc
Does that work for you?

Note that I have deliberately left a 'back door' open in order to unlock the database again if needed.
However, if you want to study how it works, its much easier to download the unlocked version from my website

I don't see the "enable content" banner but I do get the "security risk - microsoft has blocked macros..." banner and the autoexec macro is prevented from running. (I get this banner in the unlocked version as well.)

I read through the article again and I don't see that the trusted file/location topic is explicitly addressed. Are you preventing the "enable content" ribbon by signing with a commercial security certificate?

Do you see the regular ribbon with the attached? If so, then yes, maybe you accidentally changed something in your settings. If so, try it again on another computer just to compare the results.

With NoRibbon.accdb I only see the File and Customize QAT buttons. So yes, it's working.

The isladogs ribbon xml provided on the security web article works as well. Clicking File causes a UI Runtime error but this doesn't appear to affect anything.

I am using the SimpleFileNoQAT xml along with DoCmd.ShowToolbar "Ribbon", acToolbarNo. If the database file is opened from an untrusted location, only the File and QAT buttons are visible, then the entire ribbon is removed after "enable content" is selected.

 

[isladogs]

@tomasm
I covered most of the above points in my reply to your email that I sent on Sunday morning. As it seems you haven't seen that, I'll repeat some of the comments below:

I don't see the "enable content" banner but I do get the "security risk - microsoft has blocked macros..." banner and the autoexec macro is prevented from running. (I get this banner in the unlocked version as well.)

The unlocked version has no security & is designed to show all standard Access messages.
I would have expected the locked version should open with no security messages even from a untrusted location. You also don't need to unblock it after downloading to remove the 'mark of the web'.

However, your comments about the autoexec macro are wrong . The trust center statements are misleading. Certain 'safe code' such as message boxes (etc) will run from an autoexec macro even if a file is untrusted. In fact, it runs BEFORE the security banner appears. For more details, see my article

Debunking Myths about the Autoexec Macro

This article debunks several widely believed fallacies about the autoexec macro used at startup

www.isladogs.co.uk

However, autoexec macro code deemed unsafe is blocked until the file is trusted.

I read through the article again and I don't see that the trusted file/location topic is explicitly addressed. Are you preventing the "enable content" ribbon by signing with a commercial security certificate?

Yes. I used a commercial code signing certificate in the locked version. I mentioned this in the Downloads section of my article (item 12) as stated below

The VBA project in the above file has been digitally signed with a code signing certificate.
This means the file is trusted. You will not need to unblock the file to remove the 'mark of the web'

Signing a project offers several advantages:

• the file is automatically trusted so the MOTW doesn't get applied
• for the same reason, the security banner doesn't appear even in an untrusted location
• the code is protected from changes.

For full details, see my article

VBA Project Signing - 1

This article explains how to use the new VBA project signing feature in Access. It also compares the use of this feature with using trusted locations when distributing your Access applications

www.isladogs.co.uk

The isladogs ribbon xml provided on the security web article works as well. Clicking File causes a UI Runtime error but this doesn't appear to affect anything.

As you say, the UI error doesn't affect functionality but I'll investigate this.
Ticking the client setting Show Add-In User Interface Errors is useful for diagnosis purposes but offers no benefit to the end user. I recommend leaving it unticked except when testing ribbon code