Differences
This shows you the differences between two versions of the page.
|
tech:mysql:creating_mysql_users_for_a_database [2014/11/19 06:37] |
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> | ||
