infoqoch's 개발 블로그

mysql, 사용자와 권한 설정

• • •

사용자 식별

  • 계정명과 접속지점(호스트명, 도메인, IP)으로 사용자를 식별함.
  • 아이디와 아이피는 따옴표로 감싼다.
  • 모든 외부 컴퓨터에서 접속 가능한 계정을 생성할 경우 호스트를 %로 입력한다.
'svc_id'@'192.168.0.1'
'svc_id'@'%'

사용자 계정 관리

시스템 계정과 일반 계정

  • MySQL 8.0 이후 SYSTEM_USER 권한에 따라 시스템 계정과 일반 계정으로 구분
  • 시스템 계정은 다음 작업 가능
    • 계정 관리(계정 생성, 삭제, 권한 부여 및 권한 삭제)
    • 다른 세션(Connection) 또는 다른 세션의 쿼리를 강제 종료
    • 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정

계정 생성

  • MySQL 8.0 이후부터 계정 생성(CREATE USER)과 권한 부여(GRANT)는 구분해서 실행.
  • 아래와 같이 계정 생성.
DROP USER 'user'@'%';
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

  • 사용자의 인증 방식과 비밀번호를 설정.
  • Native Pluggable Authentication: MySQL 5.7 버전까지의 기본 방식. 일반적인 계정과 비밀번호를 통한 인증 방식
  • Caching SHA-2 Pluggable Authentication: MySQL 8 버전의 기본 방식. SSL/TLS 또는 RSA 키페어를 필요로 함.
  • 기존 호환성을 위하여 Native Pluggable Authentication을 8.0에서 사용할 수 있다.
  • 글로벌 변수로 인증 방식을 설정하는 방식은 다음과 같다: SET GLOBAL default_authentication_plugin="mysql_native_password"

REQUIRE

  • 서버에 접속할 때 SSL/TLS 사용 여부를 설정.
  • 기본값은 NONE으로 비암호화 채널로 연결.

PASSWORD EXPIRE

  • 비밀번호 유효 기간을 설정
  • 미입력 시 default_password_lifetime에 따름.

  • 응용 프로그램 접속용 계정에 유효 기간 설정은 위험할 수 있음!

  • PASSWORD EXPIRE : 계정 생성과 동시에 비밀번호의 만료 처리
  • PASSWORD EXPIRE NEVER : 계정 비밀번호의 만료 기간 없음
  • PASSWORD EXPIRE DEFAULT : default_password_lifetime 시스템 변수를 따름
  • PASSWORD EXPIRE INTERVAL n DAY : 비밀번호 유효 기간을 설정

PASSWORD HISTORY

  • 한 번 사용했던 비밀번호를 재사용하지 못하도록 설정 가능.

  • PASSOWRD HISTORY DEFAULT: password_history 시스템 변수에 저장된 개수 만큼 비밀번호의 이력을 저장하며, 저장된 이력의 비밀번호는 재사용할 수 없다.

  • PASSWORD HISTORY n: 비밀번호 저장 이력을 최근 n 개 까지로 설정

  • password_history의 기본값은 0으로 패스워드 재사용 제한 없음. 히스토리에 저장하지 않음.
  • SELECT * FROM mysql.password_history로 이력 조회 가능.

PASSWORD REUSE INTERVAL

  • 한 번 사용했던 비밀번호의 재사용 금지 기간 설정.
  • PASSWORD REUSE INTERVAL DEFAULT : password_reuse_interval 변수의 값을 따름
  • PASSWORD REUSE INTERVAL n DAY : n 일자 이후 비밀번호를 재사용 할 수 있게 설정

PASSWORD REQUIRE

  • 비밀번호 만료 후 새로운 비밀번호 변경할 때, 만료된 비밀번호가 필요할지 결정.

  • 특별한 설정이 없을 경우 password_require_current에 따르며 기본 값은 OFF임.

  • PASSWORD REQUIRE CURRENT : 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정.
  • PASSWORD REQUIRE OPTION : 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정.

ACCOUNT LOCK / UNLOCK

  • 계정을 잠글지 결정.
  • ACCOUNT LOCK
  • ACCOUNT UNLOCK

비밀번호 관리

고수준 비밀번호

  • 비밀번호의 글자 조합을 보안 정책에 따라 유효성 검증 가능.
  • 비밀번호 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 설치해야 한다.
    • 해당 컴포넌트는 플러그인으로도 설치 가능하지만 보안 상 컴포넌트가 낫다.

이중 비밀번호(Dual Password)

  • 응용 프로그램이 사용하는 계정의 비밀번호를 쉽게 변경하기 어렵다. 이 문제를 해결하기 위하여 계정의 비밀번호 두 개를 동시에 사용할 수 있는 기능이 추가되었다.
  • RETAIN CURRENT PASSWORD으로 새로운 비밀번호를 생성한 후, 기존 비밀번호가 더 이상 필요 없을 때 DISCARD OLD PASSWORD로 제거 한다.
CREATE USER 'my-user'@'%';
ALTER USER 'my-user'@'%' IDENTIFIED BY 'old_password';
ALTER USER 'my-user'@'%' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;
ALTER USER 'my-user'@'%' DISCARD OLD PASSWORD;

권한(Privilege)

  • MySQL 5.7 버전까지 권한은 글로벌 권한과 객체 단위의 권한으로 구분됨.
    • 객체 단위 권한: 데이터베이스나 테이블 등 객체에 적용되는 권한.
    • 글로벌 권한: 객체 단위 권한 이외의 권한: FILE, ROLE, TABLESPACE, USER, PROCESS, REPLICATION, SUPER, SHUTDOWN 등.
  • MySQL 8.0 부터 동적 권한이 추가됨.
    • 동적 권한이란 MySQL 서버가 시작되면서 동적으로 생성되는 권한.
    • 5.7 버전까지의 SUPER 권한이 8.0부터는 동적 권한으로 분산됨.
  • 권한의 대상은 전체(.), 데이터베이스(database.*), 테이블(database.table), 칼럼(select(column))으로 설정 가능. 실제 권한을 위한 쿼리는 아래와 같음.
  • 보통 칼럼에 대한 권한은 사용하지 않음. 해당 설정으로 인해 해당 테이블의 모든 칼럼에 대한 권한을 체크해야 하며, 이로 인한 성능 상 문제가 발생할 수 있다. 이 경우 VIEW를 만들어 해당 뷰에 대한 권한을 체크하는 형태로 구현한다.
-- 글로벌 권한. *.* 만 가능
GRANT SUPER ON *.* TO 'my-user'@'%';

-- DB 권한. database.* 가능
GRANT EVENT ON *.* TO 'my-user'@'%';
GRANT EVENT ON employees.* TO 'my-user'@'%';

-- 테이블 권한. database.table 가능
-- 특정 칼럼에 대한 권한 설정 가능
GRANT SELECT ON *.* TO 'my-user'@'%';
GRANT SELECT ON employees.* TO 'my-user'@'%';
GRANT SELECT ON employees.departments TO 'my-user'@'%';
GRANT SELECT(dept_no) ON employees.departments TO 'my-user'@'%';
  • 권한의 내역은 다음과 같이 조회 가능하다.
show grants;

select * from mysql.user;
select * from mysql.db;
select * from mysql.tables_priv;
select * from mysql.columns_priv;
select * from mysql.procs_priv;
select * from mysql.global_grants;

역할(Role)

  • MySQL 8.0 부터 권한을 묶은 역할이 추가.
  • role을 설정 후, 해당 계정이 set role을 통해 해당 역할을 활성화 하면 사용 가능. sel role은 로그아웃마다 초기화.
  • activate_all_roles_on_login=ON 으로 글로벌 변수를 설정하면 로그인할 때 자동으로 역할이 세팅됨.
-- 권한 생성
create role role_emp_read, role_emp_write;
grant select on employees.* to role_emp_read;
grant insert, update, delete on employees.* to role_emp_write;
create user 'my-role-user'@'%';
grant role_emp_read to 'my-role-user'@'%';

-- my-role_user로 로그인 후 조회
show grants; -- role_emp_write
select current_role();
select * from employees; -- 설정한 권한이 없어서 조회 불가능

set role role_emp_read;
select * from employees; -- 조회 가능
  • 권한은 계정과 내부적으로 거의 동일한 객체로 취급됨: SELECT user, host, account_locked FROM mysql.user;
  • 권한은 접두사(role_)등으로 구분할 필요가 있음.
  • 권한을 사용하여 계정 관리를 좀 더 세세하게 관리할 수 있음.
    • 계정의 생성 권한(CREATE USER)과 계정의 역할(CREATE ROLE)을 분리할 수 있음. 특정 사용자는 계정의 역할만 부여할 수 있음.
    • ROLE은 account_locked가 Y로 되어 있음. 그러므로 로그인이 불가능.
  • 다음 방식으로 역할을 조회
select * from mysql.default_roles;
select * from mysql.role_edges;

RealMySQL 8.0, 3장을 참고하여 작성하였습니다.