Wednesday, June 1, 2011

How to create a new user in mysql

Creating a new super user

The command is :
mysql> GRANT ALL PRIVILEGES ON *.* TO 'randeep'@'%' IDENTIFIED BY 'randeep' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;
+---------+----------------+------------------+
| user | host | password |
+---------+----------------+------------------+
| root | localhost | 27c30f0241a5b69f |
| root | mysql.lap.work | 27c30f0241a5b69f |
| root | 127.0.0.1 | 27c30f0241a5b69f |
| randeep | % | 0d9b9da57a2d7649 | >> new user (% means all hosts)
+---------+----------------+------------------+
4 rows in set (0.00 sec)
mysql>


Creating a user without grant option :

mysql> grant all privileges on *.* to 'abc'@'localhost' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;
+---------+----------------+------------------+
| user | host | password |
+---------+----------------+------------------+
| root | localhost | 27c30f0241a5b69f |
| root | mysql.lap.work | 27c30f0241a5b69f |
| root | 127.0.0.1 | 27c30f0241a5b69f |
| abc | localhost | 4b5698aa4603595b | >> newly added user.
| randeep | % | 0d9b9da57a2d7649 |
+---------+----------------+------------------+
5 rows in set (0.01 sec)
mysql>

How to secure anonymous user in mysql

There are two ways for securing the anonymous user.

1. Set a good password for anonymous user.
2. Disable it.

Setting password for anonymous user :

mysql> set password for ''@localhost=password('password');
Query OK, 0 rows affected (0.00 sec)

OR

mysql> set password for ''@FQDN=password('password');
Query OK, 0 rows affected (0.00 sec)
mysql>


2. Deleting the anonymous user.

Before:
mysql> select user,host,password from user;
+------+----------------+------------------+
| user | host | password |
+------+----------------+------------------+
| root | localhost | 27c30f0241a5b69f |
| root | mysql.lap.work | 27c30f0241a5b69f |
| root | 127.0.0.1 | 27c30f0241a5b69f |
| | localhost | 27c30f0241a5b69f | ->anonymous user
| | mysql.lap.work | 27c30f0241a5b69f | ->anonymous user
+------+----------------+------------------+
5 rows in set (0.00 sec)

Deleting..
mysql> delete from mysql.user where user = '';
Query OK, 2 rows affected (0.00 sec)

After:
mysql> select user,host,password from user;
+------+----------------+------------------+
| user | host | password |
+------+----------------+------------------+
| root | localhost | 27c30f0241a5b69f |
| root | mysql.lap.work | 27c30f0241a5b69f |
| root | 127.0.0.1 | 27c30f0241a5b69f |
+------+----------------+------------------+
3 rows in set (0.00 sec)
mysql>

Dont forget to flush privileges after deleting/modifying users or resetting passwords.

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>

How to set password for root user in mysql database

This explains how to set password for root user from command line interface.

Login to mysql as root user without password after installing mysql
[root@mysql ~]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Changing the root password for the first time
[root@mysql ~]# mysqladmin password abc123

Loging as before without password
[root@mysql ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Loging with new password
[root@mysql ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Changing the current password to another
[root@mysql ~]# mysqladmin -u root -pabc123 password redhat

Dont forget to flush privileges after deleting/modifying users or resetting passwords.

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>