- Preamble: a reliable and secure context for the computer
- HFSQL security
- HFSQL server setup
- Users and rights
- Stored procedures
- Files exceeding 2 GB
- Isolation and maintenance
- Securing the applications
- GDPR: General Data Protection Regulation
- Impact of security on performance
- Computer configuration
- Virtual servers
- More tips
HFSQL Client/Server database: Recommendations
Available only with this kind of connection
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 features:
Preamble: a reliable and secure context for the computer
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 computer : It is recommended to check the following points to ensure security :
- 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 operating system (64-bit Windows/Linux) must have all the latest 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:
- 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...) is very important, since it can determine the file management capabilities as well as the rights that can be set. 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: The following elements are used to insure the network security of the computer:
- 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: The implementation of a local firewall must be performed with an incoming access opened to the port of HFSQL server only (port 4900 in TCP by default).
- Local anti-virus: The implementation of a 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: The implementation of 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!
- Managing the user sessions: Don't leave sessions opened 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: The possibilities of remote access to the system (remote desktop, VNC, SSH...) must be secured to the maximum, and the possibilities must be reduces to the minimum.
- Managing the shares: The useless shares must 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 server setup
The security of an HFSQL server starts from its setup.
When installing an HFSQL server, 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.
Remark: During a setup in 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.
- 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.
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:
- Select the HFSQL Client/Server database in the Control Center.
- In the tab corresponding to your database, click the vertical "Backups" tab.
- 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 at 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.
Remark: The NAS servers are recommended for the backup 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 for each group, the HFSQL Control Center is used to grant read rights, write rights, creation rights, deletion rights, ... ("Managing rights" option found in the "Rights" group of tabs corresponding to the server, to the database and to the database files).
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.
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).
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 that requires a user and a password to connect, each data file (table) can also be protected by encryption and by a password. The encryption algorithm (128 bits, RC5 12 rounds, RC5 16 rounds) is defined in the WINDEV/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 a high-security level ". 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).
Furthermore, the option for managing the files exceeding 2 GB must be enabled in the WINDEV/WEBDEV analysis corresponding to the databases ("Details" tab in the description window of files):
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:
- Display the characteristics of an HFSQL server:
- In the "Parameters" group, click "Server configuration".
- Click "System":
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. The log process can be defined for each item : therefore, you have the ability not to include some items in the log for performance reasons (size of the log) or for security reasons (avoid duplicating very sensitive data).
To enable the log process on the data files:
- In the data model editor, display the description window of the data files ("Description of data files" in the context menu of a file).
- In the "Various" tab, specify the type of log process to implement:
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:
- 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
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.
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.
Be careful about the different processes that can be enabled on the server (anti-virus, disk check, ...): no process other than the HFSQL server must 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
Remark: You have the ability to sign the executable and the setup if only the signed applications are authorized by the system.
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.
The recommended physical characteristics of the computer:
- Multi-processors, multi-cores, in order for the requests to be processes in parallel by the the HFSQL server
- 64-bit technology : In order for the HFSQL server to be able to use more than 2 GB of RAM, it must be installed inn its 64-bit version, on a 64-bit system and on a 64-bit computer.
- 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 an important number of accesses to the disks (read and write operations), so the presence of fast disks can significantly increase the speed of some operations.
- RAID : The implementation of a RAID system is used to get better security and to distribute the read operations on 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.
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.
- 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).
This page is also available for…