How to give a fancied DBA read only version of the tables. (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 12:37
Joined
Apr 27, 2015
Messages
7,054
I’ve been asked — more accurately, tasked — to provide a government employee with access to the data in the app I maintain. I considered opening the back end in read-only mode, but that won’t work because the primary need is to run queries for analysis. He wants to crunch numbers without changing anything, and even if he understood the distinction between non-action and action queries and promised to behave, I’m not comfortable granting that level of access. The security controls I have can be bypassed by someone determined, and I’m not willing to take that risk.

A safer approach would be to create a secured copy of the back end (for example, a script that copies the BE to a controlled folder) and give him access to that copy for his analysis. I believe that provides the functionality he needs while protecting production data, but I wanted to solicit any other ideas from the group before I proceed.
 
I’ve been asked — more accurately, tasked — to provide a government employee with access to the data in the app I maintain. I considered opening the back end in read-only mode, but that won’t work because the primary need is to run queries for analysis. He wants to crunch numbers without changing anything, and even if he understood the distinction between non-action and action queries and promised to behave, I’m not comfortable granting that level of access. The security controls I have can be bypassed by someone determined, and I’m not willing to take that risk.

A safer approach would be to create a secured copy of the back end (for example, a script that copies the BE to a controlled folder) and give him access to that copy for his analysis. I believe that provides the functionality he needs while protecting production data, but I wanted to solicit any other ideas from the group before I proceed.
Are we talking accdb back end?
 
Can't you create a FE DB, that just has the Select queries required?
Make it an accde?
 
Are we talking accdb back end?
Yes sir

Can't you create a FE DB, that just has the Select queries required?
Make it an accde?
If I am understanding you correctly Paul, then no. He wants to be able to query the data QBE style to get the Program Manager answers to immediate questions. If there was a way to disable action queries then that would be ideal.

After talking to the "DBA" directly, the requirement has changed somewhat. He as managed to obtain permission to load MySQL on his government furnished laptop - not an easy accomplishment - he wants to move the data to an instance and then do his hocus-pocus from there. My plan now is to export what he needs into a CSV file that he can then import to wherever he wants.

This should be interesting because my tables are heavily normalized and will make NO sense to him unless he understands relationships and PK, FK concepts - which he doesn't.

Taking a page from the "Pentagon Wars", I will simply give him what he wants...
 
This should be interesting because my tables are heavily normalized and will make NO sense to him unless he understands relationships and PK, FK concepts - which he doesn't
Normally when you data warehouse you denormalize the data, you do not mirror your tables. That is inefficient for most reporting. You should flatten out as much as possible before exporting.
 
Normally when you data warehouse you denormalize the data, you do not mirror your tables. That is inefficient for most reporting. You should flatten out as much as possible before exporting.
Just got off the phone with him. The flattening out is already done with an exported query (Excel) that is emailed daily. What he really wants is a way to get an up-to-date copy of it on demand. Amazing how you can get to the root of the issue when you talk directly to the user. As a contractor, I avoid talking to the folks there unless the PM (GS-15) has blessed it.
Well hopefully there is documentation on how it all works?
Working on that too. When the contract was renewed, two additional deliverables are a user manual (which will be ignored, but thank GOD for SnagIt!) and a relationships diagram.

Easy day.
 
Working on that too. When the contract was renewed, two additional deliverables are a user manual (which will be ignored, but thank GOD for SnagIt!) and a relationships diagram.
You might look into the Total Access Analyzer tool by FMSINC dot com. The client might even pay for it. When I am looking at an unfamiliar database, I find this very helpful to see how things fit together. I believe Isladogs has a similar tool but I've never compared them so I can't comment on specific features.
 
I’ve only skim read the thread but it’s easy enough to make the tables read only as suggested in the thread title. You need to assign the dbSystemObject property.

My database analyzer has similar functionality to the FMS TAA app. Suggest you look at the detailed descriptions of both tools. Mine has a free evaluation version. Can’t remember if that is also true for TAA
 
If it is actually a query you are giving access to, you could set the type to snapshot
 
The flattening out is already done with an exported query (Excel) that is emailed daily. What he really wants is a way to get an up-to-date copy of it on demand
So provide him a copy of the live flattened tables upon demand. Never grant access to the production accdb to anyone, except the users.
 

Users who are viewing this thread

Back
Top Bottom