Multi User Login/Restrictions

[Stang02GT]

I am in the process of creating a database for a "Team" here at work. One thing that needs to be incorporated with this database is each user will have a log in and password I know how to do that.

What I am not sure of is, I need the "Team Lead" to have Access to reports and different forms when he logs in. The other users should not have access to these items.

Is it possible to assign user permission in access?

[ADezii]

Is it possible to assign user permission in access?

Yes it is. The easiest approach would be to assign/deny specific Object Permissions to Groups, not Users, then specifically place Users into the appropriate Group. Be forewarned that this is a very confusing and fairly complex process that will take a lot of time and effort on your part. Be sure to save the original Workgroup Information File (WIF)!

[n8kindt]

hehe i just finished creating a login from scratch. it took me about 10hrs to do it (now it would take me about 4hrs since i now know exactly what to do), and i feel like it is as secure of a login as you can get with access. basically what i did was create a login form that opened on startup and upon the user logging in, i stored all of that person's data (name, email, permissions, whatever you need to know about the user) into a hidden form called "frmCookies ." it runs at all times until the user logs out and then this form is closed. and if anyone tries to open ANYTHING without logging in first, i set the "deactivate " event of the login to close the database if there were no cookies in place--completely locking anyone (myself included i found out!) out of the database if they haven't entered in their username and password correctly first. frankly, that wouldn't have been needed if i just converted the file to a .accde or (.mde if u dont have access 2007) format but it makes sense if you want certain users to make design changes and build queries in the main access program. if u have any further questions on how to set one up, make sure to pm me.

now, i don't mean to hijack your thread here, but since it's related (and you would probably be interested as well) i'm still working on trying to figure out how to have it automatically logout a user after a set amount of time. if anyone has any ideas please post them!

[Stang02GT]

now, i don't mean to hijack your thread here, but since it's related (and you would probably be interested as well) i'm still working on trying to figure out how to have it automatically logout a user after a set amount of time. if anyone has any ideas please post them!

No problem at all feel free to ask more questions in here because i'll learn from the questions your asking and you will get answers to the ones i'm asking.

[NeoPa]

Rather than asking other questions in this thead, ask it in a new thread, but post interoperating links between them.

That way a question will be noticed more easily by ALL the experts, and at the same time, any interested parties (already subscribed to this thread) have the opportunity to link easily across if they choose.

[n8kindt]

No problem at all feel free to ask more questions in here because i'll learn from the questions your asking and you will get answers to the ones i'm asking.

so did u get your question answered? if u need any help, let me know

[Stang02GT]

so did u get your question answered? if u need any help, let me know

Yes and no. I know you can do it, but i'm not exactly sure how. I still have to play around with it. Do you have any suggestions as the best way to start ?

[PianoMan64]

Yes and no. I know you can do it, but i'm not exactly sure how. I still have to play around with it. Do you have any suggestions as the best way to start ?

The best way to get started is, you have to learn how to create the MDW file (a.k.a. Workgroup file) that will house all your users in the database.

If everyone is going to have access to all form, then the simplist way to accomplish that is simply create a usergroup in the Workgroup file that will have the permissions that you would want all users except for youserlf to have when using the application. If the default settings for account USER is acceptable, then you can use that as a default.

The trick in all this is the Function CurrentUser(). This function is what makes the world go around with implimenting the Workgroup file.

You can test for who the current logon user is by calling this function, and then make desicions based on who it is.

Keep in mind that you can't hardcode every user into the program, so what I would recommend, is to setup a User_Config table that will house all the user settings for various elements of the program. Example:

Table structure
 
User_Config
------------------------------------------------------------
UserName					Primary Key
IsAdmin						Yes/No

When you open the form, you have a function that will read the database and see if the user is an Administrator user, then you can have the program deside what that user will have access too if for some reason the user is NOT an Admin.

Example:

[Code=vb]

Function IsAdmin() as Boolean
On Error Goto UserNotFound
Dim MyDB as DAO.Database
Dim MyRS as DAO.Recordset

Set MyDB = CurrentDB()
Set MyRS = MyDB.OpenRecord set("SELECT IsAdmin FROM User_Config WHERE UserName='" & CurrentUser() & "'",dbOpenSnapS hot)

IsAdmin = MyRS!IsAdmin
MyRS.Close
MyDB.Close

Set MyRS = Nothing
Set MyDB = Nothing
Exit Function

UserNotFound:
IsAdmin = False
MyRS.Close
MyDB.Close

Set MyRS = Nothing
Set MyDB = Nothing
End Function
[/Code]

Then you simply in your body of the code you make a desision what items or controls that user is going to be able to see on that form.

Example:

[code=vb]

If IsAdmin() = True Then
me.ControlName. Visible = True
Else
me.ControlName. Visible = False
End if

[/Code]

[ADezii]

[CODE=text]Public Function fIsUserAnAdmin( ) As Boolean
Dim wrk As DAO.Workspace
Dim grpAdmins As DAO.Group
Dim usr As DAO.User

Set wrk = DBEngine.Worksp aces(0)
Set grpAdmins = wrk.Groups!Admi ns

fIsUserAnAdmin = False 'iniitialize to False

For Each usr In grpAdmins.Users
If usr.Name = CurrentUser() Then 'yep, an Admin
fIsUserAnAdmin = True
Exit For
End If
Next

Set usr = Nothing
Set grpAdmins = Nothing
Set wrk = Nothing
End Function[/CODE]

[Stang02GT]

Thank you very much, I am going to sit down and play with this later today.

[n8kindt]

i'll go ahead and post my code too, i guess. i'm not using a workgroup file and i had actually never heard of it before. i also like ADezii's function so maybe you could play around with our codes and even make a hybrid out of them somehow. let us know what u come up with.

this click event belongs in the login form when the user clicks ok. the form contains two textboxes: username and password

Private Sub Command4_Click()
    Dim rs As dao.Recordset
    Dim tbl As TableDef
    Dim db As dao.Database
    Dim User, PW As String
    Dim TriesL As Integer
    
    
    PW = Nz(Password.Value, "")
    User = Nz(Username.Value, "")
    
    
    Set db = CurrentDb
    Set tbl = db.TableDefs("tblAccounts")
    Set rs = tbl.OpenRecordset()
    
    
    If User = "" Then
    MsgBox "Please enter a username."
    Username.SetFocus
    Exit Sub
    End If
    
    If PW = "" Then
    MsgBox "Please enter a password."
    Password.SetFocus
    Exit Sub
    End If
    
    If Tries.Value < 4 Then
        Do While Not rs.EOF
        'Debug.Print rs!Username
        If rs!Username = User Then
            'Debug.Print rs!Password
            If rs!Password = PW Then
                
                
                GoLogin.Value = True
                DoCmd.Close
                DoCmd.OpenForm "frmCookies", acNormal, , , acFormEdit
                Forms!frmcookies.Visible = False

                'lookup and store permissions assigned to departments

                Forms!frmcookies.Username.Value = rs!Username
                Forms!frmcookies.Password.Value = rs!Password
                Forms!frmcookies.accounting.Value = rs!accounting
                Forms!frmcookies.commission.Value = rs!commission
                Forms!frmcookies.WebStore.Value = rs!WebStore
                Forms!frmcookies.editing.Value = rs!editing
                Forms!frmcookies.admin.Value = rs!admin
                Forms!frmcookies.showroom.Value = rs!showroom
                Forms!frmcookies.Email = rs!Email
                Forms!frmcookies.priveleges = rs!priveleges
                
                DoCmd.OpenForm "start"
                DoCmd.RunMacro "autoexec1"
                
                
                GoTo CLoseRs
            Else
            Tries.Value = Tries.Value + 1
            Tries = 3 - Tries.Value
            MsgBox "Incorrect username or password. You have " & TriesL & " tries remaining."
            Username.SetFocus
            Exit Sub
            End If
                

            
        Else: GoTo NextLoop
        End If
        
NextLoop:
        rs.MoveNext
        Loop
        Tries.Value = Tries.Value + 1
        MsgBox "Incorrect username or password."
        Username.SetFocus
         Exit Sub
    Else
    MsgBox "This application has been forced to close."
    DoCmd.CloseDatabase
    End If
    
CLoseRs:
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

using frmCookies, i can reference all the user's settings, and even prompt them for a password without having to open recordsets everytime a form wants to check a user's permissions.

[Stang02GT]

I will Definitely post whatever i come up with!