Introduction
In the database world, security and monitoring of the activity performed on databases are essential and cannot be overlooked. This applies to every organization that handles personal data, and it becomes a far greater challenge when many databases on different engines are accessed by many people from different business units (and sometimes from different companies).
Even so, access monitoring is often not configured, and the lack of common rules for how users connect to databases makes it hard to trace the actions performed on database instances.
The most common issues
The most common issues we see with database access are listed below.
- No general rules are defined for database access by the different users (internal and external, such as suppliers).
- To connect to the database through a client, users rely on shared application or generic accounts instead of named accounts. This is a serious security problem, because it makes it impossible to identify who connected and which operations they performed on the database.
- Some instances do not have auditing enabled, and on others it is enabled only to track certain operations in the database (for example, creating new tables). This inconsistency makes it difficult, and in some cases impossible, to monitor the activity performed on the instance by the different accounts.
Its goal is to detect suspicious activity and any errors. It also helps ensure the security and transparency of the activities performed.
Auditing records many types of operations, such as:
● login attempts;
● queries and commands executed;
● errors and malfunctions.
This information is usually stored in audit files on disk or in audit tables inside the database.
Solving issues of this kind requires a few actions:
- define rules for how users connect to the databases;
- define who can access the databases and which operations they can perform, based on their needs;
- find an alternative to auditing without enabling it directly in the database;
- define a secure method for managing database access credentials;
- block unauthorized access to the databases;
- identify a common tool for connecting to the databases, with features that help solve the issues listed above.
A real case and its solution
To identify, track, and manage access by users with standard (non-superuser) privileges, here is how we completed the installation and configuration of DBeaver Team Edition for one of our customers. It is a centralized web-based tool for connecting to and working with different types of databases. The best-known DBMSs that integrate with the tool include Oracle, PostgreSQL, MySQL, and SQL Server. See the full list of database engines, relational and non-relational, supported by DBeaver Team Edition.
DBeaver Team Edition offers many features that help solve these problems. Below we list some of them and briefly describe why they were decisive in choosing this tool, given the customer's requirements.
First, DBeaver Team Edition performs auditing itself, without enabling it in the database, which ensures that information is collected consistently across all instances. Access to the tool is by named account, and the tool's internal audit log tracks not only the databases a user connected to, but also the operations and queries they ran. For each entry, it records which tool user performed the operation, at what time, and whether it succeeded.
Below is a sample screenshot.
DBeaver Team Edition also provides structured management of user access to the databases. It relies on two concepts: teams, which are groups of users defined in the tool (in this project, we mapped them to the supplier companies), and projects, which are sets of database connections for a specific application and can be assigned to one or more teams.
This also lets us manage access credentials by storing and sharing them at team level, without individual users knowing the username and password. In practice, a dedicated DBeaver user with agreed grants is created in the database. This user is configured in the connection of the corresponding DBeaver project and can be used by all members of the teams that project is assigned to. Individual users therefore do not need to know this user's password, because the connection is ready to use.
1. define the teams and projects on which to structure DBeaver;
2. inventory the named accounts already present in the databases (for both business users and external suppliers);
3. create the named users, teams, and projects in DBeaver;
4. create the dedicated DBeaver user in the databases, so that the existing named accounts can be removed;
5. set up the database connections in the relevant DBeaver projects;
6. assign users to the corresponding teams;
7. assign projects to the relevant teams.
Introducing DBeaver Team Edition brought several benefits:
- the customer now has a single company-wide tool that lets the different users (internal and external) connect to the various types of databases, instead of a proliferation of Toad, SQL Developer, DBeaver, and so on;
- auditing is handled by the tool itself, so it is possible to monitor who performs which operations, when, where they connected, and with which account, across all databases. All logs have a similar format, even though they come from different databases, which makes comparison and analysis easier. There are storage benefits as well: there is no need to create records in dedicated audit tables or audit files on disk, because auditing is centralized in a central database created for this purpose;
- suppliers can no longer connect to the databases with the application account through local clients, only through DBeaver, which they access with named accounts;
- credentials are no longer handed over to suppliers, because the connections are already set up in DBeaver.
