Solved

ACCDB databases and Active Directory

Posted on 2013-11-01
6
898 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

18 Experts available now in Live!

Get 1:1 Help Now