Solved

Access to Back End Tables in MS Access

Posted on 2011-09-30
16
324 Views
Last Modified: 2012-05-12
I have looked around but seem to be lost in other questions...

I have a database that contains confidential client information.  It is an Access 2003 database.
I'm wondering if there is any way to restrict access to the back end in case people gain access rights to view the folder containing the back end and look at the data in there, or even worse, modifying it.  The database is also going to be upgraded to 2010.  There is no user-level security on it at this point.  Any use in upgrading the back end to SQL?...I don't know how to do this, but I thought about it as an option.  Ideally it would be inaccessible to someone using Crystal Reports or something like that also, but first I just want to keep people from opening it using Access.  
Any ideas out there?  Thanks Experts!
0
Comment
Question by:Nero83
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 36893034
You can password protect the database and require all users to have their own login.
You can then restrict access to tables and views through user permissions.

mlmcc
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36893051
The linked table in the back-end (containing the information) could be linked to the front-end when an authorized user signs in.

If this causes problem with the operation for the other users, a dummy table could be replace the database.

Logic:
1. User starts the database:
2. User checked for validity?
        Yes: Link the table from back-end if it is not
              (after deleting the dummy table).
        No: Create a dummy table, if necessary.
              (after deleting the linked table).

Mike


 
0
 

Author Comment

by:Nero83
ID: 36893155
Thanks for the input.

@ mlmcc:  So, user-level security will work on a linked back-end?  
I heard that security is different for Access 2010...will I be in bad shape when this gets converted in a few months?

@ Mike:  how are you checking for validity on this?  This might be beyond me in setting up...not sure how to link to one and not the other based on login.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36893262
re:> how are you checking for validity on this?

We need to have the user names in a table (for now, later could be stored in much secure location where others cannot reach).

If in a module you try:

MsgBox CurrentUser()

It will give you the type of user name or ID you need to store in a table.

Then, on the start up, the CurrentUser() will be compared with the list. If they match then the table gets linked.

Mike
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36893264
I don't know if it works or not but I would think it would or you may be able to protect it in the front end.

mlmcc
0
 

Author Comment

by:Nero83
ID: 36893329
@ mlmcc: I'm looking to protect it from someone getting into it without going through the front end...

@ Mike, same deal...if it's not going through the linking process, but the tables are just getting looked at cold from the outside by someone being sneaky...

Unless I'm not understanding you...

Thanks!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36893375
To protect it that way you need to add a password to the database.

Are there any legitimate users who should access it directly rather than through the front end?
If not don't add any users except for the Admin so someone can make changes.

mlmcc
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36893413
re:> I'm looking to protect it from someone getting into
There are two sets of information we want to safe guard.
1. The list of users. (If someone can access the list of user, s/he can add another name to bypass your security). To do this, we can add the list to "ControlTip Text" property of a control on the form, for example):

- Add a text  box to your form (txtUsers):
width=0
Tab Stop= No

To its “ControlTip Text” property, add:
smith, dking, shunt, admin
(Whatever user names you want to include).

In a code check to see if you can read the user names:

Sub Test()
    MsgBox CurrentUser()
    MsgBox Forms!Switchboard!txtUsers.ControlTipText
End Sub

re:> ...getting into it without going through the front end...

Do you mean to say  ..getting into it by going through the front end...?

If they have access to the back-end then, no one can stop them to have access to it. What we are trying to do is to stop them accessing that linked table from front-end.

Mike
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Nero83
ID: 36893681
Mike,
Thanks for the details.
Actually, I meant "without going through the front end".  It seems that anyone with access to the front end would also have access to the back end without going through the front end, correct?  My understanding is that people need read/write access to that folder in order to use the database.
Maybe I should "unsplit" it to force them to go through the front end...?

Thanks.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36894311
I don't believe you can UNSPLIT the database.

mlmcc
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 36897105
With Access 2003 you can use user level security to limit the casual user. But that's about it. If someone can get hold on the database file, tools on the web priced at few dollars can break the security. Access 2010 doesn't have that feature but the backend itself can be encrypted.

Your best, fastest, and simplest method is to move the data to SQL Server 2008 Express where you can set up precise and documentable access security. Both Access and SQL Server feature wizards to make most of the conversion of data and link these to the frontend via ODBC. This method requires minimal adjustment of the frontend.

/gustav
0
 

Author Closing Comment

by:Nero83
ID: 36899562
Gustav,
Sounds good.  I looked at this online and am now slightly confused, but it is good to know that that is the kind of option I will have to go with.  So, it's an ODBC connection to the front end...interesting.  Do you happen to know if it will involve much from my IT department who are generally unsupportive of much that is new or requires work?  Maybe I'll reserve that for a different post...
Thanks.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36899776
Not much but something. A running instance of SQL Server Express (free version) or higher.

And at clients, either a DSN entry for the database has to be created, or you can use a DSN-less connection (browse for this, much info is found on the web).

/gustav
0
 

Author Comment

by:Nero83
ID: 36899828
OK.  Thanks.  
Do you happen to know how much change is required on the front end if any?  Can I just keep editing the front end in Access like I always have been, creating queries, forms, reports, etc., but after conversion of the back end just the link will point to a new source?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36899845
It's just the links that change.

Further, a few very Access-specific things may need adjustment, mostly for queries. Lots of info can be found on the web.

/gustav
0
 

Author Comment

by:Nero83
ID: 36900038
I'll check it out.  Thanks again for the info.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

30 Experts available now in Live!

Get 1:1 Help Now