[MySQL] µ¥ÀÌÅͺ£À̽º »ý¼ºÇÏ°í »ç¿ëÀÚ¿¡°Ô ±ÇÇÑ ºÎ¿©Çϱâ
µ¥ÀÌÅͺ£À̽º »ý¼ºÇÏ°í »ç¿ëÀÚ¿¡°Ô ±ÇÇÑ ºÎ¿©Çϱâ
µ¥ÀÌÅͺ£À̽º Ãß°¡
1 | mysql> CREATE DATABASE testdb; |
testdb¶ó´Â À̸§ÀÇ database¸¦ Ãß°¡ÇÑ´Ù.
»ç¿ëÀÚ »ý¼º
1 | mysql> CREATE USER 'testuser' @ 'localhost' IDENTIFIED BY 'xxxxxx' ; |
testuser¶ó´Â À̸§ÀÇ user¸¦ Ãß°¡ÇÑ´Ù. (ºñ¹Ð¹øÈ£´Â xxxxxx)
»ç¿ëÀÚ ±ÇÇÑ ºÎ¿©
1 2 | mysql> GRANT ALL PRIVILEGES on testdb.* TO 'testuser' @ 'localhost' ; mysql>FLUSH PRIVILEGES ; |
testuser¿¡°Ô testdbÀÇ ¸ðµç Å×ÀÌºí¿¡ ´ëÇÑ ¸ðµç ±ÇÇÑÀ» ºÎ¿©ÇÑ´Ù.
FLUSH PRIVILEGES ¸í·É¾îµµ ÀØÁö¸»°í ½ÇÇàÇØ¾ß ÇÑ´Ù.
ÀÌÁ¦ testuser´Â testdbÀÇ INSERT, DELETE µî ¸ðµç¸í·É¾î¸¦ ½ÇÇàÇÒ ¼ö ÀְԵȴÙ.
¸¸¾à ¸ðµç ±ÇÇÑÀÌ ¾Æ´Ï¶ó ÀϺΠ±ÇÇѸ¸ ºÎ¿©ÇÏ·Á¸é ALL PRIVILEGES ´ë½Å ¸í·É¾î¸¦ ³ª¿ÇÏ¸é µÈ´Ù.
1 | mysql> GRANT INSERT , UPDATE , INDEX , ALTER ON on testdb.* TO 'testuser' @ 'localhost' ; |
»ç¿ëÀÚ Á¶È¸
»ç¿ëÀÚ Á¤º¸´Â user Å×ÀÌºí¿¡ ÀúÀåµÈ´Ù.
user Å×À̺íÀº mysql µ¥ÀÌÅͺ£À̽º¿¡ Á¸ÀçÇϹǷΠuse mysql¹®À» ¸ÕÀú ½ÇÇàÇؾßÇÑ´Ù.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql>use mysql; mysql> SELECT host, user FROM user ; + -----------------------+----------+ | host | user | + -----------------------+----------+ | 127.0.0.1 | root | | ::1 | root | | localhost | | | localhost | testuser | | localhost | root | | localhost.localdomain | | | localhost.localdomain | root | + -----------------------+----------+ 7 rows in set (0.00 sec) |
»ç¿ëÀÚ ±ÇÇÑ Á¶È¸
testuserÀÇ ±ÇÇÑÀ» Á¶È¸ÇÑ´Ù.
1 2 3 4 5 6 7 8 | mysql>SHOW GRANTS FOR 'testuser' @ 'localhost' + ---------------------------------------------------------------------------+ | Grants for testuser@localhost | + ---------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'testuser' @ 'localhost' IDENTIFIED BY PASSWORD '...' | | GRANT ALL PRIVILEGES ON `testdb`.* TO 'testuser' @ 'localhost' | + ---------------------------------------------------------------------------+ 2 rows in set (0.00 sec) |
Âü°í - µ¥ÀÌÅͺ£À̽º¿Í »ç¿ëÀÚ »èÁ¦ ¹× ±ÇÇÑ Á¦°Å
database »èÁ¦
1 | mysql> DROP DATABASE testdb; |
testdb µ¥ÀÌÅͺ£À̽º¸¦ »èÁ¦ÇÑ´Ù.
»ç¿ëÀÚ »èÁ¦
1 | mysql> DROP USER 'testuser' @ 'localhost' ; |
testuser »ç¿ëÀÚ¸¦ »èÁ¦ÇÑ´Ù.
¸ðµç ±ÇÇÑ Á¦°Å
1 | REVOKE ALL ON testdb.* FROM 'testuser' @ 'localhost' ; |
testdb¿¡ ´ëÇÑ testuserÀÇ ¸ðµç ±ÇÇÑÀ» Á¦°ÅÇÑ´Ù.
ƯÁ¤ ¸í·É¾î¿¡ ´ëÇÑ ±ÇÇÑÀ» Á¦°Å
1 | REVOKE DROP ON testdb.* FROM 'testuser' @ 'localhost' ; |
testdb¿¡ ´ëÇÑ testuserÀÇ DROP ±ÇÇÑÀ» Á¦°ÅÇÑ´Ù.