[DB] SQL(DCL) / 원격 접속 도구(MySQL WorkBench)

*Rocky Linux 9.4와 MariaDB 기반으로 정리한 포스팅입니다. 

 

계정과 권한을 관리하자: DCL(Data Control Language)

DCL(Data Control Language)는 사용자 계정을 생성/삭제 및 계정에 부여된 권한을 설정하는 데 사용된다. 먼저 Mysql 데이터베이스에 들어간 후,  다음 쿼리문을 통해 사용자 계정 목록을 확인해 보자.

use mysql; 
SELECT user,host,password FROM mysql.user;

<출력 결과>

 

다른 계정을 생성하지 않았다면, 세 개의 빌트인 계정(시스템 관리 계정)들이 출력될 것이다. 빌트인 계정들은 기본적인 데이터베이스 관리 및 운영에 필수적인 역할을 하는 계정들이므로 함부로 삭제하면 안 된다. 이제 DCL 명령어들에 대해 알아보자. 

 

1) 계정 생성/삭제/변경

**MySQL에서는 계정을 계정 이름만으로 구분하지 않고, '계정명'@'호스트이름' 조합으로 구분한다. 

 

CREATE USER: 계정 생성

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 

username@host : 생성할 계정 
password : 계정의 비밀번호 


CREATE USER 'happy'@'localhost' IDENTIFIED BY '1234'; 
// localhost에서만 접속할 수 있고(원격으로는 접속 불가), 비밀번호는 '1234'인 happy라는 계정을 생성한다. 
// 같은 이름의 계정이어도 어디에서 접속 가능하냐에 따라 다른 계정으로 취급됨(동명의 게정이 있을 수 있음)
// SSH를 이용해서 접속하는 것도 localhost로 접근하는 것이다

 

DROP USER: 계정 삭제

DROP USER 'username'@'host'; 

EX) 
DROP USER 'happy'@'localhost';

 

ALTER USER: 비밀번호 

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password'; 

EX) 
ALTER USER 'happy'@'host' IDENTIFIED BY '1004';

** 최근에는 SET, UPDATE 대신 ALTER USER를 통해 비밀번호를 변경하는 것을 권장하고 있다. SET, UPDATE를 사용하는 경우, 비밀번호를 일반 데이터처럼 저장하게 되어 보안 상 취약해질 우려가 있다. ALTER USER를 사용하는 경우 암호화와 같은 해당 비밀번호와 관련된 추가 보안 조치를 자동으로 처리해 주기 때문에 보안 상 더 안전하고, 권장되는 것이다. 

 

 

2) 사용자 권한 관리

계정의 권한 확인

SHOW GRANTS FOR 'username'@'host' 

SHOW GRANTS; 
// 현재 로그인된 계정의 권한을 확인 가능(간접적으로 내가 어떤 계정에 접속해 있는지 확인도 가능하다)

 

**권한 종류와 관련해 정리해 놨으니, 필요하면 펼쳐서 확인해 보자!

데이터베이스 관련 권한

• ALL [PRIVILEGES]: 모든 권한을 포함합니다.
• CREATE: 데이터베이스를 생성할 수 있는 권한.
• DROP: 데이터베이스를 삭제할 수 있는 권한.
• GRANT OPTION: 다른 사용자에게 부여된 권한을 재부여할 수 있는 권한.

 

테이블 관련 권한

• SELECT: 테이블의 데이터를 조회할 수 있는 권한.
• INSERT: 테이블에 새로운 데이터를 추가할 수 있는 권한.
• UPDATE: 기존 데이터를 수정할 수 있는 권한.
• DELETE: 테이블에서 데이터를 삭제할 수 있는 권한.
• INDEX: 인덱스를 생성하거나 삭제할 수 있는 권한.
• ALTER: 테이블 구조를 변경할 수 있는 권한 (예: 열 추가/제거).
• CREATE: 새로운 테이블을 생성할 수 있는 권한.
• DROP: 테이블을 삭제할 수 있는 권한.

 

기타 권한

• EXECUTE: 저장 프로시저 및 함수를 실행할 수 있는 권한.
• FILE: 서버 파일 시스템에 파일을 읽고 쓸 수 있는 권한.
• TRIGGER: 테이블에 트리거를 생성할 수 있는 권한.
• SHOW VIEW: 뷰를 조회할 수 있는 권한.
• CREATE ROUTINE: 저장 프로시저 및 함수를 생성할 수 있는 권한.
• ALTER ROUTINE: 저장 프로시저 및 함수를 수정할 수 있는 권한.
• EVENT: 이벤트 스케줄러를 사용할 수 있는 권한.
• USAGE: 최소한의 권한. mariadb 서비스에 접속 가능. 기초 구문 실행 가능

 

GRANT : 권한 부여 

GRANT privilege ON database.table TO 'username'@'host'; 

privilege: 부여할 권한 (예: SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES) 
database.table: 권한을 부여할 데이터베이스와 테이블  
username@host : 권한이 부여될 계정

EX) 
GRANT ALL PRIVILEGES ON *.* TO 'king'@'localhost'; 
// 'king@localhost'에게 *.*(모든DB의 모든 테이블)에 대한 '모든 권한'을 부여한다.

GRANT SELECT, INSERT ON animalDB.* TO 'bear'@'localhost'; 
// bear 계정에게 특정 DB(animalDB)의 모든 테이블에 대해 SELECT, INSERT 권한을 부여한다.

 

REVOKE : 권한 해제

REVOKE privilege ON database.table FROM 'username'@'host' ; 

privilege: 해제할 권한 (예: SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES) 
database.table: 권한을 해제할 데이터베이스와 테이블  
username@host : 권한이 해제될 계정

EX)
REVOKE DELETE, UPDATE ON starmall_dbs.board_tbl FROM 'happy'@'localhost'; 
//  'happy'@'localhost' 계정으로부터 startmall_dbs DB의 board_tbl 테이블에 대한 UPDATE와 DELETE 권한을 해제한다.

 

GRANT를 이용해 사용자 계정 생성과 동시에 권한 부여하기

GRANT ALL PRIVILEGES ON database.* TO 'username'@'host' IDENTIFIED BY 'password';

EX) 
GRANT ALL PRIVILEGES ON starmall_dbs.board_tbl TO 'king'@'localhost' IDENTIFIED BY 'qwer1234'; 
//'king@localhost'에게 starmall_dbs DB의 모든 테이블에 대한 '모든 권한'을 부여한다. 
// 사용자가 없으면 새로 생성하고 권한까지 부여한다. 이 때 비밀번호는 'qwer1234'로 설정한다.

 

 

**원격 접속 도구

 MySQL(MariaDB)에서 가장 많이 사용되는 원격 접속 도구와 각 도구들의 특징은 다음과 같다. 

 

MySQL 명령어 

• CUI 방식의 기본 관리 도구, 사용자 생성이나 백업/복원 등의 관리 작업을 수행할 때 사용함.
• mysql -u username -p -h server
  • username : 접속할 계정명 
  • server : 접속할 MySQL 서버의 IP 주소

MySQL WorkBench 

• MySQL 전용 접속 프로그램이며, 설치해서 사용. 
• GUI 도구이므로 사용이 편하며, 데이터를 다루는 작업에 많이 사용함.

phpMyAdmin

• 웹 브라우저로 접속하는 방식으로, 접속 프로그램을 별도로 설치할 필요가 없어서 편리함.

 

MySQL WorkBench 사용 방법

1. MySQL Workbench를 설치한다(버전 및 OS 확인).

MySQL :: Download MySQL Workbench

MySQL :: Download MySQL Workbench

 

2. MySQL(MariaDB) 서버에 원격 접속용 계정을 만들어준다.

GRANT ALL ON duoDB.* TO 'duo_admin'@'%' IDNETIFIED BY '1234';

// localhost를 제외한 모든 주소에서 접속가능한 duo_admin 계정을 생성한다. 
// 비밀번호는 '1234' 이고 duoDB의 모든 테이블에 ALL PRIVILEGES 권한 부여됨; 
// '%' => 모든(모든 호스트에서 접근이 되지만 localhost에서만 접근 불가)

**CREATE USER로 사용자 계정을 만들어준 경우, 권한을 따로 부여해주지 않으면 접속이 되지 않는다.

 

3. MySQL WorkBench를 다음과 같이 설정해서 MySQL 서버에 접속해 준다.

• username :  접속할 계정명  ex) duo_admin
• hostname : MySQL 서버의 IP 주소
• Store in Vault : 클릭 후  비밀번호 입력
• Default Schema : 기본으로 참조할 스키마(데이터베이스)를 설정 - 선택사항

 

*만약 정상적으로 접속이 되지 않는다면, 포트(3306)가 열렸는지, 방화벽 설정, 계정 설정(권한 부여 및 호스트 설정) 등을 확인해 보도록 하자. 

 

 

 

힘들지만 오늘도  해낸 나를 위한 한 마디,

"우리가 지금보다 더 나아지려고 노력할 때, 우리 주변의 모든 것들이 지금보다 더 좋아질 것이다", 파울로 코옐료

 

"권태를 이겨내고 다시 한번 배움의 즐거움을 찾아보자"