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>