Database Design for Security: Best Practices to Keep Sensitive Information Safe

Keeping data safe and secure is a top priority for database designers and administrators. Learn how to design a database for security in this article.

When designing a database, security should be one of the top aspects to be considered. Databases store sensitive and valuable information, making them an attractive target for malicious actors. Protecting the data and ensuring its integrity and confidentiality are some of the most critical aspects when designing and implementing a database. In this article, we will discuss database security best practices and how to implement them.

There are a lot of things to consider when implementing database security best practices; before we start reviewing them you can get a quick introduction in our article A Beginner’s Guide to Database Security.

Authentication

Authentication refers to the process of verifying the identity of an individual or a system before granting access to a resource (such as a database). Implementing strong authentication mechanisms to validate the identity of users is a must when designing a database.

Authentication can be based on three primary factors:

• Something the user knows (e.g., a password or PIN).
• Something the user has (e.g., a physical token or a mobile device).
• Something the user is (e.g., biometric data like fingerprints or facial recognition).

You may want to read my article How to Store Login Data in a Database; it not only describes the best ways to store passwords and PINs, it also explains two-factor authentication (2FA) and social authentication. (2FA combines two or more factors for increased security; social authentication is provided by trusted companies like Facebook, Twitter, or LinkedIn or other platforms like Google or Microsoft.) The article also discusses using secure authentication protocols like LDAP, OAuth, or OpenID Connect for enhanced security without storing authentication information in the database.

Enforcing complex passwords, enabling multi-factor authentication, and limiting the number of failed login attempts strongly reduces the chance of non-authenticated users accessing the database. In our article Best Practices for Designing a User Authentication Module, this and other aspects (like how to design a password recovery mechanism) are covered in detail.

Authorization and Access Control

Once we’ve authenticated a user, we are sure that they are the person they claim to be. But not all users have the same permissions and privileges on a database. Implementing strong access control mechanisms is crucial for safeguarding your database. This involves granting appropriate permissions to users and limiting access to authorized individuals or applications. Utilize role-based access control (RBAC) to assign specific privileges based on user roles and responsibilities. Additionally, regularly review and update access permissions to prevent unauthorized access.

Role-Based Access Control

This mechanism simplifies granting the required permissions to multiple users by defining roles. Each role consists of a set of permissions, usually associated with a specific task or position. Roles can also (usually) be nested, so you can create roles, grant permissions to them, and then grant those roles to another role. Once you have defined your roles, you grant them to each user rather than granting the user individual permissions.

Almost all database engines support (and promote) this security model for DB user management, allowing you to create roles like database administrator, database developer, app developer, tester, etc.

This model is not only useful for organizing database users. An application may also benefit from a similar approach – defining application roles based on required activities, granting each role the appropriate permissions, and assigning roles to end users.

Encryption

Data encryption is essential for protecting sensitive information stored in the database. Encryption is available on most database engines; this feature converts information into secret code that hides the information's true meaning. Utilize strong encryption algorithms and ensure that encryption keys are securely managed to prevent unauthorized access to encrypted data.

Let’s see how to implement encryption at a general level; we will cover encrypting specific sensitive information in another section.

Encryption at Rest

This technique involves encrypting data before storing it on disk and then decrypting it when it is read from disk into memory by the database instance. This ensures any user who gains access to the database files (without having the required encryption key) will not be able to understand the data stored on the files.

This method is usually called Transparent Data Encryption, since it is completely transparent to the database users. It can be enabled by a database administrator for all (recommended) or some of the files of the database – or even for one or some columns. Then encryption/decryption is automatically performed by the database each time it writes or reads from disk.

This encryption is also applied to backups (since they usually copy data from blocks in the database files). The database files are secured on the server where they reside, but the backups are also secure if someone gets access to them (e.g. if a carry disk containing a backup is lost or stolen).

This feature uses two levels of encryption:

• A master key is created and stored outside the database, in a secure wallet or HSM
• One encryption key is created to encrypt database files. This key is stored internally in the database and encrypted with the master key.

When the instance is started, it uses the master key to decrypt the regular keys and then uses it to encrypt/decrypt data that is written/read from disk.

It is extremely important to ensure that only the database instance has access to the master key. It is also important to create a secure backup of it, since the regular encryption key is required; without it the database will become unusable.

Encryption in Transit

Implementing Encryption in Transit ensures that data is not sneaked by non-authorized users that may get access to the network (intranet or internet) while data is in transit from the database server to the end-user application/device.

Enabling Data in Transit encryption is usually a very simple configuration task that does not require end-user intervention. It can be achieved by using TCPS protocol instead of TCP for communication; some databases (like Oracle) also allow native in-transit encryption. It is simpler to set up, with no additional cost or licenses. There’s also no need to install certificates, as explained in this blog post. Other database providers offer similar options that can ensure that data is not compromised while being in transit.

Sensitive Data

Not all data stored in a database has the same value or may produce the same harm if it is stolen or accessed without permission. Each country and state may have specific laws and regulations about which particular pieces of information are considered sensitive; some of the best known are the EU’s General Data Protection Regulation (GDPR), the California Consumer Privacy Act (CCPA) under California data privacy laws, or the USA’s Health Insurance Portability and Accountability Act (HIPAA), which describes how medical information must be stored and shared.

When we talk about sensitive information, we usually refer to any of the following:

• Medical/health records (an individual's past, present, or future physical or mental health data, including payment information related to medical care).
• Cardholder data (credit card number, expiration date, CVC).
• Personal data (e. gender, racial or ethnic origin, political affiliation, religious belief, genetic and biometric data, etc.).
• Personal Identifiable Information, or PII (any information that may uniquely identify a person, like social security number, passport number, or driver's license number). While encryption secures data in the database, removing your PII from the internet is crucial for full protection.

We have already seen the usage of encryption to protect the entire database from unauthorized access; now we are going to discuss how to implement additional encryption to ensure that specific pieces of information stored in the database are not directly available for users without an additional decryption process.

Column-Level Encryption

This type of encryption is not performed in the database; it’s done on the client side. Sensitive data is stored encrypted on the database, but the key used to encrypt and decrypt data is not available for the database instance. This key is kept only by the authorized users. Users who do not possess the correct key to decrypt data will be able to query the database data, but they will obtain illegible information.

This way, data is not only protected from users who do not have access to the database (but do have access to the disks or servers). It is also protected from authenticated database users who do not have the required permissions to access those particular pieces of sensitive information.

Although the database is not responsible for the encryption and decryption of the data, it is part of database design work to identify the columns that will actually require this encryption and ensure that they are created using the appropriate data type to store the encrypted values.

All Together Now

Column encryption can be implemented in conjunction with regular “at rest” and “in transit” encryption, as shown below:

A scenario taking full advantage of encrypting options would include:

• Database instance encrypting or decrypting data written or read from the disk (green arrows), including the already encrypted (outside of DB) CardNumber column, using the “orange” database encryption key.
• Database instance and clients encrypt and decrypt data that is transferred in the network (blue arrows), including the already encrypted (outside of DB) CardNumber column, using TCPS or native network encryption keys (blue keys).
• Unauthorized users receive the data, but they cannot decrypt the CardNumber column since they do not have the column-level encryption key (green key). Meanwhile, authorized users can see the information: they do have the required column-level key to decrypt the values stored in the column. Note that this key is not stored in the database and is not even known by the database.

Additional Database Administration Security Aspects

Security must be considered not only when designing and creating a database, but also once it is in use. There are several administration tasks that will help keep the database secure.

Auditing and Logging

Implement comprehensive auditing and logging mechanisms to track and monitor activities within the database. This includes:

• Login Attempts: Both failed and valid-but-suspicious login attempts (i.e. from strange locations, outside regular office hours, etc.) should be logged and reviewed.
• Sensitive data access: Every time-sensitive data is accessed, either by authorized or non-authorized users, it should be logged and monitored.
• Sensitive and authorization data modification: Any change to sensitive data or login information (username, password, security questions, email recovery address, etc.).

Auditing allows you to identify suspicious behavior, track changes to sensitive data, and investigate potential security incidents. Ensure that audit logs are securely stored and regularly reviewed to detect any unauthorized access or suspicious activities. Most database products include several mechanisms both to capture and analyze audit information, like Audit Vault in the Oracle database.

Backup and Recovery

Implement regular backups of your database to protect against data loss due to hardware failures, natural disasters, or malicious attacks. Encrypt the backup files and store them in secure off-site locations. Test the recovery process periodically to ensure that backups are valid and can be restored when needed.

Regular Patching and Updates

Stay up to date with the latest patches and updates for your database management system. Vendors often release security patches to address vulnerabilities and improve the overall security of their software. Regularly applying these updates helps protect your database against known security flaws and reduces the risk of unauthorized access.