Showing posts with label regulatory compliance. Show all posts
Showing posts with label regulatory compliance. Show all posts

Tuesday, February 01, 2022

Auditing Database Access and Change: A Necessity More Than a Nicety

The increasing burden of complying with government and industry regulations imposes significant, time-consuming requirements on IT projects and applications. And nowhere is the pressure to comply with regulations greater than on data stored in corporate databases.

Organizations must be hyper-vigilant as they implement controls to protect and monitor their data. One of the more useful techniques to protect your company’s database data is through data access auditing, also known as simply database auditing. Database auditing is the process of monitoring access to, and modification of, selected database objects and resources within operational databases and retaining a detailed record of the access where that record can be retrieved and analyzed as needed.

A data access auditing capability enables companies to produce an audit trail of information with regard to their database data. This audit trail should contain information such as what database objects were impacted, who performed the operations, and when the activity occurred. A comprehensive audit trail of database operations, coupled with an analysis engine to review and analyze the audit trail allows data and security professionals as well as IT auditors to perform an in-depth analysis of access and modification patterns against data in your database systems. Only when armed with such details is it possible to comply with regulations, pass security audits, and drill down into the details to review potential vulnerabilities for effective issue resolution.

A Look at the Regulations and Requirement

A fine-grained audit trail is necessary to comply with many regulations that apply to organizations of all types.

Many of the PCI Data Security Standard requirements emphasize the importance of real-time monitoring and tracking of access to cardholder data, as well as continuous assessment of database security health status.

HIPAA, the Health Insurance Portability and Accountability Act, directs health care providers to protect individual’s health care information going so far as to state that the provider must be able to deliver a list of everyone who even so much as looked at their patient’s information. Could you produce a list of everyone who looked at a specific row or set of rows in any database you manage?

And then there is the Sarbanes-Oxley Act (SOX) which has the goal of reducing fraud and conflicts of interest, as well as improving disclosure and financial reporting. Section 404 of the SOX Act specifies that the CFO must guarantee the accuracy of the processes used to add up the numbers; processes that access and manipulate data in a database system. As such, it is important to be able to track who changed database schemata and database data for SOX compliance

And these are only a few of the pertinent national, international, regional, and industry regulations that must be understood and complied with.

Database Access Auditing Techniques

So now that we understand why database access auditing is important, let’s take a look at how it can be accomplished. There are several popular techniques that can be deployed to audit your database structures.

The first technique is trace-based auditing, which is typically built directly into the native capabilities of the DBMS. For example, the Audit trace feature of IBM Db2 for z/OS. When an audit trace is started, the DBMS begins to cut trace records when activity occurs against audited objects (selected by DDL option). However, Db2 only captures the first read or write per unit of work, which will clearly miss activities as most UOWs encompass more than one read or write. Alternately, Db2 audit policies can be created for named tables to capture all activity, which improves the data captured, but can create an excess of audit records that need to be stored in SMF data sets.

So, there are problems with this technique including a high potential for performance degradation when audit tracing is enabled, a high probability that the database schema will need to be modified, and insufficient granularity of audit control, especially for reads.

Another technique is to scan and parse transaction logs. Every DBMS uses transaction logs to capture every database modification for recovery purposes. If you can read the log and interpret the data (which can be challenging as the data is not simple) it is possible to identify what data was changed and by which users. The biggest drawback to this technique is that database reads are not captured on transaction logs.

Additional issues with relying on log analysis for auditing data access include: it is possible to disable logging such that modification information will not be on the log and therefore not captured; performance issues scanning volumes and volumes of log files looking for only specific information to audit; and the difficulty of retaining logs over long periods for auditing when they were designed for short-term retention for database recovery.

And that brings us to the third, and preferred, method of database auditing for organizations that are serious about regulatory compliance: professional software that proactively monitors and intercepts all SQL requests as they are executed by the DBMS. It is important that all SQL access is audited by monitoring for SQL at the database level, not just by sniffing network calls. This is important because not every SQL request goes over the network, especially for the mainframe platform where much of the activity is centralized and many important business transactions never venture over an IP network (e.g., a CICS or IMS transaction accessing Db2).

Proactive intercept-based database audit monitoring does not require transaction logs, does not require database schema modification, should be highly granular in terms of specifying what to audit, and should incur only minimal overhead.

One such product that implements intercept-based auditing for Db2 database access is DBARS, which stands for “Db2 Access Recording Services,” available from ESAI Group

DBARS

Using DBARS for your Db2 database auditing needs makes sense as it offers a high-speed method for intercepting all Db2 database activities, not just modifications but also all reads. Furthermore, DBARS does not rely on Db2 tracing; instead, it uses a proprietary interface to intercept all Db2 SQL statements, regardless of origin. Therefore, you can use DBARS to create audit reports of all Db2 activity. Furthermore, DBARS provides the ability to block suspicious SQL activity, giving you the ability to prevent fraudulent access attempts based on specific parameters and patterns.

As you look into your mainframe Db2 database auditing needs and compare functionality against the advice proffered in this article, you will see that DBARS offers all of the functions needed for auditing access to sensitive data in Db2 tables.

Important Features for a Database Auditing Solution

As you investigate the database access auditing requirements for your organization, you should make sure that the solutions you examine support your DBMS using intercept-based auditing, instead of the other methods.

You should also compile a list of the types of questions that you want your solution to be able to answer. A good database access auditing solution should be able to provide answers to at least the following questions:

  • Who accessed the data?
  • At what date and time was the data accessed?
  • What program or client software was used to access the data?
  • For batch mainframer users, what was the z/OS job name?
  • From what location was the request issued?
  • For distributed Db2 access, what were the names of the external server, application, and workstation?
  • What SQL was issued to access the data?
  • Was the request successful; and if so, how many rows of data were accessed or modified?
  • If the request was a modification, what data was changed? (A before and after image of the change should be accessible.)
Of course, there are numerous details that must be investigated for each of these questions. You will want to be able to review recent activities, but you will also want to be able to review actions that happened in the past, so a robust database access auditing solution should provide an independent mechanism for the long-term storage and access of audit details. It should be easy to query the audit trail, perhaps even offering canned queries for the most common types of queries. Nonetheless, the audit information should be accessible using industry-standard query tools to make it easier for auditors to customize queries as necessary.

An alerting capability is also desirable, such that when certain SQL activity is intercepted an alert is triggered to take further actions, such as recording an exception, sending information to a log, or pinging a DBA or security admin.

Advanced auditing solutions also provide the ability to proactively block suspect access to the database. For example, you may want to stop any attempted access outside of normal, scheduled programs over the weekend. At any rate, it is desirable for an auditing solution to be able to block activities based on parameters such as user name, program name, IP address, execution time, type of access, and the like. Such a capability is important because preventing fraudulent access is preferable to allowing it and reporting that it happened!

It is also important for a comprehensive database auditing solution to provide a mechanism to audit privileged users, such as DBAs and SYSADMs. Many privileged users have blanket access to all corporate data. Although they can access and modify it at their discretion, they should not be accessing and modifying production data without due cause. A database auditing solution enables organizations to implement a “trust, but verify” policy with their privileged users. This allows the administrators to retain the authority they need to be able to do their jobs, while at the same time giving the organization the peace of mind that everything the privileged users are doing is tracked for security and compliance purposes. Without a database auditing solution in place, privileged users are a potential compliance problem lurking within every database implementation.

The Benefits of a Professional Database Auditing Solution

The bottom line is that database auditing should be a crucial component of your organization’s data protection strategy. Auditing database activity is a core requirement of compliance with many government and industry regulations, but auditing is also an essential component of securing and protecting the important production data in your database systems.

Be sure to study the auditing and compliance requirements of your organization and to augment your DBMS with the appropriate tools to bolster the auditability of your databases.

Wednesday, September 04, 2019

The Power of Data Masking for Data Protection

Data privacy regulations and the desire to protect sensitive data requires methods to mask production data for test purposes. Data masking tools create structurally similar data that is not the same as the actual data, but can be used by application systems the same way as the actual data. The capability to mask data is important to be in compliance with regulations like GDPR and PCI-DSS, which place restrictions on how personally identifiable information (PII) can be used.

UBS Hainer’s Masking Tool for BCV5 (their test data management solution) offers robust masking of Db2 for z/OS data. I wrote about this capability previously on the blog last year (see Data Masking: An Imperative for Compliance and Governance, November 12, 2018), and if you are looking for a concise, yet thorough overview of the product’s data masking capabilities I point you to that blog post.

So why am I talking about data masking again? Well, it is a thorny problem that many organizations are still struggling with. As much as 80% of sensitive data resides in environments used for development, testing, and reporting. That is a lot of data that is ripe for exposure.

But I also wanted to share a new video produced by UBS Hainer that explains how data masking can help you to stay compliant and protect your sensitive data. It is well worth your time to watch this 2 minute video if you need to better address the protection of sensitive data at your shop.



Click to watch the video

Data masking is not a simple task, and as the video helps to explain, there is much to consider. To effectively mask your data requires a well-thought-out process and method for implementation to achieve success. As such, a tool like BCV5 Masking Tool can simplify how you address your Db2 data protection requirements. It provides dozens of easy to use masking algorithms implemented using Db2 user-defined functions. It ensures that the same actual value is translated to the same masked value every time. And the value will be a plausible value that works the same as the data it is masking. The tool understands thing like referential integrity, unique constraints, related data, and so on.


A reliable method of automating the process of data masking that understands all of the complicated issues and solves them is clearly needed. And this where UBS Hainer’s BCV5 Masking Tool excels.



Monday, November 12, 2018

Data Masking: An Imperative for Compliance and Governance



For those who do not know, data masking is a process that creates structurally similar data that is not the same as the values used in production. Masked data does not expose sensitive data to those using it for tasks like software testing and user training. Such a capability is important to be in compliance with regulations like GDPR and PCI-DSS, which place restrictions on how personally identifiable information (PII) can be used.

The general idea is to create reasonable test data that can be used like the production data, but without using, and therefore exposing the sensitive information. Data masking protects the actual data but provides a functional substitute for tasks that do not require actual data values.

What type of data should be masked? Personal information like name, address, social security number, payment card details; financial data like account numbers, revenue, salary, transactions; confidential company information like blueprints, product roadmaps, acquisition plans. Really, it makes sense to mask anything that should not be public information.

Data masking is an important component of building any test bed of data – especially when data is copied from production. To be in compliance, all PII must be masked or changed, and if it is changed, it should look plausible and work the same as the data it is masking. Think about what this means:

  • Referential constraints must be maintained. If primary or foreign keys change – and they may have to if you can figure out the original data using the key – the data must be changed the same way in both the parent, and child tables.
  • Do not forget about unique constraints. If a column, or group of columns, is supposed to be unique, then the masked version of the data must also be unique.
  • The masked data must conform to the same validity checks that are used on the actual data. For example, a random number will not pass a credit card number check. The same is true of the social insurance number in Canada and the social security number in US, too (although both have different rules).
  • And do not forget about related data. For example, City, State, and Zip Code values are correlated, meaning that a specific Zip Code aligns with a specific City and State. As such, the masked values should conform to the rules,

A reliable method of automating the process of data masking that understands these issues and solves them is clearly needed. And this is where UBS Hainer’s BCV5 comes in.

BCV5 and Data Masking

Now anybody who has ever worked on creating a test bed of data for their Db2 environment knows how much work that can be. Earlier this year I wrote about BCV5 and its ability to quickly and effectively copy and move Db2 data. However, I did not discuss BCV5’s ability to perform data masking, which will be covered in this blog post.

A component of BCV5, known appropriately enough as The Masking Tool, provides a comprehensive set of data masking capabilities. The tool offers dozens of masking algorithms implemented as Db2 user-defined functions (UDFs), written in PL SQL so they are easy to understand and customize if you so desire.

These functions can be used to generate names, addresses, credit card numbers, social security numbers, and so on. All of the generated data is plausible, but not the real data. For example, credit card numbers pass validity checks, addresses have matching street names, zip codes, cities, and states, and so on...

BCV5 uses hash functions that map an input value to a single numeric value (see Figure 1). The input can be any string or a number. So the hashing algorithm takes the input value and hashes it to a specific number that serves as a seed for a generator. The number is calculated using the hashing algorithm, it is not a random number.


Figure 1. The input value is hashed to a number that is used as a seed for a generator

Some data types, such as social security numbers or credit card numbers, can be generated directly from the seed value through mathematical operations. Other types of data, like names or addresses, are picked from a set of lookup tables. The Masking Tool comes with several pre-defined lookup tables that contain thousands of names and millions of addresses in many different languages.

Similar input values result in totally different generated values so the results are not predictable and the hashing function is designed to be non-invertible, so you cannot infer information about the original value from the generated value.

The functions are repeatable – the same source value always yields the same masked target value. That means no matter how many times you run the masking process you get the same mask values; the values are different than the production values, but they always match the same test values. This is desirable for several reasons:

  • Because the hashing algorithm will always generate the same number for the same input value you can be sure that referential constraints are taken care of. For example, if the primary key is X598, any foreign key referring to that PK would also contain the value X598… and X598 always hashes to the same number, so the generated value would be the same for the PK and all FKs. 
  • It is also good for enforcing uniqueness. If a unique constraint is defined on the data different input values will result in different hashed values… and likewise, repeated input values will result in the same hashed output values (in other words, duplicates). 
  • Additionally, this repeatability is good for testing code where the program contains processes for checking that values match.
Data masking is applied using a set of rules that indicate which columns of which tables should be masked. Wild carding of the rules is allowed, so you can apply a rule to all tables that match a pattern. At run time, these rules are evaluated and the Masking Tool automatically identifies the involved data types and performs the required masking.
You can have a separate set of rules for each Db2 subsystem that you work with. Depending on your requirements, you can either mask data while making a copy of your tables, or you can mask data in-place (see Figure 2).


Figure 2. Mask data when copying or mask-in-place.


Masking while copying data is generally most useful when copying data from a production environment into a test or QA system. Or you can mask data in-place enabling you to mask the contents of an existing set of tables without making another copy. For example, you may use this option to mask data in a pre-production environment that was created by making a 1:1 copy of a productive system.

What About Native Masking in Db2 for z/OS?

At this point, some of you are probably asking “Why do I need a product to mask data? Doesn’t Db2 provide a built-in ability to create a mask?” And the answer is “yes,” Db2 offers a basic data masking capability, but without all of the intricate capabilities of a product like BCV5.

Why is this so? Well, Db2’s built-in data masking is essentially just a way of displaying a different value based on a rule for a specific column. A mask is an object created using CREATE MASK and it specifies a CASE expression to be evaluated to determine the value to return for a specific column. The result of the CASE expression is returned in place of the column value in a row. So, it can be used to specify a value (like XXXX or ###) for an entire column value, or a portion thereof using SUBSTR.

So native Db2 for z/OS data masking can be used for basic masking of data at execution time. However, it lacks the robust, repeatable nature for generating masked data that a tool like BCV5 can provide.

This overview of Db2 for z/OS data masking has been brief, but I encourage you to examine Db2’s built-in capabilities and compare them to other tools like BCV5.

Poor Masking versus Good Masking

The goal should be to mask your data such that it works like the actual data, but does not contain any actual data values (or any processing artifacts that make it possible to infer information about the actual data).

There are many methods of masking data, some better than others. You should look to avoid setting up poor data masking rules.

One example of bad masking is just setting everything to NULL, blank, or XXXXXX. This will break keys and constraints and it does not allow applications to test everything appropriately because the data won’t match up to the rules – it is just “blanked out.”
Another bad approach is shifting the data, for example A – B, B – C, etc. Shifting is easy to reverse engineer making it easy to re-create the original data. Furthermore, the data likely won’t match up to business rules, such as check digits and correlation.

You can avoid all of the problems and hassles of data masking by using a product like BCV5 to mask your data effectively and accurately. Take a look at the data masking capabilities of BCV5 and decide for yourself what you need to protect your valuable data and comply with the industry and governmental regulations on that data.

Monday, March 17, 2014

Types of DB2 Tools

As a user of DB2, which I'm guessing you are since you are reading this blog, you should always be on the lookout for useful tools that will help you achieve business value from your investment in DB2. There are several categories of tools that can help you to achieve this value.

Database Administration and Change Management tools simplify and automate tasks such as creating database objects, examining existing structures, loading and unloading data, and making changes to databases. Without an administration tool these tasks require intricate, complex scripts to be developed and run. One of the most important administration tools is the database change manager. Without a robust, time-tested product that is designed to effect database changes, database changes can be quite time-consuming and error prone. A database change manager automates the creation and execution of scripts designed to implement required changes – and will ensure that data integrity is not lost.

One of the more important categories of DB2 tools offers Performance Management capabilities. Performance tools help to gauge the responsiveness and efficiency of SQL queries, database structures, and system parameters. Performance management tools should be able to examine and improve each of the three components of a database application: the DB2 subsystem, the database structures, and the application programs. Advanced performance tools can take proactive measures to correct problems as they happen.

Backup and Recovery tools simplify the process of creating backups and recovering from those backup copies. By automating complex processes, simulating recovery, and implementing disaster recovery procedures these tools can be used to assure business resiliency, with no data being lost when the inevitable problems arise.

Another important category of DB2 tool is Utilities and Utility Management. A utility is a single purpose tool for moving and/or verifying database pages; examples include LOAD, UNLOAD, REORG, CHECK, COPY, and RECOVER. Tools that implement and optimize utility processing, as well as those that automate and standardize the execution of DB2 utilities, can greatly improve the availability of your DB2 applications. You might also want to consider augmenting your utilities with a database archiving solution that moves data back and forth between your database and offline storage.

Governance and Compliance tools deliver the ability to protect your data and to assure compliance with industry and governmental regulations, such as HIPAA, Sarbanes-Oxley, and PCI DSS. In many cases business executives have to vouch for the accuracy of their company’s data and that the proper controls are in place to comply with required regulations. Governance and compliance tools can answer questions like “who did what to which data when?” that are nearly impossible to otherwise answer.

And finally, Application Management tools help developers improve application performance and speed time-to-market. Such tools can improve database and program design, facilitate application testing including the creation and management of test data, and streamline application data management efforts.

Tools from each of these categories can go a long way toward helping your organization excel at managing and accessing data in your DB2 databases and applications...

Monday, July 07, 2008

A Video Interview on Long-term Retention

When I spoke at the Techxans event in Houston this past May (2008) I was interviewed beforehand on what my presentation would cover. And lo' and behold, the Techxans folks have put that interview up on YouTube, so I thought I'd share it here with my regular blog readers. Enjoy!