Last Updated on 1월 13, 2024 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 8.0 버전에서의 User Comment 와 User Attribute 기능에 대해서 내용을 확인해보려고 합니다.
Comment 와 Attribute
MySQL 8.0.21 버전에서 user 생성이나 변경(alter) 시 user comment 와 user attribute 를 추가할 수 있는 기능이 추가되었습니다.
이제 유저를 생성하거나 변경(alter) 할 때 유저에 대한 추가적인 사용자 정의 정보를 포함할 수 있게 되었습니다.
DB 유저에 대한 추가적인 정보를 포함할 수 있는 좋은 기능 외에 포스팅에서 소개되는 내용 이외에 해당 기능을 통해 다양하게 활용할 수 있는 방법이 여러가지 있을 것으로 생각 하고 있습니다.
Comment
User Comment 는 유저 생성이나 변경 시 추가할 수 있는 8.0.21에 새롭게 추가된 기능으로 계속 사용하던 테이블 코멘트 또는 컬럼 코멘트 와 같이 자유 형식으로 입력할 수 있는 기능입니다.
•생성시
create user 'jade'@'localhost' identified with 'mysql_native_password' by 'jade' comment '해당 유저는 db팀 dba입니다.';
위와 같이 유저를 생성할 때 코멘트를 추가할 수 있습니다.
또는 변경(alter) 시에도 아래와 같이 추가(또는 변경) 할 수 있습니다.
• 변경시
-- 변경
alter user 'jade'@'localhost' comment '해당 유저는 쿠폰팀 BE 개발자입니다.';
-- 조회
mysql> select * from information_schema.user_attributes
-> where user = 'jade'\G
*************************** 1. row ***************************
USER: jade
HOST: localhost
ATTRIBUTE: {"comment": "해당 유저는 쿠폰팀 BE 개발자입니다."}
추가한 Comment 를 확인하는 컬럼을 살펴보면 information_schema.user_attributes 의 ATTRIBUTE 인 것을 확인할 수 있습니다. 즉 comment 옵션을 통해서 입력된 내용도 ATTRIBUTE 의 속성값의 일부로 관리되게 됩니다.
그래서 user comment 와 user attributes 는 ATTRIBUTE 열에 함께 저장됩니다.
추가한 속성 정보는 mysql.user 나 information_schema.user_attributes 에서 확인할 수 있습니다.
ATTRIBUTE
User Attribute 은 하나 이상의 키-값 쌍으로 구성된 JSON 개체이며, CREATE USER 명령어에서 ATTRIBUTE 사용시 'json_object'를 포함하여 설정하게 됩니다.
유저 생성시 아래와 같이 ATTRIBUTE 옵션을 사용하여 생성을 합니다.
• 유저 생성
-- 유저 생성
CREATE USER 'jade'@'localhost' identified with 'mysql_native_password' by 'jade'
ATTRIBUTE '{"emp_name":"정현호", "team":"dbteam","account_expire":"2022-07-30","request_number":"ACCOUNT-123"}';
-- 확인
select user,host,user_attributes from mysql.user
where user='jade'\G
*************************** 1. row ***************************
user: jade
host: localhost
user_attributes: {"metadata": {"team": "dbteam", "emp_name": "정현호", "account_expire": "2022-07-30", "request_number": "ACCOUNT-123"}}
변경시에는 alter user 를 수행하며 됩니다.
• 유저 속성 변경
-- 유저 속성 변경
alter user 'jade'@'localhost'
attribute '{"emp_name":"정현호", "team":"검색개발팀","account_expire":"2022-07-30","request_number":"account-456"}';
-- 확인
select user,host,user_attributes from mysql.user
where user='jade'\G
*************************** 1. row ***************************
user: jade
host: localhost
user_attributes: {"metadata": {"team": "검색개발팀", "emp_name": "정현호", "account_expire": "2022-07-30", "request_number": "account-456"}}
같은 SQL 문에서 COMMENT 와 ATTRIBUTE 를 함께 사용할 수는 없습니다.(둘다 사용하면 에러 발생) COMMENT 는 ATTRIBUTE 에서 유일하게 별도로 옵션으로 존재하지만, 관리되는 정보 항목에서는 ATTRIBUTE 중 하나이기 때문입니다.
이렇게 attribute 를 통해서 정보를 입력하면 여러가지 장점 중에 또 한가지 장점으로는 사용중인 시스템에 유저에 대한 prefix 나 suffix 가 없을 경우에 서비스 계정과 개인계정(개발자 계정) 의 구분을 쉽게 할 수 있다는 것입니다.
예를 들어 개인 계정에 대해서는 이와 같이 attribute 를 입력하여 attribute 가 null 인 사용자와 내용이 있는 사용자를 구분하여 서비스 계정과 개인계정을 명시적으로 구분하거나 attribute 에서 "type" : "service_account" 와 같이 계정의 타입에 대한 정보를 넣음으로써 서비스 계정과 개인계정 또는 배치 계정, Datalake 계정 등 계정의 유형 등을 명시적으로 DB 내에서 조회를 통해서 명확히 구분 및 확인이 가능할 것입니다.
별도의 장표나 관리 테이블을 통해서 계정의 특성 등을 관리하였다면 이와 같이 attribute 기능을 통해서 MySQL 자체에서 정보를 입력함으로써 더 손쉽고 더 적은 관리 비용이 소모될 것으로 예상됩니다.
사용자 생성시 속성과 주석을 같이 사용하려면 ATTRIBUTE 에서 COMMENT 인자 값을 포함해서 사용하면 됩니다.
• ATTRIBUTE 와 COMMENT 를 함께 사용하여 생성
-- 유저 생성
CREATE USER 'tom'@'localhost'
identified with 'mysql_native_password' by 'tom'
ATTRIBUTE '{"emp_name":"톰 카이트", "team":"devops","account_expire":"2022-07-30","request_number":"account-789","comment":"새로 신설되는 팀의 멤버 입니다"}';
-- 확인
select user,host,user_attributes from mysql.user
where user='tom'\G
mysql> select user,host,user_attributes from mysql.user
-> where user='tom'\G
*************************** 1. row ***************************
user: tom
host: localhost
user_attributes: {"metadata": {"team": "devops", "comment": "새로 신설되는 팀의 멤버 입니다", "emp_name": "톰 카이트", "account_expire": "2022-07-30", "request_number": "account-789"}}
-- 코멘트 변경
alter user 'tom'@'localhost' comment '톰은 팀 리더로 승격되었습니다.';
-- 확인
select user,host,user_attributes from mysql.user
where user='tom'\G
*************************** 1. row ***************************
user: tom
host: localhost
user_attributes: {"metadata": {"team": "devops", "comment": "톰은 팀 리더로 승격되었습니다.", "emp_name": "톰 카이트", "account_expire": "2022-07-30", "request_number": "account-789"}}
JSON 조회
attribute 로 지정한 정보는 json 으로 저장되기 때문에 MySQL 에서 제공하는 함수를 사용하여 json 을 다양하게 조회할 수 있습니다.
mysql.user 나 information_schema.user_attributes 를 이용해서 조회하면 됩니다.
• 예시1)
JSON_PRETTY(json_val) 는 PHP 및 다른 언어 및 DBMS 시스템에서 구현하는 것과 같은 유사한 JSON 값을 보기 좋은 형태로 출력합니다.
select user, host, JSON_PRETTY(user_attributes) user_attributes
FROM mysql.user
where user_attributes is not null;
+------+-----------+--------------------------------------------+
| user | host | user_attributes |
+------+-----------+--------------------------------------------+
| jade | localhost | {
"metadata": {
"team": "검색개발팀",
"emp_name": "정현호",
"account_expire": "2022-07-30",
"request_number": "account-456"
}
} |
| tom | localhost | {
"metadata": {
"team": "devops",
"comment": "톰은 팀 리더로 승격되었습니다.",
"emp_name": "톰 카이트",
"account_expire": "2022-07-30",
"request_number": "account-789"
}
} |
+------+-----------+---------------------------------------------+
• 예시2) 각 속성을 명시적으로 조회
보통의 테이블의 컬럼을 구분하여 조회 하듯이 출력할 수 있습니다.
select attribute->>"$.emp_name" as 사원명, attribute->>"$.team" as 팀명, attribute->>"$.account_expire" as 계정만료일, attribute->>"$.request_number" as 기안번호, attribute->>"$.comment" as 주석 from information_schema.user_attributes where attribute is not null; +---------------+-----------------+-----------------+--------------+---------------------------------------------+ | 사원명 | 팀명 | 계정만료일 | 기안번호 | 주석 | +---------------+-----------------+-----------------+--------------+---------------------------------------------+ | 정현호 | 검색개발팀 | 2022-07-30 | account-456 | NULL | | 톰 카이트 | devops | 2022-07-30 | account-789 | 톰은 팀 리더로 승격되었습니다. | +---------------+-----------------+-----------------+--------------+---------------------------------------------+
• 예시3) json_extract 특정 조건을 검색하여 조회
like '%검색어%' 를 하여도 되지만 제공되는 native json 함수를 이용해서 아래와 같이 검색 조건을 사용해서 조회할 수도 있습니다.
select json_pretty(attribute) user
from information_schema.user_attributes
where 1=1
and json_extract( attribute, '$.team' ) = "devops";
+------------------------------------------------------------+
| user |
+------------------------------------------------------------+
| {
"team": "devops",
"comment": "톰은 팀 리더로 승격되었습니다.",
"emp_name": "톰 카이트",
"account_expire": "2022-07-30",
"request_number": "account-789"
} |
+------------------------------------------------------------
Attribute 를 활용한 예시
MySQL 서버 8.0. 21 에서 추가된 User 를 attribute 통해서 입력된 유저 속성에 대한 정보를 일반 테이블을 조회하여 원하는 데이터만 추출할 수도 있고 key 별로 컬럼 구분하는 형태로도 추출하는 등 다양하게 데이터를 추출할 수 있게 되었습니다.
이 정보를 토대로 활용할 수 있는 방법은 아주 다양할 것으로 예상합니다.
먼저 단순하게 user 를 조회하는(attribute 속성을) 쿼리 결과에 대해서 html 로 파싱을 한다면, 그 파싱 된 html 를 간단하게 웹 서버로 서빙 한다면 간단한 유저 목록화 페이지 형태로도 사용할 수 있을 것입니다.
이렇게 파싱된 html 코드를 활용하여 워드프레스 와 같은 HTML을 통한 포스팅을 하는 시스템을 이용하여 별도의 페이지 작성, 즉 유저 목록화 시스템을 별도로 구성하는 것도 고민해볼 수 있을 것 같습니다.
아이디어에 따라서 REST API 를 제공하는 협업도구도 위와 같은 장표(또는 테이블 내용) 를 입력하여 목록화 할 수도 있을 것입니다.
(보통의 경우 Notion 이나 confluence)
또는 위의 예제와 같이 만료일이 지정 되어있고 관리를 한다면 만료일에 가까워진 계정에 대한 정보를 슬랙이나 이메일 등으로 통지를 해보는 것도 생각해 볼 수 있습니다.
[채널에 알리거나 작성하는 것에 따라서 개인 DM으로 발송] / Sending Webhook
또는 계정 당사자에게 리눅스 쉘 스크립트를 통해서 메일로 발송해보는 것도 하나의 활용 방안이 될 수도 있을 것입니다.
리눅스 터미널에서는 mutt 나 mail(또는 mailx) 를 통해서 이메일을 보낼 수 있습니다.
관련된 내용으로 아래 2개 포스팅을 참조하셔도 좋을 것 같습니다.
위에서 소개된 기능이 없었을 때에는 계정에 대한 정보를 신청 받은 그룹웨어나 Jira 등을 통해서 목록화 하거나 별도의 IDM 이 있다면 해당 시스템을 이용, 또는 별도의 관리 DB에서 테이블을 만들고 거기에 여러가지 정보를 입력하여 관리하는 등으로 개발자(또는 개인 계정) 계정에 대한 관리를 하였던 것에서, MySQL 유저 정보에 JSON 형태로 추가 정보를 넣을 수 있는 기능이 추가됨에 따라 한층 더 DB 유저에 대한 관리가 손쉽게 할 수 있을 것으로 예상되며, 여러 가지 좋은 아이디로 활용될 수 있을 것으로 예상하고 있습니다.
이번 포스팅은 여기서 마무리하도록 하겠습니다.
Reference
Reference URL
• dev.mysql.com/create-user-comments-attributes
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
