FE/BE Queries, Forms and Reports Based on Location

[jdoathout]

I have a database that needs to be deployed to a kiosk for civilian drivers. The Data is HIPPA Related and needs to be restricted to that individuals needs. However it all has to come together in singular data. The Terminal would be accessed by 12 individuals Daily with different data sets. Their use would be approximately 10 minutes a day. The system is deployed on a networked drive. Basically what I need to do is limit access to client information to the transport drivers by county. Is there a way to use the query's already built into my system through VBA code to restrict viewing of data in the main system? Reports also need to be based of the specific employee so that they are limited in undoing data integrity. I plan on implementing a user password login system with privledges but not sure how to restrict data the query's produce and reports.

[Rabbit]

In terms of security, Access is one of the least secure options available. Anything you can do to secure the data can be undone by an experienced user. For example, you can easily bypass all code by holding down the shift key when opening the database.

If security is a real high priority, you should look at enterprise solutions. If Access is all you have to work with, the best you can do is to bring the security to a level at which only very advanced users can bypass all the security.

You can read this article for some ideas on implementing security.
http://bytes.com/topic/access/insigh...atabase-access

[jdoathout]

Rabbit, Read the article and is great but I dont think it addresses my needs. I am thinking I am going to have to set up variables and some how use those variables in the query as a show or don't show based on user ID? Not sure how to implement this though. Unfortunately yes, this is a government run hospital transportation system and only available through access. I have mentioned other options but government is set in its ways I guess. If you know how I might implement variables to achieve my needs please feel free to send me something.

[Rabbit]

As far as filtering data in a query, you can use parameters and the WHERE clause of a SQL query.

Whether or not that provides any form of security is another question. (The answer is no, too easy to get around)

[jdoathout]

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Think this is something like what I am trying to achieve. Not sure, will have to experiment.

[Rabbit]

What path you take depends on the specifics. There are a dozen ways to filter queries.

[zmbd]

jdoathout:
What you are attempting to do with "User Level" access was removed with V2010 and wasn't the best in the earlier versions of Access as anyone with the correct (and all too often easily obtained) workspace information could bypass the security. You need something like SQLServer, MYSQL, or ORACLE as Rabbit suggested to begin with. I think even even SQL-ServerExpress might be more secure; however, I've never looked into its functionality.

With that said, following the link given by Rabbit should be your very first step. YOU MUST UNDERSTAND THE INFORMATION PROVIDED by Rabbit FIRST.Very little, if any of your efforts with queries, forms, vba, etc.. will do you any good at all in securing the database until you understand that information!

Next, you will need to find a way to encrypt the very sensitive data such as: 37.AES Encryption Algorithm for VBA and VBScript

You will also want to split the front and backends along with password/encryption. This can be somewhat difficult and a few different ways to accomplish.

The remainder is somewhat beyond what I would feel comfortable with in offering advice as I don't know your IT nor the network setups and that would be required knowledge as we might be able setup differnt folders, and workgroups with a master DB... hmm....

[NeoPa]

It sounds to me like your question, for all its references to security etc, is really about filtering the data appropriately. I'm not saying ignore the security issues. Far from it. I just believe they are extraneous to this topic - once the question is better understood. I would go further. Like Z and Rabbit before him, I would advise strongly that you take note of this very important issue, but elsewhere than this thread.

As for filtering by a user, that depends entirely on how you plan to manage your user information. I suggest you get that sorted out first, as what you ask for depends heavily on the choices you make there.

[zmbd]

In this case, I don't think this is a simple need to filter. If I had thought it were then I would have pointed the means of setting up a select query and pointed OP towards in the direction of basic database design.

However, in this case, All of us "Experts" know that it is very easy to open the navigation pane, even if hidden, how to bypass start-up code and the like; thus, because by its nature HIPAA class information requires a greater deal of care than Access can normally provide.

Some things that come to mind:

deployed to a kiosk for civilian drivers

Kiosks by their very nature are inherently insecure and usually in a fairly heavily accessed area; however, say this is a more restricted area... we still have an issue with the shift-key and other special keys - even if an autokeys macro is in place.
V2010 has some macros that can run even when the shift key is pressed and I am currently working on that aspect with my current project... maybe a way to trap this.

Now by splitting the database, and developing an Executable, we can prevent the creation of forms and queries, we still can not absolutly ensure that no means of direct access to the tables is available... this is not easily doable in the current version of Access (2007/2010). But we can make it more difficult.

Data is HIPPA Related and needs to be restricted to that individuals needs

There must be a highly reliable means of preventing access to this data. My thought is a second encrypted backend with the connection string stored as an encrypted value in a table. User could input their password, the connection established, relevant information queried and pulled, and the connection closed. Part of the logout process would be to re-enter their password as a means of "validating" any changes and the the connections re-established for syncing data.

employee so that they are limited in undoing data integrity.

As above.

government run

Dad is Ret.USAF; StpF. Ret.USN. - counting University, I spent 23 years of my life dealing with the Military; I have an idea of what you are up against. I also know what will happen if that data gets into the wrong person's hands. Hope you have a good legal defense fund.

Now, let's talk about that security thing again....

[NeoPa]

I suppose it comes down to whether the question is about how to use Access to present the appropriate data to the user, or how to ensure that the data behind the scenes is inaccessible to everybody apart from that presented by the objects within the project. My own reading of the question leads me to believe the former is the case. It seems you believe the latter is. We'll have to see how it pans out (if ever it does).

Both are important and interesting questions, but they should not be together in a single thread if both are to be understood well, and have the effect they deserve. Each dilutes the other, which I don't believe benefits anyone.

[zmbd]

With that I can agree!

I can also see how in this case, it may require some integration of the two parts. This will be a tough one.

jdoathout: I'll leave it to you to decide the course.

[zmbd]

Sorry... couldn't wait, as I've got to get this thought down while the kids are in bed or I'll lose it in the midst of that thing called life :)

Now I'm sure that there's something I'm WAY over simplifying, and I know that I am way oversimplifying some things here, and I may even be just flat out wrong about this approach AND there's bound to be some sort of performance hit with this method.

I expect and Would like the other Experts to read and comment as this is just a "Proof of Concept," well, not even really a "Proof" at this stage; however, I lack a better word at this point.

However,
Here's something that just popped up in an old thread that I had read about years and years ago however, I'd forgotten it was even a possibility until I saw it again: Link to an ACCESS query in another ACCESS database I had dismissed this as being useful for any reason whatsoever in V2003 and earlier. Maintaining queries in the front-end was easy enough and so was/is rolling forward updates. However, the method for getting at the queries along with this question... the old brain went... hmmmmmmm

Now I haven't tried this yet on anything of any size of significance; however, my thoughts are to build your main database and all of the queries and forms that you'll need. This should be the main administrative portion of the database.
Split the database into frontend and backend.
Now As forms can be based on queries, open a second database that we'll build to be the kiosk frontend.
Using the method above, "Remote Link" to the queries that will restrict your user's information that you've built in the administrative front end...
You can build a form that takes the user name and password, feed that to your remote queries and so forth.
You can build the reports in the kiosk frontend too.

Now what I did try just now is two separate databases. TestDB1 which will serve as the Kiosk frontend and TestDB2 - This will be our backend/admin.
Now I didn't split TestDB2 as I've suggested above as I'm only looking at POC

In TestDB2 I created a parameter based query (qry_1), and an open query (qry_2) on a very simple table of 26 made-up names and an autonumber field.

Then in my TestDB1 I used the method as given in the thread above to "remote link" to the queries TestDB2.Query1 and TestDB2.Query2. I also built two bound forms, one for each query. When ran, qry_1 prompted for the parameters... couldn't get it to look at the form; however, I didn't try too hard either as this is just POC. qry_2 ran as expected returning everything; however, within the form I could set the filters etc...

Main thing, is when I tried to get at the underlying tables, it wasn't quite easy to do... I knew the path; however, with a Kiosk the user shouldn't have rights to the file explorer. You can remove the Ribbon, QAT, and close the navigation pane down so they wont have access to the queries; thus, making it very difficult to open the backend

I'm thinking that using this method along with the links to secure the database as given before, would make things much more difficult for your end-users to muck about... even making the frontend a ACCDE file after design using this method would keep the user from modifying anything...

Just a thought for the security side.
- The networking side will still need to be addressed by your IT department. That is well beyond the scope of this forum.
- SQL would be for a different thread me thinks.