Security Question

[Jim Moore]

I am new to Access and have the following question:

I have an Access97 application that I want to programmatically restrict
Access to the application itself and to limit a users right to read and/or
update records in other Tables within my application.

My application consists of a Front End database and numerous linked Tables
from Backend databases.
The primary Table of my application, called Personnel, that is in a Backend
database called HumanResources.

The Personnel Table contains the following fields:
Last_Name
First_Name
Middle_Name
SSN
Job_Title
Position
Location
(I believe that I will have to duplicate the Personnel table from the
backend database HumanResources and place it in the front end to accomplish
this task)

When my application starts I want to programmatically collect the following
information:
Retrieve the Domain a user is logged in to
Network name of the current machine (Computer Name)
Network logon ID of the current user (User Name)
Full name of the user currently logged in

With the data above collected I want to programmatically perform the
following analysis:
1) User is logged into a valid (known) Domain. (Domains will be listed in
the code - We have 7 Domains in our Enterprise)
a) If user is not logged into a known Domain then I want to display a Dialog
"Access is Denied" then when user clicks OK the application closes.
b) If user is logged into a known Domain then continue validation process
2) User Name matches positions 3-12 of the Computer Name (to ensure the user
is using
their assigned computer to access the application)
a) If user is not logged into their own computer then I want to display a
Dialog "Access is Denied" then when user clicks OK the application closes.
b) If user is logged into their own computer then continue the validation
process
3) Validate that the Full name of the user currently logged in exists in the
Personnel Table (Fields Last, First, Middle)
a) If the full name of the user currently logged in does not exist in the
Personnel Table I want to display a Dialog "Access is Denied" then when user
clicks OK the application closes.
b) If the full name of the user exists in the Personnel Table I want to
continue

With the above satisfied I then want to prompt the user for their User Name
(previously determined)
a) If the user name entered does not match the user name of the current user
logged into the computer then I want to display a Dialog "Access is Denied"
then when user clicks OK the application closes.
b) If the user name entered matches the user name of the current user logged
into the computer the I want to prompt the user for their SSN
When the user enters their SSN I want to validate that against the SSN
stored in the Personnel Table
a) If the SSN entered does not match the SSN for the user attempting to log
in the application I want to display a Dialog "Access is Denied" then when
user clicks OK the application closes.
b) If the SSN entered matches the SSN for the user attempting to log in the
application they are granted access.

Once access is granted to a user I then want to limit their access
(Read/Write/Delete) to certain data fields (in Forms) based on their
Job_Title; Location and Position.

Thank you in advance,
Jim

 

[Scott McDaniel]

Wow. Quite a hefty requirements list for a newsgroup <grin>

You can retrieve the Network login, machinename, etc etc via API calls ...
see www.mvps.org for code of this nature ... however, I believe you'll find
it MUCH easier to implement security on your database via the built in
security. Download and read-read-read the Access Security FAQ (
http://support.microsoft.com/default.aspx?
scid=/support/access/content/secfaq.asp ) , then apply security to a COPY of
your database.

I say this for several reasons, not the least of which is you are storing
people's SSN in an unsecured table ... a HUGE, HUGE no-no in this day and
time of increasing identity theft

.... basically, you're trying to reinvent the wheel. If you _properly_
implement Access Security, you'll eliminate the need for validating the
Domain, username, etc etc ... you simply build groups, add users, add users
to appropriate groups, then grant permissions to various
forms/reports/tables etc based on group permissions. If a user attempts to
login - from ANYWHERE - and they are not a valid member of the workgroup,
**they can't get in** ... so it doesn't matter what domain they come from,
or what computer they're using to access the database. If they do login,
then they would be allowed to view ONLY those objects to which you have
given them permission ... for example, let's say you have a group named
DataEntry, and the only requirement for them is to be able to add new
employees to the HumanResource table. Your user would login, and you would
write code which would determine what objects they are allowed to access.
For example, a member of the DateEntry group would be allowed to ADD new
people to your HumanResources table but would not be able to VIEW or EDIT
existing users (so they couldn't see sensitive information about colleagues,
for example) ... while a member of the Managers group WOULD be allowed to
browse and edit existing records in this table ... but would NOT be able to
open this table in design view (which would be reserved for the Developers
group). If they clicked on a menu item for which they have no permissions,
you can check those permissions and throw a msgbox at them ... even if you
don't, Access security will deliver a (rather cryptic) error message to them
and disallow access ... all without you having to write any code!!

Another issue to consider: If your tables aren't secured, how will you stop
unauthorized users from accessing the tables? Even an unsophsticated user
can easily figure out how to build a new Access database and link to your
tables ... at which time they are privy to ALL of the information contained
in your tables ...

This is not to say that Access security is simple to implement. It's
anything but, and you will have some learning to do. The link referenced
above is the "bible" of security, but there are other good references (jack
macdonald's site, for one, the Microsoft Jet Programmer's Guide for
another, another new book just out regarding this ... sorry can't recall the
title but I'm sure you could Google and find it). Just make sure you learn
security on a COPY of your database ... and that you fully understand
security before implementing it on your live database.


In addition to this if you MUST ensure that UserA ONLY logs in via their
desktop named UserADesktop, you could always check this AFTER UserA logs in
.... which is fairly simple after you capture the Machine_Name:

If Dlookup("strMachineName","tblPeople","strUserName='" & CurrentUser & "'")
<> MachineName Then
MsgBox "Sorry, you must login from your own system", vbOKOnly
Application.Quit
End If

This woudl require that you have a function named "MachineName" (see below),
and would require that you store this information in a table somewhere, but
basically that's it ...


'************* place this in a standard module *****************
Private Declare Function api_GetComputerName Lib "Kernel32" Alias
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function MachineName() As String
'**************************************************
'Purpose: Returns the ComputerName
'Returns: The Windows Networking name of the computer
'Credit goes to Dev Avish at www.mvps.org for this code ...
'**************************************************
On Error Resume Next

Dim NBuffer As String
Dim Buffsize As Long
Dim Wok As Long

Buffsize = 256
NBuffer = Space$(Buffsize)

Wok = api_GetComputerName(NBuffer, Buffsize)
atCNames = Trim$(NBuffer)

End Function

 

[Jim Moore]

Thank you for your reply Scott it is greatly appreciated.

As I stated I am new to Access and from what I have seen from other posts in
newsgroups - you PRO's are a wealth of information and could lead me in
right direction from the beginning instead of spinning my wheels.

We operate in a secure environment already and we have many geographically
separated offices and many remote employees is why I was thinking about
doing it this way. Remote users use encrypted VPN connections to access
company resources from the field. The database will reside on a secure
server and only MY user's will have permission's to it. I wanted to try to
limit my need to manage/distribute .mdw's to all of my user's. Fortunately
we are not worried about our users trying to bypass security by the method
you mentioned and unfortunately, the business we are in SSN's of employees
are pretty much common knowledge already. Even though I mentioned using the
SSN as a method of authentication, that was just as an example - I was going
to use another data field that is personal and not common knowledge in place
of the SSN.

I will execute on your advice and take it from there.

Thanks again,
Jim

 

[Scott McDaniel]

You're welcome Jim. Be aware, however, of the pitfalls of trying to run a
traditional Access database app across a VPN ... you would be much better
off to do so via Terminal Server, Citrix, etc that to try and run a
frontend/backend setup. The issue of distributing .mdw files can be
troublesome, but it's really a simple matter to (a) write code that checks
for a new .mdw file on database startup and (b) download the file to the
workstation before allowing access. Also, some find no trouble maintaining
one .mdw file (on the data server) which every users accesses to log onto
the database ... however, some have noted corruption issues.

 

[Jim Moore]

Scott,

Again many thanks.

You're welcome Jim. Be aware, however, of the pitfalls of trying to run a
traditional Access database app across a VPN ... you would be much better
off to do so via Terminal Server, Citrix, etc that to try and run a
frontend/backend setup. The issue of distributing .mdw files can be
troublesome, but it's really a simple matter to (a) write code that checks
for a new .mdw file on database startup and (b) download the file to the
workstation before allowing access. Also, some find no trouble maintaining
one .mdw file (on the data server) which every users accesses to log onto
the database ... however, some have noted corruption issues.


posts attempts exists