Access Security

[red]

Hi,
I was wondering if anyone could take a look at my code and see wher
I'm going wrong. I have an access database that I want to only open t
authorized people. I have created a table in the database and include
their names and roles. The database opens to a switchboard and I don
know if this is a problem. Thanks in advance for any help or advice.

Option Compare Database
Option Explicit
Private strCurrentUser As String
Private strRole As String
Dim Result As Variant
Function Startup()
On Error GoTo Err_Startup

Dim DbsCurrent As Database
Dim rsDBUsers As Recordset 'Used to find current user'
role
Dim strCriteria As String 'used to search for user nam
in role table

'Refer current DB
Set DbsCurrent = CurrentDb

'strCurrentUser and strRole are global variables which will stor
the current user name
'and role throughout the current session
strCurrentUser = NetworkUserID()
strRole = "Default"

'Find if user has a role assigned in tblDBUsers
Set rsDBUsers = DbsCurrent.OpenRecordset("tblDBUsers"
dbOpenSnapshot)

Do Until rsDBUsers.EOF
'If user does have a role in tblDBUsers, store that role i
strRole
If rsDBUsers!UserName = strCurrentUser Then
strRole = rsDBUsers!Role
Exit Do

End If
rsDBUsers.MoveNext
Loop

rsDBUsers.Close
DbsCurrent.Close

'Default role is not allowed to access database
If strRole = "Default" Then
MsgBox "You do not have permission to access this database."
vbExclamation, _
"Access Denied"
Application.Quit acQuitSaveAll
End If

Select Case strRole
Case "Administrator":
Application.MenuBar = "mnuBlank"
DoCmd.OpenForm "Switchboard"
Case "User":
Application.MenuBar = "mnuBlank"
DoCmd.OpenForm "Switchboard"
Case "Default":
MsgBox "You do not have permission to access this database."
vbExclamation, _
"Access Denied"
Application.Quit acQuitSaveAll
End Select

Exit_Startup:
DbsCurrent.Close
DoCmd.SetWarnings True

Exit Function

Err_Startup:
MsgBox "Error in 'Startup' function:" & Chr(13) & Chr(10)
Err.Description, _
vbExclamation, "Startup"
Resume Exit_Startup
End Function

Bottom line is its not restricting access to anyone
Thanks,
Re

 

[Douglas J. Steele]

Well, you're fooling yourself if you think that that will keep a
semi-knowledgable user out, but...

Is your routine running? ("Startup" doesn't have any special meaning in
Access: you must call the routine somehow) Does it return the correct Role
value for the user?

FWIW, you could replace the following code

'Find if user has a role assigned in tblDBUsers
Set rsDBUsers = DbsCurrent.OpenRecordset("tblDBUsers",
dbOpenSnapshot)

Do Until rsDBUsers.EOF
'If user does have a role in tblDBUsers, store that role in
strRole
If rsDBUsers!UserName = strCurrentUser Then
strRole = rsDBUsers!Role
Exit Do

End If
rsDBUsers.MoveNext
Loop

rsDBUsers.Close
DbsCurrent.Close

with

'Find if user has a role assigned in tblDBUsers
strSQL = "SELECT Role FROM tblDBUsers " & _
"WHERE UserName = '" & strCurrentUser & "'"
Set rsDBUsers = DbsCurrent.OpenRecordset(strSQL ,dbOpenSnapshot)

If rsDBUsers.EOF = False Then
strRole = rsDBUsers!Role
Else
strRole = "Default"
End If

rsDBUsers.Close
DbsCurrent.Close

 

[TC]

What is to stop someone holding down the shift key when they open your
database? Then the switchboard will not run, & they can do whatever
they want in it. Or they could link, from one of their own databases,
to the tables in your database, thus being able to do what they wanted,
again with no control from your code.

HTH,
TC

 

[red]

Thanks for the responses;
TC,
your absolutly right about using the shift key, I hope (probably no
giving enough credit to the average computer user) that if they get th
deny text box, they wont know to use the shift key.
Douglas Steele,
Thanks for the code. Ill give it a try and let you know how I ge
along.

VR to both,
Re

 

[TC]

Red, I agree that simple precautions work in simple cases. If the users
don't know how to use the Shift key bypass, and so on, your method will
probably work for them.

Cheers,
TC