📔설명
MySQL의 사용자와 권한 및 역할에 대해 알아보자!
🍵사용자 식별
MySQL의 사용자 계정은 단순히 사용자의 아이디뿐 아니라 해당 사용자가 어느 IP에서 접속하고 있는지도 확인
=> 계정 언급 시 아이디와 호스트를 항상 함께 명시
'root'@'localhost'만약 모든 외부 컴퓨터에서 접속이 가능한 사용자 계정을 생성하고자 할 경우 호스트 부분에 % 문자를 대체하면 된다.
'dang'@'%'🧉사용자 계정 관리
1. 시스템 계정과 일반 계정
시스템 계정 : SYSTEM_USER 권한을 가지고 있는 계정
=> 시스템 계정과 일반 계정을 관리할 수 있음
일반 계정
=> 시스템 계정 관리 불가능
select user, host, account_locked from mysql.user
where user like 'mysql.%';
mysql.sys : 기본으로 내장된 sys 스키마의 객체(뷰, 함수, 프로시저)들의 DEFINER로 사용
mysql.session : MySQL 플러그인이 서버로 접근할 때 사용되는 계정
mysql.infoschema : information_schema에 정의된 뷰의 DEFINER로 사용
2. 계정 생성
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
pssword require current default;IDENTIFIED WITH
사용자의 인증 방식과 비밀번호 설정
MySQL 서버의 기본 인증 방식을 사용하려면 identified by 'password' 형식으로 명시
-- native authentication을 기본 인증 방식으로 설정
set global default_authentication_plugin="mysql_native_password"REQUIRE
MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부 설정
=> 설정 X시 비암호화 채널로 연결
PASSWORD EXPIRE
비밀번호의 유효기간 설정
=> 별도 설정 X시 default_password_lifetime 시스템 변수에 저장된 기간으로 유효 기간 설정
PASSWORD HISTORY
한 번 사용했던 비밀번호를 재사용하지 못하게 설정
=> MySQL서버는 mysql DB의 password_history 테이블을 사용해 이전에 사용한 비밀번호 기억
select * from mysql.password_history;PASSWORD REUSE INTERVAL
한 번 사용했던 비밀번호의 재사용 금지 기간을 설정
PASSWORD REQUIRE
비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호를 필요로 할지 말지 결정
ACCOUNT LOCK / UNLOCK
계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보 변경시 계정을 사용하지 못하게 잠글지 여부 결정
🍶비밀번호 관리
비밀번호 유효성 체크 규칙 적용을 위해서 validate_password 컴포넌트를 사용
-- 설치 (이미 설치되어있음)
install component 'file://component_validate_password';
--컴포넌트 확인
select * from mysql.component;
show global variables like 'validate_password%';
validate_password.dictionary_file 시스템 변수에 금칙어들이 저장된 사전 파일을 등록하면, 비밀번호가 명시된 단어를 포함하고 있는지를 검증한다.
금칙어는 validate_password.policy가 Strong일 때만 작동한다.
set global validate_password.dictonary_file='prohibitive_word.data';
set global alidate_password.policy='STRONG';Dual Password : MySQL에서 계정의 비밀번호로 2개의 값을 동시에 사용할 수 있게 함
=> 2개의 비밀번호 중 하나만 일치하면 로그인 통과
--dual password 사용
--기존 비밀번호를 세컨더리 비밀번호로 설정
alter user 'root'@'localhost' identified by '새 비밀번호' retain current password;Dual Password를 이용해 프라이머리 비밀번호를 새 비밀번호로 바꾼 후, 응용 프로그램의 소스코드나 비밀번호를 바꿔주고 다시 세컨더리 비밀번호는 보안상 삭제하는 것이 좋다.
--세컨더리 삭제
alter user 'root'@'localhost' discard old password;🍾권한(Privilege)
글로벌 권한 : 데이터베이스나 테이블 이외의 객체에 적용되는 권한
객체 권한 : 데이터베이스나 테이블을 제어하는 데 필요한 권한
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
위 표는 정적 권한이라고 한다.
정적 권한 : MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한
동적 권한 : MySQL 서버가 시작되면서 동적으로 생성하는 권한
1. 권한 부여
GRANT privilege_list ON db.table TO 'user'@'host';grant option 권한은 grant 명령의 마지막에 with grant option을 명시해서 부여
-- 글로벌 권한
grant super on *.* to 'user'@'localhost';글로벌 권한은 특정 DB나 테이블에 부여될 수 없어, 글로벌 권한 부여시 항상 *.*를 사용
=> *.* : 모든 DB의 모든 오브젝트를 포함해 MySQL 서버 전체를 의미
-- DB 권한
grant event on *.* to 'user'@'localhost';
grant event on employees.* to 'user'@'localhost';DB 권한은 특정 DB에 대해서만 권한을 부여하거나 서버에 존재하는 모든 DB에 대한 권한을 부여할 수 있어 위처럼 모두 사용 가능
=> 특정 DB에 대해서만 권한을 부여하기 때문에 오브젝트 권한처럼 db.table로 테이블 명까지는 명시X
-- 테이블 권한
grant select, insert, update, delete on employees.department to 'user'@'localhost';테이블의 특정 칼럼에 대해서만 권한 부여시 grant 문법이 조금 달라진다.
=> 칼럼에 부여 가능한 권한은 delete 제외
--update는 dept_name 칼럼에 대해서만, select와 insert는 모든 칼럼
grant select, insert, update(dept_name) on employees.department TO 'user'@'localhost';🍷역할(Role)
권한을 묶어서 역할을 사용 가능
--역할 정의
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;--계정에 역할 부여
grant role_emp_read to reader@'localhost';--계정이 가진 권한 확인
show grants;역할은 잘 부여됐으나, 실제로 실행해보면 권한이 없다는 에러가 발생한다.
--계정에 활성화된 역할 조회
select current_role();--역할 활성화
set role 'role_emp_read';계정을 로그아웃했다가 다시 로그인하면 다시 역할이 활성화되지 않은 상태로 초기화된다.
-- 서버에 로그인 시 자동으로 역할 활성화
set global activate_all_roles_on_login=ON;