Friday, 17 May 2013

Create user in mysql


Create an admin user who can access anything from anywhere

mysql> grant all privileges on *.* to 'admin'@'%' identified by 'password';

Create an user who can access any database from a network colcation

mysql> grant all privileges on *.* to 'admin'@'192.168.%' identified by 'password';

*If somehow this doesn't work, execute this
update mysql.user set Host='192.168.%' where User='admin';

Create an user who can only access from localhost
mysql> grant all privileges on *.* to 'admin'@'localhost' identified by 'password';

Create an user who can access only one fix database
mysql> grant all privileges on dbname.* to 'admin'@'localhost' identified by 'password';

Here is the description of every word in the above command

grant all privileges - its granting permission(so it creates user also)
on dbname.* - its dbname and table name access restriction (*.* means all db, dbname.* means only one datase)
to 'admin'@'localhost' - its first quoted string is username, and 2nd quoted string is host access, who can connect to the mysql db, in the current only, only localhost host users would be allowed to connect
identified by 'password'; - its the password which is required to connect the mysql db server

Note : 
Here do not get confused with "bind-address" configuration in mysql configuraiton file, which actually provides binding access, click here to read more about "bind-address" and access point
Post a Comment