Понеделник VI 02, 2014

Building MySQL with boost on windows

As you've probably heard already MySQL needs boost to build.

However, in the good ol' MySQL tradition, the above link does give you only the instructions on how to build it on linux. And completely ignores the fact that there're other OSes too that people develop on.

To fill in that gap, I've compiled a small step by step guide on how to do it on windows. Note that I always, as a principle, build out-of-source.

The typical setup I have is :

bzr clone lp:~mysql/mysql-server/5.7 mysql-trunk
cd mysql-trunk
mkdir bld
cd bld
cmake -DWITH_DEBUG=1 -DMYSQL_PROJECT_NAME=mysql-trunk ..
devenv /build debug mysql-trunk.sln

This has been tested to work on a 32 bit compile using VS2013 on a Windows7 64 bit build. Note that you'll need other things too (bison, eventually openssl etc) that I will assume you already have set up.


  1. Download Boost 1.55.0. It's the *only* version that is known to work currently.
  2. Extract boost_1_55_0/ from the zip to c:\boost\boost_1_55_0
  3. Go to Control Panel/System/Environment variables and set WITH_BOOST=C:\boost\boost_1_55_0 in User variables. Make sure you restart your open command line terminal windows after this ! 
  4. If you're upgrading from non-boost build, remove your bld/ directory and create a new one.
  5. run cmake as you'd typically do. You should get:
    -- Local boost dir C:/boost/boost_1_55_0
    -- Local boost zip LOCAL_BOOST_ZIP-NOTFOUND
    -- BOOST_INCLUDE_DIR C:/boost/boost_1_55_0
  6. Build as normal (devenv /build debug ...). It should work.

Четвъртък III 27, 2014

I'm speaking about MySQL tools on BGOUG's spring conference

I will be speaking about MySQL at the Bulgarian Oracle Users Group's Spring Conference.

In furthering my mission to increase the MySQL knowledge among my countrymen I will try to give the grand tour of all the modern MySQL administrative and development tools that the MySQL team provides.

This will be the 3d BGOUG conference I'm speaking to. And I expect no less than the stellar organization and the great audience that I had the privilege to interact with during the previous 2 conferences.

We had a lot of fun too ! Parties from the last 2 conferences are legendary !

So if you happen to be in Bulgaria at that time and like talking about MySQL Sandanski is the place to be on 13-15 Jun !

See you there !

Вторник VIII 13, 2013

Speaking about security and 5.6 on MySQL Connect 13

I will be presenting on both MySQL Connect 2013 and on Oracle OpenWorld 2013. So if you happen to be attending the conferences please come and see what's new in MySQL 5.6 security and even play with it during the hands on lab I'll be doing.

I will be delivering a MySQL Connect talk : 

Session ID: CON1888
Session Title: MySQL 5.6 and Security: What’s New
Venue / Room: Hilton - Taylor
Date and Time: 9/22/13, 10:00 - 11:00

And a HandsOn Lab :  

Session ID: HOL9735
Session Title: MySQL Security Best Practices
Venue / Room: Hilton - Franciscan A/B
Date and Time: 9/22/13, 14:30 - 15:30

And my Oracle OpenWorld talk is :

Session ID: CON2255
Session Title: Quick Dive into MySQL
No Date/Time atm 

Петък IX 16, 2011

Centralized Login Management Comes to MySQL: Introducing PAM and Windows Authentication Support

You probably remember the world of new possibilities introduced to MySQL 5.5. If you do, you probably would agree that no API is useful by itself.

This is why you need authentication plugins that fit the largest possible number of authentication setups.

I can only guess what these authentication solutions look like for your server. But chances are that your OS has a pretty good idea on how best to authenticate users in it. This is why we've decided to hook MySQL to the two most widely used OS authentication APIs : Pluggable Authentication Modules (a.k.a PAM) and the Windows Security Support Provider Interface

Let's see first how PAM works.

We'll first consider the simple scenario where each OS user holds a corresponding user account in MySQL's mysql.user table. Sounds pretty easy to setup ? Right : you just need to remember to create a MySQL user for each of the people that have access to the computer and grant them some access.

There's one inconvenience though : you'll need to maintain the correspondence between the MySQL user and the OS user. Ask the user to update his mysql password in addition to his OS password, remember to disable access in both mysql and the OS etc.

This is where the PAM authentication plugin in its simplest form can help you: if properly configured it will ask the OS if this user can login each time a user tries to login to the MySQL server. All you need to do is create your MySQL users in such a way that they use the PAM plugin to login.
Note that externally authenticated MySQL users don't need a password stored into the mysql.user table, as they are relying on the PAM library to check the login credentials. Good ! One less password to worry about and maintain.

So how do I set things up ?
First I need to check if I have the PAM library set up on my box. Usually PAM keeps its configuration in /etc/pam.d. Looking in my macbook's /etc/pam.d I see a number of configuration paths

mbpro:~$ ls -la /etc/pam.d/
total 0
drwxr-xr-x   17 root  wheel   578 Aug 29 20:46 ./
drwxr-xr-x  103 root  wheel  3502 Sep 12 19:22 ../
-rw-r--r--    1 root  wheel   264 Aug 29 20:42 authorization
-rw-r--r--    1 root  wheel   174 Aug 29 20:46 checkpw
-r--r--r--    1 root  wheel   200 Aug 29 20:46 chkpasswd
-r--r--r--    1 root  wheel   203 Aug 29 20:46 cups
-rw-r--r--    1 root  wheel   204 Aug 29 20:46 ftpd
-r--r--r--    1 root  wheel   587 Aug 29 20:46 login
-r--r--r--    1 root  wheel   152 Aug 29 20:46 login.term
-r--r--r--    1 root  wheel   191 Aug 29 20:42 other
-r--r--r--    1 root  wheel   197 Aug 29 20:46 passwd
-r--r--r--    1 root  wheel   211 Aug 29 20:46 rshd
-rw-r--r--    1 root  wheel   408 Aug 29 20:46 screensaver
-rw-r--r--    1 root  wheel   140 Aug 29 20:46 smbd
-r--r--r--    1 root  wheel   602 Aug 29 20:42 sshd
-r--r--r--    1 root  wheel   356 Aug 29 20:46 su
-r--r--r--    1 root  wheel   203 Aug 29 20:46 sudo
Good, looks like the PAM library is already configured and under heavy use. Now all I need is to pick the right PAM path for my MySQL server. login sounds good. Let's see what's in it :
mbpro:~$ cat /etc/pam.d/login
# login: auth account password session
auth       optional       pam_krb5.so use_kcminit
auth       optional       pam_ntlm.so try_first_pass
auth       optional       pam_mount.so try_first_pass
auth       sufficient     pam_serialnumber.so try_first_pass serverinstall
auth       required       pam_opendirectory.so try_first_pass
account    required       pam_nologin.so
account    required       pam_opendirectory.so
password   required       pam_opendirectory.so
session    required       pam_launchd.so
session    required       pam_uwtmp.so
session    optional       pam_mount.so
I will not go into the details of all this, but will just highlight pam_opendirectory.so. This means that this PAM config will use Apple's OpenDirectory to verify logins. Nice ! But there's a number of bookkeeping modules that I don't really need for MySQL : e.g. pam_launchd.so.

I need something simpler. Hmm, let's try some of the existing daemons. Let's check cups.

$ cat /etc/pam.d/cups 
# cups: auth account password session
auth       required       pam_opendirectory.so
account    required       pam_permit.so
password   required       pam_deny.so
session    required       pam_permit.so

Good ! pam_opendirectory.so still there, there's auth and it's really short and sweet. This should do just fine. I could have created my own pam configuration file. And if I call it mysql the PAM plugin will pick it up without me needing to specify an authentication string. But I'll stick to what I have already.

Let's start the MySQL server and make sure the PAM plugin is available and installed.

mysql> select plugin_status from information_schema.plugins where plugin_name='authentication_pam';
select plugin_status from information_schema.plugins where plugin_name='authentication_pam'

| plugin_status |
| ACTIVE        |
1 row in set (0.04 sec)

Now I can experiment. I need to create a MySQL user for my gkodinov OS account

mysql> create user gkodinov identified with 'authentication_pam' as 'cups';
create user gkodinov identified with 'authentication_pam' as 'cups'

Query OK, 0 rows affected (0.00 sec)

One can never check too much, so let's see how is the user defined in the mysql.user table

mysql> select Password,plugin from mysql.user where user = 'gkodinov';
select Password,plugin from mysql.user where user = 'gkodinov'

| Password | plugin             |
|          | authentication_pam |
1 row in set (0.00 sec)

Goody ! No password to maintain and worry about.

I can now try logging in. Note that I'm not supplying my password as an argument on the command line : it's insecure. It's better to enter it on the prompt.

 ../client/mysql -u gkodinov -p test
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.16-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(),current_user();
| user()             | current_user() |
| gkodinov@localhost | gkodinov@%     |
1 row in set (0.00 sec)

Wow ! I'm in. Let's see what happens if I mistype my password.

 ../client/mysql -u gkodinov -p test
Enter password: 
ERROR 1045 (28000): Access denied for user 'gkodinov'@'localhost' (using password: YES)

Nothing unexpected. But there's nothing in the logs either (depending of course on you logging setup. On Linux you'd get a note in the secure syslog saying that the login failed). This can be quite annoying, specially if you're setting up PAM. So what can I do to diagnose PAM related problems ? I can start the server in a special debugging mode by setting the AUTHENTICATION_PAM_LOG environment variable to 1 before starting my server. Use it with caution : it prints a lot of data on the server's standard error. Thus it's not suitable for production use. You better set up your PAM modules or your external credentials storage to log authentication errors.

Among other things the debug log prints is the PAM error returned by the pam_authenticate() function. So for my failed attempt above I'd get:

auth_pam_server:password haha received
auth_pam_server:pam_authenticate rc=9
auth_pam_server: rc=9
PAM error: authentication error


The above debugging log exposes one more possible problem that users should be aware of : When using PAM authentication the passwords are transferred from the client to the server in clear text. This may be dangerous if you're doing it over a network link that others can listen to. Unfortunately this is about the only way PAM can work : the library requires the password in clear text. So plan carefully and use SSL encryption of the client connection where needed !

Now that we seen how the PAM plugin can be used in simple scenarios it's time to think about more complex ones.

If you have more than a handful of users creating all these MySQL accounts for each such user can quickly become a problem. Each time you e.g. add somebody to your corporate directory you'll need to go to each and every MySQL server instance and add it as an externally authenticated MySQL user. And what's worse : grant him the privileges that he needs.

Luckily in such environments large groups of users share the same set of privileges : e.g. all DBAs have access to all the data in a MySQL server instance, all accountants have access to the tables holding the accounting data etc. While designing your application you have a pretty good idea what these typical privilege sets are. And you probably even have "template" users of different kinds : e.g. "accountant" with all the rights necessary to do his job. Or "dba" with a larger set of privileges etc. And those are pretty static user definitions. They change only when your application logic changes.

How cool would it be to only have these template users defined in each MySQL server and somehow map incoming logins to them ? And keep all logins and the mapping to the template users definable at OS level ? Of course the MySQL DBAs still need to be able to control the mapping. But other than this it's a good idea to let the OS user definitions be the only place where you need to add a new user account when you e.g. hire somebody.

The PAM plugin can answer this call. But since the PAM library API is so basic the PAM plugin needs to do the user mapping somehow otherwise.

In Unix the most obvious way of handling groups of users is through OS groups. Each user can belong to several such groups. E.g. on my Mac I have:

$ groups
staff com.apple.sharepoint.group.2 com.apple.access_screensharing everyone 
_appstore localaccounts _appserverusr admin _appserveradm _lpadmin _lpoperator 
_developer com.apple.sharepoint.group.1
This means my user id belongs to all the above groups. The information about the group membership is available to programs through the group information functions of the C standard library.

We have two questions to answer:

  • How can we make the standard C library group information functions talk to an external directory (e.g. LDAP or OpenDirectory) ?
  • How can we use OS groups to map OS logins to MySQL "template" user accounts that have the rights granted to them ?

The answer on the group mapping functions question is pretty standard. E.g. On linux you'll need to tweak your /etc/nsswitch.conf to allow groups to be stored and retrieved from a remote repository. In this way standard C library function like e.g. getgrnam() can connect to a remote repository and retrieve group and user information from it.

On my Mac I don't actually need to do anything : the C library functions just talk to OpenDirectory. And I can use the standard Mac admin tools to assign groups to my users.

This is why I will assume that the group mapping C standard library functions work in conjunction with the user verification PAM profile used and will concentrate on doing the mapping of OS groups to MySQL template users using the PAM plugin.

The second question has an easy answer too : the PAM plugin supports proxy users. So we can map the incoming logins using a default proxy user. To achieve this we use the full PAM authentication string syntax :

<pam service name>,<os_group_name>=<mysql user name>, 
    <os_group_name>=<mysql user name>, ...

I assume my MySQL application has 2 "template" users : reader and writer. I will be mapping these to the everyone and admin OS groups. I do this by thoughtfully creating my MySQL default proxy user (as root) :

mysql> create user reader identified with 'authentication_pam' as 'cups';
create user reader identified with 'authentication_pam' as 'cups'

Query OK, 0 rows affected (0.00 sec)

mysql> create user writer identified with 'authentication_pam' as 'cups';
create user writer identified with 'authentication_pam' as 'cups'

Query OK, 0 rows affected (0.00 sec)

mysql> create user ''@'' identified with 'authentication_pam' 
   as 'cups,admin=writer,everyone=reader';
create user ''@'' identified with 'authentication_pam' 
  as 'cups,admin=writer,everyone=reader'

Query OK, 0 rows affected (0.00 sec)

mysql> grant proxy on reader to ''@'';
grant proxy on reader to ''@''

Query OK, 0 rows affected (0.01 sec)

mysql> grant proxy on writer to ''@'';
grant proxy on writer to ''@''

Query OK, 0 rows affected (0.00 sec)
Now when I login with my user account (gkodinov) I get :
mysql> select user(), current_user(), @@proxy_user;
| user()             | current_user() | @@proxy_user |
| gkodinov@localhost | writer@%       | ''@''        |
1 row in set (0.00 sec)
What does this mean ? I've logged in as gkodinov@localhost (as returned by user()). My effective user id (who's privileges set will I be enjoying during my session) is 'writer@%' (as returned by current_user()). And I was able to achieve this using the ''@'' proxy user. Note that the order in the authentication string is important : I've purposely specified the writer mapping before reader's. I did this because I assume that if a user is a member of both OS groups (as gkodinov is) I prefer it to map to the writer group.

Note also that I've defined my reader and writer users as authenticated through PAM. This is to cover "direct" login attempts for login ids reader and writer. In this case the initial MySQL user lookup will find the more specific names (reader and writer) instead of the catch-all user. But this will not matter, as both the template MySQL users and my proxy user are defined as authenticated using the PAM plugin, so the same PAM plugin will be used, this time without a proxy user.

As usual you can find more about the PAM plugin in its excellent online documentation.

Now let's see how Windows Authentication Plugin works

The plugin name is authentication_windows. Unlike the PAM plugin it is not forced to use the clear text password. The Windows API allows programs to use single sign on. Once the user is authenticated with the Windows OS any program can take a "ticket" and verify that this ticket was produced by a specific OS user. As a result the Windows authentication plugin doesn't even require the client to send a password. At connect time it will securely find out what OS user was used to run the MySQL client. In fact you don't even need a user name as well. If you don't supply a user name some clients will use a predefined user name ("auth_windows") and let the server windows authentication plugin to retrieve the OS user name and do the mapping to an SQL user based on it. As a result you also don't need to touch on ''@'' as you do with PAM.

You can find more detailed example on using the Windows authentication plugin in its documentation (in the Using the Windows Authentication Plugin section).

Понеделник IX 12, 2011

I'm speaking about pluggable authentication at Oracle Open World

If you're attending the Oracle Open World 11, come hear me talking about pluggable authentication and developments around it (and if you're not attending here's one more good reason why you should :) ).

The current what/when/where for my talk are :

Session ID19181
Session TitleMySQL Authentication Options
Venue / RoomMarriott Marquis - Golden Gate B
Date and Time10/3/11, 11:00

There's also a MySQL community reception were you'll find most of the mysql experts attending Oracle OpenWorld. MySQL meetings are fun :)

And if you miss this, there's always the MySQL demo booth @ the exhibition floor that will be packed with MySQL experts.

Понеделник I 03, 2011

MySQL 5.5 brings in new ways to authenticate users

Ever wanted to use your server's OS for authenticating MySQL users ? Or the corporate LDAP repository ?
Unfortunately options like the above are plentiful nowadays. And providing hard-coded support for protocol X or service Y is not the best possible idea.
MySQL 5.5 has taken the step into the right direction by providing an infrastructure allowing one to make the server understand different authentication protocols by creating a set of simple plugins (one for the client and one for the server).
So now you can easily extend MySQL to search for and authenticate users in your favorite user directory.
In fact the API supplied is so versatile that we took the possibility to re-design the current "native" authentication mechanism into a built-in always-on plugin !
OK, let me give you an example:
Imagine we have a bunch of users defined in your OS, e.g. we have a user joro with his respective password. And we have a MySQL instance running on the same computer.
It would not be unexpected to need to let joro access and/or modify MySQL data.
The first step is to define him as a MySQL user. And there's a problem right there : MySQL's
CREATE USER joro@localhost IDENTIFIED BY 'joros_password'
statement needs a password. And this is a password in no way related to the password that joro have set up in the OS. What's worse : if joro changes his OS password this will in no way be reflected in MySQL. So he'll need to change his MySQL password in a separate step. Not very convenient, specially when you have a lot of users.
This is a laborious setup for joro's DBA as well : he'll have to disable his access in both MySQL and the OS should he decides that joro's out of the "nice" list.
Now mysql 5.5 to the rescue:
Imagine that the smart DBA has created a MySQL server plugin that will check if the name of the user logging in is a valid and enabled OS name and if the password supplied to the mysql client matches the OS and has called this plugin 'auth_os'.
Now all that's left to do is to define joro as a MySQL user that will be authenticated externally.
This is done by the following command :
CREATE USER 'joro'@'localhost' IDENTIFIED WITH 'auth_os';
Now joro can login to MySQL using his current OS password.
Note : joro is still a valid MySQL user, so you can grant privileges to him just like you would for all other users. What's better: you can have users that authenticate using different mechanisms in the same server. So you can e.g. safely experiment with external authentication for selected users while keeping your current user base operational.
What happens under the hood when joro logs in ?
The server will find out by the user definition that it needs to use a non-default authentication and will ask the client to "switch" to using the appropriate client-side plugin (if of course the client is not already using it). If the client can't do this (e.g. because it's an old client or doesn't have the necessary plugin available) the server will reject the login. Otherwise the server will let the server-side plugin decide (while possibly talking to the client side plugin and the OS user directory) if this is a valid login or not.
If it is the login process will continue as usual, while if it's not the login will get rejected.
There's a lot more that MySQL 5.5 can do for you than just the simple case above. Stay tuned for more advanced use cases like mapping groups of external users to a single MySQL user (so you won't have to have 1-to-1 mapping between your external user directory and your mysql user repository) or ways to control the process as a DBA.
Or you can simply skip ahead and read the relevant topics from MySQL's excellent online documentation. Or take a look at the example plugins in plugin/auth. Or take a look at the test suite in mysql-test/t/plugin_auth.test.

Writing about the interesting features and oddities of the MySQL server and related code.


« Март 2015