Usually, when you think of data profiling, you think of running queries against databases for building reports. But I would like to present another practical example of using data profiling to investigate a potential data breach. The example given in this blog post is a fictitious example based on real-life scenarios using randomized data to illustrate concepts.  

Data breaches are publicized more frequently and it is a real concern for both organizations and individuals. Data breaches happen when there is an unauthorized release of private/confidential data that could also include tampering or having intellectual-property stolen. In many countries, some regulations require companies to disclose data breaches and pay hefty fines based on the number of users affected.

For this fictitious example, there was a “security incident” involving an ex-sales employee possibly accessing a highly critical internal system that contains sensitive personal data. Luckily, I’m familiar with internal processes and all data is stored centrally in a database. But my report will need to be objective and presented in a meaningful way.

Key questions we would likely want to know are:

1. What evidence proves whether the ex-employee has gained access to systems after his termination date?
2. What systems were accessed or at risk for the duration of the unauthorized access?
3. What corrective actions can be done to prevent similar security incidents from reoccurring?

Here are the facts about the system:

• Accessible via the internet and supports many modules including a scheduler and billing system
• Billing system requires a user login whereas the scheduler can be accessed via a logged-in or anonymous user
• Fictitious ex-employee is William Thomas Smith whose termination date is Sept 23, 2019.

For the simplicity of the blog post, we will use the following tables to do our data profiling exercise.

Q1. What evidence proves whether the ex-employee has gained access to systems after his termination date?
Best-case would be that I perfectly match the login used by William Smith. But in reality, I’d likely need to do a fuzzy match across multiple fields (first/last names, email, etc) to account for variability in spellings. For my example, I was unable to match based on “William Smith”. But I was able to fuzzy match on Bill Smith instead of William Smith for the name and some variation of the email address and IsInternal flag. With this combination, I can conclude with a high probability that Bill T Smith is most likely our ex-employee. However, I’m also suspicious that multiple accounts are created with the same password and by Brian who is not a team lead or a manager. But I’ll address that later.

Q2. What systems were accessed or at risk for the duration of the unauthorized access?

From the userSession table to see what modules got accessed and see that only the scheduler module was accessed. Also querying ModifiedDate and ModifiedBy across all tables does not show that the user has changed any info (not shown). However, it is still possible that William could’ve altered the data in the period between his termination date and the timestamp just before a current employee modified it. But the system does not support that detailed level of logging and the goal is to find evidence that William changed data. Therefore, I’d have to conclude that William’s login was used to gain access after his termination date but we cannot prove that he has modified any data. I also learned that William resigned from Sales and likely went to work for a competitor. So he might be motivated to steal design information rather than cause sabotage. However, the data in the userSession table reflect a user activity pattern that ventures into only 1 part of the application that is already publicly accessible.

Q3. What corrective actions can be done to prevent similar security incidents from reoccurring?

Although some user activity was found, no data has been changed and limited view to publicly viewable areas of the application. I’m not 100% convinced that William made the login because the password is too common. I later confirmed my assumption with the sales lead that password sharing is a common practice within the sales team which points to security flaws in both technical and business processes.

Final recommendations
So based on the results from data profiling, I’m able to determine that the login likely used by the ex-employee William Smith had activity after his termination date. The login activity only showed read-only access to non-sensitive information. But it seems more likely that someone on the Sales team, not William, used the login.

But to avoid a potentially more disastrous scenario, I would make the following recommendations to address security issues (task owner in brackets):

1. Mitigate repudiation problem – implement strong password policies/ two-factor authentication, restrict back-end access to DB systems (IT)
2. Update the security policy and communicate security changes – update the acceptable use policy which includes no password sharing and training staff on security practices (management)
3. Update HR offboarding process to revoke access – revoke user permissions during HR offboarding process and the opposite for HR onboarding process. (HR)
4. Implement security change process – implementing system controls/changes need to be requested, approved and documented. (management)

In a perfect world, the data you are searching for is easily searchable in a single database and documentation is always current.  However, often you have a legacy system which has fields that are repurposed to store different types of data. For example, descriptors or memo fields can contain important business information or links to other side systems. Another issue that is commonly encountered is database fields are not consistently named – for example, OrderNo, OrderID, and OrderNumber.  This is often the result of siloed development or the functionality of the legacy system being extended beyond what the system is originally designed to do.

Based on a sample search term, I wanted a systematic way to discover what primary data entities are associated with a specific business process as well as the entity relationships. The solution also needed to account for scenarios where data can exist in mismatched in field names or in a set of information within a single database field.  For my specific case, the database was a SQL Server with 1000s of columns across many tables supporting a well-known ERP system with multiple application modules.

One way to handle this issue is to build SELECT queries using the metadata catalog and using the search term’s value and length to improve the probability of making a match.   This method works best with strings that are intuitively unique which improves the probability of the search.  So as an example, a search term “CAF-0006099” as an order number would work better than “6099”.

Below is the sample code I’ve written to aggregate the search results of dynamically generated metadata search queries.  The IsFound column will flag on the first wildcard match which is better for scanning large tables.  This method of search will not be 100% as a wildcard search can incorrectly make a positive match on both “CAF-0006099” or “CAF-0006099Z”.  But as a first pass, this code is still useful in shortlisting fewer columns from potentially 1000s of columns across many tables. From there, you can do a more detailed level of querying.

declare @search_term as varchar(255), @sql_template varchar(8000)
declare @max_rows int, @row_ctr int, @sql_stmt varchar(8000)

set @search_term ='CAF-0006099';

if object_id('tempdb..#SearchResults') is not null drop table #SearchResults

create table #SearchResults(
  IsFound int,
  ColName varchar(8000)
 );

if object_id('tempdb..#cmdlist') is not null drop table #cmdlist

create table #cmdlist(
    RowID int identity(1,1),
    cmd varchar(8000)
);

set @sql_template ='insert into #SearchResults(IsFound, ColName) '
+ 'select top 1 1 IsFound, ''[$schemaName$].[$tableName$].[$colName$] as ColName'' ColName '
+ 'from [$schemaName$].[$tableName$] with (Nolock) where [$colName$] like ''%$searchTerm$%'';'


insert into #cmdlist(cmd )
select
replace(replace(replace(replace(
    @sql_template ,'$schemaName$',c.Table_schema)
    ,'$tableName$',c.Table_name)
    ,'$colName$',c.Column_name)
	,'$searchTerm$',@search_term)
as SqlStmt
from information_schema.columns c
where c.DATA_TYPE like '%char%' and c.CHARACTER_MAXIMUM_LENGTH>= len(@search_term )

set @max_rows =@@rowcount
set @row_ctr =0

while @row_ctr < @max_rows
BEGIN
    select @sql_stmt = cmd from #cmdlist where RowID= (@row_ctr +1)
    exec (@sql_stmt);

    set @row_ctr=@row_ctr +1;
END

select ColName, IsFound
from #SearchResults;