Grant command is used for this purpose:
GRANT PRIVILEGES ON DATABASE OBJECTS TO USER@HOST IDENTIFIED BY ‘PASSWORD’;
For example:
GRANT ALL PRIVILEGES ON *.* TO ‘lamp@localhost’ IDENTIFIED BY ‘secret’;
It will create the user lamp that can access anything locally. To change to the lamp user at MySQL command prompt, type:
exit
Then start MySQL from the command line with the new username and password. The syntax for specifying the username and password when starting MySQL is:
mysql –h hostname –u username –p password
If you don’t want users to access tables other than their own, replace* with the name of the user’s database, like this:
GRANT ALL PRIVILEGES ON ‘store’.* TO ‘lamp@localhost’ IDENTIFIED BY ‘secret’;
You need to run the above line as root or as someone with permission. In the above given code, here ‘store’ correlates to the name of database to which the privileges are assigned.
