Access Security

26 Oct 2005 4:00 PM

red

Hi,
I was wondering if anyone could take a look at my code and see where I'm going wrong. I have an access database that I want to only open to authorized people. I have created a table in the database and included their names and roles. The database opens to a switchboard and I dont 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's role
Dim strCriteria As String           'used to search for user name in role table

'Refer current DB
Set DbsCurrent = CurrentDb

'strCurrentUser and strRole are global variables which will store 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 in 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,
Red -- red ------------------------------------------------------------------------ red's Profile: http://www.highdots.com/forums/m1210 View this thread: http://www.highdots.com/forums/t3051816

26 Oct 2005 10:54 PM

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Show quoteHide quote

"red" <red.1xi***@no-mx.forums.yourdomain.com.au> wrote in message
news:red.1xio5z@no-mx.forums.yourdomain.com.au...
>
> Hi,
> I was wondering if anyone could take a look at my code and see where
> I'm going wrong. I have an access database that I want to only open to
> authorized people. I have created a table in the database and included
> their names and roles. The database opens to a switchboard and I dont
> 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's
> role
> Dim strCriteria As String           'used to search for user name
> in role table
>
> 'Refer current DB
> Set DbsCurrent = CurrentDb
>
> 'strCurrentUser and strRole are global variables which will store
> 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 in
> 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,
> Red
>
>
> --
> red
> ------------------------------------------------------------------------
> red's Profile: http://www.highdots.com/forums/m1210
> View this thread: http://www.highdots.com/forums/t3051816
>

27 Oct 2005 2:10 AM

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

27 Oct 2005 7:29 AM

red

Thanks for the responses;
TC,
your absolutly right about using the shift key, I hope (probably not giving enough credit to the average computer user) that if they get the 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 get along.

VR to both,
Red -- red ------------------------------------------------------------------------ red's Profile: http://www.highdots.com/forums/m1210 View this thread: http://www.highdots.com/forums/t3051816

28 Oct 2005 4:50 AM

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