어플리케이션에서 암호화 복호화를 수행해서 저장하여도 되지만... 어플리케이션 로그등에 평문 개인정보가 남는 문제가 생겨서 DB 펑션으로 해결 하기로 함.
적용 암호 알고리즘
AES (AES128과 AES256은 암복호화 키의 길이에 따라 16bit 면 AES128, 32bit면 AES256 으로 분리되는 것 같다)
우리는 AES256으로 할 예정이므로 32bit의 암호화 키를 미리 준비 하자.
1. 암호화
CREATE FUNCTION AES256_ENCRYPT(plainText VARCHAR(1000)) RETURNS varchar(1000)
DETERMINISTIC
BEGIN
DECLARE returnVal VARCHAR(1000);
SELECT HEX(AES_ENCRYPT(plainText, '32bit 암호화키'))
INTO returnVal;
RETURN returnVal;
END
2. 복호화
CREATE FUNCTION AES256_DECRYPT(encryptedText VARCHAR(1000)) RETURNS varchar(1000)
DETERMINISTIC
BEGIN
DECLARE returnVal VARCHAR(1000);
SELECT CAST(AES_DECRYPT(UNHEX(encryptedText), '32bit 암호화키') AS CHAR)
INTO returnVal;
RETURN returnVal;
END
(암호화키는 당연히 암호화 할 때 사용한 암호화 키를 넣어 줘야 함)
3. 사용법
3-1. 고객정보 테이블(예시) 생성
CREATE TABLE CUSTOMER_INFO (
SEQ INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '고객 순번',
PHONE VARCHAR(255) NOT NULL COMMENT '전화번호',
NAME VARCHAR(255) NOT NULL COMMENT '이름',
BIRTH_DT VARCHAR(255) NOT NULL COMMENT '생년월일',
GENDER VARCHAR(255) NOT NULL COMMENT '성별',
EMAIL VARCHAR(255) NOT NULL COMMENT '이메일',
PRIMARY KEY (SEQ),
INDEX (PHONE)
) ENGINE=InnoDB COMMENT="고객 정보";
SELECT
SEQ,
AES256_DECRYPT(PHONE) AS PHONE,
AES256_DECRYPT(NAME) AS NAME,
AES256_DECRYPT(BIRTH_DT) AS BIRTH_DT,
AES256_DECRYPT(GENDER) AS GENDER,
AES256_DECRYPT(EMAIL) AS EMAIL
FROM CUSTOMER_INFO
WHERE PHONE = AES256_ENCRYPT('01012345678')
4. 참고1 ( DETERMINISTIC )
펑션 선언에 쓰인 DETERMINISTIC 구문은 생략할 경우 기본 적으로 NOT DETERMINISTIC 로 정의 된다, 해당 구문은 동일한 입력값에 대해 동일한 결과값을 반환 하는 경우에만 유효 하며 성능에 지대한 영향을 주니 참고 하자.
5. 참고2 (암호화키 보안 문제)
위와 같이 함수를 선언하는 경우 사용자 생성시 모든 권한을 부여 하게 되면 사용자가 마음만 먹으면 언제든지 암호회 키를 조회 할 수 있다.
이것을 회피 하는 방법으로는
함수를 생성한 definer 계정 외에 일반 사용자 계정을 분리 하고 일반 사용자는 해당 함수의 실행 권한만 부여 하는 방법으로 가능하다.
아래와 같이 계정 생성시 모든 권한이 부여된 사용자.
MySQL [mysql]> create user 'user01'@'192.168.1.101' identified by '비밀번호';
Query OK, 0 rows affected (0.01 sec)
MySQL [mysql]> grant all privileges on mydatabase.* to 'user01'@'192.168.1.101';
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> show grants for 'user01'@'192.168.1.101';
+--------------------------------------------------------------------+
| Grants for user01@192.168.1.101 |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`192.168.1.101` IDENTIFIED '비밀번호' |
| GRANT ALL PRIVILEGES ON `mydatabase`.* TO `user01`@`192.168.1.101` |
+--------------------------------------------------------------------+
2 rows in set (0.000 sec)
MariaDB [mysql]>
해당 사용자에게 펑션의 권한을 삭제 해보자
MySQL [mysql]> REVOKE EXECUTE ON FUNCTION mydatabase.AES256_DECRYPT FROM 'user01'@'192.168.1.101';
ERROR 1403 (42000): There is no such grant defined for user 'user01' on host '192.168.1.101' on routine 'AES256_DECRYPT'
MySQL [mysql]>
모든 권한을 가진 사용자에게서 특정 권한만 제거 할 수 없는 듯
이미 부여된 권한 제거
MySQL [mysql]> REVOKE ALL ON mydatabase.* FROM 'user01'@'192.168.1.101';
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MySQL [mysql]>
사용자의 실제 권한에 따라 차등 부여 (여기서는 조회, 입력, 수정, 삭제만 부여)
MySQL [mysql]> GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'user01'@'192.168.1.101';
Query OK, 0 rows affected (0.01 sec)
MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MySQL [mysql]>
위 사용자에게 보안이 필요한 펑션의 실행 권한만 부여
MySQL [mysql]> GRANT EXECUTE ON FUNCTION mydatabase.AES256_ENCRYPT TO 'user01'@'192.168.1.101';
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> show grants for 'user01'@'192.168.1.101';
+-------------------------------------------------------------------------------------+
| Grants for user01@192.168.1.101 |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`192.168.1.101` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase`.* TO `user01`@`192.168.1.101` |
| GRANT EXECUTE ON FUNCTION `mydatabase`.`aes128_encrypt` TO `user01`@`192.168.1.101` |
+-------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
MySQL [mysql]>
MariaDB [mysql]> show global variables like 'simple_password%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| simple_password_check_digits | 1 |
| simple_password_check_letters_same_case | 1 |
| simple_password_check_minimal_length | 8 |
| simple_password_check_other_characters | 1 |
+-----------------------------------------+-------+
4 rows in set (0.002 sec)
MariaDB [mysql]>
사전 배경
일년에 몇번씩 받는 보안점검... 항상 문제가 되는 것이 DB 계정의 비밀번호 복잡도를 강제하고 있는지 여부
가장 낮은 수준의 요건이 비밀번호 최소자리수 8자 이상 영문 대문자 1, 소문자 1, 특수문자 1 이상, 비밀번호 변경 주기 90일 이내 정도는 항상 등장하고
추가로 최근 n회 비밀번호 사용 금지, 사용자 계정명, 전화번호 등 유추하기 쉬운 비밀번호 금지
(참고로 우리가 사용하는 MariaDB 10.4.* 에서는 최근 n회 비밀번호 금지, 사용자 계정명이 포함된 비밀번호 금지 등이 적용 할 수 있는 방법이 없다. 아마도 MariaDB 10.5 이상에서 지원 하는 듯?)
기본 적인 복잡도라도 적용이 필요 한 상황, 이미 비밀번호에 대한 제약조건이 적용되어 있거나, 플러그인이 설치 되어 있으면 다행이지만
DB 설치 후 아무 작업도 하지 않으면 계정 비밀번호에 대한 제약이 없다.(MariaDB 10.4.x 기준)
적용
simple_password_check 플러그인을 통해 MariaDB Password 복잡도를 강제 하는 방법
MySQL 5.7 은 기본 포함, 없으면 상위 버전에서 simple_password_check.so 파일을 복사해서 설정 가능함
(참고 : MariaDB 10.x 는 MySQL 5.7에 해당, 이후 버전은 상호 DB간 별도로 소스를 운영하면서 더이상 호환이 되지 않는 부분이 많음)
1. 플러그인 설치 여부 확인
1-1. 플러그인 디렉토리 위치 확인
MariaDB [mysql]> show global variables like 'plugin_dir%';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.002 sec)
MariaDB [mysql]>
1-2. simple_password_check.so 유무 확인
[root@mydbserver user01]# ls -al /usr/lib64/mysql/plugin/
합계 8920
drwxr-xr-x. 3 root root 4096 4월 2 2021 .
drwxr-xr-x. 3 root root 20 4월 2 2021 ..
-rwxr-xr-x 1 root root 72048 2월 19 2021 auth_ed25519.so
-rwxr-xr-x 1 root root 11136 2월 19 2021 auth_gssapi_client.so
-rwxr-xr-x 1 root root 11672 2월 19 2021 auth_pam.so
drwx------ 2 mysql root 27 4월 2 2021 auth_pam_tool_dir
-rwxr-xr-x 1 root root 11672 2월 19 2021 auth_pam_v1.so
-rwxr-xr-x 1 root root 11152 2월 19 2021 caching_sha2_password.so
-rwxr-xr-x 1 root root 75688 2월 19 2021 client_ed25519.so
-rwxr-xr-x 1 root root 11120 2월 19 2021 dialog.so
-rwxr-xr-x 1 root root 11776 2월 19 2021 disks.so
-rwxr-xr-x 1 root root 20208 2월 19 2021 file_key_management.so
-rwxr-xr-x 1 root root 107344 2월 19 2021 ha_archive.so
-rwxr-xr-x 1 root root 74504 2월 19 2021 ha_blackhole.so
-rwxr-xr-x 1 root root 103352 2월 19 2021 ha_federated.so
-rwxr-xr-x 1 root root 144376 2월 19 2021 ha_federatedx.so
-rwxr-xr-x 1 root root 6585928 2월 19 2021 ha_mroonga.so
-rwxr-xr-x 1 root root 197648 2월 19 2021 ha_sphinx.so
-rwxr-xr-x 1 root root 1190840 2월 19 2021 ha_spider.so
-rwxr-xr-x 1 root root 243816 2월 19 2021 handlersocket.so
-rwxr-xr-x 1 root root 11888 2월 19 2021 locales.so
-rwxr-xr-x 1 root root 11792 2월 19 2021 metadata_lock_info.so
-rwxr-xr-x 1 root root 6992 2월 19 2021 mysql_clear_password.so
-rwxr-xr-x 1 root root 12536 2월 19 2021 query_cache_info.so
-rwxr-xr-x 1 root root 16240 2월 19 2021 query_response_time.so
-rwxr-xr-x 1 root root 61544 2월 19 2021 server_audit.so
-rwxr-xr-x 1 root root 11096 2월 19 2021 sha256_password.so
-rwxr-xr-x 1 root root 11800 2월 19 2021 simple_password_check.so <--- 이놈
-rwxr-xr-x 1 root root 11936 2월 19 2021 sql_errlog.so
-rwxr-xr-x 1 root root 46184 2월 19 2021 wsrep_info.so
[root@mydbserver user01]#
플러그인 파일은 존재 함 ( MariaDB 10.4.x 기준 기본 패키지로 설치 되어 있고 활성화만 안되어 있을 것임)
1-3. simple_password_check 플러그인이 활성화 되어 있는지 확인
MariaDB [mysql]> show global variables like 'simple_password%';
Empty set (0.002 sec)
MariaDB [mysql]>
MariaDB [mysql]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | REPLICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| SQL_SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_MUTEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_SEMAPHORE_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| unix_socket | ACTIVE | AUTHENTICATION | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+
54 rows in set (0.002 sec)
MariaDB [mysql]>
안되어 있음.
2. 플러그인 설치
MariaDB [mysql]> install plugin simple_password_check soname 'simple_password_check.so';
Query OK, 0 rows affected (0.039 sec)
MariaDB [mysql]> show plugins;
+-------------------------------+----------+---------------------+--------------------------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+---------------------+--------------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | REPLICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| SQL_SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_MUTEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_SEMAPHORE_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| unix_socket | ACTIVE | AUTHENTICATION | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| simple_password_check | ACTIVE | PASSWORD VALIDATION | simple_password_check.so | GPL |
+-------------------------------+----------+---------------------+--------------------------+---------+
55 rows in set (0.002 sec)
MariaDB [mysql]>
플러그인 설치 완료
3. 복잡도 설정
3-1. 기본 설정 확인
MariaDB [mysql]> show global variables like 'simple_password%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| simple_password_check_digits | 1 |
| simple_password_check_letters_same_case | 1 |
| simple_password_check_minimal_length | 8 |
| simple_password_check_other_characters | 1 |
+-----------------------------------------+-------+
4 rows in set (0.002 sec)
MariaDB [mysql]>
비밀번호 최소 8자, 숫자 1, 서로다른 영문 대소문자 1개 이상, 특수문자, 숫자 1자 이상으로 설정이 되어 있음
CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
생성된 사용자에게 지정
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
사용자별 비밀번호 유효기간 확인
MariaDB [mysql]> show create user 'monty'@'localhost';
+--------------------------------------------------------------------+
| CREATE USER for monty@localhost |
+--------------------------------------------------------------------+
| CREATE USER `monty`@`localhost` IDENTIFIED BY PASSWORD '*암호삭제' |
+--------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [mysql]>
전체 사용자 암호 유효기간 확인
WITH password_expiration_info AS (
SELECT User, Host,
IF(
IFNULL(JSON_EXTRACT(Priv, '$.password_lifetime'), -1) = -1,
@@global.default_password_lifetime,
JSON_EXTRACT(Priv, '$.password_lifetime')
) AS password_lifetime,
JSON_EXTRACT(Priv, '$.password_last_changed') AS password_last_changed
FROM mysql.global_priv
)
SELECT pei.User, pei.Host,
pei.password_lifetime,
FROM_UNIXTIME(pei.password_last_changed) AS password_last_changed_datetime,
FROM_UNIXTIME(
pei.password_last_changed +
(pei.password_lifetime * 60 * 60 * 24)
) AS password_expiration_datetime
FROM password_expiration_info pei
WHERE pei.password_lifetime != 0
AND pei.password_last_changed IS NOT NULL
UNION
SELECT pei.User, pei.Host,
pei.password_lifetime,
FROM_UNIXTIME(pei.password_last_changed) AS password_last_changed_datetime,
0 AS password_expiration_datetime
FROM password_expiration_info pei
WHERE pei.password_lifetime = 0
OR pei.password_last_changed IS NULL;
만일을 위해 시스템, 서비스운영 계정은 제외 하고 개별 사용자 계정에만 비밀번호 만료일자를 지정 하였다.
비밀번호 만료시
'SQL Error [1820] [HY000]: (conn=xxxxxxxx) You must SET PASSWORD before executing this statement'
오류 발생함
MySQL/MariaDB root 계정으로 접속해서
SET PASSWORD FOR 'User'@'Host' = PASSWORD('Password');
가격은 사악하지만, 손바닥 만한 놈이 USB 2.4GHz , 블루투스 연결이 되고 리모콘기능까지 있다고 해서 구입했습니다.
물건을 받고 라즈베리파이에 리시버 연결 해보니 여윽시나 잘 연결이 안됩니다.
다행이 7인치 터치 디스플레이가 있어서 블루투스로 잡으니 잘 동작 합니다.
하지만 라즈베리파이 설치 후 부팅하면 바로 USB로 잡혔으면 좋겠는데... 잘 안됩니다.
혹시나 드라이버 문제일까 싶어서 Windows 10 노트북에 USB 리시버를 꼽아 보니 'Wireless Receiver'가 준비 됐다고 나오고 장치에도 표시가 되지만 키보드의 파란색 불이 깜빡거리면서 연결이 되지 않습니다.
구매한지 몇달이 지났지만... 상품페이지 후기란에 보니 다른 사람들은 USB로도 잘 사용하고 있다고...
판매자에게 문의 했습니다.
답변
Hello, please follow the steps below to connect.
1. Open the keyboard
2. Press and hold the "FN+Caps" key, and you will observe that the LDE light on the keyboard turns green
3. Press and hold "FN+Shift". When the green light flashes rapidly, you insert the receiver and the light stops flashing, indicating a successful connection.
If you still cannot connect after following the steps below, please leave me a message and I will give you a free receiver as a gift.
1. 리시버를 PC에서 분리한 상태로 키보드 전원 On
2. "FN+Caps"키를 길게 누르면 키보드 왼쪽 상단의 녹색 LED가 깜빡 거립니다.
3. 다시 "FN+Shift"키를 길게 누르면, 녹색 LED가 빠르게 깜빡입니다. 이때 USB 수신기를 노트북에 삽입하면 연결이 되면서 깜빡임을 멈추고 녹색 LED가 켜진 상태로 됩니다.
정상 작동 와우~
위 작업은 아마도 USB리시버와 키보드간의 페어링이 끊어진 경우 하면 되는 것 같고
USB리시버를 다시 꼽았다 켜도 바로 연결 됩니다.
추가1 : 라즈베리파이 최초 설치 후 특수키 (@, #) 안되는 것
설치시 키보드 레이아웃을 us로 한 경우 shift + 2 => ?, shift + 3 =? 이상한 문자가 찍힘
Fn + F1 : 브라우저 열기 Fn + F2 : 메일 프로그램 시작 Fn + F3 : 계산기 시작 Fn + F4 : 미디어 플레이어 시작 Fn + F5 : 이전 미디어로 돌아가기 ※1 Fn + F6 : 재생 / 일시정지 ※1 Fn + F7 : 다음 미디어로 진행 ※1 Fn + F8 : 미디어 중지 ※1 Fn + F9 : 설정을 저장 ※2 Fn + F10 : - Fn + F11 : 좌측 Ctrl, Caps Lock 키 기능 교체 Fn + F12 : 키 잠금 설정 / 해제 ※3 (사전 소프트웨어에서 키 할당 필요)
결국 발송 일자를 yyyyMMdd 형태로 컬럼을 추가 하고 푸시종류 + 발송일자 + 고객 순번으로 Unique Index를 생성하면 되지만 왠지 SCHEDULE_DT와 중복되는 느낌입니다.
가상 컬럼을 이용한 인덱스를 사용해 보겠습니다.
1. 테이블 생성
DROP TABLE IF EXISTS OCCASIONAL_PUSH_STACK;
CREATE TABLE OCCASIONAL_PUSH_STACK (
STACK_SEQ INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '대기 순번',
PUSH_TYPE VARCHAR(20) NOT NULL COMMENT '푸시 종류',
CUST_SEQ INT(11) UNSIGNED NOT NULL COMMENT '고객 순번',
SCHEDULE_DT VARCHAR(14) NOT NULL COMMENT '발송 희망 일시',
REG_DATE DATETIME NOT NULL COMMENT '등록 일시',
PRIMARY KEY (STACK_SEQ)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT='비정기 푸시 발송 대기 테이블';
2. 인덱스 추가
ALTER TABLE OCCASIONAL_PUSH_STACK
ADD STD_DATE VARCHAR(8) AS (LEFT(SCHEDULE_DT, 8)) VIRTUAL AFTER CUST_SEQ,
ADD UNIQUE INDEX (PUSH_TYPE, STD_DATE, CUST_SEQ);
CUST_SEQ 컬럼 다음에 SCHEDULE_DT 테이블의 8자리를 잘라서 가상 컬럼을 만들고, 해당 컬럼을 포함한 유니크 인덱스를 생성한다는 의미 입니다.
VIRTUAL 은 매번 사용 될 때 마다 문자열 연산을 하게 되므로 STORED 로 사용하는게 성능에는 더 유리할 것 같습니다.
3. 테이블 확인
CREATE TABLE `occasional_push_stack` (
`STACK_SEQ` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '대기 순번',
`PUSH_TYPE` varchar(20) NOT NULL COMMENT '푸시 종류',
`CUST_SEQ` int(11) unsigned NOT NULL COMMENT '고객 순번',
`STD_DATE` varchar(8) GENERATED ALWAYS AS (left(`SCHEDULE_DT`,8)) VIRTUAL,
`SCHEDULE_DT` varchar(14) NOT NULL COMMENT '발송 희망 일시',
`REG_DATE` datetime NOT NULL COMMENT '등록 일시',
PRIMARY KEY (`STACK_SEQ`),
UNIQUE KEY `PUSH_TYPE` (`PUSH_TYPE`,`STD_DATE`,`CUST_SEQ`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='비정기 푸시 발송 대기 테이블';
