[SQL] 사용자 추가

GRANT에 대해 공부를 하기 전에 일단 SQL문 종류에 대해 한 번 정리해보겠다. 그렇기에 DDL, DML, DCL에 대해서 알아보자

DDL (Data Definition Language, 데이터 정의어)

데이터베이스를 정의하는 언어로, 데이터의 전체 골격을 정하는 것이라 보면 된다. 데이터베이스, 테이블 등 생성하는 역할을 한다.

이 외에도 SCHEMA, DOMIAN, TABLE, VIEW, INDEX를 정의 및 수정 삭제할 때도 사용된다. 그렇기에 누구나 건들게 하면 아주 큰일날 수도 있기 때문에 DBA나 DB를 관리할 수 있는 사용자 외에는 DDL은 금하도록 해야 한다.

종류	설명
CREATE	데이터베이스, 테이블 등 생성
ALTER	테이블 수정
DROP	데이터베이스, 테이블  등 삭제
RENAME	테이블 이름 변경
TRUCATE	테이블 초기화

DML(Data Manipulation Language, 데이터 조작어)

정의된 데이터베이스를 바탕으로 입력된 레코드를 조회, 수정, 삭제하는 등의 역할을 하는 언어.

데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용된다. 즉, 데이터베이스 사용자와 시스템 간 상호작용하는데 사용된다 보면 된다.

종류	설명
SELECT	데이터 조회
INSERT	데이터 삽입
UPDATE	데이터 수정
DELETE	데이터 삭제

DELETE? DROP? TRUNCATE?
보았을 땐 삭제나 초기화나 비슷한 역할이지 않을까 싶은데, 차이는 다음과 같다.
DELETE 명령어는 데이터는 지워지지만 테이블 용량은 줄어 들지 않고, 원하는 데이터만 지울 수 있다. 이는 잘못 삭제한 것도 되돌릴 수 있다.
TRUNCATE 명령어는 용량이 줄어 들고, 인덱스 등도 모두 삭제 된다. 테이블은 삭제하지는 않고, 데이터만 삭제한다. 한꺼번에 다 지워야 한다. 삭제 후 절대 되돌릴 수 없다.
DROP 명령어는 테이블 전체를 삭제, 공간, 객체를 삭제한다. 삭제 후 절대 되돌릴 수 없다.

DCL(Data Control Language, 데이터 제어어)

데이터를 제어하는 언어이다. 데이터의 보안, 무결성, 회복 병행 수행제어 등을 정의하는데 사용한다.

 

유저 설정

권한 이전에 유저 추가하는 방법은 다음과 같다.

먼저 아래의 명령어를 활용하면 해당 유저의 권한을 볼 수 있다.

SHOW GRANTS FOR "유저명";

해당 유저는 RDS에서 만든 마스터 유저라 다음과 같이 full 권한이 들어가 있는 것을 볼 수 있다.

또한 다음과 같은 명령어를 통해 디비의 유저들을 조회할 수 있다.

SELECT * FROM mysql.user;

유저 생성

유저를 한 번 만들어 보자.

CREATE USER 'seokju'@'localhost' IDENTIFIED BY '1234';

- seokju라는 유저를 만들었고, 비밀번호는 1234, localhost에서만 접근 가능하다.

docker 밖에서는 접근이 안 된다

docker에서는 접근이 잘 된다

유저 삭제

삭제를 해보고, 어디서든 접근할 수 있게 만들어보자

DROP USER 'seokju'@'localhost';

# 존재하는 경우에만 삭제는 다음과 같이 IF문을 걸어준다.
DROP USER IF EXISTS 'seokju'@'%';

위의 명령어로 삭제하고,

CREATE USER 'seokju'@'%' IDENTIFIED BY '1234';

호스트 부분을 @로 바꿔서 밖에서도 접속을 시도해보자.

docker 밖에서도 잘 된다!

이름 변경

사용자의 이름은 다음과 같이 변경할 수 있다.

RENAME USER 'seokju'@'%' TO 'sjh'@'%';

비밀번호 변경

비밀번호 변경도 할 수 있다!

SET PASSWORD FOR 'sjh'@'%' = PASSWORD ('12345');

 

권한 (GRANT, REVOKE)

GRANT

root 유저로 접속했을 때와 위에서 만든 유저로 접속했을 때 db 조회를 한 법 봐보자.

root

seokju는 볼 수 없다..

SHOW GRANTS FOR 'seokju'를 봐보자.

해당 뜻은 지금 유저는 있는데, 아무런 권한이 없어서 그런 것이다. USAGE의 의미가 글로벌 권한은 'N'으로 되어있는 것이라 한다. 이계정에 특정 권한을 나중에 승인할 것이라는 가정을 한다.

뭘 할 수가 없다 ㅠㅠ

한 번 내 계정에 hsge 디비에 대해서 전권을 줘보자.

GRANT ALL PRIVILEGES ON hsge.* TO 'seokju'@'%';

hsge 디비에 권한이 생긴게 보인다.

GRANT ALL PRIVILEGES ON hsge.pet_img TO 'seokju'@'%';

위와 같이 특정 테이블에 대해서만 권한을 줄 수 있다.

다른 테이블을 조회해보면 건들 수 없다.

이번엔 hsge DB 모든 테이블에 select, update, delete만 되게 권한을 줘보자

GRANT select, update, delete ON hsge.* TO 'seokju'@'%';

테이블 조회도 되고,

수정도 되고

삭제도 된다.

삽입은 안 되는 것을 볼 수 있다.

REVOKE

이번엔 권한을 뺏어보자

REVOKE ALL on hsge.* FROM 'seokju'@'%';

다시 못 봄 ㅠㅠ

권한 뺏어도 한 번 use hsge 한 것은 연결 끊기기 전까지 사용가능한 거 같다.

특정 권한만 빼는 것은 다음과 같다.

REVOKE select ON hsge.* FROM 'seokju'@'%';

 

ddl-auto=create인 환경에서 command denied 됨을 볼 수 있다.

DB도 정말 공부할게 많음을 느끼며, 실습했던 코드는 다음과 같다.

CREATE USER foo@test IDENTIFIED BY 'password';

SELECT * FROM mysql.user;

CREATE USER 'seokju'@'172.17.0.1' IDENTIFIED BY '1234';

DROP USER 'seokju'@'localhost';

CREATE USER 'seokju'@'%' IDENTIFIED BY '1234';

DROP USER 'seokju'@'127.0.0.1', 'seokju'@'172.17.0.1';

DROP USER IF EXISTS 'seokju';

DROP USER IF EXISTS 'seokju'@'%';

# 사용자 이름 변경
RENAME USER 'seokju'@'%' TO 'sjh'@'%';

SET PASSWORD FOR 'sjh'@'%' = PASSWORD ('12345');

CREATE OR REPLACE USER 'sjh'@'%' IDENTIFIED BY '1234';

RENAME USER 'sjh'@'%' TO 'seokju'@'%';

# 권한 부여하기
SHOW GRANTS FOR 'root';

SHOW GRANTS FOR 'seokju';

# 모든 권한 다 줘보자
GRANT ALL PRIVILEGES ON hsge.* TO 'seokju'@'%';

GRANT ALL PRIVILEGES ON hsge.pet_img TO 'seokju'@'%';

GRANT select, update, delete, insert ON hsge.* TO 'seokju'@'%';

# 권한 뺏어버리기~
REVOKE ALL on hsge.* FROM 'seokju'@'%';

REVOKE select ON hsge.* FROM 'seokju'@'%';

---

https://www.codingfactory.net/11336

MariaDB / 사용자 만들기, 수정하기, 삭제하기, 권한 부여하기, 제거하기

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=blash100&logNo=70041229042 

[MySql]사용자 추가하기 (계정 추가하기)