Learn More About Your Database Structure And Table Relations

A 6 Handshakes Theory

Most of us are familiar with the concept of 6 handshakes or six degrees of separation – the idea is that anyone on the planet can be connected to anyone else in just six steps. So through just five other people, you can be effectively connected to the Queen of England, Bill Gates, or even a Thai elephant trainer. So, you never know who can be your friends’ friends. The same principle works very well for databases.

Sometimes it’s very hard to know how different data in your database may be connected. This also happens due to the fact that oftentimes sensitive data is collected by some people and protected by other people and departments. Databases have a tendency of getting bigger and complicated with time. It’s not uncommon to find a database that is 10 or 15 years old. As a result, it is a real problem to reveal hidden and unseen links between data within a database.

But this all changes when you use the Table Relations tool included in DataSunrise Database Security Suite. This is the tool that can help you connect and protect data even if you know nothing about the database you want to protect. This tool makes sure that not a bit of your sensitive data is left unattended and unprotected.

Discovering Table Relations

But how did DataSunrise create such a useful tool? The success of realization, first of all, stems from the fact that we know how the databases that we protect function. We can protect more than 30 types of most popular databases both relational and non-relational.

The main ways of establishing the relations between tables and columns within your database are:

• using primary and foreign keys.
• using JOIN and WHERE clauses in database traffic to see how different tables interact with each other within a database. To be able to use database traffic as a source of information you need to start database audit and set up a Learning Rule, which will be learning from the database audit. All these tools are included in DataSunrise Database Security Suite.
• using database query history. This method requires collecting and keeping your database query history, so your database should be configured properly. Then you need and to set up a Periodic Task which will be periodically retrieving queries that may be useful for establishing potential table relations.

After DataSunrise has found all the relations between the columns you can see them in a diagram like this. In the picture below table “job info” is one table away from the table “order”, however, as you can see, they’re related. These relations were established automatically by the DataSunrise Table Relations tool.

Practical Use of Discovered Table Relations

After you have discovered and collected information about table relations for your database you may use it for the following:

1. To supplement and complement data discovery results.
2. When you use static and dynamic data masking DataSunrise will prompt you to mask the related columns as well.
3. In case of static masking the discovered table relations enable to make a partial transfer of rows from several related tables without breaking the database consistency. This consistency may be broken if the tables are related with each other through constraints or such constraints are logically implied.

Setting up a Learning Rule to Discover Table Relations

You can discover all the table relations in a database instance using a DataSunrise learning rule. In the DataSunrise UI you need to give a name to a learning rule, specify a database type and a database we want to establish relations for:

In the Filter Statement section select the Table Relations option, then select databases, schemas, tables and columns to process, after that save relations to a table relations instance you created earlier:

Setting up a Periodic Task to Discover Table Relations

Alternatively, you can set up a periodic task which will help you to discover table relations. This is just another way of discovering table relations.

1. Click New to set up a new task:

2. Fill in the required information including the task type and specify where to save the table relations. Click Save at the bottom of the page to save the rule:

3. The saved periodic task will appear on the list of tasks. Click it to enter:

Conclusion

The Table Relation tool reveals unseen at first sight relations between columns of different tables, looking at it from the point of view of database users and the way how they use it. It significantly enhances the process of sensitive data discovering, usually performed before data masking. It the modern world of security standards such as GDPR, HIPAA, etc. this gives additional opportunities to detect different types of PII leaks and is another barrier for cybercriminals.