DB security

[mike30x]

Is it possible to use the information created using the database security wizard?

I would like to have a drop-down menu of the users & Admin in a form designed by myself. Can i get the infromation to do this or is there another way i can limit other peoples access using my own security form.

Does anyone have any examples on this?

 

[Dennisk]

isn't this a double post. as i've replied to this post already

 

[mike30x]

Sorry i thought i posted a reply there yesterday and was wondering why i got not answer, it turns out it never went through so i decided to ask again so you are right.

What i asked yesterday which never sent was how do i use the call back function i have serached high and low for it and have not seen it referenced. Would you be able to show an example of its use?

 

[ianward]

Hi i did find a function that gets the user names when using access' user level security as follows:

Function GetUserNames(ctl As Control, varID As Variant, varRow As Variant, varCol As Variant, varCode As Variant) As Variant

    Static swrk As Workspace
    Static sastrUsr() As String
    Static sintUsrCnt As Integer
    Dim usr As User
    Dim varReturn As Variant
    Dim strName As String
    
    Set swrk = DBEngine.Workspaces(0)
    Select Case varCode
    Case acLBInitialize
        sintUsrCnt = 0
        swrk.Users.Refresh
        ReDim sastrUsr(1 To swrk.Users.Count)
        For Each usr In swrk.Users
            strName = usr.Name
            If strName <> "Engine" And strName <> "Creator" And strName <> "Admin" Then
                sintUsrCnt = sintUsrCnt + 1
                sastrUsr(sintUsrCnt) = strName
            End If
        Next usr
        ReDim Preserve sastrUsr(1 To sintUsrCnt)
        varReturn = True
    Case acLBOpen
        varReturn = Timer
    Case acLBGetRowCount
        varReturn = sintUsrCnt
    Case acLBGetValue
        varReturn = sastrUsr(varRow + 1)
    End Select

    GetUserNames = varReturn

End Function

add this to your form then create a combobox and set its rowsourcetype to "GetUserNames"

There is also a password change form that is really usefull which can be found on this forum herehttp://www.access-programmers.co.uk/forums/showthread.php?t=111547

as for allowing access to certain elements i adapted and saved the following module

Option Compare Database
Option Explicit
'Do not UnComment line below unless basic user rights are required
'you will then will need next number in binary series

'Public Const tcUsers As Integer = 1
Public Const tcReadOnly As Integer = 1
Public Const tcFullData As Integer = 2
Public Const tcFullPermission As Integer = 4
Public Const tcCpC As Integer = 8
Public Const tcAdmins As Integer = 16




Function tfIsMemberOfGroup(strworkspace As String, strUser As String, strGroup As String) As Boolean
    Dim varTemp As Variant
    Dim wrkTemp As Workspace
    
        On Error GoTo PROC_ERR
    
    If strworkspace = "" Then
        Set wrkTemp = DBEngine.Workspaces(0)
    Else
        Set wrkTemp = DBEngine.Workspaces(strworkspace)
    End If

varTemp = wrkTemp.Users(strUser).Groups(strGroup).Name
tfIsMemberOfGroup = True

PROC_EXIT:
    Exit Function

PROC_ERR:
    tfIsMemberOfGroup = False
    Resume PROC_EXIT

End Function

Function tfGroupsToArray(strworkspace As String, arrin() As String) As Integer
Dim wrkTemp As Workspace
Dim intCount As Integer
Dim intcounter As Integer

    On Error GoTo PROC_ERR
    
  If strworkspace = "" Then
        Set wrkTemp = DBEngine.Workspaces(0)
    Else
        Set wrkTemp = DBEngine.Workspaces(strworkspace)
    End If

intCount = wrkTemp.Groups.Count
ReDim arrin(0 To intCount - 1)
For intcounter = 0 To intCount - 1
    arrin(intcounter) = wrkTemp.Groups(intcounter).Name
    Next intcounter
    
tfGroupsToArray = intCount

PROC_EXIT:
    Exit Function

PROC_ERR:
    tfGroupsToArray = 0
    Resume PROC_EXIT

End Function

Public Function tfUserLevel() As Integer
    Dim UserLevel As Integer
    'If CurrentUser = "Ian Ward" Then
    'UserLevel = 255
    'else
    'If tfIsMemberOfGroup("", CurrentUser, "Users") Then UserLevel = UserLevel + tcUsers
    If tfIsMemberOfGroup("", CurrentUser, "ReadOnly") Then UserLevel = UserLevel + tcReadOnly
    If tfIsMemberOfGroup("", CurrentUser, "FullData") Then UserLevel = UserLevel + tcFullData
    If tfIsMemberOfGroup("", CurrentUser, "FullPermission") Then UserLevel = UserLevel + tcFullPermission
    If tfIsMemberOfGroup("", CurrentUser, "CpC") Then UserLevel = UserLevel + tcCpC
    If tfIsMemberOfGroup("", CurrentUser, "Admins") Then UserLevel = UserLevel + tcAdmins

'End If
tfUserLevel = UserLevel

    
End Function

add the following to the on_load event of your page:

Dim ultemp As Integer
ultemp = tfUserLevel

If ultemp And tcCpC Then
Me.cboSearch.Enabled = True
Me.btnGoSearch.Enabled = True
Me.btnReports.Enabled = True
Me.btnHiTech.Enabled = True

End If

to enable the elements available to that user. (nb - all buttons and text are disabled on the form view, and only enabled if a user has the necessary rights)

This will acheive what you want I think, but can be very long winded and full of mishaps waiting to happen. I have seen other posts which describe a model using a table within access containing user names and security/priviledge levels which does seem easier, have a look at both methods and see which is best for you. in either case make a backup of your database prior to trying any of the above...

Regards - Ian

 

[mike30x]

Thanks Ian will give it a go, if anyone has any improvements to make on this please feel free.

 

[mike30x]

No joy with the code keep getting a compile error do you have a sample of the database with this code in use?

 

[mike30x]

Actually got the first bit of code working its great. The piece of code below keeps getting a compile error any idea why. Also how do i add the password field.

'Public Const tcUsers As Integer = 1
Public Const tcReadOnly As Integer = 1
Public Const tcFullData As Integer = 2
Public Const tcFullPermission As Integer = 4
Public Const tcCpC As Integer = 8
Public Const tcAdmins As Integer = 16

 

[Rabbie]

Actually got the first bit of code working its great. The piece of code below keeps getting a compile error any idea why. Also how do i add the password field.

'Public Const tcUsers As Integer = 1
Public Const tcReadOnly As Integer = 1
Public Const tcFullData As Integer = 2
Public Const tcFullPermission As Integer = 4
Public Const tcCpC As Integer = 8
Public Const tcAdmins As Integer = 16

What error message are you getting? I tested this and it compiled OK for me on A2003.

 

[mike30x]

It might be the way i have attached the two pieces of code from ian. Did you just attach the second piece of code under the other or how did you link them up?

Also did you attach the code to the combo box to activate "on Click"?

 

[ianward]

'Public Const tcUsers As Integer = 1
Public Const tcReadOnly As Integer = 1
Public Const tcFullData As Integer = 2
Public Const tcFullPermission As Integer = 4
Public Const tcCpC As Integer = 8
Public Const tcAdmins As Integer = 16

ReadOnly
FullData
FullPermission
etc.... are groups which i have set up in my security file so you would need to amend these to reflect your groups, I think the defaults are users & admins

I think also that the Microsoft DAO 3.6 Object Library needs to be referenced if you dont already have it referenced.

Im a little confused around adding the password field, as the password the change password form relates to is the password setup in user security stored in the workgroup file. - unless i have mis-understood your question.

Hope this helps - Regards Ian

 

[gemma-the-husky]

i dont think you can read the passwords in the security file,

They are stored encrypted.

You can reset them if you have admin privileges, but not read them

 

[mike30x]

I knew that this would take a bit of programming but i thought it would have been used many times. If i dont use the BD security is there any other way to limit people access?