[MySQL] 계정 생성 및 권한 부여, 그리고 역할

본 포스팅은 MySQL 8.0 기준으로 작성된 글입니다.

출처 : Real MySQL 8.0 (저자. 백은빈, 이성욱)

---

1. MySQL 이 계정을 다루는 방법

MySQL 에서는 계정을 아래와 같은 형태로 관리합니다.

'blog_user'@'localhost'

@를 기준으로 앞에 'blog_user' 는 DB에 접속하기 위한 ID, 뒤에 'localhost' 는 DB에 접속 가능한 IP 혹은 도메인 정보가 들어가게 됩니다.

즉, 위 계정이 의미하는 것은 "blog_user 라는 ID 로 localhost 에서 접속이 가능한 계정" 라고 할 수 있겠습니다.

 

-- 계정 예시

-- 1. localhost 에서 접속 가능한 blog_user
'blog_user'@'localhost'

-- 2. 모든 IP 에서 접속 가능한 blog_user
'blog_user'@'%'

-- 3. 192.1.1.2 에서 접속 가능한 blog_user
'blog_user'@'192.1.1.2'

 

위 3개의 계정은 같은 'blog_user' 라는 ID 를 사용하지만, MySQL 내에서는 서로 다른 계정으로 인식합니다.

select host, user from mysql.user where user = 'blog_user' 쿼리 결과

각각의 blog_user 가 서로 다른 계정으로 인정되기 때문에 각각의 계정이 별도의 패스워드를 가질 수 있습니다.

그렇다면, 어떻게 패스워드 검증을 진행하게 되는 것일까.

 

동일한 ID 에 대해 패스워드를 검증하기 할 때, 접속이 가능한 IP 범위가 좁은 경우 가 우선순위를 가지게 됩니다.

예를 들어, 'blog_user'@'localhost' 계정의 패스워드가 '1234'이고, 'blog_user'@'%' 계정의 패스워드가 'abcd' 라고 한다면,

아래와 같이 접속을 시도해야 합니다.

만일, blog_user 라는 id 로 100.100.100.100 에서 접속하는데, 패스워드를 1234 로 사용한다면, MySQL 은 아래와 같은 결과를 반환합니다.

접속 실패 안내문 - MySQL Workbench

 

2. 계정 생성 방법

모든 DB가 그렇듯 MySQL 도 root 계정으로 쿼리를 통해 계정을 생성하게 됩니다.

기본적인 틀은 아래와 같습니다.

create user [ID] identified by [Password]

 

위와 같이 계정(user) 를 생성하면, 앞에서 설명한 접속 가능한 IP 는 자동으로 '%'(모든 IP에서 접속 가능) 로 적용됩니다.

계정 생성 과 그 결과

 

 

3. 권한 부여

MySQL 에는 3가지 개념의 권한이 존재합니다.

1. 객체 권한 : 데이터베이스나 테이블을 제어하는데 필요한 권한. 
2. 글로벌 권한 : 데이터베이스와 테이블 이외의 객체들을 제어하는데 필요한 권한. 주로 서버관리 관련 권한들이 있음.
3. 동적 권한 : 백업 및 복제 등, 5.7이전 버전에서 SUPER 로 사용됐던 권한을 잘게 쪼개어서 만든 권한. 백업관리자, 복구관리자 등 세세한 업무분담을 위해 8.0 버전 이후에 도입된 권한체계.

 

이 중에서 개발자 입장에서 가장 많이 사용하고 접하게 되는 객체 권한에 대해 이야기해볼까 합니다.

 

권한은 계정에 부여하는 자격과 같은 것으로, grant 라는 명령어로 부여할 수 있습니다.

grant [권한, 쉼표를 구분자로 여러 개 가능] on [DB].[스키마] to 'ID'@'host'

부여할 권한을 명시하고, 해당 권한을 어떤 데이터베이스의 어떤 스키마를 대상으로 적용할 것인지, 그리고 해당 권한을 어떤 계정에게 부여할 것이지를 나타내는 명령어 입니다.

 

grant 명령어의 예시는 아래와 같습니다.

-- localhost 접속에 대해 meat_shop 이라는 데이터베이스의 모든 스키마에 대해 모든 권한을 부여
grant all privileges on meat_shop.* to 'dev_user'@'localhost';

-- 외부 접속에 대해 meat_shop 이라는 데이터베이스의 partners 테이블에 대해 SELECT 권한만 부여
grant SELECT on meat_shop.partners to 'dev_user'@'%';

-- 외부 접속에 대해 meat_shop 이라는 데이터베이스의 products 테이블에 대해 SELECT, INSERT, UPDATE 권한을 부여
grant SELECT, INSERT, UPDATE on meat_shop.products to 'dev_user'@'%';

 

위 예시에서 보이는대로, grant 뒤에 'all privileges' 라고 하면, on 다음에 제시된 스키마에 대해 모든 권한을 부여한다는 의미입니다.

 

아래 쿼리를 실행하여 권한이 제대로 들어갔는지 확인할 수 있습니다.

-- 특정 DB 전체에 부여된 권한 확인
-- DB.* 로 권한을 부여하는 경우, DB 를 대상으로 모든 권한을 부여하는 것이기 때문에 아래 쿼리로 확인해야 함.
select * from mysql.db;

-- 특정 테이블에 부여된 권한 확인
-- DB.Table 로 권한을 부여하는 경우, 특정 테이블에 권한을 부여하는 것이기 때문에 아래 쿼리로 확인해야 함.
select * from mysql.tables_priv;

 

4. 역할(Role) 활용하기

역할은 MySQL 8.0 이후 버전부터 새로 생긴 개념으로 여러 권한을 묶어서 관리하는 객체입니다.

과거에는 계정을 권한과 직접 매핑해서 사용했다면, 8.0 이후부턴 역할을 만들어서 역할에 여러 권한을 부여하고, 계정에는 역할을 부여하는 식으로 권한을 관리할 수 있습니다.

역할을 사용하는 절차는 아래 단계를 따릅니다.

1. Role(역할) 을 생성
2. Role(역할) 에 grant 로 권한을 지정
3. User(계정) 에 grant 로 Role 을 지정

 

먼저, 역할을 생성해보겠습니다.

역할은 MySQL 내부적으로 계정과 관리체계가 동일하기 때문에, 생성하는 명령어도 계정과 매우 유사합니다.

create role [이름];

 

role 생성 예시는 다음과 같습니다.

-- 조회용 role 을 생성
create role 'READER';

-- 명령용 role 을 생성
create role 'WRITER';

 

이렇게 Role 을 생성하면, 아직 어떠한 권한도 부여받지 못한 빈 역할이 생성된 것입니다.

Role 생성 여부를 확인하기 위해선, 계정을 확인하기 위해 실행한 쿼리와 동일한 쿼리를 실행하면 됩니다.

역할 또한 계정과 같은 방식으로 관리된다.

 

그리고 생성한 Role 에 각각 원하는 권한을 부여할 수 있습니다.

-- READER 역할에 SELECT 권한 추가
grant SELECT on meat_shop.* to 'READER';

-- WRITER 역할에 UPDATE, DELETE, INSERT 권한 추가
grant UPDATE,DELETE,INSERT on meat_shop.* to 'WRITER';

 

READER 역할에는 Select / WRITER 역할에는 Insert, Update, Delete 권한 부여

 

권한이 부여된 역할을 원하는 계정에 매핑해주면, 역할을 활용한 권한 부여가 완료됩니다.

-- 모든 IP 에서 접속이 가능한 user_role 계정에 READER 역할 부여
-- 결과적으로 SELECT 만 가능
grant READER to 'user_role'@'%';

-- localhost 에서 접속이 가능한 user_role 계정에 READER, WRITER 역할 부여
-- 결과적으로 SELECT, INSERT, UPDATE, DELETE 모두 가능
grant WRITER, READER to 'user_role'@'localhost';

계정에 역할을 부여한 결과 조회

역할과 계정은 mysql 내부적으로 동일하게 관리되기 때문에 생성에 제약은 별도로 없지만, 혼동을 방지하기 위해 역할의 경우 이름 앞에 구별할 수 있는 prefix (예를 들면 'role_') 를 붙이는 것을 권장합니다.

 

이제 Role 을 적용했으니, Role 이 적용된 사용자로 접속해 쿼리를 실행하면 되지만, 실행되지 않습니다.(?!)

 

SELECT 가 실패한 이유를 살펴보면 아래와 같다고 합니다.

Error Code: 1142. SELECT command denied to user 'user_role'@'172.17.0.1' for table 'partners'

 

Role 의 경우, 계정에 접속하여 활성화하는 과정을 거쳐야 적용되게 됩니다.

현재 계정에 적용되어 있는 Role 은 아래 쿼리를 통해 확인해 볼 수 있습니다.

select current_role();

적용된 Role 이 없음을 나타내는 결과 (NONE)

 

부여된 Role 을 적용하기 위해서는 아래 명령어를 한 번 실행해야 합니다.

-- READER role 설정
SET ROLE 'READER';

Role 을 부여한 후, current_role() 결과

 

이 과정을 거치고 나면, SELECT 쿼리가 정상적으로 실행됩니다.

 

 

하지만, 접속했던 계정을 로그아웃하고, 다시 접속해서 동일하게 SELECT 쿼리를 실행하면 맨 처음과 같이 SELECT 가 실패하게 됩니다.

즉, 접속을 할 때 마다 SET ROLE 명령을 실행해줘야 한다는 것인데 이는 너무 불편한 점이겠죠.

 

이는, MySQL 의 글로벌 설정 중 Login 시점에 역할을 활성화하는 설정이 꺼져 있기 때문에 발생하는 일입니다.

그래서 아래와 같이 환경설정을 변경해준다면, 별도의 SET ROLE 명령 없이도, 적용된 Role 을 활용할 수 있게됩니다.

-- 해당 명령은 root 계정으로 실행해야 됨.
SET GLOBAL activate_all_roles_on_login=ON;