Solved

ACCDB databases and Active Directory

Posted on 2013-11-01
6
920 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
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
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.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now