access security??? 4

[Ryon]

ok, let me start off by giving a little decription of my database. Im trying someting a little different then the switchboard approach.
I have one form with with various buttons at the top to act as a switchboard. Each one making a subform visible and the others not visible.

I have hidden the database window and the access window to make the DB appear like a stand alone application which is all controlled from the main form.

I have been asked to secure the database which I am a beginner at)depending on user accounts(logon window). With the admins having complete control of the db.

now to the questions...

1) If I use a logon form that stores the user name,group as a public variable, is there a way to unlock certain fields and give the admins the power to update the fields? easily?
can access security control field level??

keep in mind that I have 6 or 7 subforms that the admins would want unrestricted access to.

I know i could create more suforms with unrestricted access and control them from being accessed from the controls at the top, but this might tend to make the database very large and take longer to open.

I could also make seperate front ends,one for users and one for admins, doubling any updating I need to acomplish.

2) by setting up the front end this way am I going to run into any situation with record locking, visible-not visible. compared to open-closed. This database is looking like it will have around 10 users.


any help would be much appreciated.

Thanks
Ryan

 

[JeremyNYC]

Ryan,

Check out the MS Security FAQ. Don't bother trying to build your own system, as it will not be as secure as the stuff built in, and you'll not have a developer community familiar with it. There's a copy of the FAQ on my website, or you can get it from MS.

As for field-level security, there a r3e a couple of ways to go about that. One is to break your table into two tables, related one o one. Then you can secure one table but not the other. A better way to go about it, though, is to find out if the user is a member of your custom admin group and then either show or hide (or allow/disallow edits to) the fields in question based on that bit of data. There's code in the FAQ for this.

If at all possible, avoid making two front ends, as it will mean LOTS more maintenance. Two sets of forms will be more or less the same. It takes some fiddling, but you can usually take care of this stuff with code that checks what groups the user belongs to and reacts accordingly.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[psemianonymous]

First, I'd recommend you use Access' built-in security system. It's a beast to get used to, but it's the *only* way to secure your data. Print, read, and reread the FAQ/manual from Microsoft after my comments. I have links below.


1. The login form is built into the security system, though you will have to set up a specific shortcut for your database to use Access security properly. So replace coding time with configuring time.

2. The users can be put in groups, and you can check group membership at run-time for any member. Unfortunately, this feature is not built-in. I will post my code below, though I admit that the function I wrote is not optimal. It works.

3. You can see who is logged on via the CurrentUser() function. This can be used for automatically filling in "CreatedBy" fields in your tables, or for automatic logging/auditing, if you see what I mean.

4. Setting specific functionality: What I did in a previous app was to add a "Admins" tab to the main menu. On the form's load event, I set the Admins tab visible ONLY IF the user was a member of the admins group. If you need to give admins separate behavior, use the isMemberOf("Admins&quot function I have provided below.


Microsoft Access Security FAQ:

http://support.microsoft.com/support/kb/articles/Q165/0/09.asp

http://download.microsoft.com/download/access97/faq1/1/WIN98/EN-US/Secfaq.exe

(This is the Security FAQ for *ALL* version of Access)

http://support.microsoft.com/support/access/content/secfaq.asp

(On-Line version of the Security FAQ)

Public Function isMemberOf(groupName As String, Optional usrname As String = "") As Boolean
    Dim ws As Workspace
    Dim grp As Group
    Dim myGroups As Groups
    
    'Special case: the database is opened with the standard workgroup file.  Give no access
    'in this situation.
    If UCase(CurrentUser) = "ADMIN" Then
        isMemberOf = False
        Exit Function
    End If
    
    If groupName = "" Then
        isMemberOf = False
        Exit Function
    End If
    
    Set ws = getWorkspace()
    
    If usrname = "" Then
        usrname = loggedInUser()
    End If
    
    Set myGroups = ws.Users(usrname).Groups
    isMemberOf = False
    For Each grp In myGroups
        If grp.Name = groupName Then
            isMemberOf = True
            Exit Function
        End If
    Next grp
    
    ws.Close
    
    Set grp = Nothing
    Set myGroups = Nothing
    Set ws = Nothing
End Function


Public Function getWorkspace() As Workspace
    Set getWorkspace = DBEngine.CreateWorkspace("", "USERNAME_CHANGED_FOR_TEK_TIPS", "PASSWD_CHANGED_FOR_TEK_TIPS")
End Function

 

[Ryon]

Thanks for all your help, I will hit the books.

2 more questions though before I get started.


Can I use a custom logon window with access security?

by setting up the front end with miltiple subforms am I going to get into any trouble with record locking, visible-not visible. compared to open-closed.

thanks again, you guys are great. I do not know what I would do without this site

 

[Ryon]

BTW, have some stars

Ryan

 

[psemianonymous]

Can I use a custom logon window with access security?


You ... can ... though the only way I can think of is a nasty workaround: have a "logon.MDB" or "logon.MDE" that uses MSACCESS.EXE command-line parameters to fill in the user and password parameters and open up your REAL front-end.

The built-in logon screen is standard, so once your users grow accustomed to it, they can easily switch to a new database with the same logon screen. So I'd recommend you *not* overwrite the existing one, but I don't care much either way.



by setting up the front end with miltiple subforms am I going to get into any trouble with record locking, visible-not visible. compared to open-closed.

I don't get the question exactly--but maybe I just haven't gotten to implement something like this. I know that you can leave a subform unbound, and then ONLY set the SourceObject for the subform if the user has access to the subform. This is how I have managed subforms in the past. I don't know your exact question, so I'll just stop attempting to answer...


Pete

 

[Ryon]

pete,

Could you go into more detail regarding how you work subforms. I'm intriged.

I have one form with 6 of 7 subforms. I have somewhat of a switchboard on the form header which when you select an option 1 form is visible and 6 are not.

I do not know if this is the best way to do this...

I would like to be able to have one subform and be able to load one subform at a time. this would clean up my code ALOT

hopefully this explains it better. thanks for you help
you will definitly get a star if you help me figure this one out


Ryan

 

[RickSpr]

When I read your initial post, I wondered why you used multiple subforms. Subforms have rather a lot of overhead for this purpose, and can cause slow loading. Do these subforms display recordsets? If so, why not open them as ordinary forms instead?

You could use a Tab control. That would relieve you from having to write code to show/hide the controls associated with a menu button. If you prefer the button look, you can set the tab control's Style property to "Buttons". You can put subforms on the tab pages, if you really need to, but if a subform doesn't display records you'd be better off putting the subform's controls directly on the tab page, to save on subform overhead.

Another solution would be to use a single subform control, and change its SourceObject property to load different subforms into it. (I think this is what you were referring to in your previous post.) This is even more efficient than using subforms on tab pages. It has a downside, though: Each time you switch subforms, the previous subform's record is saved and the subform is destroyed, so when you switch back the subform will be reinitialized (positioned to the first record, with the first control focused, any filtering or sorting reset, etc.). This also means you can't switch to another subform to check something while you're editing a record. With separate subforms or tabs, the state information is retained when switching, you you can switch back and forth without losing your place.

Some of your original questions don't seem to have been addressed yet:

1. You can store the user account name in a Public variable, but a user can be a member of more than one group, so in the general case a single group variable won't work. You could use a public variant containing a dynamic array of group names, though.

2. If you have the user and groups in public variables, you can test them anywhere to decide whether to show/hide controls, etc. It's easy. However, I think it would be simpler and even easier to decide whether the user is an administrator at startup time, and just use a Public Boolean variable named, for example, UserIsAdmin. You could use the function below to do this as follows:
UserIsAdmin = UserIsInGroup("DBAdmins&quot

3. Access user-level security is applied at the database or object level, not at the field level. You will probably need VBA code to enforce field-level security.

Note that if you have table A with a mix of unrestricted and restricted fields, you can create table APrime with the same key (1-to-1 relationship with A) and move the restricted fields into it, then protect APrime with user-level security. (JeremyNYC mentioned this above.) That eliminates the possibility of a VBA bug exposing the data to unauthorized changes. It may also be all you need if everybody can read the data; if an unauthorized user tries to change it, they'll get a "You don't have permission..." error message. But if the APrime data isn't even visible to unauthorized users, you'll still need VBA code so that you don't even try to open the table if the user is unauthorized. (One technique is to change a form's Record Source to use either a query that joins the two tables, or one that includes only the unprotected table.)

4. It's hard to say whether having all the subforms open all the time will cause you problems with record locking. It depends on what kind of record locking you're using, on whether the user saves changes before switching subforms, etc. The potential is certainly there. But it would also be there if you used a tab control or individual form windows that can be left open.

Opening alternative subforms in a single subform control could avoid some locking problems, because switching subforms would cause any pending changes to be saved before the switch. But this is also what causes the "downside" effects I described above.

Here's a simpler equivalent of foolio12's group membership test function:

Public Function UserIsInGroup(GroupName As String, Optional UserName As String) As Boolean
    Dim grp As DAO.Group
    
    If Len(UserName) = 0 then UserName = CurrentUser()
    On Error Resume Next
    Set grp = DBEngine(0).Users(UserName).Groups(GroupName)
    UserIsInGroup = Not grp Is Nothing
    Set grp = Nothing
End Function

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Ryon]

WOW, Rick you are the man.

The solution you gave for the subforms using the source object control is just what i'm looking for. this will cleanup my code dramatically.

The reason I'm trying to do it this way is, I have built a couple of databases already using the common switchboard control. Having some extra time on this one, I wanted to try something different. If it didnt work it would be easy enough to change to a switchboard and multiply forms in a matter of hours.

I wanted to try and give the appearance of a single page program with the switchboard on the header and no access in the background

The issue you describe using this method, I dont think will be a problem with this database.

As for the security. Im going to take some time and read up on the access security before I begin. This is one access function that I havent even begun to scratch the surface.
I do however completely understand your post concerning the one to one relationship and the table level locking. This will most likely be how I do it. The data does not need to be invisible, just uneditable, as you described.

Thank you for your help guys, I think I'm on the right path now or at least headed that way.

Im sure I'll be back again


have a star

Ryan

 

[KornGeek]

foolio,
I have a function similar to yours to check the membership in groups for my users, but I never thought to check to see if the user is "admin" and automatically return False. That's a clever idea. I may implement that. Have a star for it.

The way I set mine up, I create a MyAdmin account that is in the Admins group and the Users group. I then remove the admin account from the Admins group, disable all permissions from the Users group, create a series of groups with no permissions, and one MyDBUsers group. I remove the ability to open the database at all except to users belonging to the MyDBUsers group.

When new user accounts are created in my database, they are added to the MyDBUsers group. When they have permissions enabled or disabled, I do this by adding them to or removing the from an appropriate group (which has no permissions itself). Then, when a user attempts to use a feature, I check to see if they are a member of the appropriate group.

This system was cumbersome to implement at first, but it seems to be a good balance of security and convenience for my needs.

(If you have any feedback on my approach, feel free to share it.)