MySQL Administrator Command

These are some MySQL administrator commands-

Create User

These are the syntax for creating new user and granting permissions-

CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'password';

Grant Permission

Here is the syntax for granting permissions-

GRANT USAGE ON 'database_name'.* to 'user_name'@'host_name' IDENTIFIED BY 'password';

Here is the syntax for granting all permissions-

GRANT ALL PRIVILEGES ON * to 'user_name'@'host_name' IDENTIFIED BY 'password';

Here is the syntax for granting specific permissions-

GRANT SELECT,INSERT,UPDATE,CREATE TEMPORARY TABLES ON * to 'user_name'@'host_name' IDENTIFIED BY 'password';

Grant Permission to Specific IPs

Here is the syntax for granting specific permissions to specific IPs, suppose the IP starts with '110.11.2' -

GRANT SELECT,INSERT,UPDATE,CREATE TEMPORARY TABLES ON * to 'user_name'@'110.11.2.%' IDENTIFIED BY 'password';




Show User Privileges

Here is the syntax to show user privileges-

SHOW GRANTS FOR 'user_name'@'host_name';




MySQL Administrator Exercise

Write a mysql statement to create a new user and set password and privileges for an existing database.

Solution

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';




Related MySQL Exercises

MySQL concatenate
MySQL Get Current Date, User, Version
MySQL Get Maximum Value
MySQL where clause
MySQL order by
MySQL get difference between two dates
MySQL Pattern Matching
MySQL Join
MySQL Regular Expression
MySQL delete duplicate row
MySQL update multiple rows
Get nth highest salary using MySQL
Display the nth row from MySQL
Commit and rollback in mysql
MySQL SELECT top 5
MySQL display the alternate rows




Read more articles


General Knowledge



Learn Popular Language