Getting Active Directory Group Membership in Access

Okay, it’s time for something a little geeky that I’m rather proud of.

Here’s the deal:  At work, we have a Microsoft SQL database with an Access front end.  At the moment, there’s no real permissions model or anything, but my boss wants me to start fine-tuning that a little.  I want something that both I can look at and that she can look at and have it make sense, while also making sure it’s secure enough where any halfway savvy user can’t just break it.  I thought about a number of different approaches, including…

• Creating a set of tables and queries in the database itself that handles permissions via Access VBA functions.  Finding a way to restrict access to the tables to just the people I want access to would have been problematic, in that it would require a second step (Access Workgroup Security or MS SQL security), which defeats the whole point.
• Access Workgroup Security - This was a no-go because of how the Access front-end is deployed.  Plus, it seemed overkill for something we want to throw in gradually.  That said, we may end up adopting this later, or just changing the front-end so it’s web-based instead of Access based, that way it’s a little easier to manage or abstract away some of the
• MS SQL security - Good on a table-by-table basis, but not particularly useful or easy to manage on a column-by-column basis, which is basically what we’re looking for.

So, I thought to myself, what if we managed group membership in Active Directory (editable by only domain administrators here) and created a function in Access that could query that to determine whether or not a user is a member of an appropriate group?  Sounds simple, right?  Well, it took a bit, but, after playing with Scriptomatic and finding a WMI call that Access would actually let me pull off, this ended up becoming the final result:

Function GetGroupFromAD(GroupName As String) As Boolean
' Checks to see if the current user is in the group name
' passed to the function. Useful for managing permissions.

    GetGroupFromAD = False

    Dim strGroupComponent As String
    Dim strPartComponent As String
    Dim strDomain As String

    ' These strings build the WQL query that's passed via WMI.
    strDomain = """Your Domain Name"""

    strGroupComponent = "GroupComponent='Win32_Group.Domain=" & _
              strDomain & ",Name=""" & GroupName & """'"
    strPartComponent = "PartComponent='Win32_UserAccount.Domain=" & _
             strDomain & ",Name=""" & Environ("UserName") & """'"

    Set objWMIService = GetObject("winmgmts:\\" & _
             Environ("ComputerName") & "\root\CIMV2")
    Set colltems = objWMIService.ExecQuery("SELECT * FROM " & _
             "Win32_GroupUser WHERE (" & strGroupComponent & _
             " AND " & strPartComponent & ")", "WQL")

    ' If the query returns a record, that means the
    ' user is a member of the group. Otherwise, the
    ' query will return no records.
    If colltems.Count > 0 Then
        GetGroupFromAD = True
    End If

End Function

Then, you could do things like…

Private Sub Form_Current()
    Me.SomeField.Locked = Not GetGroupFromAD "Group Name"
End Sub

Granted, someone could still get into the table itself and munge things if that was their pleasure, but, to be fair, if you’re using an Access front-end, you’re probably not looking for Fort Knox-style security anyways.

Enjoy!

Poor Gaius Baltar…

I ate far too much Mexican today - my stomach feels like it’s going to explode in a salsa-filled, refried bean infused tidal wave of death and destruction. As usual, though, that’s entirely besides the point.

Battlestar Galactica is one of those shows that I would absolutely love to watch when the episodes come out. Unfortunately, I came into the series way too late, and, from where I’m sitting, there is nothing worse than jumping in the middle of a series, especially one where most episodes build on top of each other like BSG. So, I’ve been slowly purchasing the DVD box sets, watching them, one by one.

This weekend, I finished Season 2.5, which, for various geek-related reasons, I like to think of as Season 2 SP1. If you’ve never watched Battlestar Galactica (the new version, not the campy ’70s version, which is still decent in its own right), much of this isn’t going to make sense. If you actually watch it as thoroughly as you’re supposed to, much of this will be old news, seeing as they’re up to Season 4 now and all.

Some things that ran through my mind after finishing it (and, yes, I’m going to be purchasing Season 3 soon, and, yes Rachel, I’ll use your Amazon link):

1. Dr. Baltar is such a tool. So, let’s see here… he dooms humanity by letting his Cylon love buddy get close and cuddly to his defense system (NOTE: What defense department would actually allow one person to design their entire system? Honestly, I think the Colonials deserved to lose). Thanks to his Cylon lover hacking his defense system, there are all of 40,000+ humans left. So, when he runs across another copy of his Cylon lover, what does he do? He gives her a gun and, eventually, a nuclear warhead. What does she do with the warhead? She shoots it off, letting the Cylons know where New Caprica is. In short, he manages to successfully sell humanity out to the Cylons twice within two seasons. Brilliant!

2. Though I’ve been trying to keep my eyes closed on various plot spoilers as the series has progressed, I am aware that Season 3 is where a lot of people become unhappy because of the supposed parallels between the Cylon occupation of New Caprica and our occupation of Iraq. Before I see Season 3, here’s my take on this:

The Cylon occupation of New Caprica would be, at best, analogous to Germany occupying Israel in 1949 - yeah, maybe the Germans were a little better behaved by then towards Jews than they were in 1945, but, c’mon, you think the Jews wouldn’t make life for any German occupier a living hell? Point being, we didn’t kill 99.99999999% of the Iraqi population, so any comparisons between an occupation of Iraq and a Cylon occupation of the remainder of humanity is a false one. Consequently, I absolutely refuse to take the Cylon occupation personally. I just won’t have any of it.

3. Black Market was, in fact, as bad as I heard. It didn’t advance the plot at all, it didn’t make any sense… yeah. It just didn’t jive. Here’s hoping they don’t do something quite that stupid again. The only bright note of that episode is they wiped out Commander Fisk, who really didn’t make any sense as far as the rest of the cast goes. While we’re at it…

4. Wow, the Pegasus offered up surprisingly little resistance to Adama taking over. I’m sure a lot of them were happy that the very MILF-like Admiral Cain died, but, even so, they spent maybe three episodes tops on how well Pegasus would be able to integrate with the Battlestar Galactica crew. It just seemed a little too easy.

Now, with all that said, I just want to point out that writing a decent science-fiction show is tough - way tougher than writing anything else, in my opinion. Fans of science fiction shows are far more demanding as far as plot consistency goes. That BSG is turning out as well as it is, in my opinion, is a damn good thing… even if I already know that a bunch of unlikely people will later turn out to be Cylons. Though, for what it’s worth, at least Colonel Tigh will have an excuse for his incompetence…

Thus endeth my geekitude. You may now get back to the rest of your Internet.

UPDATE: Holy frak! It would seem I was more in tune with the Porke Method than I realized, sloshing through some of it at the same time that Rachel did. Sweet. Yeah, I already know about Razor, and, yeah, I was planning on waiting ’til after Season 3 to deal with that, though I understand that, chronologically speaking, it happens between Season 2 and Season 3.

MY EARS! THEY HAVE BEEN VIOLATED! (a.k.a. I discovered the Star Wars Christmas Album)

Oh, the bad, horrible places my computer takes me to… I’m, of course, referring to a review of the Star Wars Christmas Album. Now, I know what you’re already thinking - how bad could a review be? Not too bad… not too bad at all, actually… unless they do something truly heinous, something truly devious, something truly evil:

Have a link to the music.
(NOTE: Be nice enough to visit the article before clicking on this - seriously, they have to pay for the bandwidth somehow. Oh… it’s a trap!)

Oh, it’s terrible, all right. It has all of the charm and decency of the Star Wars Christmas Special, only with songs being sung by C-3PO and R2-D2.

Trust me - it’s much worse than you can imagine.

It’s playing on my laptop right now. I’d stop it, but I’m afraid it might choke me or something. Seriously, the dark side is strong with this one. My ears can’t repel assery of that magnitude!

(Okay - I stopped it. Not dead yet. Thank Allah and praise Vishnu!)

God… shoot me. I can’t unhear it. I can’t! I really can’t! It’s going to take hours of therapeutic vodka shots up my nose to properly dull my brain stem sufficiently to make the madness stop! GAAAAAHHHHH!!!

I yam what I yam

Courtesy of Pamibe, I noticed that she had taken a series of personality tests. Well, I’m a sucker for personality tests. So, without further ado, behold… the borderline autism exposed by yours truly!

Oh yes - that’s right. I’m right there with great people like the Olsen twins, Rick Moranis, and Gerald Ford… among others.

Now, if you’ll excuse me, I have a Union to preserve…

We need more geekiness

Thankfully, one of my friends is more than happy to oblige… I bring to you the latest addition to my blogroll:

Cardozaisms

It’s the musings of a college professor who is just so slightly better educated than I am… and a little more verbose. You’ll never see a critique of FDR’s administration like this again.

Enjoy!

This is far too cool

Of course, by “cool”, I mean, “geeky as hell.”

Glenn Reynolds, the irascible law professor of Instapundit fame, enjoys throwing little articles like this at his unwitting readership. After digging a little further into the article, I found this…

Lord of the Rings as Property Law

Let’s take a hit of that, shall we?

The novel The Lord of the Rings was a phenomenon. The movie trilogy based upon it has grossed over a billion dollars and won a slew of Oscars.

But what’s really interesting about the work is that it is about property law.

Seems Like a Property Exam

Consider the following facts which seem ripped from a first year property law exam:

1. Sauron holds ownership in the Ring through accession, by working one thing (base metals) into a new thing (a ring of power)
2. He is dispossessed by Isildur, who now holds possession in the Ring.
3. Isildur loses the Ring (he has a manifest intent to exclude others but no physical control) when it slips off his finger as he was swimming in the Auduin river to escape from Orcs.
4. Déagol finds the Ring.
5. He is dispossessed by Sméagol (a.k.a. Gollum).
6. Gollum loses the Ring and it is finally found by Bilbo.
7. Bilbo gifts the Ring to Frodo. Later, Aragorn (the heir of Isildur) tells Frodo to carry the ring to Mordor, making Frodo his bailee.
8. Sam, assuming that Frodo is dead, takes the Ring according to instructions to help Frodo with the Ring in grave circumstances. Sam is acting here as a (fictional) bailee and he returns possession to Frodo after finding him still alive.
9. At the end of the book, Gollum restores his possession of the ring. Seconds later, he and the Ring are both destroyed. At this point all property held in the Ring disappears.

The article then proceeds to explain whether or not Sauron has a legal right to claim The Ring as his property. Go ahead and read the article - I’m not spoiling the ending for you.

This does make me wonder what other novels and pieces of literature can be examined with a purely legalistic mind. Would an examination of the legal constructs of 1984 be compelling? What about a legalistic examination of Star Trek II: Wrath of Khan?

Oooh… I just got goosebumps thinking about that last one. Who, in fact, really owns the Genesis device? Is it Dr. Carol Marcus? The United Federation of Planets? Khan Noonian Singh? Based on that analysis of the LoTR in the above post, it would seem that Khan is merely a possessor. What gets interesting, however, is that the UFP did, in fact, grant resources towards the construction of the Genesis Device (namely, the U.S.S. Reliant, among other things), so, depending on the terms of the grant provided to Dr. Marcus, the UFP may have sole legal ownership of the Genesis Device.

I’m stopping now.