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

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.

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.

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.

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.

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?

Do they currently use jquery?

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.

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.

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.

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.

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.

My two opening questions in "ID: 41776665" say "Hi!" :)

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.

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

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.

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.

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?

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.

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.