Protected fields

[Guest]

Is the only way to put security on certain fields in a database to create an
input form for each level of security? Example: Mail log - Secretary logs
it in with various data fields. Staff can log out by entering a completion
date. I only want staff to be able to update the completion date and not any
other fields of the record. What would be the easiest and best way to
accomplish this?

 

[Sandra Daigle]

Have you implemented Access Security? If so are your users assigned to
groups? If you haven't already done so this would be the place to start.
Here are some references on setting up security:

Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

After you have secured the database, you can determine whether the user is
in a particular group and then lock or unlock controls based on their group
membership using code like the following:

me.CompletionDate.locked=UserInGroup(currentUser(),"Staff ")

The UserInGroup function can be done a couple of ways the first involves 2
functions - one to ensure that the group exists, then if it does exist, the
second to determine whether the user is in the group. This method is a bit
more reliable than the second I'll show later:
Public Function UserInGroup(strUser As String, _
strGroup As String) As Boolean
Dim ws As dao.Workspace
Dim strUserName As String
Dim usr As dao.User
Dim inti As Integer
Dim fUserInGroup As Boolean
Set ws = DBEngine.Workspaces(0)
If GroupExists(strGroup) Then
For inti = 0 To ws.Groups(strGroup).Users.count - 1
If ws.Groups(strGroup).Users(inti).Name = strUser Then
fIsUserInGroup = True
End If
Next inti
End If
UserInGroup = fUserInGroup
set ws=nothing
End Function


Public Function GroupExists(strGroup) As Boolean
Dim ws As dao.Workspace
Dim strUserName As String
Dim usr As dao.User
Dim inti As Integer
Dim fGroupExists As Boolean
Set ws = DBEngine.Workspaces(0)
For inti = 0 To ws.Groups.count - 1
If ws.Groups(inti).Name = strGroup Then
fGroupExists = True
End If
Next inti
GroupExists = fGroupExists
set ws=nothing
End Function


Here's the one function method - note that it does not differentiate the
error that occurs if marked line fails.

Function UserInGroup(strUser As String, _
strGroup As String) As Boolean
Dim ws As Workspace
Dim strUserName As String
Set ws = DBEngine.Workspaces(0)
On Error Resume Next
' if this normally line fails then the user is not in the group
' but it could fail due to some other error condition so
' this method might be considered a bit sloppy
strUserName = ws.Groups(strGroup).Users(strUser).Name
UserInGroup = (Err = 0)
End Function

 

[Guest]

I haven't read all your message yet, but thought you'd get a laugh. I hadn't
gone thru the security yet. Did that with the Securty Wizard just now. Now
I'm locked out of my own database! Luckily it was a copy for me to "play"
with while trying to figure out this stuff. Set up all my users in different
groups following the wizard and put mye in Admin, but I'm locked out now.
I'll recopy the original database and try what you've written here. Thank
you and I'll get back
Hutch

Have you implemented Access Security? If so are your users assigned to
groups? If you haven't already done so this would be the place to start.
Here are some references on setting up security:

Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

After you have secured the database, you can determine whether the user is
in a particular group and then lock or unlock controls based on their group
membership using code like the following:

me.CompletionDate.locked=UserInGroup(currentUser(),"Staff ")

The UserInGroup function can be done a couple of ways the first involves 2
functions - one to ensure that the group exists, then if it does exist, the
second to determine whether the user is in the group. This method is a bit
more reliable than the second I'll show later:
Public Function UserInGroup(strUser As String, _
strGroup As String) As Boolean
Dim ws As dao.Workspace
Dim strUserName As String
Dim usr As dao.User
Dim inti As Integer
Dim fUserInGroup As Boolean
Set ws = DBEngine.Workspaces(0)
If GroupExists(strGroup) Then
For inti = 0 To ws.Groups(strGroup).Users.count - 1
If ws.Groups(strGroup).Users(inti).Name = strUser Then
fIsUserInGroup = True
End If
Next inti
End If
UserInGroup = fUserInGroup
set ws=nothing
End Function


Public Function GroupExists(strGroup) As Boolean
Dim ws As dao.Workspace
Dim strUserName As String
Dim usr As dao.User
Dim inti As Integer
Dim fGroupExists As Boolean
Set ws = DBEngine.Workspaces(0)
For inti = 0 To ws.Groups.count - 1
If ws.Groups(inti).Name = strGroup Then
fGroupExists = True
End If
Next inti
GroupExists = fGroupExists
set ws=nothing
End Function


Here's the one function method - note that it does not differentiate the
error that occurs if marked line fails.

Function UserInGroup(strUser As String, _
strGroup As String) As Boolean
Dim ws As Workspace
Dim strUserName As String
Set ws = DBEngine.Workspaces(0)
On Error Resume Next
' if this normally line fails then the user is not in the group
' but it could fail due to some other error condition so
' this method might be considered a bit sloppy
strUserName = ws.Groups(strGroup).Users(strUser).Name
UserInGroup = (Err = 0)
End Function





--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Is the only way to put security on certain fields in a database to
create an input form for each level of security? Example: Mail log
- Secretary logs it in with various data fields. Staff can log out
by entering a completion date. I only want staff to be able to
update the completion date and not any other fields of the record.
What would be the easiest and best way to accomplish this?

 

[Sandra Daigle]

You wouldn't be the first person that has done this! Good thing you were
working on a backup - that's always rule number 1 in my book!!

Read the FAQ and Whitepaper then follow them closely - I still follow these
when securing a db since I don't actually do it all that often (most of my
work seems to be in existing dbs).

Good luck!

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I haven't read all your message yet, but thought you'd get a laugh.
I hadn't gone thru the security yet. Did that with the Securty
Wizard just now. Now I'm locked out of my own database! Luckily it
was a copy for me to "play" with while trying to figure out this
stuff. Set up all my users in different groups following the wizard
and put mye in Admin, but I'm locked out now. I'll recopy the
original database and try what you've written here. Thank you and
I'll get back
Hutch

Have you implemented Access Security? If so are your users assigned
to groups? If you haven't already done so this would be the place to
start. Here are some references on setting up security:

Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

After you have secured the database, you can determine whether the
user is in a particular group and then lock or unlock controls based
on their group membership using code like the following:

me.CompletionDate.locked=UserInGroup(currentUser(),"Staff ")

The UserInGroup function can be done a couple of ways the first
involves 2 functions - one to ensure that the group exists, then if
it does exist, the second to determine whether the user is in the
group. This method is a bit more reliable than the second I'll show
later:
Public Function UserInGroup(strUser As String, _
strGroup As String) As Boolean
Dim ws As dao.Workspace
Dim strUserName As String
Dim usr As dao.User
Dim inti As Integer
Dim fUserInGroup As Boolean
Set ws = DBEngine.Workspaces(0)
If GroupExists(strGroup) Then
For inti = 0 To ws.Groups(strGroup).Users.count - 1
If ws.Groups(strGroup).Users(inti).Name = strUser Then
fIsUserInGroup = True
End If
Next inti
End If
UserInGroup = fUserInGroup
set ws=nothing
End Function


Public Function GroupExists(strGroup) As Boolean
Dim ws As dao.Workspace
Dim strUserName As String
Dim usr As dao.User
Dim inti As Integer
Dim fGroupExists As Boolean
Set ws = DBEngine.Workspaces(0)
For inti = 0 To ws.Groups.count - 1
If ws.Groups(inti).Name = strGroup Then
fGroupExists = True
End If
Next inti
GroupExists = fGroupExists
set ws=nothing
End Function


Here's the one function method - note that it does not differentiate
the error that occurs if marked line fails.

Function UserInGroup(strUser As String, _
strGroup As String) As Boolean
Dim ws As Workspace
Dim strUserName As String
Set ws = DBEngine.Workspaces(0)
On Error Resume Next
' if this normally line fails then the user is not in the group
' but it could fail due to some other error condition so
' this method might be considered a bit sloppy
strUserName = ws.Groups(strGroup).Users(strUser).Name
UserInGroup = (Err = 0)
End Function





--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Is the only way to put security on certain fields in a database to
create an input form for each level of security? Example: Mail log
- Secretary logs it in with various data fields. Staff can log out
by entering a completion date. I only want staff to be able to
update the completion date and not any other fields of the record.
What would be the easiest and best way to accomplish this?