RE: 2007 User Level Security

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" wrote:

Okay
--
Dave Hargis, Microsoft Access MVP


"Eka1618" wrote:

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~

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Eka1618" wrote:

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~
.

Quantcast