ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

This content has been translated automatically.  Click here  to view the French version.
Help / WLanguage / Managing databases / HFSQL / Managing HFSQL Client/Server
  • Overview
  • Preamble: a reliable and secure context for the machine
  • HFSQL security
  • HFSQL server setup
  • Backups
  • Users and rights
  • Stored procedures
  • Encryption
  • Files exceeding 2 GB
  • Tracing
  • Tests
  • Isolation and maintenance
  • Securing the applications
  • GDPR: General Data Protection Regulation
  • Impact of security on performance
  • Computer configuration
  • Virtual servers
  • More tips
WINDEV
WindowsLinuxJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac Catalyst
Others
Stored procedures
Overview
This help page presents the main recommendations for optimizing the security and performance of an HFSQL Client/Server database according to the constraints and desired features.
This document presents:
Preamble: a reliable and secure context for the machine
Before configuring the HFSQL server, the choice of the context in which the HFSQL server will be installed is essential for security.
Indeed, a setup performed on a computer in free access, running an operating system not upgraded from its security corrections, without anti-virus and without firewall, will never be a secure setup, regardless of the parameters of HFSQL server.
This document does not present the security about the setup environment of HFSQL server.
Several guides about the security according to the selected environment are available.
However, let's go over the main aspects about the environment of the HFSQL server that directly affect security:
  • Physical security of the machine To ensure security, we recommend the following:
    • Limit the physical accesses to the computer.
    • Ensure the power supply (redundant power supply, UPS, etc.).
    • Check the temperature and hygrometry of the room, etc.
    • Ensure the network accesses of the server (several network cards).
    • Remove unused devices from the computer.
    • Protect the BIOS with a password and disable unnecessary features and hardware in the bios (e.g. integrated USB ports if not used).
    • Choose a secure and fast physical media (disk). This physical media will store the HFSQL server and the databases. It is recommended to choose hardware equipped with a redundancy or physical backup system (RAID, mirroring, etc.) or SAN storage.
  • Operating system The server's operating system (Windows/Linux, 64-bit version) must be up-to-date with security patches. An administrator specialized in the selected system ensures the operating system is configured properly. All unused options and services should be uninstalled or disabled (web server, FTP server, etc.).
    For a server running Windows:
    • the operating system must correspond to a "server" edition, so the lifecycle announced by the publisher is not yet complete.
    • hardware configuration in terms of socket, CPU and RAM must be at least equal to the minimum configuration requested by the publisher for the system, plus the resources required for the HFSQL engine (evaluated on the basis of load tests with the databases to be hosted).
    • Make sure the server does not have the "Active Directory" role. In this case, the system caches are disabled in write mode, which greatly reduces data update performance.
    • if available, add an exclusion in the configuration of the recovery system to prevent ".NDX" files from being backed up. In this case, indexes that are not synchronized with the data could be restored.
  • File system The file system (FAT32, NTFS, NFS, Ext3, CodaFS, HFS, etc.) is important, as it can determine file management options and the rights you can set on files. It is recommended to use a system that supports advanced rights (such as NTFS) as well as files larger than 2GB. Avoid the "network" file systems (such as NFS).
  • Network security To ensure your workstation's network security, here are a few points to consider:
    • Use a VPN or SSP in case of external access.
    • Secure the TCP/IP stack.
    • Configure the network routers and firewalls.
    • Limit or prevent external access (Internet), wireless access (WiFi, 4G, etc.).
    • Implement checks for IP addresses and/or for MAC addresses.
  • Firewall A local firewall must be set up, with incoming access only to the HFSQL server port (default TCP port 4900).
  • Local anti-virus: Local anti-virus is recommended. Some exclusion rules must be implemented on the ".ndx", ".fic", ".mmo" and ".ftx" files found in the directory of HFSQL databases to avoid slowing down the access to data.
  • Backup system: Setting up a backup and restore system. A backup is used to retrieve the data further to an operating problem (hardware failure, error of operation, theft...). The presence of a copy/backup means that this backup must be located at a different physical location, but protected like the source data!
  • User session management: Do not leave sessions open on the server. Keeping a session opened on the server means possibilities of access and represents a failure in security. The server must not be a workstation.
  • Remote access Remote access: Remote access to the system (remote desktop, VNC, SSH, etc.) should be as secure as possible, and kept to a minimum.
  • Share management: Unnecessary shares should be deleted. The HFSQL server requires no share to operate. Therefore, no share must be defined on the server (except for the administrative shares). Ideally, the service for share management ("Server" in Windows) can be stopped to have no share.
HFSQL security

HFSQL server setup

The security of an HFSQL server starts from its setup.
When installing an HFSQLserver, a service is installed on the server: HFSQL (Manta). This service is associated by default with the local system account in Windows.
For better security, we advise you to change the account associated with this service and to use a specific account.
Note When installing under Linux, this account can be changed from the HFSQL Control Center.
Then, the following rights must be granted to the account associated with the HFSQL service:
  • "read" rights on the binary files of the HFSQL server,
  • "write" rights on the server configuration file (HFConf.INI file),
  • "total control" on the directory of databases.
The account used to run the service must have the local security policy "User Rights Assignment \Perform volume maintenance tasks". Without this policy, operations that require creating files and transferring records could be slowed down.
Tips::
  • To perform backups with an external tool, rights must also be granted to the account used by this external tool on the directory of HFSQL backups.
  • The other groups and accounts must have no rights in these directories.
As soon as the HFSQL server is installed:
  • you must change the default user account ("admin" without password) and associate it with a secure password (more than 8 characters with several letters and several digits).
  • we advise you to lock the ports of the manager of HFSQL servers (MantaManager) with a firewall (ports 4999 and 5002 in UDP and TCP). The use of the firewall is optional.

Backups

The databases and the HFSQL server must be saved on a regular basis.
If the backup of databases is performed while the HFSQL server is operating, it must be performed by the HFSQL server.
To perform a backup from the HFSQL Control Center:
  1. Select the HFSQL Client/Server database in the Control Center.
  2. In the tab corresponding to your database, click the vertical "Backups" tab.
  3. In the menu of the tab corresponding to your database, in the "Backup" group, expand "New backup" and select the type of backup to perform:
    • New hot backup: the backup will be performed immediately.
    • "New scheduled backup" (recommended option): the backup will be performed on the requested date. The wizard allows you to define the backup scheduling options.
Once this backup is performed, you will have the ability to use an external tool in order to compress it or to perform any other action.
Note: NAS-type servers are recommended for backup purposes only.

Users and rights

The HFSQL server is used to create users and groups of users via the HFSQL Control Center (vertical "Users" tab and vertical "Groups" tab available in the tab of HFSQL server).
For each user and each group, the HFSQL Control Center allows you to assign read, write, create, delete, etc., rights. ("Rights management" option in the "Rights" group of the server, database and data file tabs).
Rights management
To reduce the possibilities of accidental or malicious operations, the applications that connect to the HFSQL server must use an appropriate HFSQL user account. Two different users must not use the same HFSQL user account.
In the HFSQL Control Center, define a policy with the minimum rights granted to each user and to each group of users.

Stored procedures

The HFSQL stores procedures use the WLanguage, giving them power and several possibilities.
It is important to limit them and to control them. All the stored procedures must be controlled by a database administrator (DBA) before they are deployed.
We advise you to only allow the database administrators to deploy new stored procedures ("Rights to configure the stored procedures" in the HFSQL Control Center).

Encryption

Several types of encryption can be performed:
  • Encrypting the connections between the clients and the server:
    When a client application connects to an HFSQL database, you can use an encrypted connection. Therefore, the data that flows over the network is automatically encrypted.
    For better security, we advise you to use this option. We advise you to define rights on the database that forbid the connections to the server without encryption ("Rights to connect to the server (encrypted connection only)" in the HFSQL Control Center).
  • Encrypting the data files:
    In addition to the HFSQL account, which requires a user and password to log in, each data file can also be protected by encryption and a password. The encryption algorithm (128 bits, 12-round RC5, 16-round RC5, etc.) is defined in the WINDEV and WEBDEV analysis by the developer. The password can be defined by the developer or by an option of a client application in order to be defined by a database administrator.
    An encryption with strong password is recommended for the data files containing sensitive data. When defining the encryption in the analysis, it must be defined for the data file (.fic), for the index (.ndx, .ftx) and for the memos.
    For maximum encryption security, check "Enable enhanced security ". This option is used to encrypt the data without storing the encryption key in the file. With this option and without the file password, the content of data file cannot be retrieved.
    These options are defined in the data model editor, in the "Details" tab of the description window of the files.
  • Applicative encryption:
    For the items containing very sensitive data (bank card number for example), we advise you to add an applicative encryption/decryption. An applicative encryption makes the data unreadable without the corresponding applicative, even with a user account for the database and with the file password.

Files exceeding 2 GB

If the size of some data files may exceed 2 GB, the HFSQL server must be installed on an operating system and with a file system allowing to manage this type of file (like NTFS).
The option to manage files larger than 2 GB must be enabled in the WINDEV or WEBDEV analysis corresponding to the databases ("Compatibility" tab of the file description window):
For a logged file, the log takes the same option as the associated data file. Therefore, this option must be enabled on a file even if only its log may exceed 2 GB.
An option is also available for the HFSQL server system files to allow files larger than 2 GB. Enable this option if the data is installed on a file system that supports the files exceeding 2 GB. To enable this option in the HFSQL Control Center:
  1. Display the characteristics of an HFSQL server:
  2. In the "Parameters" group, click "Server configuration".
  3. Click "System":

Tracing

To be able to check the operations that have been performed on the database, an tracing mechanism must be implemented on the sensitive data files.
This operation can be performed by enabling the log process on these data files. Logging can be defined at field level: it is therefore possible to exclude certain fields from the log for reasons of performance (size of log) or security (avoid duplication of highly sensitive data).
To enable the log process on the data files:
  1. In the data model editor, display the description window of the data files ("Description of data files" in the context menu of a file).
  2. On the "Logging" tab, choose the type of log you want to use:
    Enable logging
You also have the ability to enable the generation of a log file by the HFSQL server. The log file does not contain the data found in the databases but it can be used to identify the different requests received and processed by the server.
To enable this option in the HFSQL Control Center, select the "Configuration" tab of server description:

Tests

  • Regression test
    When updating the HFSQL server, the client framework or the client applications that use the HFSQL database, regression tests must have been performed beforehand on a test configuration before being implemented on the real configuration.
    Indeed, the implementation of an update that includes the correction of a security failure may provoke unexpected behaviors in the client applications and unwanted modifications in the databases.
  • Test in real conditions
    To make sure that the server is properly balanced and properly secured, tests must be regularly run on a similar configuration with similar volumes of data and a similar number of simultaneous connections to the real server.

Isolation and maintenance

  • Isolation
    For a better isolation between different databases, we recommend that you use different HFSQL servers. These HFSQL servers can be installed on the same computer in different directories, with different user accounts.
  • Maintenance
    It is important to maintain the HFSQL server and the client HFSQL layer (WINDEV framework) updated. The updates can include corrections about security failures.
The regular reindexing of the indexes found in the databases is used to insure optimized queries (via the update of statistics) and stronger files.
Beware of the various processes that may be active on the server (anti-virus, disk verification, etc.): no process other than the HFSQL server may access the databases.

Securing the applications

Securing a database means securing the applications that use the database. Indeed, the applications that access the database contain the information required to access the database. They represent a critical point for accessing the database.
Therefore, the applications that access a database must be protected by at least:
  • An authentication of users
  • An automatic lock of the application or session in case of long idle time
  • The authentication information must not be stored
  • The abilities to export data must be disabled wherever they are not strictly required in the application
  • The different options of the application must be accessible according to the user (User Groupware)
  • The setup of the application must be secured and accessible to the authorized persons only
Note It is possible to sign the executable and the installation, if the system only allows signed applications.

GDPR: General Data Protection Regulation

GDPR (General Data Protection Regulation) will replace "Data Protection Directive" on 25th May 2018.
A GDPR audit is available to easily identify and monitor personal data in a WINDEV, WEBDEV or WINDEV Mobile application..
For more details, see:
Impact of security on performance
Some security features often reduce performance. Conversely, the changes made to improve performance may affect security.
Examples: connection encryption, file encryption, logging...
Here are some reminders to improve the performance when accessing an HFSQL database (apart from optimizing the source code of applications, creating new indexes, etc.) without affecting security.

Computer configuration

The recommended physical characteristics of the computer:
  • Multi-processors, multi-cores, in order for the requests to be processes in parallel by the HFSQL server
  • 64-bit technology: For the HFSQL server to use more than 2 GB RAM, it must be installed in its 64-bit version, on a 64-bit system and on a 64-bit machine.
  • A large quantity of RAM. A large quantity of RAM allows the operating system and the HFSQL server to generate more cache during the reading of files, increasing the speed of processes. Furthermore, part of the RAM installed is used by the operating system and by the other services installed on the computer. For a large database, we recommend 16 GB of memory (or more).
  • Fast hard disks: The HFSQL server performs a large number of disk accesses (reads and writes), so the presence of fast disks can more than double the speed of certain operations.
  • RAID: Setting up a RAID system provides greater security, and also enables readings to be distributed over several disks. Read performance is improved.
  • Regular update of the index statistics of the databases (via a scheduled task of the server for example). The performance of queries is optimized when these statistics are up to date.

Virtual servers

When using a virtual server, the resources of the same physical computer are shared among several virtual servers. Therefore, the operating mode of a virtual server can affect the performance of the other servers.
For example, a process that performs intensive read operations on a physical disk in a virtual server will use all the access to the disk. All virtual servers that access this physical disk to read or write data will be slowed down.
To host the HFSQL server with a virtual server and maintain good performance, avoid using a shared physical disk or an "image" virtual disk. Whenever possible, use a Storage Area Network or a pass-through disk.

More tips

  • No screen save other than a black screen on the server because some screen savers can consume a lot of resources.
  • Ability to enable the compression of frames on the HFSQL communications. This option is useful only if the throughput of the communication network between the server and the client is low (GPRS, 3G...).
  • Server load balancing optimization. For more details, see Managing the load balancing (dynamic load balancing on server).
Minimum version required
  • Version 16
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 11/23/2024

Send a report | Local help