MySQL ) 사용자 및 권한과 역할

 

사용자

MySQL 의 사용자 계정은 해당 사용자가 어느 IP 에서 접속하고 있는지도 확인한다. 권한을 묶어서 관리하는 역할 개념도 도입됐다. 각 사용자 권한으로 미리 준비한 권한 세트를 부여하는 것도 가능하다!

사용자 식별

MySQL 기준

• 호스트명
• 도메인
• IP 주소

다음 사용자 계정은 로컬에서만 접속 가능하고, 다른 컴퓨터에서는 접속이 불가능하다. (127.0.0.1은 로컬 IP)

 

'user_id'@'127.0.0.1'

 

모든 외부 컴퓨터에서 접속이 가능하게 하려면 IP를 % 라고 대체한다.

 

'user_id'@'%'

 

192.168.0.1 인 PC에서 비밀번호를 “abcd” 라고 쳐서 접속한다면 ?

 

'member'@'192.168.0.1' // 비밀번호 1234
'member'@'%' // 비밀번호 abcd

 

→ 권한이나 계정 정보에 대해 범위가 가장 작은 것을 항상 먼저 선택하기 때문에 로그인 실패가 뜰 것

 

사용자 계정 관리

시스템 계정 및 일반 계정

종류	권한	사용자	관리 범위
시스템 계정	SYSTEM_USER 권한	DB 서버 관리자	시스템 계정, 일반 계정(생성 삭제 및 변경)
일반 계정	없음	응용 프로그램 또는 개발자	시스템 계정 관리 불가

시스템 계정 권한

• 계정 관리
  • 계정 생성 및 삭제, 계정 권한 부여 및 제거
• 다른 세션 또는 세션에서 실행 중인 쿼리 강제 종료
• 스토어드 프로그램 생성 시 DEFINER 를 타 사용자로 설정

 

 

✅ 시스템 계정 권한은 DBA(데이터베이스 관리자) 계정에는 SYSTEM_USER 권한을 할당하고 일반 사용자를 위한 계정에는 SYSTEM_USER 권한을 부여하지 않기 위해서 사용한다.

계정 생성

계정 생성	CREATE USER
권한 부여	GRANT

계정 생성 옵션

• 계정 인증 방식, 비밀번호
• 비밀번호 관련 옵션
  • 유효 기간, 이력 개수, 재사용 불가 기간
• 기본 역할
• SSL 옵션
• 계정 잠금 여부

IDENTIFIED WITH

인증 방식과 비밀번호 설정, 기본 인증 방식은 → IDENTIFIED BY PASSWORD

REQUIRE

암호화된 SSL/TLS 채널 사용 여부

PASSWORD EXPIRE

유효 기간. 단, 응용 프로그램 접속용 계정에 유효 기간을 설정하는 건 위험하니 주의.

PASSWORD HISTORY

한 번 사용했던 비밀번호 재사용 금지 설정

PASSWORD REUSE INTERVAL

비밀번호 재사용 금지 기간

PASSWORD REQUIRE

새 비밀번호 변경할 때 현재 비밀번호도 필요한지 아닌지 설정

ACCOUNT LOCK / UNLOCK

계정 생성 시 또는 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부

비밀번호 관리

고수준 비밀번호

글자 조합을 강제하거나 금칙어 설정하는 기능. → validate_password 컴포넌트 설치 필요

비밀번호 정책

• LOW : 길이만 검증
• MEDIUM : 길이와 숫자, 대소문자, 특수문자 조합 검증
• STRONG : MEDIUM 검증도 하고, 금칙어 포함 여부까지 검증
  • 금칙어 저장된 사전 파일을 등록해야 한다.

이중 비밀번호

비밀번호가 2개 ! 둘 중 어느 것이라도 맞으면 로그인 가능. 배포 상황에서 보안을 위해 비밀번호를 바꿔야 할 때 사용

비밀번호

• 프라이머리
  • 최근 설정 비밀번호
• 세컨더리
  • 이전 비밀번호

 

✅ 세컨더리 비밀번호는 계정 비밀번호 변경이 끝난 후 보안을 위해 삭제하는 것이 좋다.

권한

글로벌 권한	DB 나 테이블 이외의 객체에 적용되는 권한
객체 권한	DB 나 테이블을 제어하는 데 필요한 권한
동적 권한	MySQL 서버의 컴포넌트나 플러그인이 설치되면 등록되는 권한

권한 부여

• 권한을 부여할 때는 GRANT 명령을 사용하되
• 먼저 사용자를 생성하고 그 다음 권한을 부여해야 한다.

글로벌 권한 (SUPER)

GRANT SUPER ON *.* TO 'user'@'localhost' ;

• 특정 DB 나 테이블에 부여될 수 없다.
• 항상 “ . " 절을 사용한다.

DB 권한 (EVENT)

GRANT EVENT ON *.* TO 'user'@'localhost' ;
GRANT EVENT ON employees.* TO 'user'@'localhost' ;

• 특정 DB 또는 서버 모든 DB 에 권한 부여 가능
  • DB 테이블 뿐만 아니라 스토어드 프로그램들도 포함
• DB 권한만 부여하는 경우에는 테이블까지 명시할 수 없다.
  • DB 권한은 테이블에 대해 부여하는 게 아님.

테이블 권한 (CRUD)

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost' ;
GRANT SELECT, INSERT, UPDATE, DELETE ON employees.* TO 'user'@'localhost' ;
GRANT SELECT, INSERT, UPDATE, DELETE ON employees.department TO 'user'@'localhost' ;

• 모든 서버
• DB
• 특정 테이블

→ 전부 권한 부여 가능!

특정 컬럼에만 권한 부여

GRANT SELECT, INSERT, UPDATE(dept_name)
ON employees.department
TO 'user'@'localhost' ;

• UPDATE 는 dept_name 컬럼에 대해서만 수행 가능.

 

 

✅ 컬럼 단위의 접근 권한이 필요하다면 GRANT 명령 보다는 테이블에서 권한을 허용하고자 하는 컬럼만으로 별도의 뷰를 만들어 사용하는 방법도 있다.

역할

권한을 묶어서 역할을 사용하자! 기본적으로 그 자체로 사용될 수 없으며, 계정에 부여해야 한다.

// 권한을 부여한다.
GRANT SELECT ON employees.* TO role_emp_read ;
GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write ;

 

역할은 그 자체로 사용될 수 없다. 계정에 부여하자.

 

// 계정을 생성한다.
CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwert' ;
CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'qwert' ;

 

계정이 만들어져도 권한이 없으니 아무 쿼리도 실행할 수 없다. 역할 부여가 필요하다.

 

// 역할을 부여한다.
GRANT role_emp_read TO reader@'127.0.0.1' ;
GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1' ;

 

아직 부족하다. 역할을 사용할 수 있게 하려면 SET ROLE 명령으로 역할 활성화가 필요하다.

 

// 역할 활성화.
SET ROLE 'role_emp_read' ;

 

이제 reader 계정 사용이 가능하다 !

 

역할의 모습

• 사용자 계정과 거의 같다.
• MySQL 서버 내부적으로도 역할과 계정은 동일 객체로 취급된다.
• 단지, 하나의 사용자 계정에 다른 사용자 계정이 가진 권한을 병합해서 권한 제어가 가능할 뿐이다.

왜 이렇게 만들었을까?

데이터베이스 관리 직무를 분리하고, 보안을 강화하기 위해!