Solved

Access to Back End Tables in MS Access

Posted on 2011-09-30
16
311 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@ 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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Nero83
Comment Utility
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
Comment Utility
I don't believe you can UNSPLIT the database.

mlmcc
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'll check it out.  Thanks again for the info.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 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

14 Experts available now in Live!

Get 1:1 Help Now