GRANT command is used for creating users and giving privilege to the user for accessing database.
The syntax for the grant command:
GRANT PRIVILEGES ON DATABASE OBJECTS TO USER@HOST IDENTIFIED BY ‘PASSWORD’;
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:
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.