Showing entries 1 to 10 of 13
3 Older Entries »
Displaying posts with tag: Database Administration & Monitoring (reset)
Changes that I like in the new MySQL 8.0.21

A new release of MySQL was out on July 13 (8.0.21).
Among all the changes, there are some that I have already tested and that I really appreciate.

Who stopped the MySQL server?

In previous releases, I can already see in the error log file who stopped the MySQL server if this was done through the shutdown statement.
In MySQL 8.0.20:

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
# tail -2 /u01/app/mysql/admin/mysqld2/log/mysqld2.err
2020-07-23T20:39:08.049064+02:00 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.20).
2020-07-23T20:39:09.796726+02:00 0 [System] [MY-010910] [Server] /u01/app/mysql/product/mysql-8.0.20/bin/mysqld: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.

In MySQL 8.0.21:

mysql> …
[Read more]
The evolution of MySQL authentication mechanism

The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network.

In MySQL, there is plenty of different authentication methods available, and last versions improved the security of this concept.



At the beginning, the mechanism, called mysql_old_password, was pretty insecure: it’s based on a broken hashing function and the password is 16 bytes long. It was not so complex for attackers to find a plaintext password from the hash stored in the password column of …

[Read more]
MySQL 8 and Security – Encryption of binary logs

As I discussed in some of my recent talks at conferences (at the DOAG for example), MySQL 8 came out with new features which bring lots of improvements in terms of security.

“At-Rest” encryption has been existing from some releases by now:
– InnoDB Tablespace Encryption: by 5.7.11
– Redo and Undo Log Data Encryption: by 8.0.1
Now starting from version 8.0.14, you can also encrypt binary and relay log files. In this blog post we will see how to configure that and we will do some tests.

Case 1: Binary log files are not encrypted

Binary log files encryption is disables by default:

mysql> show variables like 'binlog_encryption';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_encryption | OFF   | …
[Read more]
My first presentation at the DOAG – “MySQL 8.0 Community: Ready for GDPR?”

This year I participated for the first time to the DOAG, the conference which takes place in November in Nuremberg. Here some key words about this event: Oracle and other technologies, 2000 visitors, more than 400 sessions, more than 800 abstracts sent, exhibitors…
And for me everything started when in June I decided to send an abstract for a MySQL session.

Preparation

I’ve been working on MySQL for several years. At the beginning of this year, I started testing the new 8.0 version. We live in an age where security is more important than ever, GDPR and other regulations force us to review some subjects such as privacy and data policies. MySQL put in place lots of improvements regarding security in this last version.
So my session proposal for the DOAG was the following one:

MySQL 8.0 Community – Ready for GDPR ?
One of the most topical subject today is security.
New …

[Read more]
Password Verification Policy in MySQL 8.0.13

The new release 8.0.13 for MySQL is available since last week.
Concerning security, this comes with a new feature already announced: the Password Verification Policy.
Let’s have a look…

This aim of this feature is to secure the attempts to change a password by specifying the old one to be replaced.
It is turned off by default:

mysql> show variables like 'password_require_current';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_require_current | OFF   |
+--------------------------+-------+

and we can activate it by several ways (as for some other password features):
1. Globally, at the server level:

mysql> set persist password_require_current='ON';
mysql> show variables like 'password_require_current';
+--------------------------+-------+
| Variable_name            | Value | …
[Read more]
MySQL 8.0 – Roles are finally there

Roles have been existing on many RDBMS for a long time by now. Starting from version 8.0, this functionality is finally there for MySQL.
The most important advantage is to define only once a role that includes a “set of permissions”, then assign it to each user, avoiding wasting time declaring them individually.

In MySQL, a role can be created like a user, but without the “identified by” clause and without login:

mysqld2-(root@localhost) [(none)]> CREATE ROLE 'r_sakila_read';
Query OK, 0 rows affected (0.03 sec)
mysqld2-(root@localhost) [(none)]> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| …
[Read more]
MySQL – Time mismatch between system and log files

I was working on MySQL upgrades to version 5.7 at a customer, when I found out that for my instance, time in the error log file didn’t correspond to local system time:

mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] ls -ltr mysqld1.err
-rw-r-----. 1 mysql mysql 13323 Apr 12 10:54 mysqld1.err

mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] tail -1 mysqld1.err
2018-04-12T08:54:55.300633Z 67 [Note] Access denied for user 'root'@'localhost' (using password: YES)

I performed the operation at 10h54 but in the error log file I saw 8h54.
I checked if there was a problem between system and instance time, but that was not the case:

mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] date
Thu Apr 12 10:56:02 CEST 2018

mysqld1-(root@localhost) …
[Read more]
MySQL – Foreign keys and “Cannot delete or update a parent row: a foreign key constraint fails” error

As you know, foreign keys establish a sort of relationship between 2 tables. MySQL requires InnoDB storage engine to support foreign keys.

In our example, we have the following parent table in a MySQL 5.7.21 server:

mysqld3-(root@localhost) [sakila]> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

and a foreign key is defined on the child table by using the “FOREIGN KEY… REFERENCES” syntax:

mysqld3-(root@localhost) [sakila]> show create …
[Read more]
Security improvements in MySQL 5.7

If you have a look on the last mysql 5.7.4 version or later you will probably see that there are several security improvements. The list of added security features and improvements can be seen on the following page: http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

There are three main improvements that are shortly described in this blog:

1. Nonempty plugin column
2. Password lifetime policy
3. mysql_install_db secured

 

Nonempty plugin column

As of MySQL 5.7.2, the server requires account rows in the mysql.user table to have a nonempty plugin column value and disables accounts with an empty value. The following error will occor when trying to connect with user having empty plugin colum:

 

2014-11-30T10:41:04.943384Z …

[Read more]
Oracle AVDF - Database Firewall Policies

The successful deployment of a Database Firewall depends on an effective policy. Oracle AVDF includes preconfigured firewall policies as described in the Firewall Policy page in the Policy tab of the Audit Vault Server console.

These include policies that log all SQL statements, or log only unique SQL statements. In addition, the Database Firewall policy editor enables you to design your own policies quickly and efficiently.

Policy rules can depend on any combination of the SQL statement type, name of the database user, IP address of the database client, operating system user name, client program name, or any exceptions you specify.

 

First policy and global concept

1. Log in to the Audit Vault Server console as an auditor, and click on the Policy tab:

 

[Read more]
Showing entries 1 to 10 of 13
3 Older Entries »