권한(Privilege)
MySQL에서 권한은 정적 권한과 동적 권한으로 나뉘며, 이 중 정적 권한은 글로벌 권한과 객체 단위 권한으로 나뉜다.
- 정적 권한 : MySQL 서버 어딘가에 고정적으로 명시된 권한
- 글로벌 권한 : 파일, 서버 단위 권한 설정
- 객체 권한 : 데이터베이스, 테이블, 인덱스 등 객체 단위 권한 설정
- 동적 권한 : MySQL 서버가 실행되면서 동적으로 실행되는 권한(컴포넌트, 플러그인 등으로 등록되는 권한)
권한 종류는 MySQL 공식 사이트 "MySQL 8.4 권한 리스트"참고
권한 부여
[글로벌 권한 부여]
글로벌 권한을 부여할 때는 ON 절에 항상 *.*을 사용해야 한다. *.*은 모든 오브젝트를 의미하며, MySQL 서버 전체를 의미한다.
mysql> GRANT RELOAD ON *.* TO 'user'@'%';구분자(
',')을 사용하여 여러 권한을 동시에 부여할 수 있다.mysql> GRANT RELOAD, SUPER ON *.* TO 'user'@'%';
[객체 권한 부여]
객체 단위로 권한을 부여할 때는 ON절에 *.*, DB명.*, DB명.DB객체명을 모두 사용할 수 있다.
MySQL 서버 내 모든 DB에 권한 부여
mysql> GRANT SELECT ON *.* TO 'user'@'%';
특정 DB에 권한 부여
mysql> GRANT SELECT ON tmp_db.* TO 'user'@'%';
특정 DB의 특정 테이블에 권한 부여
mysql> GRANT SELECT ON tmp_db.tmp_tb TO 'user'@'%';
테이블의 경우 특정 컬럼에만 권한(DELETE를 제외한 DML)을 부여할 수 있다.
하지만 이는 추천하는 방법이 아니다. 특정 컬럼에만 권한을 부여하면, 테이블의 나머지 컬럼에 대한 권한 체크가 이루어지기 때문에 성능에 영향을 미칠 수 있다.
이러한 이유로 테이블의 특정 컬럼에만 권한을 부여할 때는 VIEW 테이블을 만들어 사용하는게 좋을 수 있다.
아래 명령문은
name과age컬럼만 조회 할 수 있다.mysql> GRANT SELECT(name, age) ON tmp_db.student TO 'user'@'%';
권한 부여 확인
권한을 확인하는 방법은 다양하다.
[현재 로그인한 사용자의 권한 확인]
mysql> SHOW GRANTS;
테이블 조회 방식으로 정렬된 형태로도 확인 할 수 있다.
[계정별 권한 확인]
mysql> select * from mysql.user;[DB별 권한 확인]
mysql> select * from mysql.db;[테이블별 권한 확인]
mysql> select * from mysql.tables_priv;[컬럼별 권한 확인]
mysql> select * from mysql.columns_priv;[스토어드 프로그램(프로시저, 함수)별 권한 확인]
mysql> select * from mysql.procs_priv;[동적 권한별 권한 확인]
mysql> select * from mysql.global_grants;
역할(Role)
MySQL에서 역할(Role)은 여러 권한의 집합을 의미한다. 1개 이상의 권한을 묶어 하나의 역할로 만들 수 있고, 이를 여러 사용자에게 역할 부여할 수 있다.
MySQL에서 역할은 내부적으로 사용자(user)와 같은 객체로 취급된다.
MySQL 내부적으로 사용자와 역할은 같은 객체로 취급되기 때문에 구분하기 어렵다.
이러한 이유로 역할명의 접두사는
role_을 주로 사용한다.
역할 생성
role_tmp역할 생성mysql> CREATE ROLE role_tmp; 또는 mysql> CREATE ROLE 'role_tmp';
역할에 권한 부여
role_tmp역할에select,insert,update권한 부여해당 역할은
tmp_db의 모든 객체에 대해delete를 제외한 DML 사용 권한을 가지고 있다.mysql> GRANT SELECT, INSERT, UPDATE ON tmp_db.* TO role_tmp;
사용자에게 역할 부여
mysql> GRANT role_tmp TO 'user'@'%';
역할 활성화
부여받은 역할은 바로 사용할 수 없다.
부여받은 역할을 별도로 역할을 활성화해야 비로소 역할의 권한/기능을 사용할 수 있다.
[역할 활성화]
현재 접속 계정에 부여받은 역할만 활성화할 수 있다.mysql> SET ROLE 'role_tmp';
사용자에게 부여된 역할이 활성화되어도 사용자가 로그아웃 하게 되면, 역할의 활성화가 비활성화로 초기화된다.
이처럼 수동적인 역할 활성화가 아닌 자동으로 역할을 활성화하기 위해서는 activate_all_roles_on_login 시스템 변수를 ON으로 설정해야 한다.
[역할 자동활성화 설정]
mysql> SET GLOBAL activate_all_roles_on_login = 'ON';
역할 활성화 (기본 역할)
역할을 기본 역할로 설정/활성화하려면 SET DEFAULT 명령문을 사용해야 한다.
기본 역할로 설정/활성화된 역할은 사용자가 재로그인 해도 역할이 자동 활성화되어 있다.
[특정 역할을 기본 역할로 설정/활성화]
mysql> SET DEFAULT ROLE role_tmp TO 'user'@'%';[부여받은 모든 역할을 기본 역할로 설정/활성화]
mysql> SET DEFAULT ROLE ALL TO 'user'@'%';
기본 역할 삭제
mysql> SET DEFAULT ROLE NONE TO 'user'@'%';
역할 확인
[활성화된 역활 리스트 확인]
mysql> SELECT current_role();[계정별 부여된 기본 역할 확인]
기본 역할로 부여받은 경우 사용자가 재로그인을 하면 해당 기본 역할은 자동 활성화된다.mysql> SELECT * from mysql.default_roles;[역할에 부여된 관계 확인]
사용자별로 부여받은 역할 정보를 확인mysql> SELECT * from mysql.role_edges;[특정 역할에 부여된 권한 확인]
mysql> SHOW GRANTS FOR 'role_tmp';[특정 사용자에게 부여된 역할/권한 확인]
mysql> SHOW GRANTS FOR 'user'@'%';
