[MySQL] 사용자- 권한(Privilege)과 역할(Role)
1. 권한(Privilege)
1-1. 분류
정적권한(Static Privileges) :
- MySQL 서버의 소스코드에 고정적으로 명시되어 있는 권한
- 예를들어 SELECT, INSERT, UPDATE, DELETE 등의 기본적인 데이터베이스 작업에 대한 권한
- MySQL이 시작될 때부터 서버 내에서 항상 사용 가능
- 글로벌 권한(Global Privileges) : 데이터베이스나 테이블 이외의 객체에 적용되는 권한
- 객체 권한 (Object Privileges) : 데이터베이스나 테이블을 제어하는 데 필요한 권한
동적권한(Dynamic Privileges) :
- 서버가 시작될 때 기본으로 존재하지 않고 컴포넌트나 플러그인을 설치하거나 특정 기능이 추가될 때 동적으로 생성되는 권한
- MySQL의 기능 확장에 따라 새로운 권한이 추가될 수 있음
- 일부는 서버에 명시되어 있기도 함
- MySQL 서버를 재시작하지 않고도 동적으로 권한을 부여하거나 관리할 수 있게해 기능의 유연한 확장을 가능하게 함
1-2. 권한 확인, 부여
각 계정이나 권한에 부여된 권한 혹은 역할 확인
(* 이 전에 'migu'라는 계정을 새로 만들었다)
SHOW GRANTS FOR 'migu'@'%';
USAGE :
데이터베이스에 접근할 수 있는 기본 권한을 가진 상태
사용자는 MySQL 서버에 접속할 수 있지만, 데이터베이스나 테이블에 대해 실제 작업을 수행할 수 있는 권한은 없다.
사용자 생성에 따른 기본적인 존재를 보장
권한 부여 방법
GRANT 권한종류 ON 권한범위 TO 유저
권한 범위에 따른 권한 부여 방법
(1). 글로벌 권한
GRANT CREATE USER ON *.* TO 'migu'@'%';
글로벌 권한은 특정 DB나 테이블에 부여될 수 없기 때문에 ON 절에 항상 ' *.* ' 를 사용
( *.* = 모든 DB의 모든 오브젝트(테이블, 스토어드 프로시저, 함수 등)를 포함해 전체 MySQL 서버를 의미)
migu 계정에 CREATE USER 권한을 주고
해당 계정으로 접속해 계정생성 가능 여부 확인 > 됨
(2). DB 권한
GRANT EVENT ON *.* TO 'migu'@'%';
GRANT EVENT ON sales.* TO 'migu'@'%';
DB 권한은 특정 DB에 대해서만 or 서버에 존재하는 모든 DB에 대해서 권한을 부여할 수 있음
따라서 *.* / DB.*을 모두 사용할 수 있지만 DB권한만 부여하는 경우 테이블에 대해 부여할 수 없기 때문에 테이블까지 명시할 수 없음
여기서 DB란?
여러 테이블과 그 외의 오브젝트(예: 뷰, 스토어드 프로시저, 이벤트 등)를 포함하는 논리적 그룹
(3). 테이블 권한
GRANT SELECT, DELETE ON *.* TO 'migu'@'%';
GRANT SELECT, DELETE ON sales.* TO 'migu'@'%';
GRANT SELECT, DELETE ON sales.orders TO 'migu'@'%';
테이블 권한은 특정 DB의 오브젝트에 대해서만 or 서버에 존재하는 모든 DB에 대해서 or 특정 DB의 특정 오브젝트에 대해서만
권한을 부여하는 것이 가능
GRANT INSERT, UPDATE(dept_name) ON sales.products TO 'migu'@'%';
테이블 특정 컬럼에 대해서만 권한을 부여할 경우 위와 같이 괄호를 사용할 수 있음
위의 경우 INSERT 명령은 모든 컬럼에 대해 수행할 수 있으나, UPDATE 명령의 경우 dept_name 컬럼에 대해서만 수행 가능
이러한 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 컬럼에 대해서도 권한 체크를 하기 때문에 전체적으로 성능에 영향을 미칠 수 있기 때문에 지양하고 별도의 뷰를 만드는 방법을 고려해볼 수 있음
2. 역할(Role)
2-1. 역할이란?
역할(Role) :
- 권한을 묶어서 사용할 수 있게 하는 것
- 계정에 역할을 부여하면 해당 계정은 역할에 속한 권한을 가지게 됨
- 역할이 활성화 되어있을 때만 사용할 수 있는 상태가 됨
- 서버 내부적으로 역할과 계정은 동일한 객체로 취급 됨 ( mysql DB의 user 테이블을 살펴보면 권한과 사용자 계정이 구분없이 저장된 것을 확인할 수 있음)
2-2. 역할 생성 및 사용
(1). activate_all_roles_on_login 시스템 변수 설정
SET GLOBAL activate_all_roles_on_login=ON;
역할이 부여된 계정을 로그아웃 했다가 다시 로그인 하는 경우 역할이 활성화 되지 않은 상태로 초기화 되어 매번
SET ROLE '역할명' 명령을 실행해 해당 역할을 활성화해야 하는데,
activate_all_roles_on_login 시스템 변수가 ON으로 되어 있다면 사용자가 MySQL 에 로그인 할 때 자동으로 역할을 활성화 하게 됨
(2). 역할 생성
CREATE ROLE
-> role_read,
-> role_write;- CREATE ROLE '역할명' 으로 역할 정의
- 호스트 부분을 명시하지 않은 경우 자동으로 모든 호스트(%)가 추가됨
GRANT SELECT ON *.* TO role_read;
GRANT INSERT, DELETE, UPDATE ON *.* TO role_write;- GRANT 명령으로 각 역할에 대해 실질적인 권한 부여
각각의 역할에 부여된 권한을 확인해보면 명령한대로 잘 부여되어 있음을 확인할 수 있다.
GRANT role_read, role_write TO 'migu'@'%';- GRANT 명령으로 계정에 역할 부여
시스템 변수를 변경해둔 대로 로그인과 동시에 각 역할이 활성화 되었음을 확인할 수 있다.