Locking Down Databases

[Morphius]

Okay so here's what I did and it seems to have worked...

1) Ran the Debug/Compile on the VBA coding (found an extra "end if" that didn't belong to anything)

2) Clicked on the "Office" button

3) Went to Access Options

4) Clicked on Navigation Options and unchecked Tables, Queries, Forms, Reports, Macros, Modules.

5) Then unchecked...
-- "Use Access Special Keys"
-- "Allow Full Menus"
-- "Allow Default Shortcut Menus"

6) Then converted to ACCDE (which it did without any errors this time).
.............................

Going back into the database, I could not use Shift, nor could I see the navigation pane. All shortcut keys were disabled along with the right-click ability. The only menu options being displayed were those directly associated with text editing and print/export options.

Using a blank database...I could access (for import) the Tables and Queries but NOT the Forms, Macros or Modules as they were completely grayed out.

I also tried opening the db in Notepad and a whole bunch of garbage was displayed...so if they really wanna take the time to sort through that mess...then they can have it.

WOOOHOOO!!!!!

So then I guess the only item remaining would be....if I wanted to password protect the Tables to prevent them from copied, I could password encrypt the BE...but would I be able to bypass the password using code?

Not code that would be for each table when it's used or accessed by the FE, but simply for the entire BE?

This code works for the table access but is impractical for my use...

Dim td As DAO.TableDef
Set td = CurrentDb.CreateTableDef("Table1")
td.Connect = "MS
Access;PWD=myPassword;DATABASE=C:\M2Dev\pwProtecte d.accdb"
td.SourceTableName = "Table1"
CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh

 

[Thales750]

As a follow up to all this conversation, 2007 now offers a free runtime version availible at Microsoft.com.

So you can now offer your DB to people without Access and it will have compiled securety.

 

[Banana]

If you would like to password your BE, I *believe* (e.g. I have not actually done this) you could set a database password, then use OpenDatabase method in code with your password retrieved somehow*, and passing it in the argument. Once OpenDatabase is done, you can then refresh the links which should work as Access caches the password for the session. See if it works for you.


* If you do decide to use a password and store it in database understand that this is a security flaw. If you store it as a VBA string, a constant, or in table, or properties it can be read plaintext from Notepad. There are few workarounds, however, with their own ramifications so you will have to decide if they are acceptable for you needs:

1) Create your password by concatenating string based on object names like this:

MyPassword = Me.txtUserName.Name & Me.Name & CurrentDb.TableDef("MyTable").Name

This cannot be read plaintext except for "MyPassword" and the concatenate is compiled into machine code so there's nothing to extract out.

2) Use a hash. There are several VB/VBA code out there that allows you to use hashing algorithm. Of course, you still need to have something to hash and can count on it to get hashed.

3) Set up a pseudo log in for your users and hash their password without their knowledge. This is more secure than #1 and #2 because the password is never saved, and users can even know that their password factors in somehow to unlock the back end database, they would have to know the specific algorithm being used, whether it's salted, which make would in all probability take even longer, too long that you'd have discovered the fact they were trying to break in.


HTH.

 

[Morphius]

As a follow up to all this conversation, 2007 now offers a free runtime version availible at Microsoft.com.

So you can now offer your DB to people without Access and it will have compiled securety.

Right, but the runtime's don't come with any design capabilities either so runtime users would obviously not be a concern. The concern is regarding users with or access to full A2007 and using it pirate the design.

 

[Morphius]

If you would like to password your BE, I *believe* (e.g. I have not actually done this) you could set a database password, then use OpenDatabase method in code with your password retrieved somehow*, and passing it in the argument. Once OpenDatabase is done, you can then refresh the links which should work as Access caches the password for the session. See if it works for you.


* If you do decide to use a password and store it in database understand that this is a security flaw. If you store it as a VBA string, a constant, or in table, or properties it can be read plaintext from Notepad. There are few workarounds, however, with their own ramifications so you will have to decide if they are acceptable for you needs:

1) Create your password by concatenating string based on object names like this:

MyPassword = Me.txtUserName.Name & Me.Name & CurrentDb.TableDef("MyTable").Name

This cannot be read plaintext except for "MyPassword" and the concatenate is compiled into machine code so there's nothing to extract out.

2) Use a hash. There are several VB/VBA code out there that allows you to use hashing algorithm. Of course, you still need to have something to hash and can count on it to get hashed.

3) Set up a pseudo log in for your users and hash their password without their knowledge. This is more secure than #1 and #2 because the password is never saved, and users can even know that their password factors in somehow to unlock the back end database, they would have to know the specific algorithm being used, whether it's salted, which make would in all probability take even longer, too long that you'd have discovered the fact they were trying to break in.


HTH.

Very interesting...I'll explore this area more and let you know how it goes.

Having said that, I wish to thank everyone who contributed to this thread. All of you made is so much easier for me to locate the answers I needed. It's been very much appreciated.

Thank You All.

 

[HiTechCoach]

You may also want to check out this:

vPPC = Virtual Password Protected Connection

http://premierdatatech.co.uk/resources.aspx

At the bottom of the page you will find:

Microsoft Access 2007 - Security Toolkit
A toolkit has been developed that can be used to provide user/group level security with the new Access 2007 file format. Please feel free to download it and use it for free. We welcome any suggestions for improving it. Download from here.

and

http://premierdatatech.co.uk/supersededresources.aspx

Microsoft Access 2007 - Thoughts on data security in Microsoft Access 2007.
Microsoft has removed workgroup security (aka user-level security) in this new version of Access where the new format file (.accdb) is used. They have beefed up the database password and encryption, but if the user knows the database password (and they must to be able to use it), how can the developer stop them accessing the data directly (and don't think you can rely on Navigation Pane restrictions!)? Here are some thoughts on how this can be done. The document below explains the reasoning behind a method called vPPC Enhanced (vPPC = Virtual Password Protected Connection) and was written in late 2006. It is included here for background information only as the vPPC Toolkit supersedes it http://www.pdtl.co.uk/Access2007/Access 2007 vPPC.pdf

 

[Thales750]

Thanks Pat,

That is a shame though; because it seemed like a nice icing to the distributable cake.

Why does Microsoft continue to almost do things right.

 

[Banana]

That's because runtime does nothing in altering the file itself, only make less functionality available for the file to use, roughly speaking.

You still can convert it into MDE/ACCDE to render forms/reports/modules immune to any edits or monkeying around.

HTH.

 

[April15Hater]

Oops, did not see there was a page two. D'oh!