REPOST: A security flaw in MySQL authentication. Is your system vulnerable? Check it today..

A few days ago Sergei Golubchik of Monty Program sent an e-mail to the Open Source Security mailing list informing about a security vulnerability in MySQL authentication system. Under certain circumstances a remote attacker may easily gain access to MySQL database as any user and all they need to know is a valid user name (e.g. root user exists in nearly all installations). The problem has only been addressed in the most recent database versions.

The full details are covered in Sergei’s post linked above. Not all MySQL releases are affected as the cause appears to be related to the build environment and the options used in the binary build process. For instance binaries distributed by Oracle appear to be safe as well as those available from RedHat’s repository.

We encourage you to test this against your database if you’re running MySQL versions up to 5.1.61 or 5.5.22 to see whether you need to upgrade or not. The test can be done with a simple line that can be run in bash:

for i in `seq 1 2000`; do mysql -u root --password=somerandomcharacters -h 127.0.0.1 ; done

Remember that following even the basic security practices can often save you from getting your database hacked after a new vulnerability is published:

• never let MySQL to listen on a network interface accessible from the internet (set bind-address in my.cnf if necessary)
• build firewall rules to filter out any addresses that do not require database access
• always create a new privileged MySQL user under a different name to replace the default root account and remove root afterwards
• remove test account that is installed by default
• upgrade database as soon as possible after a new vulnerability is published 

Below is Email from  Sergei Golubchik

Security vulnerability in MySQL/MariaDB sql/password.c

---

From: Sergei Golubchik
Date : Sat, 9 Jun 2012 17:30:38 +0200

---

Hi

We have recently found a serious security bug in MariaDB and MySQL.
So, here, we'd like to let you know about what the issue and its impact
is. At the end you can find a patch, in case you need to patch an older
unsuported MySQL version.

All MariaDB and MySQL versions up to 5.1.61, 5.2.11, 5.3.5, 5.5.22 are
vulnerable.
MariaDB versions from 5.1.62, 5.2.12, 5.3.6, 5.5.23 are not.
MySQL versions from 5.1.63, 5.5.24, 5.6.6 are not.

This issue got assigned an id CVE-2012-2122.

Here's the issue. When a user connects to MariaDB/MySQL, a token (SHA
over a password and a random scramble string) is calculated and compared
with the expected value. Because of incorrect casting, it might've
happened that the token and the expected value were considered equal,
even if the memcmp() returned a non-zero value. In this case
MySQL/MariaDB would think that the password is correct, even while it is
not.  Because the protocol uses random strings, the probability of
hitting this bug is about 1/256.

Which means, if one knows a user name to connect (and "root" almost
always exists), she can connect using *any* password by repeating
connection attempts. ~300 attempts takes only a fraction of second, so
basically account password protection is as good as nonexistent. 
Any client will do, there's no need for a special libmysqlclient library.

But practically it's better than it looks - many MySQL/MariaDB builds
are not affected by this bug.

Whether a particular build of MySQL or MariaDB is vulnerable, depends on
how and where it was built. A prerequisite is a memcmp() that can return
an arbitrary integer (outside of -128..127 range). To my knowledge gcc
builtin memcmp is safe, BSD libc memcmp is safe. Linux glibc
sse-optimized memcmp is not safe, but gcc usually uses the inlined
builtin version.

As far as I know, official vendor MySQL and MariaDB binaries are not
vulnerable.

Regards,
Sergei Golubchik
MariaDB Security Coordinator

References:

MariaDB bug report: https://mariadb.atlassian.net/browse/MDEV-212
MariaDB fix: http://bazaar.launchpad.net/~maria-captains/maria/5.1/revision/3144

MySQL bug report: http://bugs.mysql.com/bug.php?id=64884
MySQL fix: http://bazaar.launchpad.net/~mysql/mysql-server/5.1/revision/3560.10.17
MySQL changelog:
  http://dev.mysql.com/doc/refman/5.1/en/news-5-1-63.html
  http://dev.mysql.com/doc/refman/5.5/en/news-5-5-24.html

Calculate the size of innodb_buffer_pool_size and key_buffer_size

As a DBA sometime you will be working on Performance Optimization/Tuning/Configuration Optimization. You must be working to tune RAM of existing/New server. Keep in mind below points.

To working with transactional database you need to configure innodb_buffer_pool_size.  It will cache Indexes as well as data. Use below query to check the size.

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

If you are working with MyISAM engine, you can calculate size of key_buffer_size using the below query. It will provide you approximate size of key_buffer_size. It cache only MyISAM Indexes.

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

Calculate MySQL Database Size

To calculate the size of database and checking the fragmentation in MySQL.  Execute below command , it will provide you size of MySQL database and Fragmentation detail.

>SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;

To know the size of  MySQL  database  .. Execute command

>SELECT table_schema "Data Base Name", sum( data_length) / 1024 / 1024 "Data Base Size in MB"  FROM information_schema.TABLES GROUP BY table_schema;

To know the size the index

>SELECT table_schema "Data Base Name", sum(  index_length ) / 1024 / 1024 "Index Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

If you are using only MyISAM tables, you can also calculate size by command(This command will be executed on shell prompt)

$du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`