Hiding Sensitive Data 1

[jreynold]

I'm stuck....
I'm expanding my company's database to include some sensitive information that I do not want all users to have access to.

I want to have one form that both the non admins and admins can use. Admins would see all the data and non admins would just see an XXXX or something in place of it.

Here is a sample of where I am at.

Table "Projects" has fields "Name" "Client" "Status"

Table "Projects-secure" has fields "Project Amount". This is the field I want to hide.

Query "ProjectsSummary" has all four above fields

Form "ProjectsSummary" uses data from the query.

Currently, if the non-admins try to open the "secure" data whether in the table, query or form, it simply doesn't let them open the object.

I'm sure there is an easy way of doing this, but my access for dummies book is not cutting it here and I can't find anything searching the forums...

Thanks in advance for any advice!

 

[Remou]

It may be best to have the non-admins use front-ends that do not include the sensitive data.

You do not show any code, so I cannot really comment on the problem you are experiencing.

If the data is very sensitive indeed, you should probably consider something other than Access.

 

[fneily]

As Remou stated or have two queries, one showing the sensitive data and one not, and two forms, one for admin and one for non-admins. Then have a front end form where they log in and you can test to see which path will be followed.

 

[JoeAtWork]

I advise against the two tables approach (i.e. "Project" and "Project-secure").

The filtering of data according to security should be done at a higher level, it should not determine the design of your database schema.

The typical Access solution is to either have two or more forms, or one form but have code that hides or disables controls according to the user's security level.

A possible option is encrypting your secure fields (but then you are pretty much forced to use unbound controls for those fields).

 

[fneily]

Point of discussion/debate
JoeAtWork, you stated that "The typical Access solution" etc.
I've found in books, real-life company situations, Codd's theory, that you may have split tables for security reasons. Example, employee info. One table has employee's name and work number that can be accessed by everyone. However, his ssn, salary, ira, etc. is in another table so only those allowed to see such info can. So these two tables are in a one-to-one relationship but split for security reasons.
But, either way works.

 

[jsteph]

For field-level security in Access use the WITH OWNER ACCESS OPTION in queries.

Set up JET security and you can make a query in which anyone can open the query which is based on a table which nobody but your secured user (not admin) can view.

So in a nutshell, secure the table completely, then create the query choosing only the fields you want 'normal' users to view, and use the aforementioned syntax (In Properies, this is under Run Permissions).
--Jim

 

[jreynold]

Thank you for all the replies, I've been out of town on business and haven't worked on anything until tonight.

So I'm trying JoeAtWork's idea of hiding controls... Does using something like below this as a procedure On Open form work?

'checkusergroup() is function which return true or false depending on whether CurrentUser is in group entered in argument.

If checkusergroup(Admins) = false
[securedfield].Enabled = false
[securedfield].visible = false
Else
[securedfield].Enabled = true
[securedfield].visible = true
End If

It seems to work for me as far as the form goes. I log in as JoeSchmo and the fields are simply not visible on the form.

To make it work I had to give the "unsecure" users the permissions to view the "secure" data, but I guess maybe in my forms I will not give them the chance to ever look at it?

Is there some other security gap here I'm missing?

 

[JoeAtWork]

Yes, that's what I had in mind. Note that once you make the control invisible, the Enabled property becomes irrelevant.

 

[JerryKlmns]

No need for If Then EndIf

Dim bIsOk as Boolean

bIsOk = checkusergroup(Admins)
[securedfield1].visible = bIsOk
[securedfield2].visible = bIsOk
[securedfield3].visible = bIsOk
[securedfield4].visible = bIsOk
...
[securedfieldN].visible = bIsOk