.::: Enable collect MariaDB User login Connect & Disconnect include prviledge using MariaDB Audit :::.

1. enable MariaDB Audit

http://teguhth.blogspot.com/2022/08/enable-server-audit-in-mariadb-mysql.html

2. convert to table

http://teguhth.blogspot.com/2022/09/load-log-file-insert-into-table-in.html

3. check filter tbl_mariadb_audit to filter connect & disconnect

MariaDB [teguhthtools]> select * from tbl_mariadb_audit where type ='connect' or type ='disconnect';
+-------------------+---------+--------+-----------+------+---------------+------------+------+---------+--------+
| date              | host    | userdb | client    | id   | connection_id | type       | db   | sqltext | status |
+-------------------+---------+--------+-----------+------+---------------+------------+------+---------+--------+
| 20240822 07:52:09 | teguhth | admin  | teguhth   |    4 |             0 | CONNECT    |      |         | 0      |
| 20240822 07:52:32 | teguhth | admin  | teguhth   |    4 |             0 | DISCONNECT |      |         | 0      |
+-------------------+---------+--------+-----------+------+---------------+------------+------+---------+--------+
3 rows in set (0.001 sec)

MariaDB [teguhthtools]>

4. check filter mysql.user to show information

MariaDB [teguhthtools]> select user,host,plugin,password_expired,Grant_priv,Super_priv from mysql.user where user ='admin';
+-------+-----------+-----------------------+------------------+------------+------------+
| User  | Host      | plugin                | password_expired | Grant_priv | Super_priv |
+-------+-----------+-----------------------+------------------+------------+------------+
| admin | localhost | mysql_native_password | N                | Y          | Y          |
| admin | %         | mysql_native_password | N                | Y          | Y          |
+-------+-----------+-----------------------+------------------+------------+------------+
2 rows in set (0.004 sec)

MariaDB [teguhthtools]>

5. create view basic

CREATE VIEW user_connection_info AS
SELECT @@hostname AS ServerName,
    connect.userdb AS user,
    connect.host AS host,
    connect.client AS client,
    connect.date AS connect_time,
    disconnect.date AS disconnect_time,
     @@VERSION AS Versions
FROM
    teguhthtools.tbl_mariadb_audit connect
JOIN
    teguhthtools.tbl_mariadb_audit disconnect
ON
    connect.connection_id = disconnect.connection_id
    AND connect.userdb = disconnect.userdb
    AND connect.type = 'connect'
    AND disconnect.type = 'disconnect'
    AND connect.date < disconnect.date
ORDER BY
    connect.date ASC;
 

    
6. create view user  user_connection_details all user

CREATE VIEW user_connection_details AS
SELECT @@hostname AS ServerName,
    connect.userdb AS user,
    connect.host AS host,
    connect.client AS client,
    connect.type AS type,
    connect.date AS connect_time,
    disconnect.date AS disconnect_time,
    user_info.Grant_priv,
    user_info.Super_priv,
    user_info.plugin,
    user_info.password_expired,user_info.is_role
 @@VERSION AS Versions
FROM
    teguhthtools.tbl_mariadb_audit connect
JOIN
    teguhthtools.tbl_mariadb_audit disconnect
    ON connect.connection_id = disconnect.connection_id
    AND connect.userdb = disconnect.userdb
    AND connect.type = 'connect'
    AND disconnect.type = 'disconnect'
    AND connect.date < disconnect.date
JOIN
    mysql.user user_info
    ON connect.userdb = user_info.User
    AND (connect.host = user_info.Host OR user_info.Host = '%')
    ORDER BY
    connect.date ASC;
 

      
7. create view user  user_connection_details specify user
CREATE VIEW user_connection_details AS
SELECT @@hostname AS ServerName,
    connect.userdb AS user,
    connect.host AS host,
    connect.client AS client,
    connect.type AS type,
    connect.date AS connect_time,
    disconnect.date AS disconnect_time,
    user_info.Grant_priv,
    user_info.Super_priv,
    user_info.plugin,
    user_info.password_expired,user_info.is_role,
 @@VERSION AS Versions
FROM
    teguhthtools.tbl_mariadb_audit connect
JOIN
    teguhthtools.tbl_mariadb_audit disconnect
    ON connect.connection_id = disconnect.connection_id
    AND connect.userdb = disconnect.userdb
    AND connect.type = 'connect'
    AND disconnect.type = 'disconnect'
    AND connect.date < disconnect.date
JOIN
    mysql.user user_info
    ON connect.userdb = user_info.User
    AND (connect.host = user_info.Host OR user_info.Host = '%')
WHERE
      connect.userdb = 'onlyone' or connect.userdb = 'onlyview'
ORDER BY
    connect.date ASC;

 

8. create user_list_info list user mariadb using info connect to connect

 or change name tbl_mariadb_audit_count_user_login_view

CREATE VIEW user_list_info AS
SELECT
    u.user,
   # u.host,
    u.plugin,
    u.password_expired,
    u.Grant_priv,
    u.Super_priv,
    u.is_role,
    u.default_role,
    (SELECT a.date FROM tbl_mariadb_audit a WHERE a.userdb = u.user AND a.type = 'CONNECT' ORDER BY a.date DESC LIMIT 1) AS connect,
    (SELECT a.date FROM tbl_mariadb_audit a WHERE a.userdb = u.user AND a.type = 'DISCONNECT' ORDER BY a.date DESC LIMIT 1) AS disconnect
FROM
    mysql.user u
group by u.user;    

 

 
9. CREATE VIEW tbl_mariadb_audit_total_view

CREATE VIEW tbl_mariadb_audit_total_view AS
SELECT
    'Total Count' AS description,
    NULL AS type,
    COUNT(type) AS total_amount
FROM
    teguhthtools.tbl_mariadb_audit;
    

10. create tbl_mariadb_audit_total_detail_view

CREATE VIEW tbl_mariadb_audit_total_detail_view AS    
SELECT
    'Grouped by type' AS description,
    type,
    COUNT(type) AS total_amount
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    type

UNION ALL

SELECT
    'Total Count' AS description,
   ## NULL AS type,
   '' AS totalx,
    COUNT(type) AS total_amount
FROM
    teguhthtools.tbl_mariadb_audit;
 

11. create view tbl_mariadb_audit_count_horisontal_view

create view tbl_mariadb_audit_count_horisontal_view as
SELECT
    `db`,
    SUM(CASE WHEN `type` = 'CONNECT' THEN 1 ELSE 0 END) AS connect,
    SUM(CASE WHEN `type` = 'DISCONNECT' THEN 1 ELSE 0 END) AS disconnect,
    SUM(CASE WHEN `type` = 'QUERY' THEN 1 ELSE 0 END) AS query,
    SUM(CASE WHEN `type` = 'READ' THEN 1 ELSE 0 END) AS `read`,
    SUM(CASE WHEN `type` = 'WRITE' THEN 1 ELSE 0 END) AS `write`,
    SUM(CASE WHEN `type` = 'TABLE_ACCESS' THEN 1 ELSE 0 END) AS table_access,
    SUM(CASE WHEN `type` = 'TRANSACTION' THEN 1 ELSE 0 END) AS `transaction`,
    SUM(CASE WHEN `type` = 'CREATE' THEN 1 ELSE 0 END) AS `create`,
    SUM(CASE WHEN `type` = 'ALTER' THEN 1 ELSE 0 END) AS `alter`,
    SUM(CASE WHEN `type` = 'DROP' THEN 1 ELSE 0 END) AS `drop`
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`;
    

12. create view tbl_mariadb_audit_count_vertical_view
CREATE VIEW tbl_mariadb_audit_count_vertical_view AS
SELECT
    `db`,
    'CONNECT' AS activity_type,
    SUM(CASE WHEN `type` = 'CONNECT' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
UNION ALL
SELECT
    `db`,
    'DISCONNECT' AS activity_type,
    SUM(CASE WHEN `type` = 'DISCONNECT' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
UNION ALL
SELECT
    `db`,
    'QUERY' AS activity_type,
    SUM(CASE WHEN `type` = 'QUERY' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
UNION ALL
SELECT
    `db`,
    'READ' AS activity_type,
    SUM(CASE WHEN `type` = 'READ' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
UNION ALL
SELECT
    `db`,
    'WRITE' AS activity_type,
    SUM(CASE WHEN `type` = 'WRITE' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
UNION ALL
SELECT
    `db`,
    'TABLE_ACCESS' AS activity_type,
    SUM(CASE WHEN `type` = 'TABLE_ACCESS' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
UNION ALL
SELECT
    `db`,
    'TRANSACTION' AS activity_type,
    SUM(CASE WHEN `type` = 'TRANSACTION' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
UNION ALL
SELECT
    `db`,
    'CREATE' AS activity_type,
    SUM(CASE WHEN `type` = 'CREATE' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
UNION ALL
SELECT
    `db`,
    'ALTER' AS activity_type,
    SUM(CASE WHEN `type` = 'ALTER' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
UNION ALL
SELECT
    `db`,
    'DROP' AS activity_type,
    SUM(CASE WHEN `type` = 'DROP' THEN 1 ELSE 0 END) AS activity_count
FROM
    teguhthtools.tbl_mariadb_audit
GROUP BY
    `db`
ORDER BY
    `db`,
    activity_type;