Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-10-2018, 07:53 AM   #1
ahmedaliazad
Newly Registered User
 
Join Date: Aug 2018
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
ahmedaliazad is on a distinguished road
How to protect my tables and queries in access from importing

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?

ahmedaliazad is offline   Reply With Quote
Old 08-10-2018, 08:22 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,674
Thanks: 10
Thanked 1,266 Times in 1,205 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 08-10-2018, 09:08 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,086
Thanks: 38
Thanked 3,267 Times in 3,164 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: How to protect my tables and queries in access from importing

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)

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-10-2018, 10:49 AM   #4
ahmedaliazad
Newly Registered User
 
Join Date: Aug 2018
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
ahmedaliazad is on a distinguished road
Re: How to protect my tables and queries in access from importing

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 is offline   Reply With Quote
Old 08-10-2018, 11:16 AM   #5
ahmedaliazad
Newly Registered User
 
Join Date: Aug 2018
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
ahmedaliazad is on a distinguished road
Re: How to protect my tables and queries in access from importing

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
ahmedaliazad is offline   Reply With Quote
Old 08-10-2018, 01:45 PM   #6
Insane_ai
Newly Registered User
 
Join Date: Mar 2009
Location: Cleveland, OH USA
Posts: 190
Thanks: 6
Thanked 16 Times in 13 Posts
Insane_ai is on a distinguished road
Re: How to protect my tables and queries in access from importing

You can make it difficult but you can't make it impossible to get at the tables.
__________________
I actually know a few things, just not all of them.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Insane_ai; 08-11-2018 at 06:55 AM.
Insane_ai is offline   Reply With Quote
Old 08-10-2018, 02:17 PM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,086
Thanks: 38
Thanked 3,267 Times in 3,164 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: How to protect my tables and queries in access from importing

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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-10-2018, 03:01 PM   #8
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,812
Thanks: 2
Thanked 387 Times in 383 Posts
Cronk will become famous soon enough
Re: How to protect my tables and queries in access from importing

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 is offline   Reply With Quote
Old 08-10-2018, 03:03 PM   #9
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,812
Thanks: 2
Thanked 387 Times in 383 Posts
Cronk will become famous soon enough
Re: How to protect my tables and queries in access from importing

BTW, can anyone tell me why my submitted responses have multiple lines between paras when I only type a single <enter>?
Cronk is offline   Reply With Quote
Old 08-10-2018, 03:47 PM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,990
Thanks: 56
Thanked 1,093 Times in 999 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: How to protect my tables and queries in access from importing

@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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-10-2018, 10:50 PM   #11
ahmedaliazad
Newly Registered User
 
Join Date: Aug 2018
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
ahmedaliazad is on a distinguished road
Re: How to protect my tables and queries in access from importing

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,
ahmedaliazad is offline   Reply With Quote
Old 08-10-2018, 11:43 PM   #12
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,808
Thanks: 79
Thanked 1,430 Times in 1,335 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: How to protect my tables and queries in access from importing

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 08-10-2018, 11:48 PM   #13
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,808
Thanks: 79
Thanked 1,430 Times in 1,335 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: How to protect my tables and queries in access from importing

Quote:
Originally Posted by Cronk View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 08-11-2018, 12:44 AM   #14
ahmedaliazad
Newly Registered User
 
Join Date: Aug 2018
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
ahmedaliazad is on a distinguished road
Re: How to protect my tables and queries in access from importing

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,
ahmedaliazad is offline   Reply With Quote
Old 08-11-2018, 01:22 AM   #15
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,808
Thanks: 79
Thanked 1,430 Times in 1,335 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: How to protect my tables and queries in access from importing

Quote:
Originally Posted by ahmedaliazad View Post
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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to protect tables in Access 2010? hannlapp General 1 03-06-2012 05:35 AM
importing excel to access keeping relationships in access tables emshim Tables 19 09-14-2007 01:18 PM
[SOLVED] Access, protect tables khaledtafech Tables 2 08-27-2005 01:13 PM
Protect Queries mark curtis General 1 07-08-2003 03:20 AM
Protect (hide) talbles and queries Herb General 2 01-14-2003 07:19 PM




All times are GMT -8. The time now is 09:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World