.::: Check Status MariaDB Database Encryption Configuration (ENCRYPTION_KEY_ID) Audit :::.

 

correlation How to Remove ENCRYPTION_KEY_ID  https://teguhth.blogspot.com/2023/05/how-to-remove-encryptionkeyid-from-file.html
correlation Enable Encryption  https://teguhth.blogspot.com/2023/06/enable-encryption-table-database-for.html

1. Check MariaDB Encryption Global Variables
🔐 Encryption Configuration → variable + key management plugin

SHOW VARIABLES WHERE Variable_name IN ('innodb_encrypt_tables','file_key_management_encryption_algorithm' );

2. Check Encryption Key Management Plugin & Library
🔐 Encryption Configuration → variable + key management plugin

SELECT SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY FROM information_schema.PLUGINS
WHERE PLUGIN_NAME LIKE '%key_management%';
SELECT SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,
    PLUGIN_NAME,
    PLUGIN_STATUS,
    PLUGIN_TYPE,
    PLUGIN_LIBRARY
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME LIKE '%key_management%';
 

3. Check Current Encryption Key ID per Tablespace
🔑 Encryption Key Status → CURRENT_KEY_ID

SELECT SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,CURRENT_KEY_ID, COUNT(*) AS tablespace_count FROM information_schema.INNODB_TABLESPACES_ENCRYPTION GROUP BY CURRENT_KEY_ID;

SELECT SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,CURRENT_KEY_ID, COUNT(*) AS tablespace_count
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
GROUP BY CURRENT_KEY_ID;

4. Check Encryption Scheme per Tablespace
🗄️ Tablespace Encryption → ENCRYPTION_SCHEME

SELECT SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,ENCRYPTION_SCHEME, COUNT(*) AS tablespace_count FROM information_schema.INNODB_TABLESPACES_ENCRYPTION GROUP BY ENCRYPTION_SCHEME;

SELECT SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,ENCRYPTION_SCHEME, COUNT(*) AS tablespace_count
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
GROUP BY ENCRYPTION_SCHEME;
 

5. Check Encryption Status of All Tables in Database
📋 Table-level Encryption → status setiap tabel

SELECT
    SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,
    SPACE,
    SUBSTRING_INDEX(NAME, '/', 1) AS dbname,
    SUBSTRING_INDEX(NAME, '/', -1) AS tablename,
    ENCRYPTION_SCHEME,
    CURRENT_KEY_ID,
    ROTATING_OR_FLUSHING
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME LIKE 'teguhthdbkey/%';

SELECT SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,SPACE,SUBSTRING_INDEX(NAME, '/', 1) AS dbname,SUBSTRING_INDEX(NAME, '/', -1) AS tablename,ENCRYPTION_SCHEME,CURRENT_KEY_ID, ROTATING_OR_FLUSHING FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'teguhthdbkey/%';

6. Check Encryption Status of Specific Table
🔄 Key Rotation Status → ROTATING_OR_FLUSHING

SELECT 
    SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,
    SPACE,
    SUBSTRING_INDEX(NAME, '/', 1) AS dbname,
    SUBSTRING_INDEX(NAME, '/', -1) AS tablename,
    ENCRYPTION_SCHEME,
    CURRENT_KEY_ID,
    ROTATING_OR_FLUSHING
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME = 'teguhthdbkey/pembelian';

SELECT SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,SPACE,SUBSTRING_INDEX(NAME, '/', 1) AS dbname,SUBSTRING_INDEX(NAME, '/', -1) AS tablename,ENCRYPTION_SCHEME,CURRENT_KEY_ID,ROTATING_OR_FLUSHING FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME = 'teguhthdbkey/pembelian';
 

7. Complete combine no 1 no 2 (🔐 Encryption Configuration → variable + key management plugin)

SELECT
    SUBSTRING_INDEX(@@hostname, '.', 1) AS hostname,

    /* InnoDB Encryption */
    @@innodb_encrypt_tables
        AS innodb_encrypt_tables,

    /* File Key Management Algorithm */
    @@file_key_management_encryption_algorithm
        AS file_key_management_encryption_algorithm,

    /* Key Management Plugin */
    GROUP_CONCAT(
        p.PLUGIN_NAME
        ORDER BY p.PLUGIN_NAME
        SEPARATOR ', '
    ) AS plugin_name,

    GROUP_CONCAT(
        p.PLUGIN_STATUS
        ORDER BY p.PLUGIN_NAME
        SEPARATOR ', '
    ) AS plugin_status,

    GROUP_CONCAT(
        p.PLUGIN_TYPE
        ORDER BY p.PLUGIN_NAME
        SEPARATOR ', '
    ) AS plugin_type,

    GROUP_CONCAT(
        p.PLUGIN_LIBRARY
        ORDER BY p.PLUGIN_NAME
        SEPARATOR ', '
    ) AS plugin_library

FROM information_schema.PLUGINS p

WHERE p.PLUGIN_NAME LIKE '%key_management%';