권한 (Privilege)
글로벌 권한과 객체 권한
- MySQL 5.7 버전까지 권한은 글로벌 권한과 객체 단위의 권한으로 구분됐다.
- 글로벌(Global) 권한 : 데이터베이스나 테이블 이외의 객체에 적용되는 권한
- 객체 권한 : 데이터베이스나 테이블을 제어하는 데 필요한 권한
- 글로벌 권한은 GRANT 명령에서 특정 객체를 명시하지 말아야 한다.
- 객체 권한은 GRANT 명령으로 권한을 부여할 때 반드시 특정 객체를 명시해야 한다.
- 예외적으로 ALL(또는 ALL PRIVILEGES)은 글로벌과 객체 권한 두 가지 용도로 사용될 수 있다.
- 특정 객체에 ALL 권한이 부여되면 해당 객체에 적용될 수 있는 모든 객체 권한을 부여한다.
- 글로벌로 ALL이 사용되면 글로벌 수준에서 가능한 모든 권한을 부여하게 된다.
정적 권한
- MySQL 5.7 버전부터 제공되던 권한이다.
- 정적 권한은 MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한을 의미한다.
글로벌 권한
| 권한 | Grant 테이블의 칼럼명 | 권한 범위 |
|---|
| FILE | File_priv | 파일 |
| CREATE ROLE | Create_role_priv | 서버 관리 |
| CREATE TABLESPACE | Create_tablespace_priv | 서버 관리 |
| CREATE USER | Create_user_priv | 서버 관리 |
| DROP ROLE | Drop_role_priv | 서버 관리 |
| PROCESS | Process_priv | 서버 관리 |
| PROXY | See proxies_priv table | 서버 관리 |
| RELOAD | Reload_priv | 서버 관리 |
| REPLICATION CLIENT | Repl_client_priv | 서버 관리 |
| REPLICATION SLAVE | Repl_slave_priv | 서버 관리 |
| SHOW DATABASES | Show_db_priv | 서버 관리 |
| SHUTDOWN | Shutdown_priv | 서버 관리 |
| SUPER | Super_priv | 서버 관리 |
| USAGE | Synonym for “no privileges” | 서버 관리 |
객체 권한
| 권한 | Grant 테이블의 칼럼명 | 권한 범위 |
|---|
| EVENT | Event_priv | 데이터베이스 |
| LOCK TALBLES | Lock_tables_priv | 데이터베이스 |
| REFERENCES | References_priv | 데이터베이스 & 테이블 |
| CREATE | Create_priv | 데이터베이스 & 테이블 & 인덱스 |
| GRANT OPTION | Grant_priv | 데이터베이스 & 테이블 & 스토어드 프로그램 |
| DROP | Drop_priv | 데이터베이스 & 테이블, 뷰 |
| ALTER ROUTINE | Alter_routine_priv | 스토어드 프로그램 |
| CREATE ROUTINE | Create_routine_priv | 스토어드 프로그램 |
| EXECUTE | Execute_priv | 스토어드 프로그램 |
| ALTER | Alter_priv | 테이블 |
| CREATE TEMPORARY TABLES | Create_tmp_table_priv | 테이블 |
| DELETE | Delete_priv | 테이블 |
| INDEX | Index_priv | 테이블 |
| TRIGGER | Trigger_priv | 테이블 |
| INSERT | Insert_priv | 테이블 & 칼럼 |
| SELECT | Select_priv | 테이블 & 칼럼 |
| UPDATE | Update_priv | 테이블 & 칼럼 |
| CREATE VIEW | Create_view_priv | 뷰 |
| SHOW VIEW | Show_view_priv | 뷰 |
객체 & 글로벌 권한
| 권한 | Grant 테이블의 칼럼명 | 권한 범위 |
|---|
| ALL [PRIVILEGES] | synonym for “all privileges” | 서버 관리 |
동적 권한
- MySQL 8.0 버전부터는 MySQL 5.7 버전의 권한에 다음의 동적 권한이 더 추가됐다.
- 동적 권한은 MySL 서버가 시작되면서 동적으로 생성하는 권한을 의미한다.
- 예를 들어, MySQL 서버의 컴포넌트나 플러그인이 설치되면 그때 등록되는 권한을 동적 권한이라 한다.
- 일부는 MySQL 서버에 명시돼 있기도 하다.
- MySQL 5.7 버전까지는 SUPER라는 권한이 데이터베이스를 관리를 위해 꼭 필요한 권한이었지만, MySQL 8.0부터는 SUPER 권한은 잘게 쪼개어져 동적 권한으로 분산됐다.
- MySQL 8.0 버전부터는 백업 관리자와 복제 관리자 개별로 꼭 필요한 권한만 부여할 수 있게 되었다.
| 권한 | 권한 범위 |
|---|
| INNODB_REDO_LOG_ARCHIVE | 리두 로그 관리 |
| RESOURCE_GROUP_ADMIN | 리소스 관리 |
| RESOURCE_GROUP_USER | 리소스 관리 |
| BINLOG_ADMIN | 백업 & 복제 관리 |
| BINLOG_ENCRYPTION_ADMIN | 백업 & 복제 관리 |
| BACKUP_ADMIN | 백업 관리 |
| CLONE_ADMIN | 백업 관리 |
| GROUP_REPLICATION_ADMIN | 복제 관리 |
| REPLICATION_APPLIER | 복제 관리 |
| REPLICATION_SLAVE_ADMIN | 복제 관리 |
| CONNECTION_ADMIN | 서버 관리 |
| ENCRYPTION_KEY_ADMIN | 서버 관리 |
| PERSIST_RO_VARIABLES_ADMIN | 서버 관리 |
| ROLE_ADMIN | 서버 관리 |
| SESSION_VARIABLES_ADMIN | 서버 관리 |
| SET_USER_ID | 서버 관리 |
| SHOW_ROUTINE | 서버 관리 |
| SYSTEM_USER | 서버 관리 |
| SYSTEM_VARIABLES_ADMIN | 서버 관리 |
| TABLE_ENCRYPTION_ADMIN | 서버 관리 |
| VERSION_TOKEN_ADMIN | 서버 관리 |
| XA_RECOVER_ADMIN | 서버 관리 |
| APPLICATION_PASSWORD_ADMIN | 이중 비밀번호 관리 |
| AUDIT_ADMIN | Audit 로그 관리 |
권한 부여
- 사용자에게 권한을 부여할 때는 GRANT 명령을 사용한다.
- GRANT 명령은 각 권한의 특성(범위)에 따라 (GRANT 명령의) ON 절에 명시되는 오브젝트(DB나 테이블)의 내용이 바뀌어야 한다.
-
ON 키워드 뒤에는 어떤 DB의 어떤 오브젝트에 권한을 부여할지 결정할 수 있는데, 권한의 범위에 따라 사용하는 방법이 달라진다.
-
TO 키워드 뒤에는 권한을 부여할 대상 사용자를 명시한다.
GRANT privilege_list ON db.table TO 'user'@'host';
- MySQL 8.0 버전부터는 존재하지 않는 사용자에 대해 GRANT 명령이 실행되면 에러가 발생한다.
- 반드시 사용자를 먼저 생성하고 GRANT 명령으로 권한을 부여해야 한다.
- GRANT OPTION 권한은 다른 권한과 달리 GRANT 명령의 마지막에 WITH GRANT OPTION을 명시해서 부여한다.
글로벌 권한 부여
- 글로벌 권한은 특정 DB나 테이블에 부여될 수 없기 때문에, ON 절에는 항상
*.* 을 사용하게 된다.
- 예를 들어,
CREATE USER 나 CRATE ROLE 과 같은 글로벌 권한
- DB 단위나 오브젝트 단위로 부여할 수 있는 권한이 아니기 때문이다.
*.* 은 모든 DB의 모든 오브젝트(테이블과 스토어드 프로시저나 함수 등)을 포함해서 MySQL 서버 전체를 의미한다.
GRANT SUPER ON *.* TO 'user'@'localhost';
DB 권한 부여
- DB 권한은 특정 DB에 대해서만 권한을 부여하거나 서버에 존재하는 모든 DB에 대해 권한을 부여할 수 있다.
*.* 이나 db.* 모두 사용할 수 있다.- 여기서 DB라 함은 DB 내부에 존재하는 테이블뿐만 아니라 스토어드 프로그램들도 모두 포함한다.
- DB 권한만 부여하는 경우에는, DB 권한은 테이블에 대해 부여할 수 없기 때문에 테이블까지 명시할 수 없다.
- 오브젝트 권한처럼
db.table 로 오브젝트(테이블)까지 명시할 수 없다.
GRANT EVENT ON *.* TO 'user'@'localhost';
GRANT EVENT ON employees.* TO 'user'@'localhost';
테이블 권한
- 테이블 권한은 다음 상황에서 모두 권한을 부여하는 것이 가능하다.
- 서버의 모든 DB에 대해 권한 부여
- 특정 DB의 오브젝트에 대해서만 권한 부여
- 특정 DB의 특정 테이블에 대해서만 권한을 부여
- 테이블의 특정 칼럼에 대해서만 권한 부여
- 테이블의 특정 칼럼에 대해서만 권한을 부여하는 경우에는 GRANT 명령의 문법이 조금 달라진다.
- 칼럼에 부여할 수 있는 권한은 DELETE를 제외한 INSERT, UPDATE, SELECT 이다.
- 각 권한 뒤에 칼럼을 명시하는 형태로 부여한다.
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';
GRANT SELECT, INSERT, UPDATE(dept_name) ON employees.department TO 'user'@'localhost';
권한 설정의 주의사항
- 여러 가지 레벨이나 범위로 권한을 설정하는 것이 가능하지만 테이블이나 칼럼 단위의 권한은 잘 사용하지 않는다.
- 칼럼 하나에 대해서만 권한을 설정하더라도 전체적인 성능에 영향을 미칠 수 있다.
- 칼럼 단위의 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 칼럼에 대해서도 권한 체크를 하기 때문이다.
- 칼럼 단위의 접근 권한이 꼭 필요하다면 GRANT 명령으로 해결하기 보다는 테이블에서 권한을 허용하고자 하는 칼럼만으로 별도의 뷰(VIEW)를 만들어 사용하는 방법도 생각해볼 수 있다.
- 뷰도 하나의 테이블로 인식되기 때문에, 뷰를 만들어 두면 뷰의 칼럼에 대한 권한을 체크하지 않고 뷰 자체에 대한 권한만 체크하게 된다.
권한 확인
- 각 계정이나 권한에 부여된 권한이나 역할을 확인하기 위해서는 두 가지 방법이 있다.
SHOW GRANTS 명령을 사용- mysql DB의 권한 관련 테이블 (표 형태로 깔끔하게 볼 수 있다)
| 구분 | 저장소 테이블 | 설명 |
|---|
| 정적 권한 | mysql.user | 계정 정보 & 계정이나 역할에 부여된 글로벌 권한 |
| 정적 권한 | mysql.db | 계정이나 역할에 DB 단위로 부여된 권한 |
| 정적 권한 | mysql.tables_priv | 계정이나 역할에 테이블 단위로 부여된 권한 |
| 정적 권한 | mysql.columns_priv | 계정이나 역할에 칼럼 단위로 부여된 권한 |
| 정적 권한 | mysql.procs_priv | 계정이나 역할에 스토어드 프로그램 단위로 부여된 권한 |
| 동적 권한 | mysql.global_grants | 계정이나 역할에 부여되는 동적 글로벌 권한 |
Reference
참고 서적
📔 Real MySQL 8.0
