[Real MySQL 8.0] 사용자 및 권한

엄혜영·2024년 7월 25일
mysql스터디
1

Real MySQL 8.0

목록 보기
4/9
post-thumbnail

사용자 식별

MySQL의 사용자는 다른 DBMS와 다르게 사용자의 계정뿐 아니라 사용자의 접속 지점(클라이언트가 실행된 호스트명이나 도메인 또는 IP주소)도 계정의 일부가 된다.

'svc_id'@'127.0.0.1'

따라서 위의 사용자 계정은 항상 MySQL 서버가 가동 중인 로컬호스트에서 접속할 때만 사용될 수 있는 계정이다.

만약 사용자 계정에 위의 계정만 등록돼 있다면 다른 컴퓨터에서는 svc_id 아이디로 접속할 수 없다.

모든 외부 컴퓨터에서 접속이 가능한 사용자 계정을 생성하고 싶다면 사용자 계정의 호스트 부분을 ‘%’ 문자로 대체하면 된다. % 문자는 모든 IP 또는 모든 호스트명을 의미한다.

사용자 계정 관리

시스템 계정과 일반 계정

MySQL 8.0부터 SYSTEM_USER 권한을 가지고 있느냐에 따라 시스템 계정(System Account)과 일반 계정(Regular Account)으로 구분된다.

  • 시스템 계정: 데이터베이스 서버 관리자를 위한 계정
    • 시스템 계정과 일반 계정을 관리(생성 삭제 및 변경)할 수 있음
    • 다른 세션(Connection) 또는 그 게션에서 실행중인 쿼리를 강제 종료
    • 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 지정
  • 일반 계정: 응용 프로그램이나 개발자를 위한 계정 시스템 계정을 관리할 수 없다.

MySQL 서버의 내장 계정

  • ‘mysl.sys’@’localhost’: MySQL 8.0부터 기본으로 내장된 sys 스키마의 객체(뷰나 함수, 그리고 프로시저)들의 DEFINER로 사용되는 계정
  • ‘mysql.session’@’localhost’: MySQL 플러그인이 서버로 접근할 때 사용되는 계정
  • ‘mysql.infoschema’@’localhost’: information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

계정 생성

MySQL 5.7 버전까지는 GRANT 명령으로 권한의 부여와 동시에 계정 생성이 가능했다.

MySQL 8.0 버전부터는 계정의 생성은 CREATE USER 명령으로, 권한 부여는 GRANT 명령으로 구분해서 실행하도록 바뀌었다.

CREATE USER 'user'@'%'
		IDENTIFIED WITH 'mysql_native_password' BY 'password'
		REQUIRE NONE
		PASSWORD EXPIRE INTERVAL 30 DAY
		ACCOUNT UNLOCK
		PASSWORD HISTORY DEFAULT
		PASSWORD REUSE INTERVAL DEFAULT
		PASSWORD REQUIRE CURRENT DEFAULT;

계정 생성 옵션

  • IDENTIFIED WITH
  • REQUIRE
  • PASSWORD EXPIRE
  • PASSWORD HISTORY
  • PASSWORD REUSE INTERVAL
  • PASSWORD REQUIRE
  • ACCOUNT LOCK / UNLOCK

비밀번호 관리

고수준 비밀번호

MySQL 서버의 비밀번호는 유효기간이나 이력 관리를 통한 재사용 금지 기능뿐만 아니라 비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능도 있다.

MySQL 서버에서 비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용하면 된다.

이전에 validate_password 컴포넌트를 설치해야 한다.

  • 컴포넌트를 설치 INSTALL COMPONENT 'file://component_validate_password';
  • 설치된 컴포넌트 확인 SELECT * FROM mysql.component;

비밀번호 정책

  • LOW: 비밀번호의 길이만 검증
  • MEDIUM: 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수먼자의 배합을 검증
  • STRONG: MEDIUM 레벨의 검증을 모두 수행하며, 금칙어가 포함됐는지 여부까지 검증

이중 비밀번호

MySQL 8.0 버전부터는 계정의 비밀번호로 2개의 값을 동시에 사용할 수 있는 기능을 추가했다.

MySQL 서버 메뉴얼에서는 이 기능을 이중 비밀번호(Dual Password)라고 소개한다.

이중 비밀번호는 2개의 비밀번호 중 하나만 일치하면 로그인이 통과되는 것을 의미한다.

이중 비밀번호 사용방법

-- 비밀번호를 "ytrewq"로 설정
ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password';

-- 비밀번호를 "qwerty"로 변경하면서 기존 비밀번호를 세컨더리 비밀번로로 설정
ALTER USER 'root'@''localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

이 상태에서 root 계정은 두 비밀번호 중 아무거나 입력해도 로그인이 된다.

세컨더리 비밀번호 삭제

ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

MySQL 서버에 접속하는 모든 응용 프로그램의 재시작이 완료되면 이제 다음 명령으로 세컨더리 비밀번호는 삭제한다. 세컨더리 비밀번호는 계정의 보안을 위해 삭제하는 것이 좋다.

권한(Privilege)

MySQL 5.7 vs MySQL 8.0

MySQL 5.7 버전까지 권한은 글로벌 권한과 객체 단위의 권한으로 구분됐다. (정적 권한)

정적 권한: MySQL의 소스코드에 고정적으로 명시돼 있는 권한을 의미한다.

MySQL 8.0 버전 부터는 MySQL 5.7 버전의 권한(정적 권한) 다음의 동적 권한이 더 추가됐다.

동적 권한은 MySQL 서버가 시작되면서 동적으로 생성하는 권한을 의미한다.
https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html

5.7 버전까지는 SUPER라는 권한이 데이터베이스 관리를 위해 꼭 필요한 권한 이었지만,

8.0 부터는 SUPER 권한이 잘게 쪼개어져 동적 권한으로 분산됐다.

따라서 각 관리자 개별로 꼭 필요한 권한만 부여할 수 있게 되었다.

사용자에게 권한을 부여할 때는 GRANT 명령을 사용한다.

이때 각 권한의 특성(범위)에 따라 GRANT 명령의 ON 절에 명시되는 오브젝트(DB나 테이블)의 내용이 바뀌어야 한다.

TO 키워드 뒤에는 권한을 부여할 대상 사용자를 명시한다.

존재하지 않는 사용자에 대해 GRANT 명령이 실행되면 에러가 발생하므로 반드시 사용자를 먼저 생성하고 GRANT 명령으로 권한을 부여해야 한다.

GRANT privilege_list ON db.table T0 'user'@'host';

각 계정이나 권한에 부여된 권한이나 역할을 확인하기 위해서는 SHOW GRANTS 명령을 사용할 수 있다.

글로벌 권한은 특정 DB나 테이블에 부여될 수 없기 때문에 글로벌 권한을 부여할 때 GRANT 명령의 ON 절에는 항상 **.**를 사용하게 된다.

**.** 은 모든 DB의 모든 오브젝트(테이블과 스토어드 프로시저나 함수 등)를 포함해서 MySQL 서버 전체를 의미한다.

DB 권한은 특정 DB에 대해서만 권한을 부여하거나 서버에 존재하는 모든 DB에 대해 권한을 부여할 수 있기 때문에 ON 절에 *.* 이나 employees.* 모두 사용할 수 있다.

테이블 권한은 서버의 모든 DB에 대해, 특정 DB의 오브젝트에 대해서만, 특정 DB의 특정 테이블에 대해서만 권한을 부여하는 것이 가능하다.

테이블의 특정 칼럼에 대해서만 권한을 부여하는 경우도 있다.

칼럼에 부여할 수 있는 권한은 INSERT, UPDATE, SELECT 3가지이며, 각 권한 뒤에 칼럼을 명시하는 형태로 부여한다.

표 형태로 깔끔하게 보고자 한다면 mysql DB의 권한 관련 테이블을 참조하면 된다.

역할(Role)

MySQL 8.0 버전부터 권한을 묶어서 역할을 사용할 수 있게 됐다.

계정의 기본 역할 또는 역할에 부여된 역할 그래프는 SHOW GRANTS 명령을 사용해 확인할 수 있다.

또는 mysql DB의 권한 관련 테이블을 참조하는 방법도 있다.

실습

클라우드 서버에 MySQL을 설치한 뒤 사용자 계정을 생성, 로컬에서 해당 계정을 사용하여 클라우드 서버의 MySQL 서버에 접속하는 실습을 진행한다.

클라우드 서버는 AWS EC2를 사용하였다.

인스턴스를 할당한 뒤 인바운드 규칙 편집으로 3306 포트를 열어주자.

터미널로 클라우드 서버에 접속한 뒤, MySQL 서버에 루트 계정으로 접속하여 remote_access 계정을 생성하였다.

이때 원격에서 접속할 수 있도록 호스트 정의를 '%'로 하였다.

이때 해당 MySQL 서버의 로컬 환경인 원격 서버에서 위의 아이디로 MySQL 서버 접속을 시도하는 경우 정상적으로 접속 가능함을 확인하였다.


그러나 내 컴퓨터 환경인 로컬에서 터미널로 MySQL 서버에 접속을 시도하면 다음과 같이 에러가 발생한다.

해당 에러를 해결하기 위해서 MySQL 설정을 변경해주어야 한다.

원격 서버에서 터미널 창에 다음과 같은 명령을 실행하면 설정을 수정할 수 있는 화면이 뜬다.
sudo nano /etc/mysql/my.cnf

여기서 bind-address = 0.0.0.0로 설정하여 모든 IP에서 접근 가능하도록 변경해주었다.

설정을 마친 후 MySQL 서버를 재시작한다.


위의 설정을 마친 뒤, 로컬 환경의 터미널에서 MySQL 서버에 접속을 시도하면 아주 잘 되는 모습을 확인할 수 있다!



이후 비교를 위해 원격 서버에서 호스트 정의를 locahost로 하여 계정을 생성한 뒤,
로컬 환경의 터미널에서 접속을 시도해보았다.

예상과 같이 해당 계정은 원격 서버에서만 접속 가능하며, 로컬 환경에서는 접속이 불가능함을 확인할 수 있다.

profile
엄혜영
누워있는게 좋은 완벽주의자
이전 포스트

[Real MySQL 8.0] MySQL 서버 설정

다음 포스트

[Real MySQL 8.0] MySQL 엔진 아키텍처

0개의 댓글