MySQL Administration

In MySQL database, only the administrator has permission to create new database and new user for the MySQL Server.

Create new user, set password and privileges

Suppose, there is an existing database name 'Company'. To create a new user and grant access to newly created user, enter this query -

mysql->GRANT USAGE ON Company.* to champ@localhost IDENTIFIED BY 'efw232@d';

This creates new user 'champ' if it doesn't already exist, and sets the password to 'efw232@d'. This statement also permits champ user access to all tables within Company database.

Grant statement privileges to user

The above statement only allows user to log into database. To create, insert new data, the administrator has to grant permission for this.

mysql->GRANT CREATE, INSERT, SELECT, UPDATE 
	 ->ON Company.*
	 ->to champ@localhost IDENTIFIED BY 'efw232@d';

The above statement grants only permission to CREATE, INSERT, SELECT, UPDATE data. To grant all possible permission to user, enter this statement -

mysql->GRANT ALL
	 ->ON Company.*
	 ->to champ@localhost IDENTIFIED BY 'efw232@d';

Change MySQL user password

The administrator can change the user password by using SET password statement. Suppose, Administrator has to change champ@localhost user account, the SET password statement is -

SET PASSWORD FOR 'champ'@'localhost' = PASSWORD('fh2398@1');

The above statement is for MYSQL version prior to 5.7.6, from version 5.7.6 mysql depreciated the above statement or removed the PASSWORD(). The statement for MySQL 5.7.6+ is as follows -

SET PASSWORD FOR 'champ'@'localhost' = fh2398@1;