Solved

Trying to come up with a way to limit access to data in an Access file?

Posted on 2016-08-30
21
45 Views
Last Modified: 2016-09-01
Experts,

This feels like a very slippery slope, but am wondering if it's possible. I have an Access file that is linked to a live database. The live database is part of a web app that I own and manage. There are a couple thousand users of the web app that can only see their data. I was wondering if it's possible for me to create a duplicate Access file that includes all linked tables but only one query and limit access to only view the query. The query would have the criteria set to only show this one customers data. My preference would be that they not even be able to open the Access file. Then I would create an Excel file that is linked to the query. They could simply refresh the Excel file to get current data. If I required a password to open the Access file, I know I can save the password in Excel, but believe the password would not be secure there.

I know this situation is perfect for an API, but we are not ready to go down that road just yet. I'm looking for a quick, but secure, fix.

Thanks,.
0
Comment
Question by:dlogan7
  • 9
  • 7
  • 3
  • +2
21 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41776665
Why does the MS-Excel workbook need to go via the MS-Access database?

Can it not retrieve data from the live database (technology/platform unknown) directly?

...and yes, you can password protect the MS-Access database, & hide all tables/queries except the one you wish the MS-Excel workbook to use.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41776719
You haven't told us what you are trying to accomplish.

Access as a tool to link to cloud based databases is flawed in many regards, starting with the user being required to have an Access license.  The Access runtime only works if you create an application using forms and reports.  Using the runtime, a user would not even see the navigation pane and so not be able to run any query aside from the security issues.  Then there is the slowness issue.  Remote connections to SQL Server or even Azure from Access are slow.

If what you are trying to do is to export the client data to a file that the client can manipulate, a .csv file is a better option.
0
 

Author Comment

by:dlogan7
ID: 41776776
The client wants to show on their website, results that are generated from data from my web app. So, as I mentioned in my original post, an API would be the preferred option. However, we are unable to go down that path at this time.

In my discussions with the client, I suggested this idea of me supplying them with a protected Access database that would then have an Excel file connected to the query that would produce their requested data. They will setup something that would have the data refreshed maybe once per night. I just need to make sure they have no way of either editing or viewing other data.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41776826
Access cannot be locked down to prevent hacking.  If other client's data is in the database, you are taking a dangerous route and open yourself up to a lawsuit if something goes wrong.  I would create a view that shows only this specific client's data and assign it to a different userID/group and a separate schema than what the rest of the tables are assigned to.  You do not want to give the user credentials that will allow him to create his own access app and link to the tables directly.  You need to restrict the damage that can be done by allowing direct access to your database.

If all the Access app can link to is the locked down view, you are safer but not safe.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41776831
Well, why two-step?
You are going to supply an Acces database with EVERYTHING and then Excel with only SOME THINGS
Why not just supply the Access file with ONLY the stuff the client needs.

It's six of one to blow in full tables, and a half-dozen of another to blow in tables based on limited queries.

What technology to you intend to use to creat these files?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41776890
Do they currently use jquery?
0
 

Author Comment

by:dlogan7
ID: 41776917
PatHartmon,

That's why I am coming to ask the experts. I don't want to provide someone access to other users data. The only way I will even think about doing it this way is if I am convinced it's safe.

Nick67,

The reason for two-step is because it's sort of how I have things setup on my end. I use Access to link to the live database. From time to time, I have customers ask me for reports in a manner they can't get from the web app. So, I will create their report in a query and then simply pull it into Excel. I was simply thinking there might be a way for me to do something similar for this customer that wants to be able to have a "live link" to their data. The Access file only has linked tables to the live database.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41776934
But, as I said, you cannot expect that the Access app will filter out only their data.  Once you give them the credentials in an Access database, they can do whatever they want by breaking into the Access db and getting the credentials which are in plain text.  That is why you need to create views for them that include ONLY their data.  Then the Access app can link to the views.  If they get the credentials for the views, that doesn't do any damage since all those credentials get the user to is his own data.  Also, you can make the view specifically not updateable to prevent an accident.  Linking Access to a server side table, the linked table will normally be updateable as long as Access can identify a unique index or primary key.
0
 

Author Comment

by:dlogan7
ID: 41776938
aikimark,

I'm not familiar with jquery and don't know if they have it. I just Googled it and not real sure what I am looking at. I've never branched beyond Access, so I am very limited in this type of knowledge. Several years ago, I hired a firm to build my web app. So, I'm sort of out here on my own managing it now.
0
 

Author Comment

by:dlogan7
ID: 41776958
PatHartman,

"That is why you need to create views for them that include ONLY their data."  That's what i am wanting to do. However, Access is the only way I know to get started. I was hoping there was a way to protect all of the credentials within Access and then allowing them to only be able to connect to the query that is limited to only show their data. My original thought was to do the "Package and Distribute" solution that's built into Access. However, after taking a look at that, I don't think they would then be able to link to the query.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:PatHartman
ID: 41776973
NOTHING can protect the credentials once they are stored in an Access database or an Excel workbook.  All you have to do is to open the .accdb using a text editor and you can easily see the connection strings as long as you know where to look.  That is why I keep going back to the view.  You can only truly secure the data at the database engine.  Once you allow people into the unsecured database, they can do anything and view anything that the credentials allow.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41777104
My two opening questions in "ID: 41776665" say "Hi!" :)
0
 

Author Comment

by:dlogan7
ID: 41777176
fanpages,

Sorry for not specifically replying to your questions. I will do that here:

Why does the MS-Excel workbook need to go via the MS-Access database?  Because I don't know how to connect to the main database via Excel. My developer helped me set up Access a few years ago. All tables from the main database are linked in Access.

Can it not retrieve data from the live database (technology/platform unknown) directly? I don't know. I had the web app built a few years ago and am now on my own to manage the darn thing. Zero knowledge about technology/platform. I'm working to get that info though.

...and yes, you can password protect the MS-Access database, & hide all tables/queries except the one you wish the MS-Excel workbook to use. Based on other replies to this question, I would not be able to safely hide login credentials to the main database. I can't go that route since other customers data would be at risk.
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 250 total points
ID: 41777203
Thanks for your responses.

Do you know the user name/password credentials &/or the Connection String that your MS-Access database uses to connect to the back-end database?

If not, you may be able to discover most of this information by opening the MS-Access database, & looking at the properties of one of the linked tables.  This may not give you the password used, unfortunately, but it may give a clue to the underlying platform of the back-end database.

If you have these details, then a direct connection from MS-Excel to the back-end database is a step closer :)

As has already been covered, if you use a View in the back-end database, rather than exposing the entire table (or tables) then this reduces the risk of a breach of data to unconnected users.  However, again as discussed above, anybody with the knowledge to do so, can circumvent that.

You could look at using multiple back-end database schemas; one for each individual client.

Or split the client data into multiple databases within the same installation, if the architecture used allows this.

Without knowing which back-end database platform you are using, this makes the whole discussion conceptual & may be somewhat misleading.

In case you had not realised, maintaining multiple sets of data for clients that are not associated with each other, within the same (singular) database was a poor initial design decision.

Are you still in contact with the original developer?  He or she may be able to provide the answers you are seeking.
0
 

Author Comment

by:dlogan7
ID: 41778085
fanpages,

Well, I've been poking around in the area where the table links were created and may have found something.

Before the original connection to the database was created I had to install MySQL Connector/ODBC 5.1. Then in Access, on the External Data tab, I selected ODBC Database. From there I had to select Machine Data Source to create the new data source.

Just this morning I was trying to learn a bit about Machine Data Source. I found that the connection credentials are actually stored in the registry. I opened the registry editor and found the section that contains this info. I took a screenshot of the information, but have blacked out any private info. I have attached the image to this thread.

Any ideas from this?
Capture3.JPG
0
 

Author Comment

by:dlogan7
ID: 41778139
fanpages,

I wanted to add another comment related to your latest post. However, don't overlook my previous post where I may have found some helpful information.

You said: "In case you had not realised, maintaining multiple sets of data for clients that are not associated with each other, within the same (singular) database was a poor initial design decision." My web app could be compared to TurboTax, in that multiple users have the ability to enter their data into the software. When they log in, they can only see their data. I'm guessing TurboTax only has one database.

The purpose for my original question is because I am trying to do something for a customer that can't be done within the software.

DISCLAIMER: My software has absolutely nothing to do with taxes. I was simply using TurboTax as an example of web based software accessed by multiple users where each users data is secure from other users.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41778215
This is a picture of the MSysObjects table in an Access database (you can show it if it is hidden in yours).  As you can see, it shows the user ID and Password in PLAIN TEXT.  All they have to do is to make the table visible using the Navigation Pane options and open this table to see the keys to the kingdom.  They don't even have to break into anything.
LinkedTables.JPG
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41778225
It would have taken less time to make the views than it has to have had this discussion.  Views are just select queries and you want them to replicate each table so they won't even have any joins.  Use the right click menu in SSMS to get SQL Server to build the select string, Add the where clause and save as a view.  Create a new user account that only has access to the views.  Use that account to link the views in the Access database.  Someone who knows what they are doing can do it in about 5 minutes per table.  Plus a few more minutes to create the userID with security and then an Access database that links to everything.
0
 

Author Comment

by:dlogan7
ID: 41778309
Capture.JPG
Mine does not show the user name and passwords. I guess the difference is because we used Machine Data Source (MDS). MDS stores that info in the registry. Doesn't really matter at this point though.

I really do appreciate you offering help. When you are referring to "views", I am assuming you are talking about the main database. If so, I have never seen it. My only access to it has always been through this one Access file. I know the service used to house it and can log in, but have no idea what I am looking at. Sounds like I need to find someone with far more expertise than me. Right?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41778693
I use user or system DSNs.  Regardless, the userID and password are stored in plain text some place where the user can get to it with minimum knowledge.  You might be able to use Access to create the views on the server by using DAO or ADO depending on your permissions but it would be much easier to use the database engine tools directly.  At its simplest, a view is just a select query.

Try the GIGs option.  Make sure you specify the database engine you are using and also the version of Access might be relevant.  This will not be an expensive job.  An hour or two at most depending on how many views need to be created. I don't want to volunteer to do this because although I know enough about SQL Server to be dangerous, I am not a DBA and databases I set up for my own testing are always unsecured so I have no experience with setting up permissions.  Make sure that whomever you hire has experience with user level security so they can do this correctly.

Basically, the views will use a hard-coded selection criteria that limits the data selected to that of this client.
0
 

Author Closing Comment

by:dlogan7
ID: 41780002
Thanks for all of the help in deciding what to do. I will be hiring someone to create a view on the main database. Since there never was a correct answer I simply spread the love to the two offered the most info. Again, thanks for your time.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

757 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

22 Experts available now in Live!

Get 1:1 Help Now