How to protect my tables and queries in access from importing (1 Viewer)

ahmedaliazad

Registered User.
Local time
Today, 13:42
Joined
Aug 10, 2018
Messages
29
Dear all,

I created application in access database for markets, but I have a big problem which is I cannot protect my linked tables in front end file from importing to new access file,

I tried to encrypt front end access with password but is not fair because when each user trying to open it, password required to open so I shall give password to all users at this situation if anyone of them try to import table it will ask for password and already they have the password,

Please can anyone help me?
 

MarkK

bit cruncher
Local time
Today, 03:42
Joined
Mar 17, 2004
Messages
8,178
If you give users access to data, there is a chance they will copy it. There is no reasonable way to prevent this.

My 2c? Only be trustworthy. Only deal with people you trust.

hth
Mark
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:42
Joined
Feb 19, 2013
Messages
16,553
depends on what you are protecting against - you can password protect the back end rather than the front end so you don't need to give your users a password and they cannot copy the tables - however anyone with a little bit of access knowledge will know where to find the password to get round this.

And there is still nothing to stop a user copying an entire backend file. If that is your concern use a different back end rdbms such as sql server (express is free)
 

ahmedaliazad

Registered User.
Local time
Today, 13:42
Joined
Aug 10, 2018
Messages
29
Thanks for reply,
but I mean how to protect my application database? are there any way after encrypting the front end file write the encrypted password inside vba code for each form?
So the user when open the front end the access not ask user for password only login through access interface that I created,

because by using this way I can prevent users from importing my tables, queries,

Thanks for your help
 

ahmedaliazad

Registered User.
Local time
Today, 13:42
Joined
Aug 10, 2018
Messages
29
Dear CJ,

If I change my back end access to SQLServer express, should I install sql server express on server and all computer clients or server only?
how to share my back end in express sqlserver on network to be accessible from all front end file
 

Insane_ai

Not Really an A.I.
Local time
Today, 06:42
Joined
Mar 20, 2009
Messages
264
You can make it difficult but you can't make it impossible to get at the tables.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 10:42
Joined
Feb 19, 2013
Messages
16,553
with respect, security is an issue that should have been designed in from the beginning of the development. Bolting it on later will be a hash up at best.

You need to develop a security policy defining what should be protected, why it needs protecting and from whom, doing what and by what methods. Once defined, you will almost certainly find you will need to rebuild some or all of your app to implement.

No security is 100% secure given enough time and resource by a determined user.

Your approach at the moment is the equivalent of simply using 'on error resume next' in your coding to stop errors from showing, rather than handling them properly.

In answer to your question, sql server should be installed on a server, each client will need to have the appropriate ODBC driver on their machine. For more information about how to do this, please make use of google.
 

Cronk

Registered User.
Local time
Today, 21:42
Joined
Jul 4, 2013
Messages
2,770
In some systems I have incorporated some level of security. It won't protect data from any sophisticated user though.



Firstly, hide the navigation panel in the back end. Anyone opening the file will see a blank screen and go no further.


For the front end I have a table of users with their logon network name and if necessary a level of access ie read only, read/write, admin who can change lookup tables and change user permissions.


Anyone new opening the FE has their network logon recorded and a message displayed saying necessary for an admin person to give them access, before bumping them out. Authorised users don't need to login or remember passwords as it's based on their network logon name.



As I said at start, it's a very low level of security but OK for a group of users who do not have any real knowledge of Access workings.
 

Cronk

Registered User.
Local time
Today, 21:42
Joined
Jul 4, 2013
Messages
2,770
BTW, can anyone tell me why my submitted responses have multiple lines between paras when I only type a single <enter>?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:42
Joined
Feb 28, 2001
Messages
26,996
@ahmedaliazad,

There is NO repeat NO way to stop a determined user from getting to the data tables IF you allow that person access to the tables via the front-end. As the others have stated, permission to read those tables is all that is needed to extract from them, and you already need a lot more than just permission to read in order to use an Access database. So in essence, you are not going to be able to protect your data. All you can do is make it more difficult. That's IT.

The reason is that tight security was never built into Access using the ACE engine, and the modern versions of Access allow you to use the .MDB (JET) files but don't implement the user level security that used to be built into JET. They don't implement it because it never really worked right anyway. It was buggier than a Louisiana swamp.

If a person has the FE file and ANY KNOWLEDGE AT ALL about Access, they will be able to eventually import things from the FE that will show them the connection string. Then they can insert their own code and queries to use that same string and suck up every last drop of data you've got. It's a fact of life.

The risk/reward factor with Access includes that you need to assure that the users are good people who know to not diddle with your stuff. If they are corporate employees then you have to have management issue a statement such as "Abuse, misuse, or unauthorized redistribution of corporate data will be a firing and lawsuit offense." Or something like that. If you do not have the comfort of having only corporate employees who can be fired for wrong-doing, then you have little or nothing except trust.

Any clever thing that you build into your FE to obscure things will be the problem, since the FE is vulnerable and that is where you would implement your clever approach. If they can see it, they can read it. If they can read it, bye-bye, cleverness.
 

ahmedaliazad

Registered User.
Local time
Today, 13:42
Joined
Aug 10, 2018
Messages
29
Already I used some tools to protect my db from using on another computer by using serial number of hard drive and making accde files, all are working well,

The things that I need is prevent users from importing my table into new blank database that all I need?

are there any way?

I tried to hide tables and queries is working but when on new blank database check show all hidden files it will show all for importing this is the real problem that I facing,

Thanks,
 

isladogs

MVP / VIP
Local time
Today, 10:42
Joined
Jan 14, 2017
Messages
18,186
As several others have already stated it is impossible to prevent a capable and determined hacker from breaking the security on any database given sufficient time.

What you can do is create sufficient hurdles so that it really isn't worth the effort needed to do so.

You can certainly do all the following and more:
- password protect your db and distribute as ACCDE so the code isn't available
- store your data in a SQL Server backend
- deep hide tables and/or make them read only.
- do all queries as SQL statements in code.
- disable the shift bypass
- hide the ribbon, navigation pane and application window etc etc

BUT there are still ways around viewing/copying your data for those who know how.

For additional information, I suggest you read this current thread at another forum which has been covering much of the same ground: http://www.accessforums.net/showthread.php?t=73182

You might also find it useful attempting my security challenges - see the links in my signature line below. Doing so may help you think about weaknesses in your security and help you learn some new skills
 

isladogs

MVP / VIP
Local time
Today, 10:42
Joined
Jan 14, 2017
Messages
18,186
BTW, can anyone tell me why my submitted responses have multiple lines between paras when I only type a single <enter>?

It seems to be a forum 'feature' when text is first written in another application such as Word or Notepad and pasted into the reply window. Not just at this forum either.

Having lost many lengthy replies when I switch to another window part way through to check something, I now always copy & paste all lengthy answers and always get the double spacing. Just remove it manually
 

ahmedaliazad

Registered User.
Local time
Today, 13:42
Joined
Aug 10, 2018
Messages
29
Dear ridders,

how I can do the following:
1.deep hide tables.
2.do all queries as SQL statements in code.

if I stored backend data in sql server it will increase or decrease the speed of my application?

I already protected my back end with password but my problem is front end link tables and queries can be imported to new blank database,
 

isladogs

MVP / VIP
Local time
Today, 10:42
Joined
Jan 14, 2017
Messages
18,186
Dear ridders,

how I can do the following:
1.deep hide tables.
2.do all queries as SQL statements in code.

if I stored backend data in sql server it will increase or decrease the speed of my application?

I already protected my back end with password but my problem is front end link tables and queries can be imported to new blank database,

Yes I'm fully aware of what you are asking. Many others have asked the same thing over the years and got the same answers.
That's why I referred you to the 3 links in my last post. Have you read them through in detail? Have you followed up ALL the suggestions made?


1. Deep hidden tables cannot be seen in the navigation pane even if show hidden objects & show system objects are both ticked
I've been developing in Access for almost 20 years & only stumbled across the method of doing this a few months ago.
If I told you how to do that, I'd also be telling everyone reading this which means their value for security would be severely diminished. You'll need to do some extensive research in Google / various forums and, if you succeed, you should also discover how the data can still be viewed/copied ... but only if you know how!

2. There is no equivalent method of deep hiding queries.
However all action queries can be executed in VBA as SQL statements. Its a straightforward method and is widely used.
e.g. CurrentDB.Execute "INSERT INTO ...." or whatever

SELECT queries cannot be executed but can be done in VBA using query defs.
There's lots of info online about this

3. Using SQL Server gives many advantages.
For example: larger file sizes possible/greater security built in and much more besides
BUT converting your BE to SQL will not necessarily make it run faster.
Indeed unless you optimise how it is used, it may actually be slower.
Learning the intricacies of SQL Server is a large project ...and I'm no expert in anything other than the basics.

If this is that important, you will need to spend significant time researching before time spent implementing and thoroughly testing.

BUT to repeat no database can ever be made 100% secure no matter what you do
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:42
Joined
Sep 21, 2011
Messages
14,038
BTW, can anyone tell me why my submitted responses have multiple lines between paras when I only type a single <enter>?

Can't tell you why Cronk, but it has been happening to me for quite a while now.

I use Chrome at home and Firefox at work, and it still does it.
 

ahmedaliazad

Registered User.
Local time
Today, 13:42
Joined
Aug 10, 2018
Messages
29
Dear ridders thanks for your reply,

can you send me the code to hide deep tables to my email if possible?
 

isladogs

MVP / VIP
Local time
Today, 10:42
Joined
Jan 14, 2017
Messages
18,186
Dear ridders thanks for your reply,

can you send me the code to hide deep tables to my email if possible?

Good try. Sorry but no for the same reasons as before.
Check all the links I gave. Do additional research yourself.

However, the following may help you understand the issues/limitations better.

Attached is a simple example database with 2 deep hidden tables & 1 form that I posted at the link supplied before
The form has a button which will toggle the visible state of one of these tables. The table is READ ONLY.
It is an ACCDE file so the code is not available for viewing
However its deliberately not fully locked down in this case so you can play with it.

If made visible, the table can of course be viewed/imported from another db.
Unfortunately, the method of doing so also works from another database...if you know how.
In fact, there is a way of viewing it even when it's deep hidden...again only if you know how.

Database security is only as good as the weakest link

There is another deep hidden table - see if you can work out its name & contents
If you succeed, please send me a PM - do NOT post the solution here
 

Attachments

  • DeepHideTablesExample.zip
    28.5 KB · Views: 228

ahmedaliazad

Registered User.
Local time
Today, 13:42
Joined
Aug 10, 2018
Messages
29
Dear ridders,

what you mean PM?

How I contact with you? I found some information in your db
 

Users who are viewing this thread

Top Bottom