blank out information

[David]

Hi,

I have now come to a difficult part of the database I am building. Basically
we have a need for certain users to be unable to view the contents of
certain fields on a form based what department they work in or based on a
security group.

The reason for this is that customers credit card details will be stored in
the database however we don't want everyone to be able to see the details
i.e. they are just not shown or x'd out.

Has anyone done this or know how to do it as I don't have a clue!

Thanks

 

[Arvin Meyer [MVP]]

Access is not absolutely secure, so anyone who really wants to get at the
data can. You can in versions 2003 and earlier use User-Level Security,
granting no permissions on the tables, then viewing data accessed only
through WITHOWNEROPTIONACCESS queries.

As far as being able to enter data without seeing it, just use an input mask
with the name "Password" and all data will appear as asterisks. Remember, if
a user can get at the table of form design, and is knowledgeable enough, he
can change that and view the data.

You can do hide columns of data with queries, and change the recordsource of
the form based upon the logged on user. Again, a sophisticated user who
knows Access well will be able to get the data by just viewing the query
directly, so you will need to hide the database window or navigation pane.

Code to get the Windows login is at:

http://www.mvps.org/access/api/api0008.htm

Code to show/hide the database window and keep users from using special keys
to view it is at:

http://www.mvps.org/access/general/gen0040.htm

 

[David]

I don't think I made myself very clear about what I am trying to do so I
will try to simplify...

1. Database contains a table of 'Employees' which has a field to state which
department they work in (sales and administration)
2. Database contains tables and associated forms for data input. The forms
are used to collect personal information about our customers for credit
scoring. One section of the form requires that the employee enters the
customers credit card information (at this point the information is
unmasked)
3. Database contains a list views of the above tables, employee can click on
the name to open up the form to view the information entered. At this point
I would like the customers credit card details to be masked (*********) if a
Sales agent is viewing the record and unmasked if an administration agent is
viewing the record.

I don't know if this will work with privileges such as those set in the
Northwind Database, could someone shed some light on this and let me know if
it is possible, I have an inkling that I would have to use some form of
code.

Thanks

 

[Arvin Meyer [MVP]]

There are no privileges set in the Northwind database. All of them are for
admin. You must set up security groups, 1 for agents, and 1 for admins.

In the form's Open or Load event, you use some code like:

If SecurityGroup = "admin" Then
txtCC.InputMask = "Password"
Else
txtCC.InputMask = ""
End If

I repeat. A user who want to get past this code only needs to comment it
out, or look at the underlying data. You cannot use code on the table
itself, only a form. You must do additional work to secure your database to
keep the CC#s secure.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[David]

Thank you,

That makes it a bit clearer.

Regards

 

[David]

Hi,

Sorry to be a pain on this one, but it is one of the most important parts of
the database and one of the main reasons for creating it. I understand and
acknowledge the security concerns and risks of using Access for such a
database however I believe that that the practices I have put in place will
be sufficient to protect the source data, this includes up scaling the data
to our SQL server and packaging the database as an installer that will only
allow the database to run in Runtime mode.

Back to my question, the database is being designed in Access 2007, I am
learning more each day but I am unsure how to create security groups. I
would appreciate it if someone could get back to me with either a guide or
instructions I would really appreciate it.

Thanks

 

[Arvin Meyer [MVP]]

If you are using the accdb format in Access 2007, user-level security has
been dropped, so you will need to create your own. I suggest using the
Windows login username so that a second login and authentication won't be
required. The code (which will go in a standard module) is at:

http://www.mvps.org/access/api/api0008.htm

You will then need to create a table with the username and the permission
level. And run some code like:

Private Sub Form_Open(Cancel As Integer)
Dim x
x = DLookup("SecurityLevel", "tblWindowsUsers", "UserName =" & "'" &
Me.txtUser & "'")
Me.txtLevel = x
If Me.txtLevel >= 50 Then
Me.SubContactPositionsButton.Visible = True
Me.ViewJobCostButton.Visible = True
End If
End Sub

This allows 2 buttons to be visible if the security level is >= to 50 (The
database that this comes from actually has 6 groups)

SQL-Server has adequate security, but it doesn't easily translate to an
Access Front-end, so I find it easier to use both, integrated security with
SQL-Server and the same type of security, custom honed for the Access
front-end. In any case, to build a professional package, you will need to
develop some significant skills with both Access and SQL-Server.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com