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).

Disclaimer: In most countries, looking at user passwords is illegal. Never try what is written below on a system that somebody other than you can access.

Oracle Grid Control documentation warns against leaving the emkey in the Grid Control repository. It says here:

After the emkey has been copied, you must remove it from the Management Repository as it is not considered secure. If it is not removed, data such as database passwords, server passwords and other sensitive information can be easily decrypted.

You may wonder: how easily?

A Bit of Background

When you deal with management tools, you want to collect metrics and run various tasks on different targets. Unless you evolve in an very advanced security infrastructure such as a PKI or another “real” Single Sign-On solution, it’s likely that you’ll need a username/password to connect to a remote server, a remote database, or a remote application server. Because Grid Control enables you to automate a great number of tasks, it has to be able to connect without prompting the users for credentials. In order to do that, it has to know the real passwords because it will itself authenticate on the targets. Because there no magic in there, it has to store these informations in its repository!

As a consequence, if the password you type to connect to the Grid Control is stored in a HASH form only, the one you store in Grid Control to run a task, collect data, or simply avoid typing it when you drill down to a target, has to be reversible. But I guess it’s the same for all management solution.

Let’s Be More Specific

Oracle Management Service 10.2 uses several ways to protect these sensitive data, including Virtual Private Database and Password Encryption.

• To overcome the first one, you have to be able to connect to the database as the SYS user.
• To overcome the second one, you have to know the encrypted password form, the key, and the associated algorithm.

Obviously, the key used to cipher the password is the emkey. It is located in $OMS_HOME/sysman/config/emkey.ora by default, and it can be generated/configured with "emctl xxx emkey". So the next question is, “Where are stored the ciphered passwords?”.

(more…)

I do realize that for most of you, there may be nothing new about the dbms_sys_sql package – knowledge of it has been floating around for quite a while. I myself discovered this package a couple of years ago while playing around with HTMLDB’s (now APEX’s) internals. I’m posting this as a response to a vox populi demand: I often see people who could benefit from dbms_sys_sql, if they only knew about it.

Sometimes you need to execute SQL (or PL/SQL) as some other user. Say you need to remove a job residing in some other user’s schema. Or you need to create a database link in a different schema. You can’t do things like these without becoming the other user. If you don’t know the other user’s password then you basically have two choices: (more…)