PlanetMysql.ru — информация о СУБД MySQL

Don’t share root user password and mysql.user table acess with anyone till you have full trust on it. Because that encrypted password is real password in MySQL so if anyone knows that than he/she can easily login with any user … Continue reading →
PlanetMySQL Voting: Vote UP / Vote DOWN

In this primer I will show how to improve the security of your MariaDB installation by using two-step verification and how to use it from your Windows GUI client.

Let’s suppose you have your data in MariaDB, installed, say, on Ubuntu. And your users connect to it to run ad hoc queries, using some sort of a Windows GUI client. You don’t want them to write the access password on post-it notes or have it auto-entered by the client. And you don’t want anyone see the password when one of the salespersons connects to the mother ship from his laptop in the Internet café. So you decide to use the two-step verification, just like Google does, to secure the access to the data.

If you don’t know what a “two-step verification” is, see, for example, this introductory video by Google.

So, 2-step verification looks great, but how can we implement it? Luckily, there is a PAM module in the Google Authenticator project, and we can use it with a MariaDB PAM authentication plugin.

Let me digress for a second. Below we will implement a 2-step verification based on Google Authenticator. But you may want to evaluate other similar solutions before putting this primer in production. There are quite a few PAM modules implementing one-time password approach (and pam_goolge_authenticator is just one of them). In particular, OPIE (and S/Key) look interesting, because they don’t require a valid Unix account and a home directory for every user. There are also hardware based solutions, where a user is required to have a small password generating device (like, for example, RSA SecurID).

But back to Google Authenticator. First, we need to install the PAM module. On Ubuntu 11.10 it is very simple: one apt-get. On distributions that don’t include it (and older Ubuntu versions), you can use manual installation instructions.

Either way, we install /lib/security/pam_google_authenticator.so (I don’t have Ubuntu 11.10, so I had to do it manually). For a 2-step verification we need to require both the normal account password and the one-time code. This can be achieved with the following PAM configuration file:

auth            required        pam_unix.so
auth            required        pam_google_authenticator.so
account         required        pam_unix.so

I had to put it in /etc/pam.d/mysql file, on your system the location may be different. Having done that, let’s install the MariaDB PAM plugin and create the user account:

Now we need to configure Google Authenticator for an account:

Time to install a password generator application. There are versions for Android, iOS, and Blackberry. Google explains the details. When the installation is finished, start the application and enter the secret key into it — manually or using the QR-code. And we’re done and can use two-step verification when connecting to MariaDB. But our users use Windows! How will it play along?

The answer depends on the Windows MariaDB client that that your users have. At the moment of writing this article, only HeidiSQL has full support for pluggable authentication. You may already have it installed — HeidiSQL is part of MariaDB Windows distribution. Alternatively, you can download it directly from the official site.

First, we use HeidiSQL session manager to configure the connection parameters. Here, we will connect as the user “serg” — the  user, that we have created above to use the PAM authentication plugin. Note that there is no special configuration for pluggable authentication on the client, it is enabled automatically when necessary.

Now we can connect to the server. It uses PAM authentication plugin, that loads Google Authenticator, that asks for a verification code. And we see HeidiSQL asking:

We start the Authenticator application, it generates the verification code:

And we use it to login! Next time the verification code will be different, and nobody looking over the shoulder will be able to steal our precious connection password.

PlanetMySQL Voting: Vote UP / Vote DOWN

Percona just released their MySQL PAM Authentication insanity, just as Oracle did before, for MySQL 5.5 and MariaDB is no better.

The Oracle module requires a module to be loaded into your client, which is done automatically if the module is present and the server supports PAM auth. The module is called ominously "mysql_clear_password" and does what it says on the tin: Your database server access password is henceforth sent from the client to the server in clear, not encrypted, hashed, salted or otherwise protected.

I suppose the Percona module does the same, although it is not being mentioned in the docs at all (or at least I have not been able to find it in there). They also openly suggest to run the database server as root, as that is the only way for an in-process PAM auth module to be able to access /etc/shadow.

*headdesk*

Does any of you know what SASL is and why it has been invented?

I know it's a pain, but it is there for a reason. Many reasons. saslauthd for example will read your authentication secrets instead of your worker process, because you are unable to write and maintain a secure codebase the size of a database server. And by speaking SASL on the wire and then handing off an authenticated connection to your actual worker code you gain access to a number of integrated mechanisms for communicating passwords in a compatible and secure manner, none of which include clear text passwords on the wire.

Can we please bury these plugins, deeply in the Mariana trench, in a CASTOR, put a warning beacon over the site and then start over, doing it right this time?

Thanks. I knew you would see the light eventually.
PlanetMySQL Voting: Vote UP / Vote DOWN

This week we have a big announcement about Sarah, 3 hosts and an extra special guest.

News
Call for papers for Percona Live: MySQL Conference & Expo 2012 is open until Monday, December 5th. The MySQL Conference & Expo is Tuesday April 10 - Thursday, April 12, 2012 in Santa Clara, CA.

To submit a paper, first register as a speaker at http://www.percona.com/live/mysql-conference-2012/user/register and then go to My Account -> Submit Proposal.

Main content
Previous podcasts about securing MySQL

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

Linux, Apache, MySQL and PHP — altogether they mean LAMP. I’m not talking about watts and bulbs.

And if you desire is for a comprehensive, robust server, your IT infrastructure has to include all of these systems.

Monitis has put together a checklist of 101 actions you can take to maximize security around LAMP.  Hopefully we’re shedding a little light around this issue for you to give you some new ideas on how to make administering your system easier — so that, in turn, you can focus on more strategic tasks. You can find previous posts about increasing security around Linux and Apache, but, in today’s post, we’ll offer tips on LAMP security around MySQL — a powerful open-source database.

MySQL security tips:

1. Grant minimal permissions

If a database user needs “read” access to two tables, you should not be giving that user full access to the entire database server. The problem is that novice database administrators will often grant full access to new MySQL users, either on a whole database or on the whole database server. Don’t make that mistake, read up on MySQL’s GRANT statements and how to use them.

2. Separate read/write users

Many web applications have distinct areas, some of which need only “read” access, while other areas might require “write” access to one or two tables, and yet other areas require write access to most of the database. Consider creating separate accounts for each area and walling the areas off. Also, double-check whether the payment processing code really needs full access, or whether INSERT access is sufficient. Use fine-grained security controls and keep up-to-date, relevant, documentation.

3. Use TLS

As with Apache, you should use SSL/TLS with MySQL as well. This may not be relevant if your database server is on the same host as your web server, but often enough it isn’t. Use “stunnel” to establish a secure connection between Apache and MySQL to prevent attackers from sniffing out your precious passwords.

4. Use syslog

Just like Apache, you should configure your MySQL server to send its logs to the syslog server. If you use a remote syslog daemon, check your setup: Make sure that the correct error levels are being logged, and that the log messages actually arrive on the other end. (See our blog
Apache and MySQL Logging with Syslog-ng)

5. Restrict user table access

The “user” table in MySQL is a rather precious table that only administrators should have access to. Disable access to the user table for all users except root — and any other administrative accounts. This applies to read and to write access, though, of course, you should be especially watchful with regards to write access.

6. Set a root password

As dumb as this sounds, the default MySQL installation often does not set a password for the “root” user. You wouldn’t leave your system root password empty, so you shouldn’t leave your MySQL root password empty either. This is a good chance to check that all the other accounts have passwords set as well.

7. Plain-text passwords

Don’t store plain-text passwords in your database, ever! The rule is as simple as that. In fact, don’t store plain-text passwords anywhere.

8. Disable external access

Hopefully at this point you’ve already configured iptables to disallow external access to your MySQL server. Just in case, you should tell MySQL not to accept connections from any hosts other than localhost. You might ask yourself what you should do if you need to connect externally? Remember, external connections should go through stunnel! To MySQL, it will look as if the connection is coming from localhost.

9. Run the MySQL server as a separate user

You should already be running Apache in a dedicated user account. Do the same with your MySQL server. Make up your own name and remember that non-default names are often good for security. You probably know this, but just in case: Don’t run your MySQL server as root, and don’t run it as “nobody”.

10. Database directory permissions

MySQL, as any other program, needs to store its data somewhere. Configuration is stored in /etc, of course, but where does the actual data go? This is installation-specific. The default location is /usr/local/mysql/data. Please make sure that this directory has very restrictive permissions, as it will include the “user” table. It will obviously also include your application data, which may or may not be sensitive.

11. my.cnf permissions

Allowing an attacker to modify your MySQL configuration is a big security issue. They could simply reset your MySQL root password and they’d get away with it.  Make sure my.cnf is only root-writable, and enforce this with a tripwire program.

12. Non-default user names

Like using a non-default SSH port, using non-default user names is only going to help your system’s security. As a minimal security measure, rename your MySQL root user to something other than “root”. For added security, consider naming your web application user something other than “www”, “php”, “web”, or “app”.

“test” user. Default user accounts are a real chore to remove, though if you remove the default “test” user account, you also get a lot of security in return. This “test” user is usually not protected with a password and can be a first entry point for an attacker. You can protect yourself from being low-hanging fruit.

13. Restrict “special” permissions

There are four “special” permissions that you have to watch out for: PROCESS, SUPER, SHUTDOWN, and FILE. The short version: Only “root” should have these permissions. The longer version is that they allow the calling user to shutdown your database server and change server variables, among other nasty stuff.

14. Disable LOAD DATA LOCAL INFILE

For speedy data loading, “LOAD DATA LOCAL INFILE” can be a real charm. You may want to enable it when you first deploy your database server. After that, you should disable this command, as it allows an attacker to read all local files that your MySQL server has access to.

15. Clean your history files

History files like “.mysql_history” often contain passwords from previous sessions. The same goes for your “.bash_history” if you’ve entered a MySQL account password on the command-line in the past. Check your history files and remove any passwords that you find and that shouldn’t be in there.

16. Backups

Yes, disaster recovery falls in the “information security” category. Backup your databases and automate the process. The backups are obvious, but how about the automation? You’ll find that you won’t be as inclined to forget to run an automated process, because it runs itself. This only goes so far, as you can’t fully automate exchanging tapes, but the further you automate the process, the more reliable it becomes. Oh, and don’t forget to test those backups!

17. Log everything

Enable logging for all connections and queries where you can. Logging all queries can be a real performance killer, so you have to make a trade-off. Yet, isn’t security always a matter of trade-offs. Log as much as you reasonably can and you’ll be thankful when someone does break in.

18. Database firewalls

Consider installing a database firewall to protect against SQL injection, but also against exploits against the database. Database firewall solutions are hard to find and often quite expensive, so you have to consider if this is worth it. You should definitely take a look.

Of course, another great way to boost security of your MySQL database is to employ a 24/7 monitoring service like Monitis. We operate whether your firewall is up or down, so there’s never any need to worry about downtime — at least on our part!

See also:

LAMP Security: 21 Tips for Apache

25 Linux Server Hardening Tips

PlanetMySQL Voting: Vote UP / Vote DOWN

We go over the open calls for papers, upcoming conferences, conversations with Oracle, and finish up our 2-part series on MySQL security.

Calls for papers:
Call for papers for Percona Live: MySQL Conference & Expo 2012 is open! They opened it on Friday, September 15th and the call will close on Monday, December 5th. The MySQL Conference & Expo is Tuesday April 10 - Thursday, April 12, 2012 in Santa Clara, CA.

To submit a paper, first register as a speaker at http://www.percona.com/live/mysql-conference-2012/user/register and then go to My Account -> Submit Proposal.

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

News/Feedback
Collaborate Call for papers is open until Sunday October 14th - http://events.ioug.org/p/cm/ld/fid=15
Collaborate is an Oracle Technology and Applications conference put on by IOUG, OAUG and Quest. It is the largest independent Oracle conference, and we are planning on having a one-day track there. Collaborate is at the Mandalay Bay Convention Center in Las Vegas, Nevada from Sunday April 22nd - Thursday 26th, 2012.

Call for papers for Percona Live: MySQL Conference & Expo 2012 is open! They opened it on Friday, September 15th and the call will close on Monday, December 5th. The MySQL Conference & Expo is Tuesday April 10 - Thursday, October 12, 2012 in Santa Clara, CA.

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

News/Feedback
Collaborate Call for papers is open until Sunday October 14th - http://events.ioug.org/p/cm/ld/fid=15
Collaborate is an Oracle Technology and Applications conference put on by IOUG, OAUG and Quest. It is the largest independent Oracle conference, and we are planning on having a one-day track there. Collaborate is at the Mandalay Bay Convention Center in Las Vegas, Nevada from Sunday April 22nd - Thursday 26th, 2012.

Call for papers for Percona Live: MySQL Conference & Expo 2012 is open! They opened it on Friday, September 15th and the call will close on Monday, December 5th. The MySQL Conference & Expo is Tuesday April 10 - Thursday, October 12, 2012 in Santa Clara, CA.

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

As per amorize.com MySQL.com was hacked and quote “infecting visitors with malware” .. true or false? …

More reading at krebsonsecurity.com too.

PlanetMySQL Voting: Vote UP / Vote DOWN

I'm looking forward to the upcoming Message Systems User Conference next month in San Francisco, not only for what looks like an excellent venue, but for the great set of quality sessions on the agenda.

There's a number of sessions I'm looking forward to attending, but I'd like to invite you to attend the sessions I'll be delivering next month (read to the end to save on conference admission):

What the Convergence of Data Security & Privacy Concerns Will Mean to Companies

The barrage of news stories about data breaches and privacy violations is taking a toll on consumer confidence.

What You'll Learn:

• Why data security and privacy issues are converging and how an erosion of consumer confidence can jeopardize data availability for communication and commerce.
• How security and privacy are connected to Message Convergence and why they should now be of concern to all ecosystem players and at all levels, Marketing as well as IT.
• What principles companies should embrace to address security and privacy in their own environments.
• How companies can safeguard their customer data and messaging streams.

New Directions in Email Deliverability

Our panel of industry experts will explore the ongoing evolution of deliverability management and new technology advances, such as adaptive delivery, that will make it easier.

What You'll Learn:

• How deliverability is a tactic companion to Message Convergence – getting messages delivered, read and acted on.
• How new advances in technology can improve deliverability management effectiveness and remove the hassles for all stakeholders.

Building Multi-Channel Apps

This session will introduce participants to the whys and wherefores of multi-channel messaging applications ­ how they deliver business value, and how to construct them. You¹ll gain both an understanding of the business strategy behind multi-channel apps, and a nuts-and-bolts working knowledge of the tools and techniques required to design, build and deploy them. Topics will include how to access multiple data sources on the fly and how to make routing determinations. For instance, once you¹ve made a judgment on content, context and preference, how to go about actually getting a message routed to its ultimate destination. We'll go in depth on the subject of multi-channel message type (MCMT), a proprietary content container format that makes it possible to inject messages into the delivery stream with content alternatives dependent on the preferred message channel.

Target Audience:

Product and program managers, developers, line of business owners.

What you'll learn:

• How multi-channel messaging delivers business value across any number of industry verticals.
• The messaging and data systems/architectures needed to deploy multi-channel messaging.
• Introduction to MCMT.
• How to configure Momentum, Mobile Momentum and Message Central for multi-channel apps.
• Understanding and acting on customer preference data.

Advanced Momentum & Message Scope

This session will extend the sessions on "Introduction to Lua" and "Momentum Essentials and Message Scope" by taking participants through advanced, Lua-based message parsing APIs. Advanced policy scripts for database-driven binding assignment and DKIM signing will be demonstrated. Participants will see practical, but advanced remediation list usage with Message Scope and learn how to create custom remediation actions.

Target Audience: System administrators, operations and support personnel and developers.

What You'll Learn:

• Various parsing techniques using Lua API functionality.
• Write Lua policy scripts that implement database-driven binding assignment and DKIM signing.
• How to integrate Momentum bounce information with an external database.
• How to integrate Message Scope with 3rd-party data feeds.
• How to create custom remediation actions with Message Scope.

It's going to be a great conference and I look forward to meeting everyone, to make it even more appealing, register now and use discount code VIP2S2 to save $250!

See you there!

PlanetMySQL Voting: Vote UP / Vote DOWN