December 06, 2007
A lot of attention is being paid these days to database security and auditing. IBM has made great improvements in security and auditing with DB2 9.5 (Viper 2). Still, there remains a security vulnerability that many auditors and security administrators are not aware of, and I doubt that DBAs will rush to inform their management. Specifically, certain monitoring commands easily expose sensitive data to the DBA.
db2 "get snapshot for all on DBNAME"
db2 "get snapshot for dynamic sql on DBNAME"
db2pd -db DBNAME -dynamic [file=filename]
A DBA who executes these commands will easily be able to see SQL statements executed within the database with literal values shown in clear text. This means DBA team members can view Social Security Numbers, Employee Numbers, Credit Card Numbers and Expirations, Names, or any data that is provided as part of a SELECT, UPDATE, or DELETE WHERE clause, or values that are provided to INSERT statements.
Here is sample output from one of the get snapshot commands shown above:
As you can easily see, the snapshot command reveals the employee number '000200' in this example.
If your business applications use dynamic SQL (and most do), be aware that any DBA with SYSADM or SYSMON privilege can view snapshots and DBAs with access to the instance owner account can run program db2pd. These users will have ready access to sensitive data without having to run SQL statements to obtain it. Audit mechanisms may capture the invocation of "get snapshot" commands, but there will be no indication of what data was returned. The program db2pd operates outside of the DB2 engine, so there will be no trace of its execution nor audit-able trail of data returned.
What can you do?
Short of throwing away your database and returning to filing cabinets filled with paper, not much. Many organizations lock down the instance owner account and prohibit direct access or logins - this blocks use of db2pd. If a DBA requires SYSADM or SYSMON privilege, they should use their own ID which has been granted the minimum required privilege level.
What should you do?
The prudent DBA manager and IT security team should also consider looking for performance monitoring and tuning tools which obfuscate the sensitive literal values from DBA or other curious eyes. In this way, performance information can be made available to DBA teams without risking exposure of sensitive data values.
A Shameless Marketing Moment
DBI's Brother-Panther for DB2 LUW masks literal values found in SQL during its SQL analysis processing. Once the literal values are replaced by token place holders, statement patterns can have their costs properly grouped and aggregated to reveal true total and relative costs of execution. When the DBA looks at the SQL, the literal values are not revealed as shown here:
If your current performance tool reveals sensitive data, or if a tool you are evaluating reveals literal values, then it is time to switch to DBI's Brother-Panther™.
Cheers,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.database-performance.info
www.database-auditing.info
Trackback Pings
TrackBack URL for this entry:
http://www.ibmdatabasemag.com/blog/main/archives/2007/12/db2_luw_securit.html
« DB2 LUW Performance: I/O Write Times (OWMS) | Main | DB2 9 DBA Exam Sample Questions: Recovering a database and LBAC »
This is a public forum. CMP Media and its affiliates are not responsible for and do not control what is posted herein. CMP Media makes no warranties or guarantees concerning any advice dispensed by its staff members or readers.
Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Media's Terms of Service.
Important Note: This comment area is NOT intended for commercial messages or solicitations of business.