Oops.

So, this is a somewhat severe case of access violation. Obviously the dearly departed did not come back to this earth just to query our database. What was happening is that developers were using this login to do some testing on production data. Among other types of compliance concerns, you’ll find that this is a hot topic for SOX 404 IT compliance, Identity and Access Management ( IAM ).

Although this is a somewhat extreme example, it does happen from time to time. However, access violations can come in these more common forms:

• An employee that has left the company logs into the database. If it’s the actual ex-employee that’s figured out how to breach your corporate firewall, and is tapping into your database, you have a real big problem.
  
• An employee that has left the department logs into the database. This is one that’s easy to miss, and very common. Imagine that an HR employee that did have access to sensitive privacy information has recently taken a job in the Finance department. For whatever reason, he decides to use his prior login to access data he shouldn’t be accessing anymore.  If this activity is caught, you’ll have problems.
  
• An employee logs in through an application account, so that they can circumvent their own security restrictions.

All of these are examples of how a company can get into trouble, if it doesn’t architect a good solution around access management. Here’s how I would design a compliance system to take care of this.

Step # 1: Be clear on your violation

Although you have the violation conceptualized, you really need to define in clear database terms how a violation happens. Once again, leverage your auditors by doing a quick 20 minute brainstorm on how access violations can show up. For instance, I’ve already highlighted three:

• Login by a deceased ( or otherwise inactive ) employee
• Login by a terminated employee
• Login by an employee from the wrong department

Your auditors may have some more ideas. Don’t forget – your auditors drive the requirements, so make sure to use them.

Step # 2: Translate functional requirement to technical definition

After you have the functional definition of each violation, translate the violation into technical terms. Don’t jump the gun and start thinking how you will build the transformations, just think about what a violation means in technical terms. What data sources will you need to tap? How will your transformational routine identify each type of potential violation?

In our example above, you will obviously need two data sources; your HR database, and your application database. Both databases have an equivalent USER_ID, so I’m going to use that for comparisons. In my database, there’s a flag to determine whether or not an employee is “active” (e.g., not dead) appropriately named ACTIVE_FLAG, so that will come in handy. We also have a DEPARTMENT field which we can leverage. Based on this data, we’ll use the following pseudo-logic for our violation.

• Merge the HR.EMPLOYEE table and the MYAPP.USERS table by USER_ID
• If HR.EMPLOYEE.ACTIVE_FLAG <> ‘Y’ then POTENTIAL_VIOLATION = ‘Y’
• If MYAPP.USER_ID not in HR.EMPLOYEE.USER_ID then POTENTIAL_VIOLATION = ‘Y’
• If HR.EMPLOYEE.DEPARTMENT changes then POTENTIAL_VIOLATION = ‘Y’

Step # 3: Build the compliance solution

Once you have all the ways a violation can occur defined in technical terms, it’s time to build the solution. Be sure to take care of change data capture on everything. You will need to know the date and time that the employee became inactive. You will also need to know exactly when the employee was deleted from the HR database, or when the employee changed departments. In addition, you will need a time history of when users were added to your application, and when they were deleted.

Also keep in mind, that you are not building a real time system to block unauthorized users from access. This is much too technically intrusive for me. It’s better to highlight potential exceptions as you notice them, and signal the appropriate access managers, so that they can follow their process for access management ( i.e. delete logins that shouldn’t be there, or follow up on department changes).

Your target table will want to highlight potential violations as they occur. Build a target table at the grain of date / time and record each potential violation that is observed from the sources. A daily update is probably good enough, but make sure to go over the refresh schedule with your auditors.

Step # 4: Productionalize and Activate Regular Reporting

The reporting solution should be pretty straightforward, just a dump from your target table on a daily ( or whatever you refresh schedule is ) basis. As stated above, your access manager’s job is easy at this point. All the exceptions are already highlighted, so the only thing left to do is handle the exception ( i.e. delete the login from the application ).

With this system in place, you can not only prevent violations, but you can easily demonstrate to your external auditor that you have things well under control. You may even want to produce some external auditor driven reports that you anticipate ahead of the audit.

Access control is a hot issue that can be controlled with a little bit of focus, and involvement of the right technical team. Making sure you architect your access management compliance system properly will keep the ghosts out of your system!