Pythian Group

A frequent inquiry concerning databases’ security is to retrieve the database role(s) associated with each user for auditing or troubleshooting purposes.

Each database user (principal) can be retrieved from sys.database_principals and the associated database roles can be retrieved from sys.database_role_members

The following code runs against ALL the databases using SP_MSForeachdb and all roles for one principal is concatenated in one row Read the rest of this entry . . .

Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).

The answers to the last pop quiz are up: http://www.pythian.com/blogs/868/pop-quiz-mysql-cluster

So here’s another pop quiz. Given the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16450949 to server version: 4.1.14-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*),length(password) from mysql.user group by length(password);
+----------+------------------+
| count(*) | length(password) |
+----------+------------------+
|       49 |               16 |
|       31 |               41 |
+----------+------------------+
2 rows in set (0.00 sec)

mysql> select password('foo');
+-------------------------------------------+
| password('foo')                           |
+-------------------------------------------+
| *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like "old%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Since the server has old_passwords set to OFF, you may think that you can delete all the entries in the mysql.user table whose passwords have a lenth of 16. So you do this for security’s sake, and then flush privileges, and none of your applications can connect to the server any more. You scratch your head, wondering how on earth those could even be used, because wouldn’t you get a “Client does not support authentication protocol” error if the old passwords were being used?

So, what is the answer to this question?

I was asked this question recently, and I thought it was a great little tidbit of knowledge to pass along. The short answer is “no”. The slightly longer answer was written up by Jan Kneschke when dealing with a forum post about proxy + connection pooling.

From http://forums.mysql.com/read.php?146,169265,169700

The clear-text password is _never_ transfered in the authentication phase.

On the network we have:
* client connects to server (no data)
* server sends a seed (40 char, one-time, random)
* client sends 40 char hash of (seed + PASSWORD(clear-text-password))
* server compares against the hash(seed + SELECT password FROM mysql.user WHERE username = )

That way we never have the password as clear-text on the wire. (only in SET PASSWORD or GRANT statements).

I was recently asked a question by someone who had attended my Shmoocon talk entitled “Why are Databases So Hard to Secure?”. PDF slides are available (1.34 Mb). I was going to put this into a more formal structure, but the conversational nature works really well. I would love to see comments reflecting others’ thoughts.

I found several things of interest in your talk about database security and several new things to think about.

In particular I realized that DBMSs have at least two hats in the world of software architecture namely as technical services (“smart file system”) and as application framework. Perhaps that “depth” is one of the reasons why dbms is hard to secure? For example, considering just the question of who or what have user roles within a DBMS deployment. From the “deep” point of view, the “user” could be an application, or a module, or just the next layer up in the architecture stack. From the “shallow” point of view, the “user” should be an actual person whose UI actions are touching the database. I think someone in the audience was advocating that, and perhaps you were too, namely using the DBMS’s permissions and roles system to authorize actual users. But that would be different from common (even careful) practice, wouldn’t it?

I meant to grab you and re-ask my question though, namely “is all this complexity caused by the inherent difficulty of securing DBMS, or is it the _cause_ of the difficulty”? So I’ll re-ask now…

Best regards

And my response: Read the rest of this entry . . .