enable and configuring auditing and masking in mysql

Posted on by AHMED

enable auditing can be done on both community and enterprise edition and its strait forward option for best approach we will use enable auditing permanently

enabling auditing

  • navigate to the below directory
cd /usr/share/mysql-8.0/
  • run the below command to run script to install audit plugin
mysql -u root -p -D mysql < audit_log_filter_linux_install.sql
  • verify that audit is working by running the below query on mysql
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%';

Screenshot 1447-03-02 at 11.19.45 AM.png

  • update the /etc/my.cnf file and add the below parameter
[mysqld]
# keep the plugin loaded and fail startup if it can’t load
audit-log=FORCE_PLUS_PERMANENT

# logging format & file
audit_log_format=JSON
audit_log_file=/var/log/mysql/audit.json

# rotation & pruning (JSON files only)
audit_log_rotate_on_size=104857600      # 100 MB: auto-rotate
audit_log_max_size=1073741824           # prune if total > 1 GB
audit_log_prune_seconds=604800          # prune files older than 7 days

Screenshot 1447-03-03 at 12.06.06 AM.png

  • create directory using the following command
install -d -m 0750 -o mysql -g mysql /var/log/mysql
  • restart mysql services sudo systemctl restart mysqld
  • check if the plugin is enabled
SHOW VARIABLES LIKE 'audit_log%';

Screenshot 1447-03-03 at 12.09.44 AM.png

  • login to mysql and enable filtering for loggin audit
-- 4.1 create a "log all" filter
SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
-- 4.2 attach it to the default account pattern (all users without an explicit filter)
SELECT audit_log_filter_set_user('%', 'log_all');

-- 4.3 verify
SELECT * FROM mysql.audit_log_filter;
SELECT * FROM mysql.audit_log_user;

Screenshot 1447-03-03 at 12.13.08 AM.png

  • test the audit by running the following query in mysql
mysql -u root -p -e "SELECT 1;"
  • read the log by reading audit file using your desire tool
tail -n 20 /var/log/mysql/audit.json

Screenshot 1447-03-03 at 12.15.55 AM.png

now audit is working fine , for more filtering and encrypting audit file and other option visit mysql website

Data Masking

data masking is important feature that help mask confidential data such as ID , credit card , data of birth and so on , it help restrict user from reading confidential data unless they are granted

  • login to mysql and install plugins for masking
INSTALL PLUGIN data_masking SONAME 'data_masking.so';

CREATE FUNCTION gen_blocklist RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_dictionary RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_dictionary_drop RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_dictionary_load RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_range RETURNS INTEGER SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_email RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_pan RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_ssn RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_us_phone RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_inner RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_outer RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_pan RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_pan_relaxed RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_ssn RETURNS STRING SONAME 'data_masking.so';

Screenshot 1447-03-03 at 12.24.02 AM.png

  • test if masking function by runinng the below query
SELECT mask_inner('This is a string', 5, 1)   AS ex1,
       mask_outer('This is a string', 5, 1)   AS ex2,
       mask_pan(gen_rnd_pan())                AS ex3,
       mask_ssn(gen_rnd_ssn())                AS ex4,
       gen_rnd_email()                        AS ex5;

Screenshot 1447-03-03 at 12.25.10 AM.png

  • on key drawback of mysql that in cannot masked already created table you have two option to overcome this if table alteady created
    • create view that return masked result
    • add column with masked value
  • for first steps i will create sample table
CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100),
  email     VARCHAR(100),
  pan       VARCHAR(19),
  ssn       VARCHAR(11)  -- format 'NNN-NN-NNNN' for demo
);

INSERT INTO customers (full_name, email, pan, ssn)
VALUES
('Joanna Bond', '[email protected]', '7706301234560807', '906-39-0007');
  • Build a masked view (so apps only see masked values).
CREATE VIEW v_customers_masked AS
SELECT
  id,
  full_name,
  mask_inner(email, 1, 10)                          AS email_masked,
  mask_pan(pan)                                      AS pan_masked,
  mask_ssn(CONVERT(ssn USING binary))                AS ssn_masked
FROM customers;
  • Lock down privileges so users only see masked data.
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT ON demo_mask.v_customers_masked TO 'app_user'@'%';
-- DO NOT grant SELECT on demo_mask.customers to this user
  • login as the new created user and query the sample table the result should return masked output
    Screenshot 1447-03-03 at 12.37.42 AM.png

this approach allow you to just give access to view and restricted access to table since dynamic masking is not support in mysql .

0
Visited 23 times, 1 visit(s) today
Category: mysql