Database Security 101: Understanding Database Access Privileges

Data is the new gold for big companies and organizations It’s considered as the lifeblood of most modern businesses and there’s a bonanza of opportunities to sell or to market to the large audience of the internet. For the big e-commerce or social media companies, data drives their capacity to generate large revenues and earnings for which data is tightly secured and has sophisticated protection against any malicious and intrusion attacks online. 

So data like gold, it’s valuable state begins once it is processed. Its raw value is full of a mess as if it’s a gigantic unsorted nibble Once its essence is structured, data’s value multiples. Just imagine, if you have an education site that allows users to pay. Once you have tons of lectures and modules that your target audience can learn, develop, and earn a degree of productivity, you have grasped the taste of opportunity and success as you have the door to regulate fees before they can get the structured data they want. Although this sounds like everybody’s dream of success, when it comes to big data and its underlying essence, there are tons of complications to process it and important concern is threats to your database.

Database threats in general have numerous and extensive sectors to look at and examine. Although, the most common causes are data theft and data breaches. Another common threat is extensive privileges or access to databases incorrectly assigned and/or provided to a user. Protecting the entire server host is a concern to anyone managing a database. Tightened your security and deal with all types of applicable attacks such as eavesdropping, altering, playback, and denial of service (DDoS) not only for the database but also for its entire underlying stack that has access or that interfaces with your data storage. 

In this blog, we’ll discuss the extent of necessity to why you need to understand and have database access privileges. 

Dangers of Wrong Access Privileges

We inevitably have to share or at least create a user either at a physical and technical level. Whilst, providing access to someone else means you trust the person. This also means the authorised person has to understand the danger and peril of sharing access and data from the outside world. 

The most important point of securing your access privileges is the level of understanding on security between your engineers such as a database administrator, security engineer, or server administrator. If the understanding is poor or lacks knowledge and experience especially of the most up-to-date vulnerabilities and exposures, it can be a problem to the organisation or the company. 

There are basic things that have to be understood and taken into account so that it has minimal or at least cannot be intruded or exposed. Otherwise, this could put your data in jeopardy from the outside world or at least to the wrong person or people. Possibly to steal your data and use it for their own sake to gain financially or they can ransom it from you and ask for money in exchange for your poor security implementation. 

In this section, let’s see some common causes of these security threats.

Sharing root Access Privilege

For an on-premise environment, a usual case of database breach relies mostly on the peril of giving the root access either at the OS level or at a database software level. There are cases where the root password is distributed and exposed to several people which should be only limited to the administrators working solely on the system. This could happen due to a lack of a security checklist or measures in the protocol before implementing the access privileges. Having a security checklist helps to track for any access and privileges that could expose risk and danger especially when a specific OS user is exposed to an intruder. The checklist also helps you discuss or have an overview of security measures in place and implemented as a protocol for your organization.

For example, a user that has root access can do a lot of damage such as removing all your data from your physical storage drive, reset the root password, create his/her own user/password that looks like a legit user (can be used for a very long time to harvest data unless caught early), sudo to a different OS user such as postgres user, and a lot more scary things to enjoy by the intruder. 

If you are using MongoDB, a user with a root access can login to your database server. As long as the intruder can locate your /etc/mongod.conf or your mongodb configuration file and locate the path of your key, it’s easy to login. For example, using this command allows you to login,

[root@testnode16 ~]# mongo -u __system -p "$(tr -d '11-1540' < /etc/mongo-cluster.key)" --authenticationDatabase local --eval "db.adminCommand( { listDatabases: 1, nameOnly:1 } )"

 

Consider a MySQL normal installation setup, a root access can be left without a password for localhost access. It's easy to gain access once you are root. File access such as $HOME/.my.cnf or viewing the contents of /etc/my.cnf will lead you to gain access easily.

It is strongly recommended to limit only or just give your root access y to the least number of people who are working directly with the server to update the packages, security updates, and apply patches that are required by the development team.

Using sudoers Properly

Mainstream open source database software such as PostgreSQL, MySQL/MariaDB, MongoDB requires creating a specific OS user. The OS user requires a specific role limited to allow the management of its capabilities within the database functionality. Proper read and write permissions need to be set for the underlying storage device path. However, there are cases that some who are using these specific users for database software have sudo privileges that are also capable of accessing the user solely designated for database access. User privileges in the OS have to be limited and it's best to limit its access based on role. For example, for Percona Server CVE-2016-6664, although this has been fixed, this type of vulnerability is an example of a possible attack from a specific user that has access to the MySQL account and gain root access. Sudo users have to be reviewed and made to understand that the role is only limited to do a specific job.

Enabling Linux Auditing System such as auditd can help improve security as it raises overlooked access privileges on the OS level that could lead to security vulnerabilities of your database. SELinux and AppArmor are good examples of security modules for your Linux environment that host your database system to help improve your security against intruders or breaches that would lead to your data being jeopardized.

Granting Database Access Privileges

Mainstream open source databases offer a granular list of privileges that can be customized to be assigned only to a specific action for a specific user. This is an extensive way to help database administrators securely have data separation and target action based on specific privileges. 

Common Access Privileges

Your most commonly used privileges shall be based on these three categories:

• Able to Read/Find such as SELECT, SHOW VIEW, FIND

• Able to Insert/Update/Delete such as INSERT, UPDATE, DELETE, REMOVE

• Able to do administrative actions such as CREATE USER, CREATE ROLE, ALTER, REPLICATION, DROP USER/TABLES/SCHEMA's, kill operations, etc.

These categories can be extended into more refined privileges based on your security checklist. It is good to define a specific user to be created with specific privileges for a specific task. For example, an application can have multiple users with its own designated privileges assigned. Though the application can be as a complex with this type of implementation. There are cases that per-user connectivity can be resource-intensive such as using ORM like Hibernate, for example. On the other hand, it depends on the architectural design of your application. The purpose of a per-user basis in an application can help sustain a more refined database access privilege and avoid harming your data from unwanted deletes, updates or a SQL injection attacking your database. 

In most cases, an application uses one user to connect to the database which is only limited to its actions specifically for the application to run. It's best that you design your application user privilege to only read-write access. Whereas if administrative actions are required, a specific script, daemon, or module in your application access has to be separated from the normal users.-.  

Database Access To Be Avoided

PostgreSQL and MySQL/MariaDB have this option to grant a user using ALL privileges. For PostgreSQL, it's also best to have your user with NOSUPERUSER. If possible, this has to be avoided at all costs. This privilege can do most of every action that can potentially destroy or harm your valuable data. You can use ALL privileges for your admin or root access but are only limited to users that require the super privileges to do administrative tasks and manage the data. 

Access On Per-Table or Per-Schema Basis

It's a good practice to only provide access to a user for only the required tables. . So, even if the user has some administrative privileges, any damage is only to a limited set of tables. Either you can set on a schema-wide; providing access to a limited table provides a granular type of privileges and it helps you keep your data out of harm. 

Access Limited To Host-Only

Connecting via its resource IP address helps limit access to your data. Avoid using '%' such that in MySQL for example,

GRANT SELECT, INSERT, DELETE ON mydb TO username@'%' IDENTIFIED BY 'password';

The extent of harm is exposed to any host to connect to and that is not what you wanted to happen. It imposes vulnerability and the challenge to intrude your database is very low.

For PostgreSQL, make sure that you have set your pg_hba.conf and the user to its specific limit of host only. This applies as well to MongoDB for which you can set it in your MongoDB configuration file or /etc/mongodb.conf. In MongoDB, you can play around with Authentication Restrictions and set clientSource or serverAddress respectively but only for which you are requiring the client or user to connect to or be validated to.

Role-Based Access Control

Role-Based Access Control (RBAC) in databases provides a convenient way to manage the user or an easy way to group a user with its designated privilege linked to a list of users or group of users.

Although you have to take note that roles are handled differently in any open source databases. For example, MySQL defined the roles as the following,

A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.

A user account can be granted roles, which grants to the account the privileges associated with each role. This enables assignment of sets of privileges to accounts and provides a convenient alternative to granting individual privileges, both for conceptualizing desired privilege assignments and implementing them.

MongoDB defines role with RBAC as,

MongoDB employs Role-Based Access Control (RBAC) to govern access to a MongoDB system. A user is granted one or more roles that determine the user’s access to database resources and operations. Outside of role assignments, the user has no access to the system.

Whereas in PostgreSQL,

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

The concept of roles subsumes the concepts of “users” and “groups”. In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.

Although these databases implement the roles specific to their usage, they share the concept of assigning roles to the user to assign privileges conveniently. Using roles allows databases administrators to manage required users to login or access the database.

Imagine if you have a list of users that you have to manage or a list of users that can be dropped or revoked when not needed anymore. In some specific cases, if a certain task needs work, database administrators can create users with roles already in place. These user(s) created can be assigned to a specific role for just a short period, then revoked once it's not needed. 

Audits also help segregate users that have a suspicion of vulnerabilities or data exposure so in that case, it helps manage the users with roles very easily.

User Management System

If your data security is handled and implemented appropriately, it paves your way to success. Although there's no perfect solution as vulnerabilities and intrusion always evolve as well. It's like a worm as it tries to lurk all the time until it's able to reach its goal to breach your security and gain access to your data. Without proper tools like alerting systems or advisories for any insecurities and vulnerabilities, it would be difficult to safeguard your data. 

ClusterControl helps you manage your users and verify or check your user's privileges from load balancers, to the main database users. It also offers advisers and alerts so that it will notify you of possible vulnerabilities or intrusions.

For instance, using a MySQL/MariaDB with a ProxySQL upfront features importing and adding of users. For importing users, it collects the list of users that are present in your current MySQL/MariaDB cluster and offers you to review its current privileges. See below,

Also in this instance, a ProxySQL user can be deactivated quickly if such vulnerability has been known for the specific user.

ClusterControl also offers you to directly manage users from your database such as for MySQL/MariaDB or PostgreSQL. For MySQL/MariaDB, you can go to → Manage → Schemas and Users.

For PostgreSQL,

→ Manage → Users Management.

 

With ClusterControl, you can customize your alerts by using the advisors. Advisors are script-based entities that are modifiable. For example, this is in a MySQL/MariaDB cluster as shown below, which can be accessed through → Performance → Advisors:

By clicking the Edit button, you can customize how ClusterControl would react in case it finds users with any host or '%" or a user without a password. See how the script is shown once you hit the Edit button.

Once ClusterControl finds out that any of these advisors are triggered, an alert shall be displayed and will also be sent to the email you have setup or if any third-party notifications are integrated, it will be notified there as well.

Conclusion

Database access privilege is one of the major targets of concern for data breaches and intrusions. If your database user is exposed or if there was a major threat to the current database version that was not patched, chances of being hacked or the target of ransomware and theft is very high. Understanding the access privileges and setting the correct limits helps you mitigate the dangers of exposing your valuable data.