User access to database on mysql

Database creation

• Create a database named wordpress

  mysql> create database wordpress;

User creation

• Create a user name wprdmin who can login from remote host only

  mysql> create user 'wpadmin'@'%' identified by 'XXXX';

• % symbols that user interconnect can connect remotely from any server. If you want to specify user can connect from particular host then use host name instead of %.

  mysql > create user 'wpadmin'@'webserver1.example.com' identified by 'XXXX';

• Above query creates an user named wpadmin which can connect only from host webserver1.example.com

Granting user access to database

• Allow grant select,insert,update,delete,create,drop,index,alter,references operation to user wpadmin on database wordpress from any remote host

   mysql> grant select,insert,update,delete,create,drop,index,alter,references on wordpress.* to 'wpadmin'@'%';

• Above query allows user wpadmin select,insert,update,delete,create,drop,index,alter,references operation on wordpress database remotely
• flush privilege to bring changes into effect

  mysql> flush privileges;

List user,host and password of all users on system

• List user,host and password of all users on system

  mysql > select user,host,password from mysql.user;

List privilege granted to particular mysql user

• List privilege granted to user wpadmin

  mysql > show grants for 'wpadmin'@'%';