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

Three ways to recover a root user password:

The order of solutions here under gets more creative on the way down :)

1. obviously, before starting messing around check my.cnf or scripts for passwords entries, then try home directories for password files
2. secondly – can you restart mysql? if yes, restart with –skip-grant-tables, log into mysql, change your password and restart without –skip-grant-tables
3. third option – (on linux / unix ONLY)
If you haven’t found the password anywhere and can’t afford to restart your mysql.

cd data/mysql
cp -rp user.MYD bck_user.MYD_`date +%Y%m%d`
cp -rp user.MYD /tmp/user.MYD
vi /tmp/user.MYD #(edit the hashed passwords next to root*)
cp -rp /tmp/user.MYD user.MYD
sudo kill -HUP `pidof mysqld`

Note that the latter method of recovering a root password CAN be easily used maliciously leaving no trace! The only way to avoid such an attack is to make the data directory ONLY readable and writable by the user used to start/stop mysql (don’t user *nix root user to own mysql since that opens another can of worms … it’s a whole other blog post).

PlanetMySQL Voting: Vote UP / Vote DOWN

Around these days last year I presented `securich` for the first time. It was at froscon 2009, barely knowing anybody, spending my 27th birthday in a hostel in Germany fixing some bugs before the actual presentation on a 10 inch netbook (my mac had some problems at the time but thats another story :)). I got a beating, verbally of course! Many of the people listening to the presentation were expecting something else since another presentation was supposed to be running at that time, some even started dozing off (encouraging? not really hehe) but after a few minutes people started getting interested and asking all kind of questions. “This awesome” I thought to myself, “questions are good, it means people are understanding and want to know more”, but the more they learnt the more they realised how young securich was as a tool, lacking fundamental features like reverse reconciling securich with mysql rather than the other way round, bugs cropping up (Giuseppe aka datacharmer made sure he found some on the fly :P) but oh well back then it was just four months old.

Now after a year and four months into it, Securich is still in Beta but MANY MANY more features were included, bugs fixed (others created) and it even made it through two sessions at the Oreilly MySQL Conference and Expo 2010 and two mysql university sessions.

The end points here are:
1. Share your knowledge and code with the community, we learn, you learn!
2. When you fall to the ground, climb back up and get on your feet again. Start running!
3. Securich is now at version 0.3.0 (10th official release), help yourself at google code your feedback is greatly appreciated!

Most of the new release is bug fixes but some of the features added in this version are:
1. Any user can now install securich not just mysql root
2. You can now grant privileges on the mysql db (by default still disabled as it runs in “strict” mode which needs to be changed to “lenient” manually)
3. The installation script now keeps the securich package intact for further many installations
4. Better error reporting (for debugging purposes)
5. Grants on tables using regexp are now case sensitive

PlanetMySQL Voting: Vote UP / Vote DOWN

This Thursday (February 25th, 13:00 UTC - way earlier than usual!), Darren Cassar will present Securich - Security Plugin for MySQL. According to Darren, the author of the plugin, Securich is an incredibly handy and versatile tool for managing user privileges on MySQL through the use of roles. It basically makes granting and revoking rights a piece of cake, not to mention added security it provides through password expiry and password history, the customization level it permits, the fact that it runs on any MySQL 5.0 or later and it's easily deployable on any official MySQL binary, platform independent.
More information here: http://www.securich.com/about.html.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.)

MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone. All sessions (slides & audio) are recorded; the links will be on the respective MySQL University session pages which are listed on the MySQL University home page.

Here's the tentative list of upcoming sessions:

• March 4: MySQL Column Databases (Robin Schumacher)
• March 11: Improving MySQL Full-Text Search (Kristofer Pettersson)

By the way, did I mention that we need more speakers to fill up the 2010 schedule? If you'd like to be a speaker, have a look at this blog article!

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL has an unusual grants system that allows a user to be specified by host, ip or network address. That is you identify a user as ’some_user’@'host.host.name’, ’some_user’@'1.2.3.4′ or ’some_user’@'10.3.%’.

That is quite a nice facility but using it is rather tricky. This potentially provides a lot more security as it allows you to specify that different types of database users can only perform certain actions from different types of hosts. So even if you know the user and password you may have trouble getting into a mysqld server. That’s good.

However, this flexibility comes at a price. There are no tools to help you manage this and I have often seen people resorting to using the simplest type of grant, for some_user@’%', or some_user@’10.%’.

I recently wrote a small template script which would allow me to configure similar grants for the same user but in different networks in a way which made this process more manageable. This is only a partial solution to the problem but does make it possible to configure in a reasonably simple fashion grants for a user with access coming from one of several locations.

An Example

An example might make this clearer.

A simple grant for a web user connection from webserver1 might be.

GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';

If we want to duplicate this for 3 web servers we need 2 more sets of grants:

GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';

GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';

If this is for a larger number of locations the grants become unmanageable (for the human to follow easily).

So my script allows me to do:

-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'

-- Grants for %WEB_USER%

GRANT USAGE TO %WEB_USER% IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO %WEB_USER%;

Adding more “web servers” just requires adding a single extra line.

With a simple case like this where there are only 2 grant lines for each user this is not so complicated, but if we had 20 or 30 grants for each user then this simplifies things a huge amount.

The script can be found here and generates the following output:

$ create_grants_from_template grants.template.sql

-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'

-- Grants for 'web'@'webserver1.example.com'

GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';

-- Grants for 'web'@'webserver2.example.com'

GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';

-- Grants for 'web'@'webserver3.example.com'

GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';

$

This output can be piped directly into the MySQL command line.

Further Improvements

While this might be helpful, it does not really solve the problem of managing grants, especially if you have a large number of database users and are managing quite a few database servers. I’ve been thinking about what I would like to do to solve this problem and while the ideas are not yet fully set think that something like the /etc/sudoers file is a good base to work from.

That is use a file which defines various things:

• database users and their passwords
• database servers and database server groups
• individual grants and groups of grants

and combines this so that a script can create the specific rights for a specific database server, the results being different from one server to another.

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL has an unusual grants system that allows a user to be specified by host, ip or network address. That is you identify a user as ’some_user’@'host.host.name’, ’some_user’@'1.2.3.4′ or ’some_user’@'10.3.%’.

That is quite a nice facility but using it is rather tricky. This potentially provides a lot more security as it allows you to specify that different types of database users can only perform certain actions from different types of hosts. So even if you know the user and password you may have trouble getting into a mysqld server. That’s good.

However, this flexibility comes at a price. There are no tools to help you manage this and I have often seen people resorting to using the simplest type of grant, for some_user@’%', or some_user@’10.%’.

I recently wrote a small template script which would allow me to configure similar grants for the same user but in different networks in a way which made this process more manageable. This is only a partial solution to the problem but does make it possible to configure in a reasonably simple fashion grants for a user with access coming from one of several locations.

An Example

An example might make this clearer.

A simple grant for a web user connection from webserver1 might be.

GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';

If we want to duplicate this for 3 web servers we need 2 more sets of grants:

GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';

GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';

If this is for a larger number of locations the grants become unmanageable (for the human to follow easily).

So my script allows me to do:

-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'

-- Grants for %WEB_USER%

GRANT USAGE TO %WEB_USER% IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO %WEB_USER%;

Adding more “web servers” just requires adding a single extra line.

With a simple case like this where there are only 2 grant lines for each user this is not so complicated, but if we had 20 or 30 grants for each user then this simplifies things a huge amount.

The script can be found here and generates the following output:

$ create_grants_from_template grants.template.sql

-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'

-- Grants for 'web'@'webserver1.example.com'

GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';

-- Grants for 'web'@'webserver2.example.com'

GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';

-- Grants for 'web'@'webserver3.example.com'

GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';

$

This output can be piped directly into the MySQL command line.

Further Improvements

While this might be helpful, it does not really solve the problem of managing grants, especially if you have a large number of database users and are managing quite a few database servers. I’ve been thinking about what I would like to do to solve this problem and while the ideas are not yet fully set think that something like the /etc/sudoers file is a good base to work from.

That is use a file which defines various things:

• database users and their passwords
• database servers and database server groups
• individual grants and groups of grants

and combines this so that a script can create the specific rights for a specific database server, the results being different from one server to another.

PlanetMySQL Voting: Vote UP / Vote DOWN