Don't Ignore More Than You Expect - Notes On Whitelisting Objects in Policies

by John Haldeman, Enterprise Architect

We had a customer using DB2/z that was receiving a lot of events from SYSIBM.SYSDUMMY1. So, they wanted to implement a rule as shown in Figure 1:

Figure 1: Policy Rule to SKIP LOGGING on SYSIBM.SYSDUMMY1 - Click to Enlarge

This seemingly simple and innocuous rule has some unintended consequences. That's what this blog post is all about.

I'm going to switch to ORACLE, because that's the lab environment I currently have running. The ORACLE equivalent rule is the following:

Figure 2: Policy Rule to SKIP LOGGING on DUAL - Click to Enlarge

Let's add another rule, to make a total of two, to illustrate that this ignores more than you might expect. That's pictured in Figure 3:

Figure 3: Policy Rule to Log Full Details on All Traffic Not Skipped by the Rule in Figure 2 - Click to Enlarge

Let's run some commands (shown in Figure 4), and see what the results are in Guardium (Figure 5).

Figure 4: Commands run in ORACLE environment

Figure 5: Resulting Data in Guardium

This looks good. The SELECT on hr.employees was captures, but the SELECT on dual was not - looks like it's running as intended.

Now for the unintended consequences. How about  the SQL in Figure 6:

Figure 6: Exection of "select count(*) from hr.employees, dual"

Same result as "select count (*) from hr.employees". In fact since "dual" is a single record table, you can append it to any list of tables and the same results will be returned (because it's a cartesian product on a table with one row in it). This statement is skipped by Guardium since it contains dual as one of the objects in the SQL statement - not what we intended.

If you are more concerned about data integrity, here's is a use of dual to modify data. It is skipped by our policy as well:

Figure 7: Execution of 'insert into hr.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) select 10000, 'John', 'H', 'john.haldeman@skipped.com', '07-JUN-02', 'AC_MGR' from dual;'

That's a compound INSERT into hr.employees with a SELECT of literal values from dual. Since it is evaluated as one statement in the policy, the record is skipped.

Now, these examples may seem contrived, but the fact is that the policy can be circumvented if the person running the commands knows enough about the policy - not a good practice. The solutions to this vary. Some people try and use regular expression matching to try and avoid missing the true positives. That approach, to me, relies on the person writing the regular expression to know an awful lot about SQL and every way a dummy table might be used. As such, my preferred method is to not ignore the statements at all. Instead filter them out at the report level where Guardium makes an effort to split the statements above into sentences and distinct objects.

One final note before I end this posting. This problem extends to all SQL based criteria that you might be whitelisting. SQL statements may contain many COMMANDS as well as objects, so if you are in the business of whitelisting watch out for the same unintended consequences with those criteria. Session level parameters on the other hand do not have the same kind of problem - this is only one database username and client IP per session for example - so whitelisting there is quite safe.