MySQL) 권한부여- GRANT

계속 오류나서 컨테이너 삭제하고 시도합니다.

 

C:\Users\W>docker ps -a
CONTAINER ID   IMAGE       COMMAND                  CREATED             STATUS             PORTS                                                            NAMES
e424c77dc540   mysql:5.7   "docker-entrypoint.s…"   About an hour ago   Up About an hour   3306/tcp, 33060/tcp, 0.0.0.0:3307->3307/tcp, :::3307->3307/tcp   mysql2
f668fc6c47e6   mysql       "docker-entrypoint.s…"   24 hours ago        Up 2 hours         0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp             mysql1

C:\Users\W>docker stop mysql1
mysql1

C:\Users\W>docker stop mysql2
mysql2

C:\Users\W>docker rm mysql1
mysql1

C:\Users\W>docker rm mysql2
mysql2

C:\Users\W>docker ps -a
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES

C:\Users\W>docker images
REPOSITORY   TAG       IMAGE ID       CREATED       SIZE
mysql        5.7       87eca374c0ed   3 weeks ago   447MB
mysql        latest    0627ec6901db   3 weeks ago   556MB

C:\Users\W>docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1234 --name mysql1 mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
a8526aa51bb7e616c35914f0d4aba854a1446483be4ef2f8771ea80e5ccff1ce

C:\Users\W>docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
a8526aa51bb7   mysql:5.7   "docker-entrypoint.s…"   9 seconds ago   Up 7 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql1

---

Workbench에서 실행.

show databases;
use mysql;

GRANT ALL ON *.* TO user1 identified by "1234";

---

C:\Users\W>docker exec -it mysql1 bash
root@a8526aa51bb7:/# mysql -u user1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

---

만들 유저

user1 : 모든권한

user2 : 보기 및 삭제권한

user3 : 보기 및 생성권한

user4 : 보기권한

 

유저생성방법1 : CREATE USER user1 IDENTIFIED BY '1234'; #유저만 만들기

유저생성방법2 : GRANT ALL ON *.* TO USER1 ........................ #유저를 만듬과 동시에 권한부여

---

유저생성방법1

# CREATE USER 사용자ID; : 사용자 추가
# CREATE USER userid@localhost identified by 'password'; : 사용자를 추가하며 비밀번호 설정
# create user {username}@{ip} identified by '{password}';
# '%'의 의미 : 외부에서의 접근 허용

CREATE USER 'user1'@'%' IDENTIFIED BY '1234';
FLUSH PRIVILEGES;

CREATE USER 'user2'@'%' IDENTIFIED BY '1234';
FLUSH PRIVILEGES;

CREATE USER 'user3'@'%' IDENTIFIED BY '1234';
FLUSH PRIVILEGES;

CREATE USER 'user4'@'%' IDENTIFIED BY '1234';
FLUSH PRIVILEGES;

권한부여

#grant {권한} privileges on {스키마}.{테이블} to {username}@{ip};

GRANT ALL PRIVILEGES ON mydb2.* TO 'user1'@'%';

---

유저생성방법2

유저생성하며 비밀번호 설정. 어떤 권한을 부여함.

#user1 : 모든권한
#user2 : 보기 및 삭제권한
#user3 : 보기 및 입력권한
#user4 : 보기권한

GRANT ALL ON *.* TO user1 identified by "1234";
GRANT SELECT,DELETE ON *.* TO user2 identified by "1234";
GRANT SELECT,UPDATE ON *.* TO user3 identified by "1234";
GRANT SELECT ON *.* TO user4 identified by "1234";

---

 

 

 

 

---

 

 

 

 

 

 

user root : db생성 및 table생성

CREATE DATABASE studydb;

USE studydb;

 

CREATE TABLE dept(

no INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20)

);

 

INSERT INTO dept(name) VALUES('A');

INSERT INTO dept(name) VALUES('B');

INSERT INTO dept(name) VALUES('C');

 

CREATE TABLE employee(

no INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20),

dept_no INT

#,FOREIGN KEY (employee) REFERENCES dept(no)

);

 

INSERT INTO employee(name, dept_no) VALUES('ga', 2);

INSERT INTO employee(name, dept_no) VALUES('na', 3);

INSERT INTO employee(name, dept_no) VALUES('da', 3);

INSERT INTO employee(name, dept_no) VALUES('la', 2);

INSERT INTO employee(name, dept_no) VALUES('ma', 2);

---

user1 : 모든권한 테스트

mysql> exit
Bye
root@a8526aa51bb7:/# mysql -u user1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studydb            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use studydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_studydb |
+-------------------+
| dept              |
| employee          |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from dept;
+----+------+
| no | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)

mysql> drop table dept;
Query OK, 0 rows affected (0.20 sec)

mysql> shoe tables;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'shoe tables' at line 1
mysql> CREATE TABLE dept(
    ->
    -> no INT PRIMARY KEY AUTO_INCREMENT,
    ->
    -> name VARCHAR(20)
    ->
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql>
mysql> INSERT INTO dept(name) VALUES('A');
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> INSERT INTO dept(name) VALUES('B');
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> INSERT INTO dept(name) VALUES('C');
Query OK, 1 row affected (0.06 sec)

mysql> show tables;
+-------------------+
| Tables_in_studydb |
+-------------------+
| dept              |
| employee          |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from dept;
+----+------+
| no | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)