External access to application.

[JLCantara]

Hello!

I am trying to secure a complex app that is almost finish. On the user side, I found the way to make my app absolutely (???) secure. But there is one problem I can't find a solution: how to prevent anyone to sneak in my app?
Of course it is possible to hide the VBA but I found nothing about the Access objects. Any one has a solution???

Thanks, JLC.

 

[KenHigg]

You used to be able to create an executable of the db. That hides everthing...

 

[JLCantara]

Hi Ken!
An executable with Access 2007? How???

Thanks, JLC

 

[KenHigg]

Mmmm. Google it

https://msdn.microsoft.com/en-us/library/bb501030(v=office.12).aspx

 

[JLCantara]

Hi Ken!

I already knew about that. OK if the user doesn't have Access but if he has it, he can create an empty app then import all your Access object since they are NOT hidden. I tried it!!! That's why I am asking!!!

Thanks, JLCan.

 

[CJ_London]

he can create an empty app then import all your Access object since they are NOT hidden. I tried it!!! That's why I am asking!!!

nothing is totally secure but you can do the following

1. forms, reports and modules - create an accde/mde file front end
2. tables - put in an external backend which is password protected
3. linked tables and queries - don't have any - create in vba code as required and delete when done - or use dsn less connections (again in vba code)

one other refinement is to put most of your code, forms and reports into a library file which can be 'hidden' somewhere, but you need to design your app like this from the beginning.

However a determined and suitably skilled user will still be able to decompile your code and discover your secrets in time.

 

[gemma-the-husky]

make an mde/accde - the tables and queries are still "open" but other objects - forms/reports/modules are secure and cannot be imported to another dbs.

personally, I don't worry too much about data being visible. most systems expose their data on request, to other applications anyway. after all, it is the user's data.

 

[JLCantara]

Hi CJ and Gemma!

Thanks for your tips!!! I will try then later today.

By the way, I tried to use VB but a critical form is quite complex (14 sub forms): my knowledge of VB is just insufficient to deal with such a monster. Note thatwith Access, the thing has been rather easy...

P.S. - Gemma: in this specific case, data are USDA and CDN nutrition DB but I substancialy modified there structure - civil servants have a strange way to make normalized DB!!!

 

[JLCantara]

Hi there, problem solved!!!!

1 - Get rid of the shift key bypass - explained by MS.
2 - Store data in a separate DB encoded with password.
3 - Remove all links to data and recreate then at run time - use an encrypted and password protected front end; this is absolutely necessary since the password of the data DB is kept as a string in the text part of your front end.
4 - Remove all the queries and store them as constants in a specific module.
5 - When a form is run, initialize your record source(s) using the constants module: this is done in Form_Load.
6 - Create from scratch your own ribbon (very easy).

Sounds complicated? Don't forget that your clients pay for your app and may want some protection on their investment... Always mention that the objects of their app are protected and that you keep ownership of them.

Of course, I wrote functions that does the job automatically.

CJ_London: app encryption seems to be very safe since it is used for server critical files. Do you thing it could be useful if I make a prototype and make it available for the public to break it???

Many thanks, JLCantara.

 

[CJ_London]

Pretty much what I said

CJ_London: app encryption seems to be very safe since it is used for server critical files. Do you thing it could be useful if I make a prototype and make it available for the public to break it???

No harm in trying, but unless there is a nugget or two of gold in there I suspect no one will bother. You will probably have more success with finding a 'friendly hacker' and paying him/her to try and break in.

 

[gemma-the-husky]

Good work - I imagine you had some fun with that!

I think the only potential downside is a slight loss of speed. because you are deleting the queries after use, access cannot develop and optimise query plans - which it uses to retrieve the data - so you may not get the most optimal results.

The visibility of a plain text password in the connect property of a table is absolutely amazing, isn't it?

 

[CJ_London]

I think the only potential downside is a slight loss of speed. because you are deleting the queries after use, access cannot develop and optimise query plans - which it uses to retrieve the data - so you may not get the most optimal results.

I do this all the time, although I tend to open a database object then use recordsets rather than create queries. Some time ago I did a timed comparison on some large datasets (circa 1m records) and found it was negligible, certainly nothing the user would notice. Of course it important to have an efficiently designed query in the first place

For me, the biggest issue on performance is network speeds.

 

[JLCantara]

Hi Gemma!

No noticeable lost of speed. Anyway I use Access since the very beginning and I noticed one think: you can always steel 1 or 2 sec. at load time because users always have something else to do: pick documents, sit more comfortably, etc.

It was indeed lots of fun. Note that I didn't mention couple thinks like disabling Shift key, ribbons, Access shortcut keys, contextual menu, etc.

Keep on the good work,
JLCantara.

 

[JLCantara]

Hi CJ!

Your wright all the way.

Thanks again, JLCantara.