TXSQL Encryption Audit Introduction klaywang 2017 -11

TXSQL Encryption & Audit Introduction klaywang 2017 -11 -14

Self Introduction l Graduated from Huazhong University of Science & Technology l 10+ database kernel development l l Dameng Database Teradata / Asterdata Oracle / My. SQL Inno. DB Tencent / TXSQL l Hobbies : running & backetball & reading

TXSQL Introduction l TXSQL(Tencent My. SQL) l l l The My. SQL Branch at Tencent Developed by TEG#CFD#My. SQL Team Support Tencent Cloud & Inner Business l Our goals(B 3 M) l l Better performance More stable More maintainable More enterprise features

Agenda l. TXSQL Encryption l My. SQL 5. 7 TDE l TXSQL Encryption l. TXSQL Audit l My. SQL 5. 7 Audit l TXSQL Audit

1. 1 My. SQL TDE

My. SQL 5. 7 TDE l Inno. DB Encrypted Table -- Tablespace Key : encrypt table data page l Tablespace encryption l Redo/undo log encryption(8. 0) l Keyring -- Master Key : encrypt tablespace key l l my_key_store my_key_generate my_key_fetch my_key_remove

TDE-Create. Tablespace 1. get master key by key id 1. 1 my_key_generate(key name, key len) Inno. DB 1. 2 my_key_fetch(key name) unencrypted master key 5. store(master key id, encrypted tablespace key) Table Space File 3. generate random table space key 4. encrypt table space key by master key Keyring Plugin

TDE-Open. Tablespace 2. my_key_fetch(key name) unencrypted master key Inno. DB 1. fetch(master key id, encrypted tablespace key) Table Space File 3. decrypt table space key by master key Keyring Plugin

1. 2 TXSQL Encryption

KEYRING-KMS l Kerying Plugins l l keyring_file keyring_okv : commercial, key managed in ORACLE Key Vault l Keyring_kms l l Integrate KMS & CAM with My. SQL TDE Implement Keyring interfaces l KMS : Key Management Service by Tencent Cloud l CAM : Cloud Access Management services provided by Tencent Cloud

KEYRING-KMS : get temp certificate * Role Based Access Control 1. assume_role(cdb secret key, cdb secret id, user id, kms role) Keyring KMS Plugin session token, tmp secret key, tmp secret id CAM

KERYING-KMS : key generate 1. list_key_deails&create_key(cmk key alias) Keyring KMS Plugin cmk key id 2. generate_data_key(cmk key id, key spec, key len) encrypted data key 3. store(key id, encrypted data key) Keyring KMS File KMS Server

KEYRING-KMS : key fetch Keyring KMS Plugin encrypted key 1. fetch(key id) Keyring KMS File 2. decrypt(encrypted key) decrypted key KMS Server

Deployment in Cloud l When a user open data encryption in cdb for the first time, create a kms role and grant the role privilege to cdb console user. l All mysqld instances of a user share the same CMK(master key in KMS), but have different data keys.

2. 1 My. SQL Audit

My. SQL Audit Plugin l Audit Plugin Notification Function l l Most of the work for an audit plugin occurs in the notification function. int (*event_notify)(MYSQL_THD, mysql_event_class_t, const void *); l Audit Event Type l l l l MYSQL_AUDIT_CONNECTION_PRE_AUTHENTICATE MYSQL_AUDIT_CONNECTION_CONNECT MYSQL_AUDIT_CONNECTION_DISCONNECT MYSQL_AUDIT_COMMAND_START MYSQL_AUDIT_COMMAND_END …… l My. SQL Enterprise Audit

2. 2 TXSQL Audit

TXSQL Audit Plugin

TXSQL Audit Plugin l l Audit event log write to allocated memory Flush thread flush event log to audit file l Audit Agent l l l Read event log from audit file and send it to audit center One physical machine has one audit agent One audit agent serves multiple mysqld instances l Audit Center l l CTSDB : Cloud Time Series Database by Tencent Cloud Audit event log is time series data

Audit Event Log l Audit Event Log { "timestamp": 1509926899, "affect. Rows": 0, "exec. Time": 367, "err. Code": 0, "rule_num": 2, "host": "127. 0. 0. 1", "instance. Name": "gz_xxx_database", "user": "root", "db. Name": "", "policy. Name": "policyname 2", "sql": "show status like '%audit%'", "sql. Type": "OTHER“ }

Audit Log Filtering l IP, user, db. Name, table. Name l Include, exclude, =, <>, regex l sql. Command l Include, exclue, regex l sql. Type l =, <> l affected. Rows, exec. Time l >, <, >=, <=, =

Audit Plugin Performance l Performance Consideration l Remove all event notify hooks before command end l Performance Comparison l l Enable audit and audit all : ~9% degradation Enable audit and audit null : ~3% degradation

Thank you! Q & A