Data security is one of the most significant aspects of administering a database. Depending on the organizational structure, if you are managing the production database, you must monitor for unauthorized access and usage. This also applies to the underlying host. In this blog we are going to explain security in open source databases.
After installing the database software, we need to do some pre checks to avoid the most common security mistakes.
The general guidelines are as follows:
Do not ever give anyone root/admin access to others! This is critical.
Learn how the database access privilege system works. Do not grant more privileges than necessary. Never grant privileges to all hosts.
Do not store cleartext passwords in your database. Instead of use SHA2() or some other one-way hashing function and store the hash value.
Don’t use default ports and make sure the chosen port is not accessible from an untrusted host.
Put the open source database behind a firewall. This protects you from at least 50% of all types of exploits in any software.
Access Control and Account Management
One of the primary functions of the database is to authenticate a user who connects from a given host and to associate that user with privileges on a database. To control which users can connect, each account can be assigned authentication credentials such as a password.
A database role is a collection of any number of privileges that can be assigned to one or more users. In modern open source databases, the majority of users come with predefined roles.
Accessing the database requires their users to authenticate themselves with a username and password. The database applies the same hash function on the password which the user entered, and compares it to the hash stored in the database. If they both match, then permission is granted. The password expiration is required for all the database users to change passwords periodically. And random password generation is required for explicit administrator specified literal passwords. One important thing to monitor is wrong password/authentication failure tracking, it is recommended to enable temporary account locking after too many consecutive incorrect-password login failures.
Handling of Expired Passwords
Once the password is expired, the database server disconnects an expired-password client connection.
[root@centos11 vagrant]# mysql -u severalnies -p Password: ****** ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
Limiting the Resources
Restrict a client’s use of database server resources, by limiting the number of simultaneous connections that can be made by any user account. For example, we can restrict the number of queries an user can execute per hour or number of times a user can connect to the server per hour.
Databases usually support encrypted connections between clients and the server using some security protocols. Using SSL, it will encrypt the connections between clients and the server. This turns the data into an unreadable format over the network layer, and avoids network eavesdropping. Once we enable SSL on the database, it starts to support encrypted connections, and won’t allow unencrypted connections.
Security Components and Plugins
Security components are easily integrated with the database. For example mysql includes several components and plugins that implement security features.
Authentication Plugins – These plugins are authenticating attempts by clients to connect to the MySQL Server. We can easily integrate with our database server.
Password Plugin – This plugin checks whether the current password is strong, and rejects any passwords that are considered weak.
Keyring plugin – This plugin encrypts tablespaces. This encryption technique works on the basis of rotating key files. Here is an example on how to encrypt MySQL 8.0.
Audit log – Auditing is the process of monitoring and recording the activity happening on the database server. It is used to log who is doing what, e.g. database operations by users, connections, or failed login attempts. If you are using a load balancer to access the database, you would need to monitor that as well. By default the audit log files are stored in mysql data directory.
20210306 02:30:06,centos14,root,localhost,519,1832,QUERY,,'select sleep(5)',0 20210306 02:30:08,centos14,s9smysqlchk,localhost,571,0,DISCONNECT,,,0 20210306 02:30:09,centos14,root,localhost,519,1867,QUERY,,'show schemas',0
If you want to store the audit log files in different locations you can add this parameter in your config file “server_audit_file_path”.
Log monitoring – Log monitoring provides the ops team with greater level of visibility into the database environment, which composes of the database instances but also the load balancers. It helps identify anomalies, when these anomalies occur and the frequency at which they occur.
Password Validation Plugin
The password is like a words, numbers and string of characters. On the database and server side, the username and password were needed for certain access. For example if the password supplied as a cleartext value, validate_password plugin checks the password against the current password policy and it rejects the password if it’s weak.
The validate_password_policy has three values LOW, MEDIUM, or STRONG. The value of LOW checks only password length, MEDIUM policy adds some conditions and STRONG policy adds the condition that password substrings that consist of 4 or more characters must not match words in a dictionary file that can be specified by modifying the validate_password_dictionary_file variable.
LDAP plugin enables database Server to accept connections from users defined in LDAP directories. In LDAP authentication, the client-side and server-side plugins communicate the password as cleartext. A secure connection between the client and server is recommended to prevent password exposure. If the client user name and host name did not match, database will reject the connection.
The LDAP configuration is very straight forward in ClusterControl. In this post we explained the configuration steps using ClusterControl.
Securing Database Backups
Data backups are important when it comes to protecting your data. It’s even more important to ensure that your backups are kept secure and available for faster recovery. ClusterControl provides comprehensive support for the backup management process, including encrypting the backup files using AES-256 encryption algorithm before shipping them offsite.
VPN access to the jump host network
To access the private network open source database from local the best method is to use VPN. VPN provides privacy and security on users to build a private network connection across a public network. A jump host is an intermediary host or an SSH gateway to access remote network servers. Jump server acts as a middle-man to connect both ends, thus making it a “jump” host to access to the other side. This is one of the popular ways to secure the server from the outside world. We can use SSH tunneling to access a remote network in a secure way, putting less effort than configuring a VPN server.
Database Access Management via Jump host
To access production database servers to more secure, a dedicated jump server is required. This dedicated jump server provide access to a private network from an external or public network, such as the internet. It provides a tiered approach for any user to reach your private network, it’s minimising the chances of a potential server attack.
How can we achieve this ?
The best practice is to set up a jump server to connect to your databases from a Linux machine, even though the DB instance is on a private network. After creating the Jump host follow the below guidelines,
Restrict publicly accessible on your databases with private subnets.
Restrict the internet gateway in route tables.
Create a new VPC or add your jump host to the same VPC as your database instance. Then add an internet gateway to your jump server with public subnets.
Allow only specific database ports to required IP’s.
Then we can create SSH tunneling to access a database server in a secure way.
SELinux is a set of kernel modifications and user space tools and it have some policy modules. The policy modules are SELinux contexts to define rules for how processes, files, ports, and other system objects interact with each other. Interaction between system objects is only permitted if a policy rule allows.
System users will be largely unaware of SELinux. Only system administrators need to consider how strict a policy to implement for their server environment. This detail gives the SELinux kernel complete, granular control over the entire system.