Hard-Boiled Compliance

How To Survive an Agile Project as a DBA

NOTE: This originally appeared on this date at Quest Software's ToadWorld, on the expert blog "John Weathington's Quest for Compliance". The link to the actual ToadWorld article is at the bottom.

Let me give you one piece of advice as a DBA, Developer, or IT manager on a compliance project, that I hope will stay with you for the rest of your career. This is a gem that I want you to record on the hard drive of your mind, then secure it down so that it never leaves.

John Weathington’s Golden Gem of Compliance Development:
Expect that your requirements will change --  a lot.

I embraced the attitude of “agile” developers more than a few years back now, and it has saved me time and time again in my development efforts. This is actually a golden rule to carry with you on any IT project. I very rarely see a situation, where the business knows exactly what they want at the time of project initiation.  This is especially true on a compliance project, because usually the urgency forces you into action fast, then change as you go.

I’ve actually done a lot of thought leading ( there are others, I’m obviously not the only one ) on developing database solutions using the agile philosophy. My work is practical, not theoretical, and I’ve been able to get demonstrated success where there was no other way to get success.

This is not an article on agile practices, and I’m not going to try to build a case for agile here. This is however, a primer for database people that need to support an agile project. This article assumes that your project manager is actually pretty advanced in his thinking, and wants to take an agile approach to building a compliance data system.

Here’s a few things you need to know about, to prepare you for how this will execute:

• As I stated in my Golden Gem, the requirements will move extremely fast. Every two to four weeks ( the iteration length ), you can expect to have a new set of requirements. These requirements may be in line with what you were thinking, or they may come completely as a surprise, forcing you to re-architect your entire solution. You won’t know the requirements ahead of time. You will learn of them on day one of the iteration. You have to get over this, and just be open-minded. The techniques in this article will help you “cope.”
• At the end of every iteration, you will be required to deliver a complete, working solution. No prototypes, mock-ups, or “half-way” solutions. This means you will need to be amazingly focused, and every hour counts.
• You will need to reframe your thinking of “re-work”. On an agile project, there is no re-work. Everything is an exploration to a better, evolving solution. Don’t think of work that’s “wasted.” No work is wasted, everything builds on discovery.
• Plan on working face to face with people, with the primary mode of communication being in-person and verbal. You may even have a partner attached to your hip all day, if your project manager is employing “Pair Programming.” If you’re the kind of person that likes “alone time”, or works better by yourself, you will have a very difficult time on an agile project.
• I hinted to this in the first bullet, but above all, keep an open mind. It would be a very rare case if you completely agreed with everything that’s going on ( unless you have some experience with agile already ). It’s is vitally important that you go with the flow. Most agile projects fail because of attitudes, and nothing else.

Okay, there’s your primer. So once the decision has been made that your project is going agile, here are 4 specific things you need to do right away to survive this:

• Setup a version control system, and practice using it. If you have a version control system like CVS or better yet Subversion in house that’s great, however you may want a separate repository for your development effort. Expect that you will be versioning out every minute or so. That’s right, every minute or so. You need to get into the habit of just blasting out code and not worrying about whether or not it will mess things up. Think of your CVS system as your “super-undo” system. Just hack. If things go bad, you can always go back to a previous version.
• Setup a build script, that will build your database, and load it from scratch. All your build SQL should be text based, so this is a perfect fit for your version control system. Make the script universal, so you don’t have to keep tweaking it. It should kill your database, poll your directories, look for SQL, execute the DDL, and execute your transformation routines – all hands-off. This script will be run every night, so you need some sort of scheduler ( i.e. cron ) to kick it off. This is called the “nightly build.”
• Setup a test harness, that will test everything in an automated fashion. The best tool I know to leverage in this case is Quest Code Tester. Agilists are huge on testing. In fact it’s not uncommon to write your tests first, then your code ( this is called Test Driven Design ). You will need to execute all of your functional tests at the end of your nightly build, to make sure that the new system is working as planned. If not, you can always rollback – don’t forget the CVS system ( there is a method here ).
• Start focusing on a very robust metadata system, that will among other things, generate the SQL that your build script will execute. You should also have a script that can look into your metadata, and create automated documentation. With things moving so fast, there will be no time to spend on documenting, and re-documenting. The system should create its own documentation.

There’s more to consider but these are the keys. Agile projects can be very demanding, but extremely worthwhile. Surviving them as a database developer requires you to setup a version control system, test harness, automated build and test script, and extremely robust metadata. And above all, keep an open mind. Your success depends on it.

... read the article on ToadWorld

Fire Your Process, Not Your People

Why is it, that when things are going wrong in a company, the first target is their own employees?

I have a major client that just announced yet another round of layoffs. I guess if you believe the news, times are hard, but is firing your employees really a good idea?

Let's get more specific into compliance. If your compliance program is not going well, is it because the people? Probably not.

Roberta Matuson, a leading HR expert who blogs on Generation Integration put it perfectly. Companies tout how "great" they are to their employees, then send them down the river at the first sight of trouble. Enough with the double talk. If things are not going right, it's probably not your people anyway.

For those of you who follow my work, you know there are three general areas of compliance; people, processes, and data systems. All three need to be in sync in order for your compliance program to be successful. The surprising reality however, is that all three are not created equal. If you pick apart compliance into these three areas, and look for the biggest contributor to dysfunctional compliance, it's not people at all - it's processes.

In fact, people only account for 5% of the problems in any compliance program. Processes are usually at the heart of a dysfunctional compliance program, because they set the foundation for everything else to come. People follow processes, and data systems are built to support processes, but process creation comes from policy, and that's what matters.

So, if things don't seem right on your compliance program, launch a project to fix ( or overhaul ) your process -- your people are probably doing just fine, leave them alone.

How To Keep Dead People Out of Your Database

NOTE: This originally appeared on this date at Quest Software's ToadWorld, on the expert blog "John Weathington's Quest for Compliance". The link to the actual article on ToadWorld is at the bottom.

What happens when you have dead people logging into your database? This actually happened to me. I was going through an audit, and the auditor wanted to know all the people that recently accessed the database. So, we produced a list of all the unique logins in the past month. Then, the auditor went to his records, and pulled out something from his “secret file.” He then started looking at the login records, and compared them to the names on his list. Before long, he made a comment that nobody in the middle of an audit wants to hear. He said, “According to my records, this person that logged in last week is deceased!”

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!

... read the full article on ToadWorld

Before All Else Fails

The other day I was having trouble with my cell phone.

I tried to call my wife, and I could hear her, however when I spoke she couldn't hear me. Initially, I thought something was wrong with the phone she was talking on, so I called her cell phone. The same thing happened.

By this time, I'm starting to figure out that something is wrong with my phone. To test this theory, I called 411. When prompted, I stated the city and state, however once again she didn't hear me on the other end. After a few attempts, the operator gave up with a "No response," then hung up.

Frustration started to set in. I started to visualize the process of taking the phone in, having them look at it, hearing that I need a new phone, picking out a new phone, transferring all the info over to the new phone, and so on.

Like I really need another "To Do."

But what was I supposed to do? I cannot operate without my cell phone.

A few hours later, I called my carrier ( Verizon ) to see what my options were. The guy who answered the phone was very polite. After explaining my problem, he said, "Okay, why don't we try this. Just turn the phone off, and turn it back on again."

I immediately started feeling like a dope. As many times as I've had to reboot my computer, and I couldn't figure out "Shut off the phone?"

Sure enough, I followed the instructions, and the phone worked fine after that.

Lesson learned - don't wait until all else fails to trip over the easy solution. Before all else fails, just hit the power switch.

Flawless Compliance Newsletter - May 2008 Issue

The May 2008 issue of Flawless Compliance is available, with Center Stage article, Lessons Learned from the Milken Opacity Index, How Transparency Can Give You a CLEAR Advantage. Also, a Billion pound British blunder, how to handle compound risk, a former Monster exec may be looking at a new job as a prison inmate, and South Park's answer to fixing the Internet.

... read the May 2008 issue of Flawless Compliance

If I Were You

This morning, the project manager for our e-x-t-e-n-d-e-d remodeling project came through with his top guy, because they think they're close to being finished. A few weeks back, they thought they were finished too, until we did a walk through ... and I guess they weren't really finished, because the result of that walk-through was about 2 pages of "unfinished stuff."

So, here we are again. The project manager said to my wife this morning, "I think we're done. We've done everything on the list." My wife turned to him and said, "What about the paint outside?" The contractor very quickly intercepted my in-flight approach to the front door, and said, "You know, we're just trying to get everything done on this contract, so that we can get paid."

Ah, there's a big surprise. They've been saying they want to get paid for about a month now, and I really don't mind paying them, except for one small thing standing in the way...

My house isn't done yet!

What my contractors consistently fail to understand, is that I am not driven by the act of paying them -- they are driven by the need to get paid. All I hear from their mouths is about what they want. I've never heard them say, "I just want to make sure that when I leave, you have a beautiful and functional house."

This is a mistake that I consistently see in compliance projects also. Please keep in mind, that whoever is paying for the project, has the focus. Their needs must be the primary concern for the project. All actions should derive from what they want. And, if they change their mind ( yes, they're allowed to do that ), then you adjust to accommodate their new needs.

If you were spending hundreds of thousands on a remodel, wouldn't you want somebody to care about what you need?

ToadWorld - To Write, or Not to Write? Good Question!

Should your compliance data system be read only, or should you be able to write back? I’ve often compared compliance data systems to data warehouses, because they downstream transactional systems. So the generally accepted axiom of data warehouse gurus is, “A data warehouse is a read-only system.”  What about a compliance data system?

... read the full article at ToadWorld

ToadWorld - Keeping It Under Control With SOD

For the last couple of weeks, we’ve been talking about common controls that you will see in the mitigation of risks at your company. First we talked about reconciliation, then last week we addressed approvals. This week we’ll wrap up this series, by talking about “SOD.”

No, I’m not suggesting that you install a new lawn. SOD stands for Segregation of Duties ( or sometimes it’s referred to as Separation of Duties ). It’s not an acronym, so you’ll never hear it pronounced like the word “sod”. However, it’s commonly shortened to its initials – S. O. D.

... Read the Full Article on ToadWorld