ACCDB databases and Active Directory

We are upgrading a database from MDB to ACCDB format.  As such, workgroup security is no longer available.

I need to restrict use of the database.  Specific reports/forms are executed by a macro that's linked to a button on a form (menu).

How can I leverage Active Directory groups inside the ACCDB database to control the security?  Specifically, how can I script the macro to evaluate the active user's security and determine if they can/can't be let in?
ParisBPAsked:
Who is Participating?
 
unknown_routineConnect With a Mentor Commented:
Specifically, how can I script the macro to evaluate the active user's security and determine if they can/can't be let in?


Unless you are the network admin you cannot do it. Imagin if evey user could use Active directory(AD) to read other logins along thier passwords.

You do not need AD to read the login of the current user. Just use VBA:

Dim User As String
User = Environ("username")


Then I suggest to maintain a table with the desired user names. Then everytime a user wants to run a Macro you can disallow him based on his login.
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
As far as I know, you can't.
AD will not reach "inside" the mdb file.

There are many utilities available to do this, ...and you can also create your own.

But this can get very complex if your security needs are complex

At the most basic end you can create a User table:
UID
uPW
uSecurityLevel

Then create a table that lists the security levels and sets what objects each user can access.

Then create a log in form to check the UN and PW,  then looks up, and stores the Security level

But again, this can start getting complex if your needs ae more than very basic security...

Lets see what other Experts post...

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
Note that Workgroup security was great, but like any "security" system, it can never be 100% full-proof.

If you need a higher level of security then consider moving to one of the SQL Server variants for the backend.

JeffCoachman
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Jeffrey CoachmanMIS LiasonCommented:
ParisBP,

oK, I re-read your post when I read unknown_routine's post.

You need to clearly state what you mean by "Security" in this case.

You said:
Specifically, how can I script the macro to evaluate the active user's security and determine if they can/can't be let in?
As was mentioned, you can certainly use AD to stop a user from opening a db file, ...but as I stated, AD will not restrict what objects they can access once they are allowed in.

You also stated:
How can I leverage Active Directory groups inside the ACCDB database to control the security?
...this implies that you may also want to control what a user can, and cannot do once inside the DB.
Again AFAIK, this is not possible with AD.

JeffCoachman
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
Maybe this is what you want:

GetActiveDirectoryFullName() Function

Return a string that represents what Active Directory has stored as the Full Name of an passed logon id (username) within the passed domain.

Syntax

CODEGetActiveDirectoryName(strDomain, strUser)

Open in new window


Where strDomain is of type String and represents the domain to the searched; "strUser" is of type String and represents the username to be sought within the domain.


Public Function GetActiveDirectoryFullName(strDomain As String, strUsername As String) As String
' http://www.utteraccess.com/wiki/index.php/GetActiveDirectoryName
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date        brief descripton
' 1.0  2013-01-30  Initial Release
'
   'Connect to Active Directory
   Dim cnn As Object 'ADODB.Connection
   Set cnn = CreateObject("ADODB.Connection")
   
   cnn.Provider = "ADsDSOObject"
   cnn.Open "Active Directory Provider"
   
   'Create the query
   Dim strSQL As String
   strSQL = "SELECT distinguishedName" & _
            " FROM 'LDAP://" & strDomain & "'" & _
            " WHERE objectCategory='user'" & _
                  " AND samAccountName = '" & strUsername & "'"

   'Get the data and return the result
   Dim rst As Object 'ADODB.Recordset
   Set rst = cnn.Execute(strSQL, , 1)

   If Not rst.EOF Then _
       GetActiveDirectoryFullName = Split(Mid(rst.Fields("distinguishedName"), Len("CN=") + 1), ",")(0)

   'Close what we open.
   rst.Close
   cnn.Close
   
End Function

Open in new window


Return Value
String.

Example
From the Immediate Window:

CODE

? GetActiveDirectoryFullName("someDomain","someUsername")
someFirstname someLastname

Open in new window




To get MemberOf


 Some crude VBA based that quickly lists all the AD groups for the current user. This needs to be change to reflect the dc values for your Active Directory. I entered Microsoft and Com which you will need to change to reflect your AD values.



Sub OnTest()
Dim strUserName As String
Dim strDistinguishedName As String
Dim x As Integer
'get username
strUserName = Application.Session.CurrentUser
' Search for this in the AD and get the DistinguishedName
strDistinguishedName = GetDN(strUserName)
' If we get a hit then list the groups for this DN
If strDistinguishedName <> "" Then Call ShowGroups(strDistinguishedName)

End Sub

Function GetDN(strUserName)

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

objCommand.CommandText = _
    "<LDAP://dc=Microsoft,dc=com>;(&(objectCategory=User)" & _
         "(displayName=" & strUserName & "));samAccountName,distinguishedName;subtree"
  
Set objRecordset = objCommand.Execute

If objRecordset.RecordCount = 0 Then
    Debug.Print "sAMAccountName: " & strUserName & " does not exist."
    GetDN = ""
Else
    Debug.Print strUserName & " exists." & objRecordset.Fields("distinguishedName")
    GetDN = objRecordset.Fields("distinguishedName")
End If

End Function

Sub ShowGroups(strDN As String)

Const E_ADS_PROPERTY_NOT_FOUND = &H8000500D

Set objUser = GetObject("LDAP://" & strDN)

intPrimaryGroupID = objUser.Get("primaryGroupID")
arrMemberOf = objUser.GetEx("memberOf")

If Err.Number = E_ADS_PROPERTY_NOT_FOUND Then
    Debug.Print "The memberOf attribute is not set."
Else
    Debug.Print "Member of: "
    For Each Group In arrMemberOf
        Debug.Print Group
    Next
End If
End Sub

Open in new window



Now that you now the user and MemberOf groups you can  roll your own security to allow permissions on objects.
0
 
ParisBPAuthor Commented:
I think we're going to be OK with the Environ() approach, possibly using sort of user table along with it.  For the most part, it's a wide-open internal database...but there's a handful of screens that need to be restricted and that's where this particular issue comes in.

Thanks a lot for your input.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.