Top 7 Reasons Why Security Goes Wrong In A Database System

Today I’m going to talk about my favorite trope, “database security”.

When done right, a good security policy not only protects your data but improves performance, system stability, and enhances the development life-cycle. When done wrong it not only risks the confidentiality and integrity of your data but it leaves your organization open to significant financial risks.

Top 7

1. User account policies
2. User account access control
3. Session connections
4. Logging/history
5. Dynamically created queries (prepared statements)
6. Host-based authentication rules
7. Sensitive data (encryption)

1. USER ACCOUNT: POLICIES

If it’s one thing that drives me crazy, it’s presiding over a database system populated with active user accounts of employees and contractors who have long since departed. This is such an easy fix, just delete the user account! However, removing accounts isn’t always straight forward because they can own relations critical to the operation of the database system.

One can set an expiration date when the user account is first created. Of course, you’ll want to consider your options when managing key application processes.

 

2. USER ACCOUNT: ACCESS CONTROL

Another security consideration is the GRANTING and REVOKING of user account privileges. This is a subtle issue because of the temptation of using a single account with super-user privileges in order to facilitate rapid product development. One must always remember to review and harden all user privileges before leaving development and moving onto QA and, eventually, production. Consider implementing a standard sanity test for all your projects in order to catch the unexpected oopsie.

Security failures typically come in two flavors:

1. User accounts with too much privilege:
   • A user account that can log in to unauthorized databases
   • A user account possessing unnecessary, redundant, escalation privileges
2. User accounts used for the wrong task:
   • A superuser account used by a monitoring process
   • An account with superuser privileges managing routine application processes. Just try logging into a system when you’re out of connections and see how that works out.

3. SESSION CONNECTIONS

Security versus performance: it’s a simple enough equation. Either one enforces encrypted sessions as mitigation against network sniffing or instead opt for as high a level of performance as possible using un-encrypted sessions.

Consider using session encryption for:

• Administrative activities
• Handling sensitive information i.e. social security numbers, passwords, etc.

A truly secure environment includes encrypting connections between the DBMS and its monitoring system. Remember, meta-data is just as important as the data itself and its leakage can provide critical information to those desirous of destabilizing your production environment.

4. LOGGING/HISTORY

One of the most basic operations of a DBMS is the logging of its events. Although no longer as referenced to as in the past, they still provide the best line of inquiry determining the root causes.

Seasoned DBAs and SREs shine during production incidents because they understand the power of this most basic system. Good logging not only empowers one the ability to mitigate an issue but, as part of a larger root cause analysis exercise, forms the basis of an improved system. Always make certain to configuring logging as many of the available variables as reasonable, you never know when you’ll need them.

 

5. DYNAMICALLY CREATED QUERIES

Dynamically created queries, i.e. prepared statements, are strings constructed and used as the basis of a SQL instruction on the DBMS.

Fortunately, this is one issue hammered deep into the heart of most developers.

The exploitation of this extremely useful facility by hostile agents is often facilitated by appending well-crafted strings to the original argument which performs additional instructions.

Server-side programming languages, such as python, php, perl, etc, provide functions calls rendering the string “safe” by removing all control characters and undesired strings. The DBMS also have these kinds of functions/sprocs thereby providing another layer of defense.

TIP: Perform a little pen-testing by using junk strings as arguments in your prepared statements and monitor the results.

6. HOST BASED AUTHENTICATION RULES

Leveraging host-based authentication rules mitigates unauthorized access by knowing ahead of time where application processes are connecting from and which addresses can connect to what databases for a particular purpose such as monitoring and administration.

Mature database management systems have the ability to accept or deny a client connection request based upon one or more parameters and includes:

• Source IP address
• User account
• Database
• Encrypted vs un-encrypted sessions

7. SENSITIVE DATA

There’s sensitive data and then there’s SENSITIVE data. You’ll know the difference by the amount of money that can be lost due to its unauthorized release. For example, on the dark web, a stolen health card number is worth substantially more than a credit card number.

Sensitive data can be protected by the use of encryption and includes the following methods:

• • Hashing the data as a checksum, i.e. translucent data: This method is commonly used when one wants to validate a decision, or process such as identifying oneself with a social security number or providing a credit card number in order to make a purchase, when the user submits the sensitive data across an encrypted session and is compared against a hashed version of the same data. This is by far the safest method available as technically speaking one is not storing any sensitive data but merely its hash.
  • Encrypting the data in the database: There are two variations encrypting data i.e. symmetrical and asymmetrical encryption. Then there’s the encryption strength where the stronger the encryption the more CPU, and time, it takes to encrypt it. Even if the data is stolen decryption is only possible with the password, private key, or a brute force attack (good luck with that!).
  • Encrypting the entire database through the use of encryption-at-rest technologies.

ATTENTION: I’ve seen in a couple of places where people decided to get smart and chose to create their own encryption algorithm. Don’t! Use the standard stuff that’s out there and avoid reinventing the wheel.

by Robert Bernier via Percona Database Performance Blog