Mysql 8.X ๋ฒ„์ „ ๊ธฐ์ค€

DB ์ƒ์„ฑ

CREATE DATABASE {DB์ด๋ฆ„};

ex)

CREATE DATABASE TESTDB;

์‚ฌ์šฉ์ž ์ƒ์„ฑ

CREATE USER '{username}'@'localhost' IDENTIFIED BY '{password}';
CREATE USER '{username}'@'%' IDENTIFIED BY '{password}';
  • %๋Š” ์™ธ๋ถ€ ์ ‘์† ํ—ˆ์šฉ
  • localhost๋Š” local์—์„œ๋งŒ ์ ‘์† ํ—ˆ์šฉ

ex)

mysql> CREATE USER 'TESTUSER'@'localhost' IDENTIFIED BY '1234';

์‚ฌ์šฉ์ž ๊ถŒํ•œ ๋ถ€์—ฌ

GRANT ALL PRIVILEGES ON {database}.* TO '{username}'@'localhost';
FLUSH PRIVILEGES;

{database}์— ๋Œ€ํ•œ ๋ชจ๋“  ๊ถŒํ•œ์„ {username}์— ๋ถ€์—ฌํ•œ๋‹ค.

๊ถŒํ•œ ์ข…๋ฅ˜

๊ด€๋ฆฌ์ž๊ธ‰ ๊ถŒํ•œ์€ ์ค„ ์ผ์ด ๊ฑฐ์˜ ์—†์–ด์„œ ์ œ์™ธํ–ˆ๋‹ค. ๊ถŒํ•œ์€ ๋ชจ๋“  ๊ถŒํ•œ์„ ์ค„ ์ˆ˜๋„ ์žˆ์ง€๋งŒ, ์•„๋ž˜์™€ ๊ฐ™์€ ์„ธ๋ถ€ ๊ถŒํ•œ์œผ๋กœ๋„ ์„ค์ •ํ•  ์ˆ˜ ์žˆ๋‹ค.

๊ถŒํ•œ๋‚ด์šฉ
CREATE, ALTER, DROPํ…Œ์ด๋ธ” ์ƒ์„ฑ, ์ˆ˜์ •, ์‚ญ์ œ
SELECT, INSERT, UPDATE, DELETEํ…Œ์ด๋ธ”์˜ ๋ ˆ์ฝ”๋“œ ์กฐํšŒ, ์ž…๋ ฅ, ์ˆ˜์ •, ์‚ญ์ œ

ex)

  1. TESTDB์— ๋Œ€ํ•œ ๋ชจ๋“  ๊ถŒํ•œ์„ TESTUSER์— ๋ถ€์—ฌํ•œ๋‹ค.
GRANT ALL PRIVILEGES ON TESTDB.* TO 'TESTUSER'@'localhost';
FLUSH PRIVILEGES;

2.TESTUSER์—๊ฒŒ ๋ชจ๋“  ๊ถŒํ•œ์„ ๋ถ€์—ฌํ•œ๋‹ค.

GRANT ALL PRIVILEGES ON *.* TO 'TESTUSER'@'localhost';
FLUSH PRIVILEGES;
  1. TESTUSER์—๊ฒŒ TESTDB์— ๋Œ€ํ•œ ๋ ˆ์ฝ”๋“œ ์กฐํšŒ ๊ถŒํ•œ๋งŒ์„ ํ—ˆ์šฉํ•œ๋‹ค.
grant select on TESTDB.* to 'TESTUSER'@'localhost';
FLUSH PRIVILEGES;
  1. TESTDB์˜ MEMBER ํ…Œ์ด๋ธ”์— ๋Œ€ํ•œ ๋ชจ๋“  ๊ถŒํ•œ์„ TESTUSER์— ๋ถ€์—ฌํ•œ๋‹ค.
GRANT ALL PRIVILEGES ON TESTDB.MEMBER TO 'TESTUSER'@'localhost';
FLUSH PRIVILEGES;