1. 개요
MySQL에서 사용자 계정을 생성하고 관리하는 방법은 다른 DBMS와 몇 가지 차이점이 있다. MySQL의 사용자 계정은 단순히 사용자의 아이디뿐만 아니라 해당 사용자가 접속하는 IP 주소를 기반으로 식별한다. 이는 보안을 강화하기 위한 중요한 특징으로, 서버에 접근할 수 있는 사용자와 그 위치를 정확히 제어할 수 있게 해준다.
MySQL 8.0 버전부터는 권한 관리 방식에 '역할(Role)'이라는 개념이 도입되었다. 이전 버전에서는 각 사용자 계정에 개별적으로 권한을 할당해야 했지만, 8.0 버전에서는 여러 권한을 하나의 역할로 묶어서 관리할 수 있게 되었다. 이를 통해 권한 관리의 효율성과 유연성이 크게 향상되었다. 예를 들어, 관리자 역할에는 데이터베이스 생성, 수정, 삭제 권한을 모두 할당할 수 있고, 사용자는 이 역할을 부여받아 필요한 모든 권한을 한 번에 얻을 수 있다.
데이터베이스 서버의 보안이 점점 더 중요해지고 있는 현대에서, MySQL의 계정 식별 방식과 권한, 역할에 대해 정확히 이해하고 있어야 한다. 계정을 생성할 때는 사용자의 신원 뿐만 아니라 접속 위치도 고려해야 하며, 권한과 역할을 적절히 할당하여 데이터의 안전성을 확보하는 것이 필수적이다. 이러한 방식으로 MySQL은 사용자의 접근 제어와 권한 관리를 보다 체계적이고 안전하게 수행할 수 있다.
2. 사용자 식별
MySQL에서 사용자 계정의 개념은 사용자의 아이디와 접속 지점을 모두 포함하는 것으로, 이는 MySQL의 보안 및 접근 제어 메커니즘의 중요한 부분이다. 예를 들어, 사용자 아이디가 'svc_id'이고 IP 주소가 '127.0.0.1'인 경우, 해당 사용자 계정은 'svc_id'@'127.0.0.1'로 표현된다. 이는 'svc_id' 사용자가 '127.0.0.1' IP 주소를 가진 컴퓨터에서만 MySQL 서버에 접속할 수 있음을 의미한다.
모든 외부 컴퓨터(host)에서 접속을 허용하려면, 호스트 부분을 '%'로 설정한다. 이는 해당 사용자 아이디로 어느 IP 주소나 호스트에서나 접속할 수 있음을 의미한다. 예를 들어, 'svc_id'@'%'는 'svc_id' 사용자가 어느 네트워크 위치에서든 접속할 수 있도록 허용한다.
MySQL에서는 동일한 아이디를 가진 여러 사용자 계정이 존재할 수 있다. 이 경우, 서버는 권한이나 계정 정보의 범위가 더 작은 사용자 계정을 선택한다. 이는 특정 사용자에게 특정 호스트 또는 네트워크 범위에 대한 접근을 제한하기 위해 설계된 기능이다. 예를 들어, 'svc_id'@'localhost'와 'svc_id'@'%' 두 계정이 있다면, 'svc_id'로 접속을 시도할 경우 무조건 'svc_id'@'localhost' 계정이 선택된다.
3. 사용자 계정 관리
MySQL에서는 크게 시스템 계정과 일반 계정 두 종류의 사용자 계정을 구분할 수 있다.
1. 시스템 계정과 일반 계정의 차이점
- 시스템 계정(System Account): SYSTEM_USER 권한을
가진계정으로, 주로데이터베이스 서버 관리자를 위한 계정이다. 이 계정은 일반 계정보다 훨씬 더 광범위한 권한을 가지며, 사용자 계정의 생성, 삭제, 권한 부여 및 제거, 실행 중인 쿼리의 강제 종료 등 데이터베이스 시스템의 핵심적인 관리 작업을 수행할 수 있다. - 일반 계정(Regular Account): SYSTEM_USER 권한이
없는계정으로, 주로응용 프로그램이나개발자들을 위한 계정이다. 이 계정은 제한된 권한을 가지고 있어, 데이터베이스의 일부 데이터에 접근하거나 특정 작업을 수행할 수 있는 권한만을 부여받는다.
2. 계정 생성 및 권한 설정
- MySQL에서는
'CREATE USER'명령을 통해 새로운사용자 계정을생성할 수 있다. 계정 생성 시에는 인증 방식, 비밀번호, SSL 옵션, 계정 잠금 여부 등 다양한 옵션을 설정할 수 있다. - 또한,
'GRANT'명령을 사용하여 계정에특정 권한을부여할 수 있다. 이를 통해 사용자가 수행할 수 있는 작업의 범위를 결정할 수 있다.
예를 들어, 아래의 SQL 명령문은 'user'라는 아이디를 가진 새로운 사용자를 생성하며, 'mysql_native_password' 인증 방식을 사용하고, 30일마다 비밀번호를 변경해야 하며, 계정은 잠기지 않는 설정을 포함한다:
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
PASSWORD REQUIRE CURRENT DEFAULT;-
IDENTIFIED WITH: 이 옵션은 사용자의 인증 방식을 지정하는 데 사용된다. 사용 가능한 인증 방식은 다음과 같다:
mysql_native_password: 이는 MySQL의 기본 인증 방식으로, 전통적인 방식을 사용한다.caching_sha2_password: SHA-256 기반의 강화된 인증 방식으로, 보안성이 높다.sha256_password: 이 역시 SHA-256을 사용하는 강화된 인증 방식으로,caching_sha2_password와 유사하다.
-
REQUIRE: MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할 지 여부를 정한다. 만약 별도로 설정하지 않으면 비암호화 채널로 연결한다.
-
PASSWORD EXPIRE: 이 옵션은 비밀번호의 만료 정책을 설정한다. 가능한 설정 값은 다음과 같다:
INTERVAL N DAY/MONTH/YEAR: 설정된 기간(일/월/년)이 지난 후 비밀번호가 만료된다.DEFAULT: 시스템의 기본 만료 정책을 따른다.NEVER: 비밀번호가 만료되지 않도록 설정한다.
-
ACCOUNT LOCK / UNLOCK: 계정의 잠금 상태를 관리하는 옵션이다. 설정 가능한 값은 다음과 같다:
LOCK: 사용자 계정을 잠근다.UNLOCK: 잠겨 있는 계정을 해제한다.
-
PASSWORD HISTORY: 이 옵션은 비밀번호 변경 이력을 저장하여, 이전 비밀번호의 재사용을 제한한다. 설정 가능한 값은 다음과 같다:
N: 최근 N개의 비밀번호를 저장하여 재사용을 방지한다.
-
PASSWORD REUSE INTERVAL: 이전 비밀번호의 재사용 간격을 설정하는 옵션이다. 설정 가능한 값은 다음과 같다:
N DAY/MONTH/YEAR: 지정된 기간 동안 이전 비밀번호의 재사용을 금지한다.
-
PASSWORD REQUIRE: 비밀번호 변경 시 현재 비밀번호 입력 여부를 결정하는 옵션이다. 설정 가능한 값은 다음과 같다:
CURRENT: 비밀번호 변경 시 현재 비밀번호를 먼저 입력하도록 요구한다.OPTIONAL: 현재 비밀번호 입력을 필요로 하지 않는다.DEFAULT: 시스템의 기본 설정을 따른다.
4. 비밀번호 관리
MySQL 서버에서 비밀번호 정책을 관리하고 강화하는 것은 데이터베이스 보안의 핵심적인 부분이다. 이를 위해 MySQL은 validate_password 컴포넌트를 제공한다. 이 컴포넌트는 비밀번호가 쉽게 유추될 수 있는 단어의 사용을 방지하고, 특정 글자 조합을 강제하는 등, 비밀번호의 유효성을 체크하는 규칙을 적용하는 데 사용된다.
비밀번호 정책은 세 가지 수준 중에서 선택할 수 있다: low, medium, strong. 기본 설정 값은 medium이다. 각 수준은 비밀번호의 강도와 복잡도를 결정한다:
- Low: 이 수준은 비교적 낮은 보안 수준을 제공한다. 기본적인
길이요구사항이나 간단한 조합 규칙만 적용될 수 있다. - Medium: 이는 중간 수준의 보안을 제공한다. 보다 강화된 길이 요구사항, 문자 조합, 그리고 일부
특수 문자의 사용이 요구될 수 있다. - Strong: 가장 높은 수준의 보안을 제공한다. 긴 비밀번호 길이, 복잡한 문자 조합, 다양한 특수 문자,
금칙어의 사용이 필요하다.
MySQL 서버 관리자는 이러한 비밀번호 정책을 적용하여 사용자가 보다 강력하고 안전한 비밀번호를 설정하도록 유도할 수 있다. 이는 전체 데이터베이스 시스템의 보안을 강화하는 데 중요한 역할을 한다. 따라서 보안이 중요한 환경에서는 strong 수준의 비밀번호 정책을 적용하는 것이 바람직하다.
일반적으로 많은 응용 프로그램 서버들은 데이터베이스 서버를 공용으로 사용한다. 이러한 구현 특성으로 인해, 특히 서비스가 실행 중인 상태에서는 데이터베이스 계정의 비밀번호를 변경하기가 어렵다. 실제로, 많은 서비스에서 데이터베이스 계정의 비밀번호는 초기 설정 이후 몇 년 동안 변경되지 않는 경우가 흔하다.
보안적 측면에서 볼 때, 데이터베이스 계정의 비밀번호는 주기적으로 변경하는 것이 바람직하다. 그러나 이전까지는 서비스를 멈추지 않고서는 비밀번호를 변경하는 것이 어려웠다.
이러한 문제를 해결하기 위해, MySQL 8.0 버전부터는 계정의 비밀번호로 두 개의 값을 동시에 사용할 수 있는 '이중 비밀번호(Dual Password)' 기능을 도입했다. 이 기능은 '프라이머리(Primary)'와 '세컨더리(Secondary)'라는 두 개의 비밀번호를 계정에 설정할 수 있게 해준다.
이중 비밀번호 기능을 사용하면, 한 비밀번호를 '프라이머리'로 다른 하나를 '세컨더리'로 설정하여, 서비스 운영 중에도 비밀번호를 안전하게 변경할 수 있다. 예를 들어, '프라이머리' 비밀번호를 새로운 비밀번호로 변경하고, '세컨더리' 비밀번호를 기존 비밀번호로 유지할 수 있다. 이후에 시스템이 새로운 '프라이머리' 비밀번호에 완전히 적응할 때까지 '세컨더리' 비밀번호를 유지한 후, 나중에 안전하게 제거할 수 있다.
이 기능은 데이터베이스 보안을 강화하면서도 서비스의 연속성을 유지할 수 있게 해주어, 데이터베이스 관리자들에게 매우 유용한 도구가 되었다.
5. 권한
MySQL에서 사용자에게 부여되는 권한은 크게 글로벌 권한과 객체 권한으로 구분된다. 이 구분은 MySQL 5.7 버전까지 적용되며, MySQL 8.0 버전부터는 추가적인 권한 분류가 도입되었다.
MySQL 5.7 버전 이하의 권한 분류:
- 글로벌 권한(Global Permissions): 이는 데이터베이스나 테이블과 같은 특정 객체에 적용되지 않는 권한이다. 글로벌 권한은
서버 전체에 영향을 미치며,GRANT명령을 사용할 때 특정 객체를 명시하지 않고 부여한다. - 객체 권한(Object Permissions): 이는 특정
데이터베이스나테이블등의 객체를 제어하는 데 필요한 권한이다. 객체 권한은GRANT명령으로 권한을 부여할 때 반드시 특정 객체를명시해야 한다.
MySQL 8.0 버전의 권한 변경:
- MySQL 8.0 버전부터는 동적 권한(Dynamic Permissions)이라는 새로운 개념이 추가되었다. 이는 MySQL 서버가
시작될 때동적으로 생성되는 권한을 의미한다. - 기존의 권한은 정적 권한(Static Permissions)으로 분류되며, 이는 MySQL 서버의
소스코드에고정적으로 명시된 권한을 의미한다.
권한 부여 방법:
- 사용자에게 권한을 부여할 때는 주로
GRANT명령을 사용한다. 이 명령을 통해 사용자에게 글로벌 권한, 데이터베이스 또는 테이블 권한, 특정 칼럼에 대한 권한 등을 부여할 수 있다. - MySQL 8.0 버전부터는
존재하지 않는 사용자에 대한 GRANT 명령 실행 시 에러가 발생하므로, 사용자를 먼저 생성한 후 GRANT 명령으로 권한을 부여해야 한다.
글로벌 권한을 부여하려면 다음과 같은 명령을 사용한다:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname';글로벌 권한은 서버 전체에 영향을 미치는 권한이다. 위 명령은 'username' 사용자에게 서버의 모든 데이터베이스와 테이블에 대한 모든 권한을 부여한다. *.*는 모든 데이터베이스와 그 안의 모든 테이블을 나타낸다.
데이터베이스에 대한 권한을 부여할 때는 다음과 같이 명령을 사용한다:
GRANT SELECT, INSERT ON database.* TO 'username'@'hostname';데이터베이스 권한은 특정 데이터베이스에 대한 권한을 설정한다. 위 명령은 'username' 사용자에게 지정된 데이터베이스에 대한 SELECT 및 INSERT 권한만 부여한다. 이는 사용자가 해당 데이터베이스에서 데이터를 조회하거나 추가할 수 있음을 의미한다.
특정 테이블이나 특정 칼럼에 대한 권한을 부여하려면 다음과 같이 할 수 있다:
GRANT SELECT(column1), INSERT ON database.table TO 'username'@'hostname';테이블 및 특정 칼럼 권한은 더 세분화된 권한 제어를 가능하게 한다. 위 명령은 'username' 사용자에게 특정 테이블과 특정 테이블의 지정된 칼럼에 대한 SELECT 및 INSERT 권한을 부여한다. 이는 사용자가 table의 column1에 대해서만 데이터를 조회할 수 있고, table에 데이터를 추가할 수 있음을 의미한다.
이러한 방식으로 MySQL에서는 다양한 수준의 접근 제어와 권한 관리를 할 수 있어, 데이터베이스의 보안과 효율적인 관리를 가능하게 한다.
6. 역할(Role)
MySQL 8.0 버전부터는 권한을 묶어서 관리하는 '역할(Role)' 기능이 도입되었다. 이 기능은 데이터베이스 보안 관리를 더욱 효율적으로 만들어준다. 역할은 사용자 계정과 유사한 형태를 가지고 있으며, MySQL 서버 내부적으로는 역할과 계정을 동일한 객체로 취급한다.
먼저, CREATE ROLE 명령을 사용하여 role_emp_read와 role_emp_write라는 두 역할을 정의한다. 이때 정의된 역할은 권한이 없는 빈 껍데기에 불과하다.
CREATE ROLE role_emp_read;
CREATE ROLE role_emp_write;이후, GRANT 명령을 사용하여 각 역할에 실질적인 권한을 부여한다. 예를 들어, role_emp_read 역할에는 employees 데이터베이스의 모든 객체에 대해 읽기(SELECT) 권한을 부여하고, role_emp_write 역할에는 데이터 변경(INSERT, UPDATE, DELETE) 권한을 부여한다.
GRANT SELECT ON employees.* TO role_emp_read;
GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;다음으로, CREATE USER 명령으로 reader와 writer라는 두 계정을 생성한다.
CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'password';
CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'password';생성된 이 계정들은 아직 어떤 권한도 부여받지 않았으므로, employees 데이터베이스에 대해 어떠한 쿼리도 실행할 수 없는 상태다. 이제 GRANT 명령으로 reader 계정에 role_emp_read 역할을, writer 계정에는 role_emp_read와 role_emp_write 역할을 부여한다.
GRANT role_emp_read TO reader@'127.0.0.1';
GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1';하지만 이 상태에서는 reader나 writer 계정으로 데이터를 조회하거나 변경할 수 없다. 역할을 사용하기 위해서는 SET ROLE 명령을 실행하여 해당 역할을 활성화해야 한다.
SET ROLE 'role_emp_read';
SET ROLE 'role_emp_write';mysql 서버의 역할이 불편하고 수동적으로 보이는데, 이는 mysql 서버의 역할이 자동으로 활성화되지 않게 설정돼있기 때문이다. activate_all_roles_on_login 시스템 변수를 ON으로 설정하면 사용자가 MySQL 서버에 로그인할 때 역할이 자동으로 활성화된다.
SET GLOBAL activate_all_roles_on_login = ON;이처럼 MySQL 8.0에서 도입된 역할 기능은 보안 관리와 권한 관리를 보다 효율적이고 유연하게 만들어준다. 관리자는 이 기능을 활용하여 데이터베이스 보안을 강화하고, 사용자 계정 관리의 복잡성을 줄일 수 있다.
