DDL을 통한 데이터 베이스 계정 생성하기, db 원격 접속 (create user)

mysql, mariadb에서 데이터 베이스 계정을 생성해 보자.

아래의 쿼리문을 통해 계정을 생성할 수 있다. 

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

 

'username' : 계정 명

'host' : 해당 계정이 데이터베이스에 연결할 수 있는 호스트 지정 

 

'host'를 설정하는 방법에는 여러 방식이 있다.

localhost

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; 라면, username 계정은 로컬 머신에서 데이터베이스 서버에 연결할 때 사용가능하다. 일반적으로 데이터베이스와 같은 서버에서 실행되는 애플리케이션에 사용된다.

 

와일드카드 '%'

CREATE USER 'username'@'%' IDENTIFIED BY 'password'; 일 경우, '%'는 와일드카드로 어떤 호스트에서든 연결이 가능하다. 해당 설정은 db에 원격 접속 시 사용될 수 있지만, 적절히 관리되지 않는다면 보안상 위험할 수 있다.

 

특정 ip 주소, 서브넷 마스크 

CREATE USER 'username'@'192.168.1.%' IDENTIFIED BY 'password'; 의 경우, 특정 서브넷 내의 모든 IP 주소에서 연결을 허용할 수 있다. 특정  네트워크 내의 범위에 있는 ip 주소로부터의 연결을 허용하는데 유용하다. 

 

호스트 이름

CREATE USER 'username'@'myhost.example.com' IDENTIFIED BY 'password'; 는 사용자가 특정 도메인 이름을 가진 장치에서 연결할 수 있도록 할 수 있다. MySQL(MariaDB) 서버는 사용자가 연결할 때 호스트 이름을 ip 주소로 변환한다.

 

 

계정 생성하기
우분투 db 서버에서 create user ‘testuser3’@‘localhost’ identified by ‘password’; 로 계정을 하나 생성해 주었다.

(계정이름 : testuser3, host: localhost에서만(즉, ubuntu db server 내에서만 가능), 비밀번호는 'password'로 설정한 상황이다.)

계정 생성 이후 변경 사항을 데이터베이스 서버가 인식하게 해 주기 위해  flush privileges; 를 실행하자.

이는 CREATE USER, GRANT, REVOKE, DROP USER와 같은 명령어를 사용하여 사용자 계정이나 권한 설정을 직접 변경했을 때, 변경 사항이 즉시 데이터베이스 서버에 반영되지 않기 때문이다.

 

*flush privileges의 역할

데이터베이스 서버는 성능 최적화를 위해 권한 검사 정보를 메모리에 캐시한다. FLUSH PRIVILEGES; 명령은 캐시를 비우고, 권한 테이블에서 최신 정보를 다시 읽어 들여 캐시를 갱신해 준다.

또, 시스템의 보안을 강화하고, 사용자 및 권한 설정의 일관성을 유지하기 위해, 권한 변경 작업 후에는 항상 FLUSH PRIVILEGES;를 실행하는 것이 권장된다. 이를 통해, 권한 관련 변경 사항을 즉시 적용시키고, 예상치 못한 접근 권한 문제를 방지할 수 있다.

 

 

 

이제 우분투 db 원격접속을 위해 우분투 네트워크에 할당된 ipv4 주소를 ifconfig 명령어로 확인해 준다. ex) inet 192.168.64.2

 

이제 testuser3로 원격 접속을 시도해 보자. 터미널(iterm2) 창에서  mysql -h '접속 ip주소' -u 계정명 -p을 입력한다.

하지만 위와 같이 'access denied for user'의 에러가 발생하면서 원격접속을 실패한다.

이는 계정을 'localhost'로 create 했기 때문이다. (만약 계정을 '%'로 create 했다면 해당 계정으로는 원격 접속이 가능하다.)

 

우분투 local에서는 'testuser3' 계정으로 접속이 가능하다는 것이다.

 

우분투 local에 ssh 접속을 한 뒤에 다시 시도해보면 정상적으로 접속이 되는 것을 볼 수 있다.

(ssh 접속 : ssh 우분투사용자계정명@해당장치 IP주소)

새로 생성한 계정으로 show databases; 입력해 보면

 

루트 계정과는 달리 적은 수의 database만을 볼 수 있다. 이는 계정 권한을 따로 설정하지 않았기 때문이다. 

 

 

 

루트 계정으로 접속 시 아래와 같이 4개의 database를 볼 수 있다.

~$ mysql -u root -p  

 

 

 

 

 

 

 

mysql database에서 아래와 같이 user, host 등의 계정 정보들을 볼 수 있다.

여기서도 볼 수 있듯이 새로 생성해 주었던 'testuser3' 계정은 localhost에서만 접속이 가능하다는 정보를 볼 수 있다. 

아래와 같이 'testuser4' 계정을 만들고 와일드카드 '%'를 통해 어디서든 접속을 가능하게 하고 비교해 보자.

외부에서 아래와 같이 원격 접속을 시도해 보면 이번에는 에러 없이 접속이 가능한 것을 볼 수 있다.

그러나 show databases; 명령에 권한이 제한적인 것을 알 수 있다. 특정 db에 대해서 해당 계정에 권한 부여를 해주어야 조회가 가능하다.

 

계정 권한 설정에 대한 내용은 다음 글에서 이어서 다루었다.

https://9taetae9.tistory.com/44

DDL을 통한 데이터베이스 계정 권한 설정 (grant all privileges on)