Create an unreadable encrypted data when imported to a blank database (1 Viewer)

[Tiprof]

I have an access database with a password protected backend which has been linked to the FE.

But I have noticed that, when a blank database is opened, data can be imported from my tables to the new access database which isn’t so Cuul because data is precious.

When I hide the linked tables too in my FE, the tables can still be imported.

is there a way to be promoted with the BE password when someone tries to import it into a new database

Or better still, is there an available VBA that can make the content in the table unreadable even if the person is able to import to a blank database.

Data should be readable only if the BE is opened and password is entered accordingly.

Any help with this will be greatly appreciated.

Thanks.

 

[The_Doc_Man]

Access is good but its level of security is not inherently so good. It is, after all, originally a product designed for small business, not designed for high security banking apps. If the back end is Access, the general answer is no, you cannot fully protect your data. You can make it difficult to see your data - but if the BE is to be usable, any encryption you add outside of whatever Access provides will become problematic. If the BE is something like SQL Server or one of the other active SQL engines then you can use permissions and other tricks of the trade to protect your data.

But then again, it is a general rule of data security that the more valuable your data, the more likely someone will try to get to it despite your best efforts. In essence, ANY data, to be usable, must be reachable - and there is the dichotomy. You have to strike a balance between difficulty and accessibility. If the data set is so valuable that you cannot risk exposure, pick another BE and use Access as the FE to provide your interface.

I believe you are also asking if there is a way to be prompted (not "promoted") regarding a new import operation, and the answer is - with Access? No. A direct import from the BE file into a blank DB cannot be detected by Access. OR if you find a way, the catch is that legit users will also be called out when they use the product as intended.

You CAN have certain logging operations performed by Windows using Access Control Lists with logging options, but you would then have to harvest the log files to see who did what - and your "good" users would be the bulk of your logs, so you would have to process the log files to find who touched what when - and whether that person was authorized.

 

[arnelgp]

just add password to your BE. and you open Tables and Queries using VBA (no Linked tables).

 

[Tiprof]

H

just add password to your BE. and you open Tables and Queries using VBA (no Linked tables).

Havent tried that before. How do I do that?

 

[theDBguy]

Check out this link.

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.

isladogs.co.uk

 

[Tiprof]

Increase Microsoft Access Performance on Access Databases with Linked Tables

Significantly increase performance of Microsoft Access databases with linked tables by maintaining an open connection to the backend database containing the linked tables.

www.fmsinc.com

Found something here
But don’t know how to apply them

will I have to a create a module with it or how do I do it?

 

[Tiprof]

Int

Check out this link.

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.

isladogs.co.uk

Interesting example
Let me try it and see how it goes

 

[theDBguy]

Int

Interesting example
Let me try it and see how it goes

It's actually a challenge. See if you can read (steal) the data.

 

[gemma-the-husky]

@Tiprof

Just a few thoughts.

I think the real issue is often that an Access database is completely different to say an Excel Spreadsheet.
Users don't give much thought to Excel security, other than perhaps adding a password. Excel users very rarely write any code.
Excel has sort of forced its way past IT departments, and has become unstoppable, maybe because it's not multi-user. No IT department would forbid the use of Excel.

When it comes to a database, it's completely different. A database is multi-user out of the box. An access database is just so powerful it's possible to completely trash your data without meaning to, and probably without even being aware of it. You hardly ever, most likely never, see ALL of the data in a database. Without a detailed understanding of the table structure seeing much of the data is meaningless anyway.

There's no "undo" button as there is in Excel, and any mistake is permanent. As a result, we tend to add a lot of code to prevent users, including ourselves as developers even, from doing things that can cause catastrophic damage, and from making silly errors - entering values that are outside plausible ranges, and so on. It's a completely different mindset. Protecting data is complex for reasons already outlined, but it's just one issue among many others. If you aren't already a skilled programmer, you will need many hundreds of hours development experience to fully get to grips with Access.

 

[Tiprof]

Increase Microsoft Access Performance on Access Databases with Linked Tables

Significantly increase performance of Microsoft Access databases with linked tables by maintaining an open connection to the backend database containing the linked tables.

www.fmsinc.com

Found something here
But don’t know how to apply them

will I have to a create a module with it or how do I do it?

About this example, do I just have to apply the code in the module and that’s it?. Because am not getting it properly

 

[gemma-the-husky]

About this example, do I just have to apply the code in the module and that’s it?. Because am not getting it properly

Investigate "persistent connection"

With Access backends, each time a user connects to a back end (when there is more than one user), Windows re-negotiates a connection, which can take a second or two per table. We can get around that by opening a hidden form, or a recordset to a table in the backend that does nothing other than to serve this purpose. Because the user now HAS a permanent (persistent) connection to the back end, there are no further delays incurred negotiating subsequent connections.

 

[Tiprof]

Am not making any progress here.
Please any help ?

 

[theDBguy]

Am not making any progress here.
Please any help ?

How did you manage with the challenge?

 

[Tiprof]

How did you manage with the challenge?

None of the suggestions helped me to fix the issue.
So am kind of stucked

 

[Tiprof]

Was thinking if there is a way to run vba code that checks and establishes the link between the FE and the BE while the FE is opened.
If that is the case, it should solve the problem.

can anyone suggest a possible way to do that ?

hopefully the code the achieve that and how to apply it

 

[GPGeorge]

About this example, do I just have to apply the code in the module and that’s it?. Because am not getting it properly

That article on the FMS site is not relevant to your problem. It's about maintaining a connection, which can be a performance enhancement, but not related to security.

Colin's example is the best you're going to find, I think, for security concerns, as theDocMan has explained.

 

[gemma-the-husky]

@Tiprof

Do you already have a fully working database that does what you want, and this is a bit of icing on the cake - or are you thinking about this at the end of the development. It might be better to do it the other way. Get the database working as you went, and then start thinking about complicated security issues.

The alternative might be to use something other than an Access back end but that's a whole new lot of complications.

 

[isladogs]

You may also want to read my recent article about the new VBA project signing feature.

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

When a code signing certificate is applied, any changes to code or to certain database objects such as action queries will invalidate the signature and prevent any code running.
If used in conjunction with an ACCDE file and other existing security measures, it will definitely make your data and code more secure.

 

[The_Doc_Man]

OK, Colin! I'm on an old enough version that Project Signing wasn't available to me, though I know exactly what it means.

@Tiprof - Colin's suggestion for signing a .ACCDE file will not prevent anyone from changing your code - but ABSOLUTELY will make it impossible for any changes to go undetected. Note, however, that if you sign the project, you cannot use FE-local tables in the project. All data MUST be in the BE or in a dynamic "side" DB file.

This will not affect the other part of your question, about detecting that database tables have been skimmed from the BE. Project signing can't be done effectively for a BE, but even if it could, using Access to IMPORT tables from your "secure" DB to a new, blank DB would not trigger any effects due to signing. That operation doesn't change the DB file and wouldn't trip the signature part.

 

[isladogs]

Sorry Doc but your second paragraph is incorrect.

Signing an ACCDB file will prevent anyone changing code or creating/editing action queries from an untrusted location. Doing either of those will invalidate the certificate and prevent any code running.
Using an ACCDE file will of couse prevent anyone viewing or changing code, but it won't prevent changes to data.

Neither does code signing require all tables to be placed in the BE. There are good reasons for doing that but those aren't related to the use of a certificate.

Using disconnected ADO recordsets instead of linked tables will make it much more difficult for anyone to anyone to steal data but there is no such thing as 100% secure whether the data is in Access or e.g. SQL Server