Data security of accdb files (1 Viewer)

[isladogs]

Yes its tblMDS

My question is why do the table names appear on the right? Seems like a bug.

Not a bug.
If you look at my example, neither of the tables appear in the Navigation Options dialog whilst they are deep hidden.
You have done something to make your tables listed. Perhaps you have assigned your tables to specific groups?

 

[JohnPapa]

tblMDS appears in the Navigation pane

 

[isladogs]

It will appear in the list if it is made visible in the navigation pane itself.
It shouldn't appear in the dialog box whilst it is deep hidden

 

[JohnPapa]

Hi, is there a corresponding deep hiding for queries, forms, reports, modules etc?

 

[isladogs]

No. Other objects can be hidden but ticking Show Hidden Objects makes them visible again

 

[JohnPapa]

Your article on security is very useful. Even though I have used what you mention, it is good to have everything in one place.

On a db I am working on, the Admin can deep hide the tables, hide the navigation pane and control the Shift ByPass.

 

[JohnPapa]

I have never used encryption and I will look into it now. I am guessing there would be a delay associated.

I have several split dbs so I am not sure that a db password would work. It would require deleting the existing links in the FE and recreating them and during updates, I create links programmatically to new tables (which I also add programmatically). It may get a bit complicated.

Can RC4 encryption work on bound fields, or the fields need to be unbound?

 

[isladogs]

I have never used encryption and I will look into it now. I am guessing there would be a delay associated.

I have several split dbs so I am not sure that a db password would work. It would require deleting the existing links in the FE and recreating them and during updates, I create links programmatically to new tables (which I also add programmatically). It may get a bit complicated.

Can RC4 encryption work on bound fields, or the fields need to be unbound?

1. I doubt you will notice any delay caused by encrypting data
2. Not sure what you are referring to here but nothing you've written would prevent the use of a database password. By using a password, the entire database is encrypted. Without it, all the data can be read using a text or hex editor.
3. Yes of course encryption works on bound fields. All the data is encrypted in my example app that I mentioned in post #11

Encrypted Split No Strings

This example app shows how an Access database can be made reasonably secure against hackers whilst still allowing full functionality to authorised users.

www.isladogs.co.uk

However if you are trying to lock down your database as much as possible, you really should move the backend(s) into SQL Server or another similar RDBMS

 

[JohnPapa]

Doesn't your example above use RC4 data encryption, instead of a db password?

Let me explain my case.

I have a FE and a BE, the latter is not password protected and would like to make it password protected.

I made a copy of BE to BE-Pass, opened it exclusively and I added a password.

The FE has several references to the Connection String, which until now does not have any reference to "PWD=" & strPassword , so I need to make several changes to make it compatible to reading a Password.

I opened FE, deleted all linked tables and tried to link to all tables (except SYS tables) of BE-Pass and I get "The Microsoft Access database engine could not find the object <table name>"

All files are on the same pc.

Any ideas?

 

[Gasman]

Google Search

 

[isladogs]

Doesn't your example above use RC4 data encryption, instead of a db password?

Let me explain my case.

I have a FE and a BE, the latter is not password protected and would like to make it password protected.

I made a copy of BE to BE-Pass, opened it exclusively and I added a password.

The FE has several references to the Connection String, which until now does not have any reference to "PWD=" & strPassword , so I need to make several changes to make it compatible to reading a Password.

I opened FE, deleted all linked tables and tried to link to all tables (except SYS tables) of BE-Pass and I get "The Microsoft Access database engine could not find the object <table name>"

All files are on the same pc.

Any ideas?

I’m using passwords to encrypt both FE and BE databases and then encrypting the data using RC4.

When you create links to a password protected Access database you need to supply both the file path and the password. As you made a copy of the database before adding a password, the path is different.
If you’ve already done all that, then it suggests you may have entered the wrong password

 

[JohnPapa]

Adding a db password to the BE is ok. The problem is connecting to it via code.

Without a password I use something like
db.TableDefs(intCount).Connect = ";DATABASE=" & strPath where strPath = is the pathname to the BE

If my password is "1234" do I need to use something like
db.TableDefs(intCount).Connect = ";DATABASE=" & strPath & ";PWD =" & strPassword where strPassword = "1234"

 

[Josef P.]

Simplest method:
1. OpenDatabase with password: Set dbBE = DBEngine.OpenDatabase(BackendPath, False, False, ";PWD=password")
2. Link tables from BE
Then the password can be read directly from the Connect property of the linked table.

Better option:
* Link table to unproteced (dummy) BE (same filename and path as original BE)
* To use linked tables in FE: open Database with password.
(see #15)

 

[isladogs]

Just to add to Josef's response, if you look at the Connect field in MSysObjects it will guide you on how to do this in code
Try "MS Access;PWD=" & STR_PASSWORD & ""

 

[JohnPapa]

1. OpenDatabase with password: Set dbBE = DBEngine.OpenDatabase(BackendPath, False, False, ";PWD=password")

I have 433 instances in one of my software packages of

set db = CurrentDb

where there is no reference to a Password

Do I need to change all of these...

 

[Josef P.]

A single open connection is enough to unlock the backend. Then the linked tables (without saved password) are usable - but also from outside.
Have you looked at the example from #15?

 

[JohnPapa]

A single open connection is enough to unlock the backend. Then the linked tables (without saved password) are usable - but also from outside.
Have you looked at the example from #15?

After the proper single open connection, would "set db = CurrentDb" work?

I have looked at #15 and for some reason I thought that the FE is .accde, but it is .accdb. Will review, thanks.

 

[Josef P.]

The problem is that with linked tables from an Access backend, you can't remove the password.
Therefore, in the example I link the tables from an unprotected backend (file path must be the same as the protected BE has later).

See procedure modProtectedBE.ChangeBackendPath

Private Sub ChangeBackendPath()

   Dim BEpath As String
   BEpath = BackendPath

' 1. BE => BE-orig
   Name BEpath As Replace(BEpath, "BE.accdb", "BE-orig.accdb")
' 2. BE-unlocked => BE
   Name Replace(BEpath, "BE.accdb", "BE-unlocked.accdb") As BEpath
' 3. relink
   ReLinkTable SourceTableName, BEpath
' 4. restore orginal files
   Name BEpath As Replace(BEpath, "BE.accdb", "BE-unlocked.accdb")
   Name Replace(BEpath, "BE.accdb", "BE-orig.accdb") As BEpath

End Sub

This will not work if the original backend is accessed by other users.

 

[Josef P.]

Some tests ...

Attachement files:

• FE-000: linked table with password
• FE-010: linked table without password
• FE-020: without linked tables (Form use DAO.Recordset)

HijackFE.accdb ... Code to get password from all above.

Challenge (for us):
* How could DAO methods be used in the frontend without the password being readable (from outside).

Note: Using an ADODB recordset connected to the backend to protect pwd works.

Question to MSFT:
* Why is it not possible to prevent the password from being stored in the TableDef for a linked table to an Access backend?
This works for ODBC.

 

[JohnPapa]

Many thanks Josef. Will look at the latest tomorrow morning.

You may want to add to the questions to Microsoft what I show in #22 above.