How to protect my tables and queries in access from importing

[isladogs]

Related to the main point of this thread, I've just posted a new thread at Access User Voice: Prevent tables & queries being copied in ACCDE files

For anyone affected by this issue, I suggest you vote for this idea & add a comment

 

[arnelgp]

here is a sample of No tables, No queries
on the Front End (FE) database.

the tables and query (only 1 on this demo) resides
of the BE.

the BE is renamed BE.abc, instead of .accdb.
the reason for this is that i could not
find a solution of setting the Recordsource
of the Report (Report1), when the BE is encrypted
with password.

therefore my solution is not having the BE to
be password encrypted, but instead renamed the
extension to something else.

to be more secure put the BE to any hidden folder
or to Windows folder so that nobody will suspect
of its existence.

For this demo both db should be in same folder.

see the code in the FE, on the frmParent (Load Event)
and on the Reports, on it's Open Event.

its a crazy idea but it worked.

 

[isladogs]

This is a response to both arnelgp's very interesting idea and to a PM from ahmedaliazad

The attached zip file LinkedNoTables.zip contains:
a) FE.accdb with no tables, 1 form & 1 report ONLY
b) BE.accdb encrypted with password ridders. It has 1 deep hidden table Table1 (but a normal hidden table would work equally well)

There is no module code in either database

For the purposes of this example, both files need to be in the same folder

The syntax that arnelgp couldn't remember for linking to a password protected table in an external database is:

"SELECT * FROM TableName IN '' [MS Access;PWD=password;DATABASE=" & full path to database];"

The same syntax works for linked queries

So in this case I used the following as record source for both form & report:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "SELECT * FROM Table1 IN '' [MS Access;PWD=ridders;DATABASE=" & CurrentProject.Path & "\BE.accdb];"
End Sub

Both form & report show data from the deep hidden table

Using Access, the only trace of its existence is in the BE system tables and the above code for the form/report in the FE.
Unless users know the BE password, there is no possible access to that table from any external database.

Add in all the other methods of securing databases (hide nav pane/ribbon/application window/taskbar ; disable shift bypass etc) that I've explained in previous posts & its beginning to seem very secure indeed.

However, the FE code also exposes the BE password.

If the FE is an ACCDE file, there is no access to the VBA code from within Access.
However, even with an ACCDE file, it is still possible to view the code externally by employing a different approach that doesn't use Access.

So you also need to encrypt the ACCDE FE with a password to prevent external hacking of your code.

After doing ALL of that, I believe it really is as secure as its possible to make an Access database

The quickest way of setting this up is probably as follows:
1. Encrypt the BE using a password
2. Link to the BE tables from the FE
3. Create the forms & reports using the external tables as the record source in the standard way.
4. Replace the record source for each form/report with code like that above
5. Remove the linked tables from the FE
6. Hide (or deep hide) the tables in the BE
7. Encrypt the FE with a password

NOTE: I think the method would also work just as well with a standard hidden table as arnelgp demonstrated

In fact I think ahmedaliazad can stop worrying about creating deep hidden tables

If anyone can see a flaw in this approach, please do let me know!

Even so, let me reiterate this point:
No matter what you do, Access databases can NEVER be made 100% secure
A capable and determined hacker can break any Access database given time

UPDATE: 23/08/2018
Attachment removed - I found a security flaw!
Updated version added to post #55

 

[ahmedaliazad]

Thanks,
It is the great way to secure the table and query of my database,
I have another question by using link table and query the speed of opening form and report is like before "when the query is in frontend file"?
If I share the backend file on network on server and the frontend on clients should I write the name of server in the path of backend db inside vba?

 

[isladogs]

Glad you're finally happy!

How are you connecting the BE to the FE?
Linked table wizard? ODBC? DSN less connection strings?

The linked table wizard creates the connection strings for you.

I use DSN less connection strings. This approach requires the server path to be part of the connection string.

As I don't use ODBC data sources, I can't advise about that method.

However you connect, it is likely the database will perform more slowly when linked over a network than is the case on your local workstation

NOTE: I've just edited my previous post to include additional information

 

[ahmedaliazad]

Dear ridders,

which connection shall I use between FE and BE? I mean which one has the high speed,

by default I used linked table and I don't know is good way or not

 

[isladogs]

All methods use linked tables and all are AFAIK roughly the same in terms of speed.

If you have more questions about linking tables in split databases, please start a new thread.

 

[ahmedaliazad]

Dear ridders I did the linked query,

But I have three problems which are:

1. the textbox that I putted to count the record on form is #Error, before using linked query I wrote in control source of textbox
"=DCount("*","QueryName")"
, but now what should I write because I deleted the query in FE.

2. I have some combobox which I using them to find record on my form, the row source of it is like
"SELECT [CompanyQ2].[Cname] FROM CompanyQ2; "
I moved the selected query into BE, so what should I write in the row source of combobox to see the query in BE,

3. When I changed the record source of forms to linked query in BE the size of form was changed and it will show one record, each time I should scroll bar one by one,

thanks for help

 

[isladogs]

For one solution to questions 1 & 2, see updated example attached.

This time, I've included a continuous form as well as a single form
If you want to use a datasheet instead, this would need to be a subform with the combo and totals textbox on the main form

Basically use the same idea for the SQL in the combo row source (or if using a listbox) as for the form and report.

I've used a recordset count for the total records

For item 3, change the size of the form in design view then save it.
It should open at the same size each time.

As previously requested, any more questions. please start a new thread.
Doing so will also make it more likely that others will contribute

UPDATE: 23/08/2018
Attachment removed - I found a security flaw!
Updated version added to post #55

 

[ahmedaliazad]

Thanks,
but what is the difference if I write the path of linked query in record source at open form event and default record source field under Data tab?

 

[isladogs]

Please start a new thread.

 

[The_Doc_Man]

ahmedaliazad -

My response is focused on your question about which linking method is faster.

In essence it is not how fast a method you use but WHEN you use it that is more obvious in terms of speed. I will try to explain.

Access uses one of two protocols to link to a back-end. If the back-end is in Access, it uses Server Message Block (SMB) protocol, which is a member of the TCP group of protocols under TCP/IP, which is THE family of protocols that everyone uses.

If the back-end is an active SQL engine such as MySQL, SQL Server, ORACLE, and a few more of that type, they use a connection protocol sometimes called SQLnet, which is ALSO a member of the TCP group under TCP/IP. There is no mix-and-match with those. The nature of the back-end file uniquely determines which protocol must be used.

Both protocols have similar characteristics but use different "connection ports" as the "listener" side of the protocol and both use "negotiated ports" for individual network connections. They are nearly identical in that aspect of the network "handshake" that they use. So there is NO PRACTICAL DIFFERENCE between the two.

Factors that DO affect the speed? Whether the connection will be encrypted or not, and whether the chosen back-end requires a password or not.

SMB is not encrypted but CAN ride a VPN. However, if the BE is Access and is encrypted using a password then an encryptor/decryptor component is part of every transaction every time. SQLNet has an encrypted variant (different port number). However, those factors will be constants no matter how or when you make the connection.

The main difference in your perception of the speed is WHEN you establish the connection and whether you have a persistent or non-persistent "session" connection between FE and BE.

The type of connection that is apparently fastest (i.e. apparent to you, the user) is a statically bound table, where WHATEVER you are going to do, you do it when the FE database comes up and verifies connectivity to the BE file/server. So the delays associated with the connections are all bundled into the time it takes for the FE/BE pair to be ready for use. If your database is slow to be ready, it is because of these initial steps caused by the static connection.

The type of connection that is apparently slowest is a dynamic non-persistent connection where WHATEVER you are going to do, you do it EVERY TIME you open a table in the BE file/server. Then your speed is a series of speed bumps while you perform a series of handshakes for each thing that uses a recordset in the BE. It is the repetition of reconnecting each time that causes the slowness.

There are techniques that one can use that would permit you to establish a persistent connection from FE to BE, because Access will use that connection to "ride" other traffic besides the event that opened the persistent connection. That makes subsequent connections appear faster because the connection is already open and any protocol handshaking is minimized. I don't know if ArnelGP's "IN" clause can take advantage of a persistent connection because I have not had occasion to try that particular syntax.

So when you ask which factors are faster or slower:

- type of BE file (Access or SQL server) is NOT significant - though if the BE is an active SQL server and the queries are of type "pass-through" you will see a speed difference based on the AMOUNT of traffic between FE and BE. But that is again an essentially constant factor and not related to the initial connection speed.
- persistent or intermittant connection IS significant
- WHEN you make the connection affects apparent start-up time OR apparent operation time. (The old "pay me now or pay me later" syndrome.)

You are in good hands with Ridders and ArnelGP for the security side of the discussion so I don't want to take way from what they are telling you. However, I felt I could contribute to the "speed" issue by explaining the factors. That is my only intent of this post.

 

[ahmedaliazad]

Thanks for your reply,

I used microsoft access as BE?

I will put the BE on sharing folder that all clients can access over the network so I need your suggestion about connection between them,

The FE include Forms and Reports,
The BE include Tables and Queries and encrypted with password

 

[The_Doc_Man]

Your intent as described seems appropriate for an Access BE file. The combination of shared BE and distributed FE is the preferred way to go. However, there is more to it than just the FE/BE configuration.

The "connection" I mentioned depends on something I don't recall you mentioning in this thread. I recommend some kind of switchboard or dispatcher form that everyone sees. You use it to block people from seeing the infrastructure of your FE file, which would be the first method of infiltration if they wanted to hack your system.

Ridders and others commented in passing about hiding the ribbon, hiding the navigation pane, disabling the various short-cuts and shift-key bypass, things like that. With a properly locked-down switchboard, anything that anyone wants to do must be supported as an option in the "dispatcher" form, which will always be open and will hide the structure of its parent DB. I leave it to you to search this forum for the myriad articles that deal with the "Switchboard" concept. I will only discuss it here in passing because it is a wheel I don't need to re-invent.

In that switchboard form you can establish a "persistent" connection to the BE by using code to open a recordset to some "trash" table (or it could be significant in some way). The code would run when the switchboard form opens (i.e. Form_Open event). In my case, I made the back end's version number the only record in a little table, and I used it to compare the FE version to the BE version. I tested it but then didn't close it. You do not close the "keep alive" recordset until your user initiates the "Application exit" sequence according to your rules on shutting things down.

You can effectively disable the little X control in the upper right of the window by trapping the Form's attempt to shut down. The Form_Unload event has a Cancel parameter, which if you set it to TRUE will prevent the form from closing even if they click the X. (This isn't perfect since Task Manager and some persistence could still shut it down.) When I had to do this, I had an internal Boolean in a public module that was initialized as FALSE when the switchboard opened. If the user clicked my "Exit" command button, the button-click code set the the variable to TRUE. When the Form_Unload event fired, the code saw the flag had been set so allowed the Exit to happen. But if someone tried any other method to close things down, the flag would still be FALSE and the Unload would be canceled. That enabled me to perform an orderly shutdown which included closing that persistent connection.

The point here is that Access optimizes its network connections. If you open the network link to the BE and then close it again, then (using Arnel's "IN external-database" syntax) you have to log in and set up encryption for every action that opens a recordset in that BE file. But if Access sees that you have a connection already open and set up for encryption/decryption, it will "ride" the connection and not go through the overhead of opening the port, establishing the encryption, performing one transaction, and closing the port again. It will re-use the open connection. Less overhead, and that is good for you to know since you expressed concern about performance.

As to the security aspect of this: The information regarding that connection is only visible in one of the hidden Access system tables, and only for as long as that connection is open. Windows versions after WinNT conform to the U.S. Dept. of Defense standards regarding isolation and object re-use, so no other process can see that information unless they have some extremely elevated Windows privileges. In which case all security bets are off anyway.

If your switchboard or dispatcher form is robust, then nobody can look INTO your process and your user can't break out of the switchboard to "peek" while running the process, which means that the dynamic connection is about as secure as is possible.

I don't know if this has been enabled for MS Access, but here is how you add security to the dynamic network link between client and server processes:

https://docs.microsoft.com/en-us/windows-server/storage/file-server/smb-security

Since this is something you do at the server end of the connection (essentially, declare a Share folder to require encrypted connections), it would simply be part of the network overhead associated with establishing the connection. By using a persistent connection as described earlier, you would reduce the overhead for each time you open a recordset. There IS a performance warning in the article because ANY time you involve encryption, you encounter delays. You pay a price for increasing security and the currency of that price is performance.

Let me be clear:

If you use Arnel's method which puts a password on the database to effectively encrypt it, you are addressing security at rest. This is meant to stop people from reading the file on the server by hacking into it. If they steal a copy of the file but don't have the password, they have to hack it, which could take a long time.

If you attempt to use SMB encryption on the server-side Share folder that holds the BE file, you are addressing security in motion. This is meant to stop people from running a "sniffer" or other network eavesdropping method to grab your data as it passes across the network. This MIGHT not be necessary if the BE is encrypted because I believe that decryption occurs AFTER the data reaches the FE file. In which case the network SMB encryption would be overkill, i.e. encrypting an already-encrypted transfer.

Note also that the method requires that the BE file would reside on a system running something like Windows Server 2012. I think all of us would be interested in knowing if you could get Access to work this way because I know the security theory but I have not seen anyone try this method of protection. Part of that might be that the network-level of encryption isn't necessary for encrypted BE cases. Please understand that I do not know what is transmitted across the connection in this case.

I mention this only because you are so emphatic about trying to protect your data. It might be more trouble than it is worth even for your extreme needs since it might indeed be a case of overkill. But I would be remiss if I didn't explain it to you as part of your concern over system security.

 

[isladogs]

I offered a solution to the OP's question back in posts #43 & a modified version in post #49.

I asked anyone who found a flaw in these to let me know.
Nobody did so ... but whilst working on a similar idea for another purpose, I found a loophole which meant the earlier versions could be hacked after all.

I'm deliberately not going to explain here what the loophole was.
However, I've hopefully fixed the issue(s) in the updated version (v3) attached.
I've also removed the earlier versions

In the example supplied, you have full access to code in the FE and have been given the BE password (ridders)
Obviously in a real world situation the FE would be a password protected ACCDE and the BE password would not be circulated

If anyone can see a security flaw in this latest version, please let me know by email or PM!
Don't tell the world in a future post!!

Once again, let me reiterate this point:
No matter what you do, Access databases can NEVER be made 100% secure
A capable and determined hacker can break any Access database given time

 

[gemma-the-husky]

I don't really understand the issues with a user getting at the tables.

In most commercial databases the developer will normally provide a way to reach the data tables - eg Sage ODBC Link. After all the data is the user's. Without the form design and code, they can't do so much with it.

 

[The_Doc_Man]

Dave, not to throw a stinkbomb towards the OP, but he apparently has not considered that with Windows, you can get at the data via screen capture anyway, although doing so would be tedious. If the person can see the data, they can get copies of the data using only the secure database functions that allow data display. That is because the Windows PrintScreen function isn't under Access control. It isn't functional under Access (that I recall, though it has been a while.)

Now, if he had underlying proprietary data that was never displayed but that allowed for some kind of conversion or computation, I COULD understand wanting to protect that intellectual property. But if his concern was just tables in general, there is an old security adage: The only truly secure system on your network is the one that isn't turned on at the moment.

 

[The_Doc_Man]

ahmedaliazad

Reading over this, I owe you an apology for an error of omission. There is one more step that is possible IF you / your company can do it.

If you have a private subnet within your company then be sure that your back end resides on a machine on the private subnet but for which there is no general internet access. The CLIENT machines that will use the BE can be exposed to the internet. But if there is a way to have a SERVER-side network that has no exposure to the world, you can still use ordinary router technology to isolate the machine hosting your BE. And in so doing, you wipe out huge numbers of potential external threats.

This method DOES NOT affect internal threats. But your company policies on firing or other penalties may deter those potential internal threats. It is all about risk. Splitting a network between in-house services and ... out-house services, for lack of a better metaphor, will add another layer of protection.

Your in-house IT people can tell you in a heartbeat whether this can be done on your company's network. But if there is any size at all to that company, I would be highly surprised to learn they had not already set up such a split network.

Again, I apologize for overlooking this method of help to secure your database BE.