Creating a User Without a Password in MySQL

I'm not sure why you would want to do this but you can create a MySQL user without a password:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9972

Server version: 5.0.67 Source distribution

 

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

 

mysql> create user barney;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select user, host, password

    -> from mysql.user

    -> where user = 'barney';

+--------+------+----------+

| user   | host | password |

+--------+------+----------+

| barney | %    |          |

+--------+------+----------+

1 row in set (0.00 sec)

 

mysql> exit

Bye

UNIX >

 

A user without a password can logon without using –p:

 

UNIX > mysql -u barney

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9981

Server version: 5.0.67 Source distribution

 

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

 

mysql> exit

Bye

UNIX >

 

And if he does include a –p, he can just press Enter when prompted to supply a password:

 

UNIX > mysql -u barney -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9982

Server version: 5.0.67 Source distribution

 

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

 

mysql> exit

Bye

UNIX >

 

Dropping a user without a password is carried out in the usual way:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10016

Server version: 5.0.67 Source distribution

 

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

 

mysql> drop user barney;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

Granting Access to a MySQL Database

In this example, I had a go at granting access to a database. First, I created a database and a user:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8718

Server version: 5.0.67 Source distribution

 

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

 

mysql> create database fred;

Query OK, 1 row affected (0.00 sec)

 

mysql> create user 'john'@'localhost'

    -> identified by 'smith';

Query OK, 0 rows affected (0.00 sec)

 

mysql> exit

Bye

UNIX >

 

Then I logged in as the new user and tried to use the new database:

 

UNIX > mysql -u john -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8721

Server version: 5.0.67 Source distribution

 

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

 

mysql> use fred

ERROR 1044 (42000): Access denied for user 'john'@'localhost' to database 'fred'

mysql> quit

Bye

UNIX >

 

That failed and the reason for failure was fairly obvious so I decided to see what I could do about it:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8734

Server version: 5.0.67 Source distribution

 

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

 

mysql> grant all on fred.* to john;

Query OK, 0 rows affected (0.28 sec)

 

mysql> exit

Bye

UNIX >

 

Next, I logged back in as the new user and tried to access the database again. It seemed to work OK. N.B. The grant all command even allows the user to drop the database:

 

UNIX > mysql -u john -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8735

Server version: 5.0.67 Source distribution

 

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

 

mysql> use fred;

Database changed

mysql> drop database fred;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

 

Finally I dropped the new user:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8761

Server version: 5.0.67 Source distribution

 

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

 

mysql> drop user john;

Query OK, 0 rows affected (0.00 sec)

 

mysql>