Review of Popular Database Management Systems for Access Control Management

Terminology

A common mistake made by many security professionals is the incorrect use of the term “database” (DB) instead of “database management system” (DBMS). Let’s get to the bottom of this.

A database is a set of independent materials presented in an objective form, systematized in such a way that these materials can be found and processed by an electronic computer;

Database Management System (DBMS) – a set of software and linguistic tools for general or special purposes, to manage the creation and use of databases.

That is, in a simplified way, the “database” – it is the data itself, presented in the form of a set of files on disks, with which just works “database management system” (DBMS) – a software product that has the means to create, populate, modify and search the database.

Developers of various applications, including developers of ACS, work with DBMS and choose DBMS for their needs.

Requirements to DBMS used in ACS

• What is a DBMS – a database management system;
• What is an ACS – access control and management system.

What are the special requirements to DBMS used in ACS from the user’s point of view?

First – reliability: no data should be lost! Failures should be minimized and should not lead to data loss, databases should be reliably protected from unauthorized access, encryption functions may be required for sensitive sites, it is also necessary to provide regular backups of databases and the possibility to restore from the archive if necessary;

Secondly, the performance: the DBMS must provide an acceptable level of performance to solve the tasks assigned to it;

Thirdly, in my opinion, this is a confidence that the DBMS will be supported by the vendor and you will not be left alone in the event of a serious failure or difficult situation.

Types of RDBMS

DBMS at the moment there are a great many and they are classified in different ways. But we will not dwell in this article on the diversity of these types, we will omit promising and exotic technologies such as object-oriented and hierarchical DBMS. The de facto standard in modern information systems are relational DBMS, where data are stored in tabular form, and we’ll talk about them. So what are the differences between all these systems? I will list the key parameters, which are important both for developers and users of the system.

Ways to access the database

1. Client-server DBMS;
2. File-server DBMS;
3. Embedded DBMS.

In client-server DBMS (Microsoft SQL Server, Oracle, Firebird, PostgreSQL, InterBase, MySQL, etc.)

• All data processing is done in one place, on the server, in the same place where (usually) the data is stored;
• Only one server has access to the data files, one system is the DBMS itself;
• Client applications send requests to process and retrieve data from the DBMS and receive responses;
• Client applications do not have direct access to the data files.

All industrial DBMSs at the moment are exactly client-server.

• In file-server DBMSs (Paradox, Microsoft Access, FoxPro, dBase, etc.), the opposite is true;
• Applications share access to all database files (usually stored in some shared file storage) and process the data together;
• Each application processes the data independently.

At the moment, file-server technology is considered outdated, and its use in large information systems is a disadvantage. The problem is that file-server DBMS do not have many of the advantages of client-server, such as data caching, parallelism of queries, high performance and have a number of disadvantages (difficulties in maintaining database integrity, recovery, locks, etc.), which in turn leads to reduced reliability and performance. The state of the database in the file DBMS must be constantly monitored and its “treatment” operations should be carried out with the help of built-in or third-party utilities.

Embedded DBMS (SQLite, Firebird Embedded, Microsoft SQL Server Compact, etc.)

• Supplied as part of a ready-to-use software product, requiring no independent installation;
• They are intended for local storage of application data and are not intended for collective use in a network.

The free embedded DBMS SQLite is widely used in the famous Android mobile OS developed by Google and in many mobile applications.

Licensing scheme

1. Free DBMS;
2. Commercial industrial DBMS (most vendors also offer a free, limited version).

File server and embedded DBMSs are almost all free, of the free client-server DBMSs the best known: Firebird, PostgreSQL and MySQL.

A purely commercial product developed by Borland: DBMS InterBase. Previously, this DBMS had a free open-source version: InterBase 6.0, but the InterBase 6.0 Open Source Edition project is no longer supported by Borland. In 2001, a group of enthusiasts created a separate Open source DBMS project, Firebird, mentioned above, which became widely known and had many fans among developers.

Most manufacturers of industrial DBMS allow you to use free editions of their products, which are cut in functionality and performance variants of the full-featured version of the DBMS.

Comparison of free and commercial DBMSs

The table below shows the limitations of the most commonly used free editions of industrial DBMS.

If the maximum database size is exceeded, database writes will stop, but this problem is easily prevented. In general, the volume is required for storing the events constantly accumulated in the system, while the rest of the data (controller settings, access subject data, access levels etc.) is relatively static and may exceed the restrictions of the free Express versions only on super-large systems. It is necessary to set up the procedure of periodically deleting the old events from the database by means of your DBMS. In many access control systems, these procedures are provided by the developers and you just need to configure them.

As for performance limitations: if the system is small and does not imply heavy loads on the DBMS, you can easily confine yourself to the free edition, it will be more than enough. If the task imposes higher requirements on the DBMS subsystem: a large number of users in the system, high event traffic and data update flow in the system (objects with a large number of temporary visitors) and high requirements for event archive depth, then you can always switch from the free edition to the commercial version by paying the required license.

DBMS in ACS

The table below contains the data from open sources regarding the type of used DBMS in popular Russian access control and management systems.

• Parsec (ParsecNET 3) – Microsoft SQL Server (2012 Express, it is declared to support versions 2008 R2 and higher) – central DB; SQLite – local databases of workstations;
• Producer Elsys (ACS – Bastion 2) – Oracle (11g Express delivery), declared support versions Oracle 12c, Oracle SE2, can also be used PostgreSQL 10 or Postgres Pro;
• Perco manufacturer (ACS – S20) – Firebird 2.0;
• Producer NVP Bolid (Orion PRO ACS) – Microsoft SQL Server (supplied 2012 Express), support for versions 2008/2012/2014 is declared;
• Manufacturer RusGuard (ACS – RusGuard) – Microsoft SQL Server (supplied with 2014 Express), declared support for versions 2014/2016;
• Manufacturer Ravelin LTD (ACS – Gate) – Microsoft Access;
• Manufacturer PromAutomatica Service (ACS – Sphinx) – MySQL;
• Manufacturer Kodos (ACS – IKB Kodos) – Firebird;
• TSS manufacturer (Seven Seals ACS) – Firebird;
• Manufacturer BOSCH (ACS – Access PE) – Manufacturer Microsoft SQL Server (version 2014 Express Edition is recommended);
• Honeywell manufacturer (ACS – Pro-Watch) – Microsoft SQL Server 2012/2014/2016;
• Manufacturer Siemens (ACS – SiPass) – Microsoft SQL Server 2000;
• Manufacturer AAM Systems (ACS – Apacs 3000) – Firebird 2.5 (included), Microsoft SQL Server 2017 is also supported;
• Manufacturer AAM Systems (Lyrix ACS) – Borland Interbase 2007 (included in the delivery set), support for Oracle 10g and Microsoft SQL Server 2005.

As you can see, most ACS vendors supply a free version of the industrial client-server DBMS Microsoft SQL Server Express Edition and a free (free) cross-platform DBMS Firefird (about 50/50).

Specific choice of this or that DBMS – a matter of taste and preferences of each manufacturer, goodness – the choice is there. When choosing, developers also take into account the convenience and ease of administration, the availability of free built-in tools for administration and development.

In addition to high reliability and performance ACS should be convenient and inexpensive to support. ACS developers are well aware that even at large sites there are often no dedicated professionals for the maintenance of ACS, with knowledge of DBMS administration, so they try to include in their products features that facilitate and automate database maintenance processes.

First of all – database backup, the basis of the basics, which allows the system administrator to sleep well. All DBMS have their own tools for creating backups, but it is considered good form when the backup function is integrated into the product and the administrator only needs to enable/configure it and periodically check its functioning.

The second common problem is data recovery after a failure. Here again, a fresh backup comes to the rescue, but if it is not available, or if restoring all possible data is critical, then extra effort is required. Fortunately, in industrial DBMSs (which is not the case with old file DBMSs like Paradox) such events do not occur very often, they can only be caused by a “dying” hard disk or a power failure. In this case, you will need the services of a DBMS administrator who will be able to use the tools built into any serious DBMS to restore the maximum of what is possible. It should also be taken into account that some vendors provide database repair services as part of their technical support.

Our Recommendations

1. When choosing an ACS, pay attention to the DBMS which is supplied together with the system;
2. If you operate an ACS, find out which DBMS it uses;
3. Evaluate the data traffic and load in your system to determine the required DBMS server hardware resources and the required DBMS edition (consult the manufacturer of your ACS if necessary);
4. If your ACS uses the Express version of Microsoft SQL Server or Oracle, you need to ask yourself: “How much of the free database capacity do we need?” Set up periodic deletion of old events from the database by means of ACS (if any) or consider migrating to a paid, unrestricted version of the DBMS;
5. Set up backup copying of databases by means of ACS or by means of DBMS and check it regularly;
6. Find a DBMS specialist (administrator) who can be contacted in case of damage to the database, find out from the technical support of the ACS manufacturer the possibility of providing such services.

Conclusion of the review article on popular database management systems

After analyzing the most popular database management systems, we have come to a few conclusions. First, MySQL is still one of the most popular choices for small and mid-sized businesses. Second, MongoDB is quickly gaining traction as an excellent choice for big data applications. And finally, both Oracle and Microsoft SQL Server are well-rounded options that can be used for a variety of purposes. If you’re looking for a new database management system or just want to compare your current system to the rest of the market, be sure to read our reviews on our website. We cover all of the major players in this industry so you can make an informed decision about which product is best for your needs.