2007 User Level Security

[Eka1618]

Hello,

I have created a DB in the new format. I needed to use the Attachment
datatype in some of my tables.

I also need to have user level security. If I convert, I dont think the new
features in 2007 like the Attachment datatype will be available. Is this only
true for those who are not using Access 2007?

I was thinking about just creating user names, passwords and a 'user type'
in my employee table, then using this information to enable/disable objects
on the forms. This would take a while to go through all of these forms
though, but I think it would work.

Does anyone have any other suggestions?


~Erica~

 

[Klatuu]

One can never totally protect a database. Someone with the knowledge to do
so can get into anything; however, here are some routines that you may find
useful.

First, here is a routine you can use when a user logs in to set their user
name and password as application level properties.

Public Function SetSecurityProp(UserInitials As String, SecurityLevel As
Integer) As Boolean
Dim prp As Property
Const conPropNotFound As Integer = 3270

On Error GoTo ErrorSetSecurityProp
' Explicitly refer to Properties collection.
CurrentDb.Properties("User") = UserInitials
CurrentDb.Properties.Refresh
CurrentDb.Properties("SecurityLevel") = SecurityLevel
CurrentDb.Properties.Refresh
SetSecurityProp = True

ExitSetSecurityProp:
Exit Function

ErrorSetSecurityProp:
If Err = conPropNotFound Then

' Create property, denote type, and set initial value.
Set prp = CurrentDb.CreateProperty("User", dbText, UserInitials)
' Append Property object to Properties collection.
CurrentDb.Properties.Append prp
CurrentDb.Properties.Refresh
Set prp = CurrentDb.CreateProperty("SecurityLevel", dbInteger,
SecurityLevel)
' Append Property object to Properties collection.
CurrentDb.Properties.Append prp
CurrentDb.Properties.Refresh
SetSecurityProp = True
Resume ExitSetSecurityProp
Else
MsgBox Err & ": " & vbCrLf & Err.DESCRIPTION
SetSecurityProp = False
Resume ExitSetSecurityProp
End If

End Function

Should you need to change the value of a property, Here is a routine to do
that:

Function ChangeProperty(strPropName As String, varPropType As Variant,
varPropValue As Variant) As Integer
Dim dbs As Object, prp As Variant
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

One the properties are set for the application, the idea is when the user
opens a form, use the form Load event to set the restrictions applicable to
the user. Here is an example:

If CurrentDb.Properties("SecurityLevel") < 30 Then
Me.cmdCancel.Enabled = False
Me.cmdInsert.Enabled = False
Me.cmdSave.Enabled = False
Me.cmdCLDelete.Enabled = False
FormSecurity Me, CurrentDb.Properties("SecurityLevel"), True

Else
Me.cmdCancel.Enabled = True
Me.cmdInsert.Enabled = True
Me.cmdSave.Enabled = True
Me.cmdCLDelete.Enabled = True
FormSecurity Me, CurrentDb.Properties("SecurityLevel"), False

End If

Public Sub FormSecurity(frm As Form, intSecurityLevel As Integer, bOnOff As
Boolean)

Dim ctl As Control
Dim li As Long
On Error Resume Next

If intSecurityLevel < 40 Then
For Each ctl In frm.Controls
If ctl.ControlType <> acCommandButton Then
ctl.Locked = bOnOff
End If

Next

frm.cmdExit.Enabled = True
frm.cmdPrint.Enabled = intSecurityLevel > 9

End If

End Sub


Another thing you can do is encrypt user names and password in the table so
even if a user gets into the back end database, they wont be able to see the
actual values:

Public Function EncryptCode(iToDo As Integer, strPass As String, _
Optional iSeed As Integer) As String
Dim strValue As String
Dim lngMxx As Long
Dim lngPlace As Long

iSeed = IIf(iSeed = 0, 105, iSeed + 95)

For lngMxx = 1 To Len(strPass)
If iToDo = 1 Then
' encode
lngPlace = (Asc(Mid(strPass, lngMxx, 1)) + 2550 + iSeed - lngMxx)
Mod 255
Else
' decode
lngPlace = 255 - (Abs((Asc(Mid(strPass, lngMxx, 1)) - 2550 -
iSeed + lngMxx) Mod 255))
End If
strValue = strValue + Chr(lngPlace)
Next

EncryptCode = strValue

End Function

Also, it would be a good idea to password protect the backend database and
just as an extra precaution, put an autoexc macro in the backend database
that just does a Quit. It will not affect it being used by the front end,
but if a user tries to open it and knows the password, it will immediatley
close. They would have to know about the AllowBypassKey property and the
password to be able to get into it, but if you encrypt the user names and
passwords, they still cant see them.

 

[Eka1618]

Thanks for these suggestions Klatuu.

I am going to be working with these example for a bit...I may have further
questions within then next couple weeks.

~Erica~

 

[pddxxx]

Erica,

There are a number of alternatives to using Access Security when it
comes to securing your database.

A shareware version of a simplified user security add-in that I've
written - LASsie (Light Application Security) for MS Access - is
available for download here:
http://www.peterssoftware.com/las.htm

Also, there's the Access Project Security Manager from
databasecreations, Inc.:
http://www.databasecreations.com/prod_apsm.htm

Hope this helps,

Peter De Baets
Peter's Software - Microsoft Access Tools for Developers
http://www.peterssoftware.com

 

[Klatuu]

Okay
--
Dave Hargis, Microsoft Access MVP

Thanks for these suggestions Klatuu.

I am going to be working with these example for a bit...I may have further
questions within then next couple weeks.

~Erica~

 

[Eka1618]

Klatuu,

In your examples, Is there anything significant about using the "Security
Level" Property as an integer?

you have :

If CurrentDb.Properties("SecurityLevel") < 30 Then
If intSecurityLevel < 40 Then
frm.cmdPrint.Enabled = intSecurityLevel > 9


I am just curious as to what these numbers mean, or if it justsomthing that
you made up.


I understand how the code shoud work so far, I'm just not familiar with
setting properties.


~Erica~

 

[Klatuu]

There is nothing significant about the security property being an integer.
It is not native to Access, it was just my own devising based on user
requirements for the project for which I originally wrote the security. In
that case, a level 10 meant you could only view some data and run some
reports. A 20 give additional permissions, and a 40 was full operational
admin rights. How you set it up is totally up to you.

If you look at the code, you will see how properties are created and
changed. It is nothing magical.

 

[Eka1618]

The properties idea works good, I have a lot of new ideas that will make
other forms work real nice!

I am a little confused with the section where you explain the code for
encrypting names and passwords.

What do the variables iToDo & iSeed represent?

and

Where should I be using this function?


~Erica~

 

[Klatuu]

You can ignore iSeed. It is used to change the sequence of numbers generated
by the random number generator. But, once you use one, you have to stay with
that number, so I wouln't use it.

iToDo tells the function to either encode a plain text string or decode an
encrypted string. Pass the function a 1 to encode a plain text string. I
usually pass it 0 to decode an encrypted string.

For example

encryptcode(1,"dhargis")
returns:
ÃŒÃÇ×ËÌÕ

And encryptcode(0,"ÃŒÃÇ×ËÌÕ")
returns:
dhargis

So, the way I use it is to encrypt user names and passwords and store the
encrypted values in the username/password table. Then when a user logs in, I
encrypt the name and password and do a DLookup to see if the user is there
and the password matches. I also have start and end dates in the table so
users are not deleted, but activated and deactivated based on those dates.

Dave Hargis, Microsoft Access MVP