Knowledge Walls
John Peter
Pune, Maharashtra, India
How to GRANT privilege in MYSQL? in Computer software engineer articles of One day One Thing to Know
1333 Views
To check users and their priviliage's 
SELECT `host`,`user`,`Grant_priv` FROM mysql.user;



If Grand_priv is 'Y' then the user can grand priviliages to other users. Otherwise 'N' means not.

Grant Priviliage's 
GRANT ALL ON `db_name`.`table_name` TO 'user_name'@'host_name' IDENTIFIED BY 'password';

* db_name
       - to put which database can access by the user. If putting * means all db's can accessible by the user

* table_name
       - to which table can access by the user. otherwise * means all tables in the db can accessible.

* user_name
       - which user going to get this permissions

* host_name
       - Which host is accessible by the user.

Examples
GRANT ALL ON *.* TO `root`@`%` IDENTIFIED BY 'pass123';
GRANT ALL ON *.* TO `root`@`localhost` IDENTIFIED BY 'pass123';
GRANT ALL ON `user_management`.* TO `root`@`%` IDENTIFIED BY 'pass123';
GRANT ALL ON `user_management`.`user_info` TO `root`@`%` IDENTIFIED BY 'pass123';

Show Granted Priviliage's 
SHOW GRANTS FOR `user_name`@`host_name`;

Examples
SHOW GRANTS FOR `root`@`localhost`;
SHOW GRANTS FOR `root`@`%`;

Drop/Revoke Grant Priviliage's in MYSQL 
REVOKE ALL ON *.* FROM `root`@`%` IDENTIFIED BY 'pass123';
REVOKE ALL ON *.* FROM `root`@`localhost` IDENTIFIED BY 'pass123';
REVOKE ALL ON `user_management`.* FROM `root`@`%` IDENTIFIED BY 'pass123';
REVOKE ALL ON `user_management`.`user_info` FROM `root`@`%` IDENTIFIED BY 'pass123';
Best Lessons of "One day One Thing to Know"
Top lessons which are viewed more times.
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details