MariaDB - 10.3 → 10.6 업그레이드 mysql.user 접근 오류와 해결 방법

MariaDB 10.3을 사용 중 EOL(End of Life)이 되어 안정성을 위해 10.6 LTS(Long-Term Support) 으로 업그레이드를 했습니다.

그러나 업그레이드 후 mysql.user 테이블에 접근하려고 하자, 아래와 같은 에러가 발생했습니다

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

MariaDB 10.4부터 테이블 구조가 변경된 것이 문제의 원인이었습니다.

이번 글에서는 MariaDB 업그레이드 과정에서 발생할 수 있는 mysql.user 접근 오류의 원인과 해결 방법을 소개합니다.

---

1. 업그레이드 배경 및 주요 변화

1-1. 업그레이드 이유

MariaDB 10.3은 더 이상 보안 패치나 업데이트가 제공되지 않으므로, 장기 지원 버전인 10.6으로 업그레이드해야 했습니다.

1-2. 주요 변화: mysql.user → 뷰(View)로 전환

• 기존(10.3 이하): mysql.user는 사용자 계정과 권한 정보를 물리적으로 저장하는 테이블
• 변경(10.4 이상): mysql.user는 더 이상 직접적인 테이블이 아니며, mysql.global_priv를 참조하는 뷰(View)로 변경

이로 인해 기존 방식으로 권한을 관리하려 하면 접근 불가 에러가 발생할 수 있습니다.

---

2. 문제 발생: 업그레이드 후 mysql.user 접근 불가

MariaDB 10.6 업그레이드 후 기존처럼 mysql.user 테이블에 접근하려 하면 다음과 같은 에러가 발생할 수 있습니다

USE mysql;
SELECT * FROM user;

-- 결과:
ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

---

3. 해결 방법: tables_priv에 권한 추가 및 테이블 재조회

MariaDB 10.4 이상에서는 mysql.user 뷰가 mysql.global_priv 테이블을 참조하도록 설계되어 있습니다.

이 뷰가 정상적으로 작동하려면, mariadb.sys 계정이 mysql.global_priv를 읽을 수 있는 권한이 필요합니다.

3-1. tables_priv에 권한 추가

아래 명령어를 실행하여 mariadb.sys 계정에 필요한 권한을 추가합니다.

INSERT INTO `mysql.tables_priv` 
(`Host`, `Db`, `User`, `Table_name`, `Grantor`, `Timestamp`, `Table_priv`, `Column_priv`) 
VALUES 
('localhost', 'mysql', 'mariadb.sys', 'global_priv', 'root@localhost', '0000-00-00 00:00:00', 'Select', '');

 

3-2. MariaDB 서버 재시작

변경 사항을 적용하려면 MariaDB를 재시작해야 합니다.

sudo systemctl restart mariadb
또는
Windows의 경우 서비스 확인

 

3-3. mysql.user 재조회

다시 mysql.user를 조회해 문제가 해결되었는지 확인합니다.

USE mysql;
SELECT * FROM user;

---

4. mysql.global_priv를 직접 조회하는 방법

mysql.global_priv는 사용자 계정과 권한 정보를 저장하는 테이블로 직접 조회하여 권한 상태를 확인할 수도 있습니다.

권한 조회 쿼리

SELECT
  Host,
  User,
  JSON_VALUE(Priv, '$.plugin') AS Plugin,
  JSON_VALUE(Priv, '$.authentication_string') AS Authentication_string,
  JSON_VALUE(Priv, '$.access') AS Access_privileges
FROM
  mysql.global_priv;

결과 예시

Host	User	Plugin	Authentication_string	Access_privileges
localhost	root	mysql_native_password	******	3
192.168.0.1	test_user	ed25519	******	32769

Access_privileges 값은 비트 필드로, 각 비트는 특정 권한을 나타냅니다.

이를 분석해 사용자의 권한 상태를 확인할 수 있습니다.

---

5. 계정 및 권한 추가 방법

5-1. mysql.global_priv 또는 tables_priv를 직접 수정

계정 권한을 추가하려면 mysql.global_priv 또는 mysql.tables_priv에 직접 데이터를 삽입하거나 업데이트할 수 있습니다.

아래 쿼리는 new_user라는 계정을 생성하고 SELECT와 INSERT 권한을 부여합니다:

INSERT INTO mysql.global_priv
(Host, User, Priv, authentication_string)
VALUES (
  'localhost',
  'new_user',
  JSON_OBJECT(
    'access', 3, -- SELECT(1) + INSERT(2) = 3
    'plugin', 'mysql_native_password',
    'authentication_string', PASSWORD('your_password')
  ),
  PASSWORD('your_password')
);

 

5-2. CREATE USER 및 GRANT 문 활용

MariaDB는 여전히 기존 방식의 SQL 명령어를 지원합니다. 이를 활용해 계정 및 권한을 생성할 수 있습니다:

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT SELECT, INSERT ON *.* TO 'new_user'@'localhost';

이 방식은 직접 테이블을 수정하는 것보다 더 안전하며, 권장되는 방법입니다.

---

6. 주의사항: mariadb.sys 계정은 삭제하면 안 됨

mariadb.sys는 MariaDB의 내부 시스템 계정으로, 데이터베이스 모니터링 및 관리 작업에 사용됩니다.

이 계정을 삭제하거나 권한을 변경하면 시스템의 정상 동작에 문제가 발생할 수 있습니다.