Solved

ACCDB databases and Active Directory

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

828 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