Logging AD User Actions (with deleted users)

Question

We are about to migrate an intranet web application from using a proprietary forms-based security to Active Directory. The application logs a variety of user actions, and there is a significant amount of data associated with user accounts. Our plan was to migrate all of these UserId columns in various tables: from a foreign key linking the proprietary system, to an Active Directory GUID. Login names are identical between the two systems, so migrating is not an issue.

However, we identified one major problem: Our security policy dictates that inactive users must be deleted from Active Directory. An orphaned GUID in our security logs makes the entries pretty meaningless to anyone viewing them.

How can an application maintain the human-readable basics (name, login, etc.) about a GUID that has been deleted from Active Directory?

We have considered the following options. One of these options may end up being the optimal, but we wish to try for better:

• Denormalize the log tables and store name/login instead of a GUID (okay for logs, not so much for active data.)
• Maintain a "cache" of AD object information where entries are never deleted
• Keeping the AD account but deactivating/locking it down

Answer 1

For logging, I favor the denormalized approach, but maintaining the id. I store the user's id along with other, human-readable indentifying information. For active user's I can still use the id in joins (if necessary). For inactive (deleted) users I have the human-readable form for use by the auditors.

Answer 2

I wouldn't fully denormalize the log table, but instead store the pertinent AD information alongside the GUID, as Tim said. However, if you will need this AD information in other areas, cache it in your user table. I would recommend against changing your security policy.

Answer 3

We have decided to use a "cache" table that will store a dictionary of GUIDs that map to friendly user information. This will require a view that abstracts an AD user list, and coalesces in missing entries from the cache.