Keep Table unviewable 1

[LeanneGodney]

Hi there,

I've set up permissions for my database adding an account called Guest with no password so anyone can get into my database but can't edit the design. This is all good, but for one thing! I don't want them to be able to view any of the data either, and only use it for data entry. The data is sensitive, covering particular incidents that have needed to be logged for security purposes. No I can set a form to only allow data entry, I can hide the table. But any person with some access knowledge will know how to perhaps set the database to VIEW HIDDEN OBJECTS to get into the table.

How can I prevent people of a certain permissions group from seeing any data other than what they're typing at that point? In the permissions wizard you can tick boxes for view, add, edit design etc, but you can't have Add WITHOUGH view... Know what I mean?

This is rather frustrating, and I'm thining of simply removing any way the person can get to the database screen at all, like remove all close buttons, and remove the window menu so they can't swap or unhide the database window...

Some help?

Thanks in advance,
Leanne

 

[BSman]

Nothing is foolproof, but you might try creating a separate front end that you place in a different directory on your server. The separate front end would be as limited as you can make it. Those users would not have server access to the "normal" front end. At least you could more tightly lock down the access.

Or if you really want to lock things down, perhaps you could create a totally separate front end AND back end that would just be for data entry. These would be in different directories from your full application/data.

For this to work effectively, you would link to this separate new data database as well as the full data database with your normal (or administrative) application and move the new data from the new data database to the full database. This is an extreme solution that would require a sort of "batching" process to insure that the new data gets into the full database on a regular, timely bases. But with this method you could sure limit the ability of the smarter users to look at older data.

 

[nealvenables]

All good suggestions.

Additionally, to make tables truly hidden, even from users who select "Show Hidden objects" in Tools->Options

Public Sub HideTables()

Dim daoDB As DAO.Database
Dim daoTDF As DAO.TableDef

Set daoDB = CurrentDb()

For Each daoTDF In daoDB.TableDefs
daoTDF.Attributes = dbHiddenObject
Next

End Sub


Then, to unhide (because they'll be hidden to you also).

Public Sub ShowTables()

Dim daoDB As DAO.Database
Dim daoTDF As DAO.TableDef

Set daoDB = CurrentDb()

For Each daoTDF In daoDB.TableDefs
daoTDF.Attributes = 0
Next

End Sub

 

[LeanneGodney]

Hi BsMan and Nealv

Thanks a lot for that. BsMan - I will be splitting the database thanks.

Nealv - that is exactly what I was looking for - thanks.

Leanne

 

[nealvenables]

Be sure to unhide your tables before running a database compact though! I've heard that the dbHiddenObject attribute is a little buggy in this scenario.

 

[LeanneGodney]

Hi Nealv

That doesnt work for some reason... I'm using Access 2000, and it doesn't like Dim daoDB As DAO.Database or Dim daoTDF As DAO.TableDef. Says "User defined type not defined"...

Do I need to use a particular reference in the VBA window?

Leanne

 

[nealvenables]

Yup.

From your VBA window select Tools -> References -> Microsoft DAO 3.6 Object Library.

 

[LeanneGodney]

Hi there,

I've actually worked out a better way of doing what I originally wanted to do... I wanted to prevent anyone from opening any of my tables cos the info is sensitive. So I've set up my own permissions. I get the users login name (windows login) and have a level specified for them within a table.

Then based on their level of permission, I get access to disable all the buttons that can give them the chance to change my form, or to get to the database window. In Tools --> startup, I've told it to hide the database window. Then in a select case statement in VBA I've told it that if the person isn't marked as an administrator then it must disable the Unhide button in the Window menu. So they can't get to unhide the database window if they aren't administrators.

I should probably mention here that I've created my own menu bar (ICT Incident Management menu) for the database. Here is the code I've put in to disable the Unhide option within the windows menu:

CommandBars("ICT Incident Management menu&quot.Controls("Window&quot.commandbar.Controls("Unhide...&quot.Enabled = False

And this will run if the person is not an administrator.

So far I've not been able to crack my own code, so have added an little catch net in the form of a button in one of the menus that reverses this ONLY if the username is my own.

I'm also going to put a password on my vba code so no one can go in there and modify it.

If anyone wants any help doing this on their own databases, llet me know.



Leanne

 

[BSman]

Instead of putting a password on your vba code, just distribute the application/database as an mde file, rather than the mdb file.

The vba is compiled with the mde format and the source code is not included in the mde file, so your users can't see it at all.

Just be absolutely sure you keep the mdb file for yourself so you can make modifications. You can't recover the vba code from an mde file. But just put your mde versions of applications on the servers where users can run them and they won't be able to do nearly as much damage if they are malicious.

The added benefit of the mde file is that it's smaller than the original mdb file.

 

[LeanneGodney]

I think I tried a mde file once before, but it didn't work on all the users' computers...

 

[Parax]

be careful that holding left shift while opening the database doesn't skip the startup process...

 

[nealvenables]

The lesson I've learned is that Access can't be made 100% secure, but we can take reasonable precautions against malicious use.

Personally, I tend to release front-ends as MDEs in which all objects are hidden.

This doesn't stop a determined individual "hacking" the system, but if they do and are caught they have no grounds for claiming it was accidental - using shift-key-bypass and checking "Show Hidden Objects" in Options is a reasonably complex task.

Additionally, as I generally use a SQL backend, I place audit trails on my sensitive tables so that all changes are logged. The logs also record the name of the form that the change was executed from. This means that if a user manipulates a table directly there is no form name on the audit trail and this gap shows on the log as a "hack".

I also make it a matter of policy with my clients that any breaches of security are a matter for Human Resources, not IT. Users are then made to understand that working around the system won't just get them a slap on the wrist - it could cost them their job.

In other words, we should code our applications so that users cannot accidentally compromise them. Any user activity that falls out of this kind of reasonable use is then a disciplinary matter.