Differences

This shows you the differences between two versions of the page.

Link to this comparison view

tech:mysql:creating_mysql_users_for_a_database [2014/11/19 06:37] (current)
Line 1: Line 1:
 +====== Creating MySQL Database & Users for the database ======
 +
 +Chosen names are as follows:
 +^ Database | p08_xxyyzz |
 +^ User | up08xxyyzz |
 +^ Password | 4PJmbVu6SdeySyW |
 +
 +===== Database Creation =====
 +<code sql>
 +DROP DATABASE p08_xxyyzz;
 +CREATE DATABASE p08_xxyyzz;
 +</​code>​
 +
 +===== User Creation =====
 +
 +==== With Access from localhost ====
 +<code sql>
 +DROP USER '​up08xxyyzz'​@'​localhost';​
 +
 +CREATE USER '​up08xxyyzz'​@'​localhost'​ IDENTIFIED BY '​4PJmbVu6SdeySyW';​
 +
 +GRANT USAGE ON * . * TO '​up08xxyyzz'​@'​localhost'​ IDENTIFIED BY '​4PJmbVu6SdeySyW' ​
 +   WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
 +   ​MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
 +
 +GRANT ALL PRIVILEGES ON `p08_xxyyzz` . * TO '​up08xxyyzz'​@'​localhost'​ WITH GRANT OPTION ;
 +</​code>​
 +
 +==== With Access from Anywhere ====
 +<code sql>
 +DROP USER '​up08xxyyzz'​@'​%';​
 +
 +CREATE USER '​up08xxyyzz'​@'​%'​ IDENTIFIED BY '​4PJmbVu6SdeySyW';​
 +
 +GRANT USAGE ON * . * TO '​up08xxyyzz'​@'​%'​ IDENTIFIED BY '​4PJmbVu6SdeySyW' ​
 +   WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
 +   ​MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
 +
 +GRANT ALL PRIVILEGES ON `p08_xxyyzz` . * TO '​up08xxyyzz'​@'​%'​ WITH GRANT OPTION ;
 +
 +</​code>​
 +
 +====== Creating root user ======
 +This is to create a '​secondary'​ root user with access from a different host (or all hosts). Login as mysql root user:
 +
 +<code sql>
 +select Host,​User,​Grant_priv,​Super_priv from user order by User, Host;
 +
 +CREATE USER '​root'​@'​%'​ IDENTIFIED BY '​somemypassword';​
 +
 +GRANT ALL ON *.* TO '​root'​@'​%'​ with GRANT OPTION;
 +
 +FLUSH PRIVILEGES;
 +</​code>​
 +
 +To drop:
 +<code sql>
 +drop user '​root'​@'​%';​
 +</​code>​
 +
 +Test login
 +<code bash>
 +mysql -hremotehost -uroot -psomemypassword mysql
 +</​code>​
  

QR Code
QR Code tech:mysql:creating_mysql_users_for_a_database (generated for current page)