5. CREATE MySQL user and GRANT PRIVILEGES
Login as root user using putty and, login to MySQL as a root user as well.
Syntax:
mysql> CREATE USER ‘<username>’@'<available_for_ip_address>’ IDENTIFIED BY ‘<password>’;
mysql> GRANT ALL PRIVILEGES ON ‘<db_name>’.'<table_name>’ TO ‘<username>’@'<available_for_ip_address>’ IDENTIFIED BY ‘<password>’;
Here “<available_for_ip_addresses>” can have value from ‘localhost’ to any qualified Public / Private IP address depending upon your requirement and if you want it to be available for all IPs just use “%” symbol.
Use a “*” symbol if you want to set the privileges for all the tables in a particular database.
User with GRANT ALL / full access:
mysql> CREATE USER ‘dbadmin’@’%’ IDENTIFIED BY ‘pwd@123’;
mysql> GRANT ALL PRIVILEGES ON db_mydb.* TO ‘dbadmin’@’%’ IDENTIFIED BY ‘pwd@123’;
mysql> FLUSH PRIVILEGES;
User with SELECT ONLY / limited access:
mysql> CREATE USER ‘dblimited’@’%’ IDENTIFIED BY ‘limit@123’;
mysql> GRANT SELECT ON db_mydb.* TO ”dblimited’@’%’ IDENTIFIED BY ‘limit@123’;
mysql> FLUSH PRIVILEGES;
When I started this post I thought I would be able to cover all the commands I have considered useful so far but I figured one post is not enough. I’ll surely come back with more commands in the next part, till then let me know if you have any query / confusion via comments.
* Every command shared is executed using MySQL installed on any Linux distribution and every Query shared is executed from MySQL Query Browser GUI Tool
© Kamalika Guha Roy
Leave a Reply