Security code

[red skelton via AccessMonster.com]

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 authenticated by their network ID. 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

 

[Douglas J Steele]

Well, you're fooling yourself if you think that's going to provide any
actual security, as all the user has to do is import the contents of your
database into another database. However, let's look at what you've got.

How are you calling the Startup routine? Do you know that's it's actually
being called? (try putting a message box at the start of the routine). If it
is being called, put a breakpoint into your code, and single-step through it
to make sure it's functioning properly.

BTW, there's no need to loop through the entire table. Use:

Dim strSQL As String

'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
End If

 

[red skelton via AccessMonster.com]

Sir,
Thanks for the answer. Please forgive my ignorance when it comes to VB as
I'm very new at it.
I am using a startup routine of "Private Function DbsCurrent_Open()" Which
obviously is wrong. If you have a sec, please expand on your suggestion of
putting a message box at the start? Again, I'm very new to this and any help
would be greatly appreciated.

Red

Well, you're fooling yourself if you think that's going to provide any
actual security, as all the user has to do is import the contents of your
database into another database. However, let's look at what you've got.

How are you calling the Startup routine? Do you know that's it's actually
being called? (try putting a message box at the start of the routine). If it
is being called, put a breakpoint into your code, and single-step through it
to make sure it's functioning properly.

BTW, there's no need to loop through the entire table. Use:

Dim strSQL As String

'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
End If

Hi,
I was wondering if anyone could take a look at my code and see where I'm

[quoted text clipped - 79 lines]

Thanks,
Red

 

[red skelton via AccessMonster.com]

Sir,
Tried inputing a Msg Box right after the "Private Function DbsCurrent_Open()"
if this is the place you're suggesting, then no, it didn't work. This leads
me to believe that as you said, the routine is not actually being called.
I'll work on that.
Thanks
Red

Sir,
Thanks for the answer. Please forgive my ignorance when it comes to VB as
I'm very new at it.
I am using a startup routine of "Private Function DbsCurrent_Open()" Which
obviously is wrong. If you have a sec, please expand on your suggestion of
putting a message box at the start? Again, I'm very new to this and any help
would be greatly appreciated.

Red

Well, you're fooling yourself if you think that's going to provide any
actual security, as all the user has to do is import the contents of your

[quoted text clipped - 23 lines]

 

[Douglas J Steele]

Somewhere you need to call that function. Since your application is
utilizing a switchboard, the appropriate place to call the function would be
in the switchboard's Open event:

Private Sub Form_Open(Cancel As Integer)
Call DbsCurrent_Open
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sir,
Tried inputing a Msg Box right after the "Private Function DbsCurrent_Open()"
if this is the place you're suggesting, then no, it didn't work. This leads
me to believe that as you said, the routine is not actually being called.
I'll work on that.
Thanks
Red

Sir,
Thanks for the answer. Please forgive my ignorance when it comes to VB as
I'm very new at it.
I am using a startup routine of "Private Function DbsCurrent_Open()" Which
obviously is wrong. If you have a sec, please expand on your suggestion of
putting a message box at the start? Again, I'm very new to this and any help
would be greatly appreciated.

Red

Well, you're fooling yourself if you think that's going to provide any
actual security, as all the user has to do is import the contents of

your
[quoted text clipped - 23 lines]

Thanks,
Red

 

[red skelton via AccessMonster.com]

Sir, thanks for your advice. I've got to go now and wont be back til Mon got
to pick up kids from school. I'll work on it again then. Dont want you to
think I've ignored you cuz its much appreciated. Have a good week end.
Red

Somewhere you need to call that function. Since your application is
utilizing a switchboard, the appropriate place to call the function would be
in the switchboard's Open event:

Private Sub Form_Open(Cancel As Integer)
Call DbsCurrent_Open
End Sub

Sir,
Tried inputing a Msg Box right after the "Private Function DbsCurrent_Open()"

[quoted text clipped - 19 lines]

 

[PosseJohn]

Doug, I understand what you are saying about 'real' security. Where do I
begin protecting my DB for real? I have a DB that everyone can open at will
and make changes or deletions.

I have a front end/back end design. Users Open the 'program DB' and it
translates their entries to the 'data DB'.

Well, you're fooling yourself if you think that's going to provide any
actual security, as all the user has to do is import the contents of your
database into another database. However, let's look at what you've got.

How are you calling the Startup routine? Do you know that's it's actually
being called? (try putting a message box at the start of the routine). If it
is being called, put a breakpoint into your code, and single-step through it
to make sure it's functioning properly.

BTW, there's no need to loop through the entire table. Use:

Dim strSQL As String

'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
End If

 

[Douglas J. Steele]

You can prevent them from making changes to your application (i.e. the
forms, reports and modules) by converting the front end into an MDE (under
Tools | Database Utilities)

You can control what they can do to the data by implementing Access
User-Level Security, and limit their permissions to only running queries
you've defined.

Warning: Access Security is not a simple undertaking. Grab the FAQ at
http://support.microsoft.com/support/access/content/secfaq.asp and read it
carefully from end to end a couple of times. Work on a copy of your database
(just in case!), and make sure you don't leave out any steps.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, I understand what you are saying about 'real' security. Where do I
begin protecting my DB for real? I have a DB that everyone can open at
will
and make changes or deletions.

I have a front end/back end design. Users Open the 'program DB' and it
translates their entries to the 'data DB'.