Solved

ACCDB databases and Active Directory

Posted on 2013-11-01
6
976 Views
Last Modified: 2013-11-01
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?
0
Comment
Question by:ParisBP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 75 total points
ID: 39616829
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
 
LVL 15

Accepted Solution

by:
unknown_routine earned 350 total points
ID: 39616851
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39616859
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39616945
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
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 75 total points
ID: 39616997
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
 

Author Comment

by:ParisBP
ID: 39617541
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

738 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