MySQL: Setting Account Resource Limits

mysql account resource limitsOne means of restricting client use of MySQL server resources is to set the global max_user_connections system variable to a nonzero value. This limits the number of simultaneous connections that can be made by any given account, but places no limits on what a client can do once connected. In addition, settingmax_user_connections does not enable management of individual accounts. Both types of control are of interest to MySQL administrators.

To address such concerns, MySQL permits limits for individual accounts on use of these server resources:

  • The number of queries an account can issue per hour
  • The number of updates an account can issue per hour
  • The number of times an account can connect to the server per hour
  • The number of simultaneous connections to the server by an account

Any statement that a client can issue counts against the query limit, unless its results are served from the query cache. Only statements that modify databases or tables count against the update limit.

An account in this context corresponds to a row in the mysql.user table. That is, a connection is assessed against the User and Host values in the user table row that applies to the connection. For example, an account 'usera'@'' corresponds to a row in the user table that has User and Host values of usera and, to permit usera to connect from any host in the domain. In this case, the server applies resource limits in this row collectively to all connections by usera from any host in the domain because all such connections use the same account.

Before MySQL 5.0.3, an account was assessed against the actual host from which a user connects. This older method of accounting may be selected by starting the server with the --old-style-user-limits option. In this case, if usera connects simultaneously from, the server applies the account resource limits separately to each connection. If usera connects again from, the server applies the limits for that connection together with the existing connection from that host.

To establish resource limits for an account at account-creation time, use the CREATE USER statement. To modify the limits for an existing account, use ALTER USER. (Before MySQL 5.7.6, use GRANT, for new or existing accounts.) Provide a WITH clause that names each resource to be limited. The default value for each limit is zero (no limit). For example, to create a new account that can access the customer database, but only in a limited fashion, issue these statements:

mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5
    ->          MAX_USER_CONNECTIONS 2;

The limit types need not all be named in the WITH clause, but those named can be present in any order. The value for each per-hour limit should be an integer representing a count per hour. For MAX_USER_CONNECTIONS, the limit is an integer representing the maximum number of simultaneous connections by the account. If this limit is set to zero, the global max_user_connections system variable value determines the number of simultaneous connections. Ifmax_user_connections is also zero, there is no limit for the account.

Lire la suite…

Collect & visualize your logs with Logstash, Elasticsearch & Redis

14/04/2019 Categories: Système Tags: , , , Aucun commentaire


Update of December 6th : although Logstash does the job as a log shipper, you might consider replacing it with Lumberjack / Logstash Forwarder, which needs way less resources, and keep Logstash on your indexer to collect, transform and index your logs data (into ElasticSearch) : check out my latest blog post on the topic.


Kibana Dashboard

Even if you manage a single Linux server, you probably already know how hard it is to keep an eye on what’s going on with your server, and especially tracking logs data. And this becomes even worse when you have several (physical or virtual) servers to administrate.


Although Munin is very helpful monitoring various informations from my servers / VMs, I felt the need of something more, and bit less static / more interactive.

There are 3 kind of logs I especially wanted to track :

  • Apache 2 access logs
  • iptables logs
  • Syslogs

After searching arround on the internet for a great tool that would help me, I read about the open source log management tool Logstash which seems to perfectly suit a (major) part of my needs : logs collecting / processing.

For the purpose of this post, I will take the following network architecture and assume and I want to collect my Apache, iptables, system logs from servers 1/2/3 (“shippers”) on server 4 (“indexer”) and visualize them :


As you can see, I am using 4 complementary applications, the role of each one being :

  • Logstash : logs collector, processor and shipper (to Redis) on log “shippers” 1-3 ; logs indexer on server 4 (reads from Redis, writes to Elasticsearch)
  • Redis : logs data broker, receiving data from log “shippers” 1-3
  • Elasticsearch : logs data persistent storage
  • Kibana : (time-based) logs data visualization (graphs, tables, etc.)

Lire la suite…

Categories: Système Tags: , , ,

How to secure SSH login with one-time passwords on Linux

As someone says, security is a not a product, but a process. While SSH protocol itself is cryptographically secure by design, someone can wreak havoc on your SSH service if it is not administered properly, be it weak passwords, compromised keys or outdated SSH client.

As far as SSH authentication is concerned, public key authentication is in general considered more secure than password authentication. However, key authentication is actually not desirable or even less secure if you are logging in from a public or shared computer, where things like stealth keylogger or memory scraper can always a possibility. If you cannot trust the local computer, it is better to use something else. This is when « one-time passwords » come in handy. As the name implies, each one-time password is for single-use only. Such disposable passwords can be safely used in untrusted environments as they cannot be re-used even when they are stolen.

One way to generate disposable passwords is via Google Authenticator. In this tutorial, I am going to demonstrate another way to create one-time passwords for SSH login: OTPW, a one-time password login package. Unlike Google Authenticator, you do not rely on any third party for one-time password generation and verification.

What is OTPW?

OTPW consists of one-time password generator and PAM-integrated verification routines. In OTPW, one-time passwords are generated apriori with the generator, and carried by a user securely (e.g., printed in a paper sheet). Cryptographic hash of the generated passwords are then stored in the SSH server host. When a user logs in with a one-time password, OTPW’s PAM module verifies the password, and invalidates it to prevent re-use.

Step One: Install and Configure OTPW on Linux

Debian, Ubuntu or Linux Mint:

Install OTPW packages with aptget.

$ sudo apt-get install libpam-otpw otpw-bin

Open a PAM configuration file for SSH (/etc/pam.d/sshd) with a text editor, and comment out the following line (to disable password authentication).

#@include common-auth

and add the following two lines (to enable one-time password authentication):

auth       required
session    optional


Fedora or CentOS/RHEL:

OTPW is not available as a prebuilt package on Red Hat based systems. So let’s install OTPW by building it from the source.

First, install prerequites:

$ sudo yum git gcc pam-devel
$ git clone
$ cd otpw

Open Makefile with a text editor, and edit a line that starts with « PAMLIB= » as follows.

On 64-bit system:


On 32-bit system:


Compile and install it. Note that installation will automatically restart an SSH server. So be ready to be disconnected if you are on an SSH connection.

$ make
$ sudo make install

Now you need to update SELinux policy since /usr/sbin/sshd tries to write to user’s home directory, which is not allowed by default SELinux policy. The following commands will do. If you are not using SELinux, skip this step.

$ sudo grep sshd /var/log/audit/audit.log | audit2allow -M mypol
$ sudo semodule -i mypol.pp

Next, open a PAM configuration file for SSH (/etc/pam.d/sshd) with a text editor, and comment out the following line (to disable password authentication).

#auth       substack     password-auth

and add the following two lines (to enable one-time password authentication):

auth       required
session    optional

Lire la suite…

How to run iptables automatically after reboot on Debian

reboot iptablesIf you have customized iptables rules, and would like to load the customized iptables rules persistently across reboots on Debian, you can leverage if-up.d scripts that are located in /etc/network/if-up.d. On Debian, any script that is marked as executable and placed in /etc/network/if-up.d gets executed when a network interface is brought up.

In order to run iptables automatically after reboot on Debian, do the following.

First, customize iptables as you wish, and then save the current iptables rule-set using iptables-save command.

$ sudo iptables-save > /etc/firewall.conf

The above command will dump the current iptables rule set into /etc/firewall.conf file which iptables-restore command can later use to restore the same rule set.

Now create the following if-up.d script called iptables that restores the saved iptables rule set.

$ sudo vi /etc/network/if-up.d/iptables
iptables-restore < /etc/firewall.conf
$ sudo chmod  x /etc/network/if-up.d/iptables

Alternatively, you can add « iptables-restore < /etc/firewall.conf » command to /etc/rc.local, which gets executed at the end of system boot-up.

Source: Xmodulo

Sécuriser son serveur Linux

Sécuriser Linux

securiser linuxCe guide va vous apprendre à sécuriser un serveur et donc vous initier aux thématiques de la sécurité informatique. En quoi est-ce important ? Par définition, un serveur est ouvert sur le monde, un minimum de sécurité est donc intéressant afin de se prémunir des attaques les plus simplistes.

La marche à suivre sera donc la suivante :

  • présentation des failles ;

  • présentation des outils pour y pallier.

Bien entendu, je ne fais pas un cours complet de sécurité informatique, ce tutoriel est une initiation. Pour faire simple, c’est un peu comme fermer les volets la nuit.

Point de vue matériel, voilà ce qu’il vous faudra :

  • un serveur embarquant une distribution xBuntu ou Debian (pour les autres distribs, le principe sera le même, mais les commandes risquent d’être différentes) ;

  • un accès root (en ssh par exemple) et une console.

Tout le tutoriel se passe en ligne de commande afin de pouvoir être accessible à tous. Comme il faudra éditer des fichiers, veuillez vous assurer d’avoir un éditeur de fichier en ligne de commande (j’utiliserai personnellement nano qui est très simple).

C’est parti !

Filtrer le trafic via le firewall


Le firewall (pare-feu en français) est l’élément indispensable pour sécuriser son serveur. Il va en effet filtrer tout le trafic en n’autorisant que les échanges permis par l’administrateur. Sans firewall correctement réglé, tous les trafics sont plus ou moins permis (c’est-à-dire qu’un attaquant peut faire ce qu’il veut chez vous) et ce genre de faille est détectable par un simple scan de ports.

Or, le noyau Linux offre déjà un pare-feu à l’utilisateur, qu’il est possible de configurer via le logiciel iptables (normalement contenu dans /sbin/iptables). S’il n’est pas installé :

apt-get install iptables

Nous allons maintenant détailler le fonctionnement d’un firewall – relativement simple. Un firewall analyse tout le trafic et vérifie si chaque paquet échangé respecte bien ses règles (critères de filtrage). Donc, il suffit de spécifier de bonnes règles pour interdire tout trafic superflu.

Les critères peuvent être divers (filtrer les ports, les protocoles, les adresses IP, etc). De base, nous allons spécifier nos règles sur les ports. Bien entendu, il faut être le plus strict possible quant au choix des règles ; c’est pourquoi, par défaut, tout firewall se règle en premier lieu en bloquant tout, absolument tout. Ensuite, nous allons « ouvrir » (autoriser le trafic) certains ports que nous voulons utiliser (par exemple pour un serveur web, nous allons ouvrir le port 80 afin que le site web soit accessible).

Déclaration des règles

Filtrage intégral

Suit la marche à suivre pour créer les règles :

1. Créons le script :

nano /etc/init.d/firewall

Et on y écrit : #!/bin/sh

2. On efface les règles précédentes pour partir sur de bonnes bases :

iptables -t filter -F
iptables -t filter -X

3. On bloque par défaut tout le trafic (si vous êtes en ssh, bien entendu, n’exécutez pas encore le script !) :

iptables -t filter -P INPUT DROP
iptables -t filter -P FORWARD DROP
iptables -t filter -P OUTPUT DROP

4. On ne ferme pas les connexions déjà établies :

iptables -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -m state --state RELATED,ESTABLISHED -j ACCEPT

Nous indiquons avec les paramètres -m et --state de ne pas fermer les connexions qui sont déjà établies.

5. On autorise le loopback (on ne va pas se bloquer nous-mêmes !)

iptables -t filter -A INPUT -i lo -j ACCEPT
iptables -t filter -A OUTPUT -o lo -j ACCEPT

Note : lo signifie localhost (le serveur lui-même).

Tout est bloqué, il ne nous reste plus qu’à ouvrir les ports utilisés.

Lire la suite…