Protecting data and auditing database access

If you are storing sensitive data, it’s time to ask yourself: “How well are you protecting your data?” Regardless of the industry, data security should always be top priority. Read this post to learn about protecting data access and auditing database access.

Layers of Data Protection

Since data is present at many different levels, let’s take a look at a generic application which is represented by the diagram below. The client could simply be a browser or software installed locally and the server could host only a database or both the database and server-side application software. Whatever the configuration, the same principles apply. We need to be able to protect the data in the database as well as while it is in transit to the client application.

Figure 1. The different layers in which data should be protected.

Protecting Data Access

The importance of protecting your data increases as the amount of data stored continues to grow or the value of the data being stored is high. In order to keep your data safe from corruption, loss or compromise, here is what you can do:

1. Create strong passwords. A strong password generally contains more than 6 characters and is a mix of uppercase/lowercase letters, numbers and special characters. The longer and more random the combination of characters are, the stronger the password is, making it more difficult for someone to guess. Having a strong password applies to both user accounts as well as accounts that are created in the database and are used by application services. Although it becomes tedious to think of a strong password for each account, make sure you don’t skip this important step as it is one of the best ways to keep your data protected.
2. Set permissions selectively. The second step in protecting data access involves ensuring that all accounts are assigned only the permissions that are needed to perform their required functions. A single database account can be created and used by an application service to access the data. The application can also manage the user account access from within the application.

In this case, the account used by the application service can usually be given full database admin privileges as it is only used by the software. The application however will need to have extra functionality so that managers can decide what each user account has access to.

In the case where each user gets their own database account and the database handles the authentication, you should also ensure that each user can only access what they need to based on their function. Rarely does any regular user require full database admin privileges so think your decisions through! Figure out who needs access to what and proceed from there.

See Also: A Quick History of Vehicle Cybersecurity

Protecting Data at Rest

Data at rest is defined as data that is not in transit between networks and/or devices. It is generally thought to be less vulnerable to theft but nevertheless it should be protected. Protecting data at rest is done using encryption, and there are a few different places that encryption can be applied. It is important to note that the keys used to encrypt the data should be kept separate from the data itself.

The first option is encrypting everything (whole-disk) at the storage level. This can be done using hardware or software. Hardware encryption tends to be more expensive but usually performs better than if the data were encrypted at the software level. Hardware encryption is also more secure than software encryption. Similar to whole disk software encryption, you can also encrypt the database files (or the whole database). Implementing encryption this way depends on your database platform but all software vendors provide a type of database encryption.

Encryption can also be done at the row/column level within a database. This differs from whole database encryption because with that, once a user gains access to a database, they can see all the data unencrypted. With column/row level encryption you can protect very sensitive data (passwords, credit card numbers) so even if you have database access, you can still only see the encrypted value.

Protecting Data in Transit

As described in the protecting data at rest section, data in transit is defined as data that is moving between devices over a network. While on all platforms, passwords are always encrypted when they are sent from the client application to the database server, that is not the case for any communication after the authentication phase. In order for all communication/data that is sent between the servers to be encrypted, TCP connections must be secured using SSL. Major database platforms including PostgreSQL, SQL Server and Oracle include functionality for enabling these secure connections.

Auditing Database Access

In addition to the protection measures described above, you should also monitor your database access in case someone does slip through the cracks and gains access to your system. As a first step, all authorized access should be documented so when any type of audit of a system is conducted, you have a baseline to compare it against.

Static Auditing

In simple terms, a static audit is a report that can be run at anytime showing who has access to the application data whether it be within the application itself or through direct access to the database objects. The static audit should include the following:

1. Who has access to data? A simple list of users that are authorized to access the application/database
2. What objects can each user access? A detailed report for each user who has access, outlining each table/data element a user has access to along with each permission they are granted on the object (select, insert, update and delete). The report should also contain system level permissions that the user is granted (create, drop, grant, revoke and alter system).

Dynamic Auditing

A dynamic audit is a real-time log of any actions that have been performed against your application/database. It’s an extension of the static audit and will capture every action a user has taken. Note: this would also include actions performed by unauthorized users if somehow an unauthorized account was created. Analysis on what this unauthorized user did while logged in could be done at a later time once an unauthorized user was detected. A dynamic audit log should contain the following information:

1. Who accessed the system? This is a simple log of when each user logged in and out of the application/database. It should also include any failed login attempts.
2. What was accessed? This is a detailed log that captures every object level access (either within application or in the database itself). For example, a database read of a table might look like this in the audit log: “username=’testuser’, database=’testdata’, ‘SELECT * FROM testdata’”. On really busy systems, this log can become large so if the requirements allow it, you could exclude/filter out known authorized application accounts in order to make the audit data volume a little more manageable. This log should also include any failed attempts at accessing an object.
3. Alert mechanism when an unauthorized access occurs. Ideally all data that is logged above should be pushed to a central repository which could be monitored by another process. This would also send out an alert when unauthorized accesses are detected. Logs would have to be cross referenced with a baseline of authorized access which is one of the reasons that the static auditing mentioned earlier is so important. If a static audit is not possible or if your environment is so dynamic that a static audit is not really feasible, then these logs could also just be searched periodically using spot checks to try to and identify if any unauthorized accesses have occurred.

Data Security Standards

There are many standards/frameworks that can help to define what type of auditing should be implemented in your environment for data security.

Using the industry standard guidelines below along with your own system requirements, you should be able to secure your data and protect it from unauthorized access:

PCI DSS - Payment Card Industry Data Security Standard

NIST - NIST Cybersecurity Framework

CIS -  CIS Controls and CIS Benchmarks

FedRAMP - Federal Risk and Authorization Management Program

For more details on data privacy and telematics security, visit the Geotab Security Center.

Related: 

What’s the Big Deal with Big Data and IoT?

Securing the Future of Connected Mobility

Dialing down risk with audit logs