Solved

MS Access user level security - read only forms?

Posted on 2007-04-04
17
2,518 Views
Last Modified: 2013-11-28
I'm trying to use user-level security in an MS Access 2003 database to disallow access to some forms (this I can do without much trouble), and allow read-only access to others so that the user can see data but not edit the value of any control on the form. Is this read-only access restriction possible?
0
Comment
Question by:KnowledgeWare
  • 7
  • 5
  • 4
  • +1
17 Comments
 
LVL 75
ID: 18853199
Yes.

mx
0
 

Author Comment

by:KnowledgeWare
ID: 18853215
Right. Good. How?
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853227
Set the AllowEdits property to False. While you're at it, also check
out AllowDeletions and AllowAdditions.

adria
0
 
LVL 75
ID: 18853265
Are you talking about when using ULS ?

mx
0
 
LVL 75
ID: 18853275
If not, the try adraughn's suggestion, noting however that is easily circumvented.

Sorry ... did mean such a short answer in my first post ... someone came in my office!

mx
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853320
If you are going to use user level security, you can assign the user to a group and make that group read only.

If you need help let us know.

adria
0
 

Author Comment

by:KnowledgeWare
ID: 18853413
Yeah, I'm trying to use ULS. Setting the Form's properties would involve a whole custom security setup, would it not? I suppose it wouldn't be that hard to build, but I'd rather not do it.

Ok, say I want to have Form A be completely blocked, and Form B to be read only, when JoeUser is logged in. I've set up the JoeUser account, and put him in the JoeUserGroup. I set the Users group to have no permissions at all, on any form, because I want to be sure that the JoeUserGroup is controlling everything. This group leaves form Form A without any permissions (specifically NOT "Open/Run"), so now Form A is blocked. I select Form B in the list, and review my options... they are:

-Open/Run
-Read Design
-Modify Design
-Administer

None of these produce the result I'm looking for (user can open the form, but not enter any data into it). 'Read Design' prevents the user from changing the design of the form, but not from changing the data it displays.

The options for controlling access to data (Read, Update, Insert, Delete data) are all disabled when I'm working with a form. I can set those on a table or query, but that doesn't help as I really only want to block access to the form, not the table (which may be used by other forms).
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853492
you could use something like this on the form's on open event:

If IsUserIngroup(JoeUserGroup) Then
Do something
Else
So Something Else
End If
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:KnowledgeWare
ID: 18853532
Yeah, I was just going over that possibility myself. Just need to figure out / be told (oh please :) ), what to call in the code to get the logged on user and his group(s).
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853590
you can use this:

Function IsUserInGroup(GroupName As String) As Boolean
Dim ws As DAO.Workspace
Dim usr As DAO.User
Dim intLoop As Integer
IsUserInGroup = False
Set ws = DBEngine.Workspaces(0)
Set usr = ws.Users(CurrentUser())
For intLoop = 0 To usr.Groups.Count - 1
If StrComp(usr.Groups(intLoop).Name, GroupName, vbTextCompare) = 0 Then
IsUserInGroup = True
Exit For
End If
Next intLoop
End Function
0
 
LVL 75
ID: 18853665
'JoeUserGroup'

Hey ... don't abuse my name, LOL :-)

mx
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853685
don't listen to him... joe's are the ones you have to look out for....

:)

0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853721
If IsUserInGroup(CurrentUser(), "JoeUserGroup") Then
    'lock the form
Else
    'don't lock the form
End If
0
 

Author Comment

by:KnowledgeWare
ID: 18853733
That looks good, thanks. I'll give it a go. :)
0
 

Author Comment

by:KnowledgeWare
ID: 18853817
Oh, and sorry Joe... I'll use a WilburUserGroup next time.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853818
haha
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 18855110
A much quicker way to determine Group membership:

Public Function IsUserInGroup(GroupName As String, UserName As String) As Boolean

Dim usr As DAO.User
Dim wsp As DAO.Workspace

On Error Resume Next
Set wsp = DBEngine.Workspaces(0)
Set usr = wsp.Groups(GroupName).Users(UserName)

IsUserInGroup = Err.Number <> 3265

End Function

Use it like this:

IsUserInGroup "Admins", CurrentUser

would return True if the currentuser were a member of the Admins group ...

There is really no reason to iterate the group members; DAO will always throw the 3265 error if you pass in the wrong group or a user who isn't a member.

The Access User Level Security FAQ has some good code examples of how to determine User and Group membership ... download it from the link of this page: http://www.jmwild.com/Accesssecurity.htm

Also, please make sure that you fully understand ULS and how it interacts with your database. The link to Jack MacDonald's at the page referenced above is (IMO) required reading for anyone wishing to use ULS in their app.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now