As I continue being a mentor for the SANS/Ryerson/Rogers Cyber Secure Catalyst program and as the group goes through working with database, I was asked to provide some guidance on using MariaDB/MySQL. The guidance I've provided below is meant to address the questions asked directly via the mentor session while also going a bit deeper into some of the basics that you should know, not only for MariaDB but also for any SQL Server database server administration.
With that in mind, let's get into the blog post.
First, verify the version of MySQL currently installed.
┌──(kali㉿securitynik)-[/tmp]
└─$ mysql --version
mysql Ver 15.1 Distrib 10.6.8-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
Looking to see if MySQL is running
┌──(kali㉿securitynik)-[/tmp]
└─$ sudo ss --numeric --listening --tcp --processes
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
At this point, it looks like it is not. Starting the MySQL service.
┌──(kali㉿securitynik)-[/tmp]
└─$ sudo systemctl start mysql.service
Verifying once again, the service is available
┌──(kali㉿securitynik)-[/tmp]
└─$ sudo ss --numeric --listening --tcp --processes
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 80 127.0.0.1:3306 0.0.0.0:* users:(("mariadbd",pid=2213,fd=21))
With the service available, time to connect to it.
┌──(kali㉿securitynik)-[/tmp]
└─$ sudo mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.6.8-MariaDB-1 Debian buildd-unstable
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Looking to see the current databases:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.009 sec)
Creating my own database and verifying its creation.
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS securitynik_db;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| securitynik_db |
| sys |
+--------------------+
5 rows in set (0.000 sec)
Change to my newly created database
MariaDB [(none)]> use securitynik_db;
Database changed
Create a table named interesting_data.
MariaDB [securitynik_db]> CREATE TABLE IF NOT EXISTS interesting_data (
-> file_no INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> owner VARCHAR(100),
-> password VARCHAR(100))
-> COMMENT 'This database contains sensitive information';
Query OK, 0 rows affected (0.027 sec)
Verify the table was successfully created
MariaDB [securitynik_db]> show tables;
+--------------------------+
| Tables_in_securitynik_db |
+--------------------------+
| interesting_data |
+--------------------------+
1 row in set (0.000 sec)
MariaDB [securitynik_db]> SHOW TABLES FROM securitynik_db;
+--------------------------+
| Tables_in_securitynik_db |
+--------------------------+
| interesting_data |
+--------------------------+
Verifying the table structure:
MariaDB [securitynik_db]> DESCRIBE interesting_data;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| file_no | int(11) | NO | PRI | NULL | auto_increment |
| owner | varchar(100) | YES | | NULL | |
| password | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
Alternatively
MariaDB [securitynik_db]> SHOW FULL COLUMNS FROM interesting_data
-> ;
+----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| file_no | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| owner | varchar(100) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| password | varchar(100) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
+----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
3 rows in set (0.001 sec)
Yet another way of verifying the table creation.
MariaDB [securitynik_db]> SHOW FULL COLUMNS FROM interesting_data \G;
*************************** 1. row ***************************
Field: file_no
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
Privileges: select,insert,update,references
Comment:
*************************** 2. row ***************************
Field: owner
Type: varchar(100)
Collation: utf8mb4_general_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 3. row ***************************
Field: password
Type: varchar(100)
Collation: utf8mb4_general_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
3 rows in set (0.000 sec)
With the table created, let's alter that table before adding some data
MariaDB [securitynik_db]> ALTER TABLE interesting_data ADD admin VARCHAR(2) NOT NULL DEFAULT 'Y';
Query OK, 0 rows affected (0.017 sec)
Records: 0 Duplicates: 0 Warnings: 0
Add multiple column that we will ultimately delete
MariaDB [securitynik_db]> ALTER TABLE interesting_data ADD comments VARCHAR(255), ADD blogs VARCHAR(255), ADD delete_me VARCHAR(255);
Query OK, 0 rows affected (0.026 sec)
Records: 0 Duplicates: 0 Warnings: 0
Show the table structure
MariaDB [securitynik_db]> SHOW FULL COLUMNS FROM interesting_data;
+-----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| file_no | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| owner | varchar(100) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| password | varchar(100) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| admin | varchar(2) | utf8mb4_general_ci | NO | | Y | | select,insert,update,references | |
| comments | varchar(255) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| blogs | varchar(255) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| delete_me | varchar(255) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
+-----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
7 rows in set (0.001 sec)
With the table created with its various columns, let's add some data.
MariaDB [securitynik_db]> INSERT INTO interesting_data(owner,password,admin,comments,blogs,delete_me) VALUES('securitynik','Testing1','Y','Systems Administrator','https://www.securitynik.com','you are to be deleted');
Query OK, 1 row affected (0.010 sec)
Note, you do not have to specify all columns, only the ones you need to fill in. Keep in mind, if something is NOT Null, you will need to have that column with a value, unless you specified a default value.
MariaDB [securitynik_db]> INSERT INTO interesting_data(owner,password,admin,comments,blogs) VALUES('booo','Testing1','Y','Guest User','https://www.example.local');
Query OK, 1 row affected (0.009 sec)
Inserting multiple records at the same time. Also note the usage of my DEFAULT keyword, to leverage the default value of this column.
MariaDB [securitynik_db]> INSERT INTO interesting_data(owner,password,admin,comments,blogs) VALUES('YouKnowWhoIAm','andYouKnowMyPassword',DEFAULT,'some user','https://www.blah.local'), ('hello','world',DEFAULT,'hi','securitynik.local');
Query OK, 2 rows affected (0.012 sec)
Records: 2 Duplicates: 0 Warnings: 0
As we have added some records, let's see what is returned by select * records
MariaDB [securitynik_db]> SELECT * FROM interesting_data;
+---------+---------------+----------------------+-------+-----------------------+-----------------------------+-----------------------+
| file_no | owner | password | admin | comments | blogs | delete_me |
+---------+---------------+----------------------+-------+-----------------------+-----------------------------+-----------------------+
| 1 | securitynik | Testing1 | Y | Systems Administrator | https://www.securitynik.com | you are to be deleted |
| 2 | booo | Testing1 | Y | Guest User | https://www.example.local | NULL |
| 3 | YouKnowWhoIAm | andYouKnowMyPassword | Y | some user | https://www.blah.local | NULL |
| 4 | hello | world | Y | hi | securitynik.local | NULL |
+---------+---------------+----------------------+-------+-----------------------+-----------------------------+-----------------------+
4 rows in set (0.000 sec)
Rather than all records, let's just select owner, password and blogs from the table
MariaDB [securitynik_db]> SELECT owner,password,blogs FROM interesting_data;
+---------------+----------------------+-----------------------------+
| owner | password | blogs |
+---------------+----------------------+-----------------------------+
| securitynik | Testing1 | https://www.securitynik.com |
| booo | Testing1 | https://www.example.local |
| YouKnowWhoIAm | andYouKnowMyPassword | https://www.blah.local |
| hello | world | securitynik.local |
+---------------+----------------------+-----------------------------+
4 rows in set (0.000 sec)
One more SELECT, this time using the WHERE clause. Notice the integer after the equal sign and the absence of quotes.
MariaDB [securitynik_db]> SELECT owner,password,blogs FROM interesting_data WHERE file_no=3;
+---------------+----------------------+------------------------+
| owner | password | blogs |
+---------------+----------------------+------------------------+
| YouKnowWhoIAm | andYouKnowMyPassword | https://www.blah.local |
+---------------+----------------------+------------------------+
1 row in set (0.000 sec)
Alternatively, select all records using the WHERE criteria. Notice in this example, I am using a string. Note the quotes after the equal sign.
MariaDB [securitynik_db]> SELECT * FROM interesting_data WHERE owner='YouKnowWhoIAm';
+---------+---------------+----------------------+-------+-----------+------------------------+-----------+
| file_no | owner | password | admin | comments | blogs | delete_me |
+---------+---------------+----------------------+-------+-----------+------------------------+-----------+
| 3 | YouKnowWhoIAm | andYouKnowMyPassword | Y | some user | https://www.blah.local | NULL |
+---------+---------------+----------------------+-------+-----------+------------------------+-----------+
1 row in set (0.000 sec)
Update a record
MariaDB [securitynik_db]> UPDATE LOW_PRIORITY IGNORE interesting_data SET owner='owner_updated_YouKnowWhoIAm', password='You changed me :-(' WHERE file_no=3;
Query OK, 1 row affected (0.009 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Verify the change
MariaDB [securitynik_db]> SELECT owner,password,blogs FROM interesting_data WHERE file_no=3;
+-----------------------------+--------------------+------------------------+
| owner | password | blogs |
+-----------------------------+--------------------+------------------------+
| owner_updated_YouKnowWhoIAm | You changed me :-( | https://www.blah.local |
+-----------------------------+--------------------+------------------------+
1 row in set (0.000 sec)
Before sharing our database with other users, let's see what permissions exists on the server.
MariaDB [securitynik_db]> SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
If you wanted to see permission for a particular user
MariaDB [securitynik_db]> SHOW GRANTS FOR root@localhost;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Alternatively ...
MariaDB [securitynik_db]> SHOW GRANTS FOR CURRENT_USER;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Let's create and verify two roles.
MariaDB [securitynik_db]> CREATE ROLE 'securitynik_db_admins';
Query OK, 0 rows affected (0.009 sec)
MariaDB [securitynik_db]> CREATE ROLE 'securitynik_db_users';
Query OK, 0 rows affected (0.012 sec)
Verify the role has been created
MariaDB [securitynik_db]> SHOW GRANTS FOR 'securitynik_db_admins';
+-----------------------------------------------+
| Grants for securitynik_db_admins |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `securitynik_db_admins` |
+-----------------------------------------------+
MariaDB [securitynik_db]> SHOW GRANTS FOR securitynik_db_users;
+----------------------------------------------+
| Grants for securitynik_db_users |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `securitynik_db_users` |
+----------------------------------------------+
Grant all privileges to the
securitynik_db_adminsMariaDB [securitynik_db]> GRANT ALL ON securitynik_db.* TO 'securitynik_db_admins';
Query OK, 0 rows affected (0.009 sec)
Grant SELECT permissions only to securitynik_db_users.
MariaDB [securitynik_db]> GRANT SELECT ON securitynik_db.* TO 'securitynik_db_users';
Query OK, 0 rows affected (0.009 sec)
With the roles created and their access specified, time to add users.
Create an admin user.
MariaDB [securitynik_db]> CREATE USER IF NOT EXISTS 'admin' IDENTIFIED BY 'Testing1' PASSWORD EXPIRE INTERVAL 365 DAY;
Query OK, 0 rows affected (0.009 sec)
Create a set of normal users
MariaDB [securitynik_db]> CREATE USER 'securitynik' IDENTIFIED BY 'Testing1', 'nakia'@'localhost' IDENTIFIED BY 'Testing1', 'neysa@localhost' IDENTIFIED BY 'Testing1' PASSWORD EXPIRE INTERVAL 180 DAY ACCOUNT LOCK;
Query OK, 0 rows affected (0.009 sec)
Reviewing the mysql.user table to identify possible fields to view
MariaDB [securitynik_db]> DESCRIBE mysql.user;
+------------------------+---------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host | char(255) | NO | | | |
| User | char(128) | NO | | | |
| Password | longtext | YES | | NULL | |
| Select_priv | varchar(1) | YES | | NULL | |
| Insert_priv | varchar(1) | YES | | NULL | |
| Update_priv | varchar(1) | YES | | NULL | |
| Delete_priv | varchar(1) | YES | | NULL | |
| Create_priv | varchar(1) | YES | | NULL | |
| Drop_priv | varchar(1) | YES | | NULL | |
| Reload_priv | varchar(1) | YES | | NULL | |
| Shutdown_priv | varchar(1) | YES | | NULL | |
| Process_priv | varchar(1) | YES | | NULL | |
| File_priv | varchar(1) | YES | | NULL | |
| Grant_priv | varchar(1) | YES | | NULL | |
| References_priv | varchar(1) | YES | | NULL | |
| Index_priv | varchar(1) | YES | | NULL | |
| Alter_priv | varchar(1) | YES | | NULL | |
| Show_db_priv | varchar(1) | YES | | NULL | |
.... TRUNCATED FOR BREVITY ....
Selecting some data points ...
MariaDB [securitynik_db]> SELECT user,host,password,default_role,max_connections,max_user_connections,is_role FROM mysql.user;
+-----------------------+-----------+-------------------------------------------+--------------+-----------------+----------------------+---------+
| User | Host | Password | default_role | max_connections | max_user_connections | is_role |
+-----------------------+-----------+-------------------------------------------+--------------+-----------------+----------------------+---------+
| mariadb.sys | localhost | | | 0 | 0 | N |
| root | localhost | invalid | | 0 | 0 | N |
| mysql | localhost | invalid | | 0 | 0 | N |
| securitynik_db_users | | | | 0 | 0 | Y |
| securitynik_db_admins | | | | 0 | 0 | Y |
| admin | % | *A7300B6D1322C0CFCE601E16B138D8139A4E07B7 | | 0 | 0 | N |
| securitynik | % | *A7300B6D1322C0CFCE601E16B138D8139A4E07B7 | | 0 | 0 | N |
| nakia | localhost | *A7300B6D1322C0CFCE601E16B138D8139A4E07B7 | | 0 | 0 | N |
| neysa@localhost | % | *A7300B6D1322C0CFCE601E16B138D8139A4E07B7 | | 0 | 0 | N |
+-----------------------+-----------+-------------------------------------------+--------------+-----------------+----------------------+---------+
9 rows in set (0.001 sec)
Add users to the securitynik_db_users role ...
MariaDB [securitynik_db]> GRANT securitynik_db_users TO 'securitynik','nakia'@'localhost','neysa@localhost';
Query OK, 0 rows affected (0.010 sec)
... and to the securitynik_db_admin role
MariaDB [securitynik_db]> GRANT securitynik_db_admins TO 'admin';
Query OK, 0 rows affected (0.009 sec)
MariaDB [securitynik_db]> SHOW GRANTS FOR 'securitynik_db_users';
+--------------------------------------------------------------+
| Grants for securitynik_db_users |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `securitynik_db_users` |
| GRANT SELECT ON `securitynik_db`.* TO `securitynik_db_users` |
+--------------------------------------------------------------+
2 rows in set (0.000 sec)
MariaDB [securitynik_db]> SHOW GRANTS FOR 'securitynik_db_admins';
+-----------------------------------------------------------------------+
| Grants for securitynik_db_admins |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `securitynik_db_admins` |
| GRANT ALL PRIVILEGES ON `securitynik_db`.* TO `securitynik_db_admins` |
+-----------------------------------------------------------------------+
2 rows in set (0.000 sec)
Set the default role for user securitynik
MariaDB [securitynik_db]> SET DEFAULT ROLE securitynik_db_users FOR securitynik;
Query OK, 0 rows affected (0.009 sec)
Ok with that in place. Time to get user securitynik to login.
Before logging in, I made the following configuration changes to track the logs for MariaDB.
┌──(kali㉿securitynik)-[~]
└─$ sudo vi /etc/mysql/mariadb.cnf
┌──(kali㉿securitynik)-[~]
└─$ sudo cat /etc/mysql/mariadb.cnf | grep --perl-regexp '\[mariadb\]' --after-context=1
[mariadb]
log_error=/var/log/mysql/mariadb.err
With this in place, the file mariadb.err is now created under /var/log/mysql/mariadb.err
┌──(kali㉿securitynik)-[~]
└─$ sudo ls /var/log/mysql/mariadb.err
/var/log/mysql/mariadb.err
Finally try to login a few times ....
┌──(kali㉿securitynik)-[~]
└─$ mysql --user=securitynik --password='Testing2' --verbose
2022-10-25 16:26:47 31 [Warning] Access denied for user 'securitynik'@'localhost' (using password: YES)
2022-10-25 16:29:50 32 [Warning] Access denied for user 'securitynik'@'localhost' (using password: YES)
2022-10-25 16:29:52 33 [Warning] Access denied for user 'securitynik'@'localhost' (using password: YES)
2022-10-25 16:29:52 34 [Warning] Access denied for user 'securitynik'@'localhost' (using password: YES)
2022-10-25 16:29:53 35 [Warning] Access denied for user 'securitynik'@'localhost' (using password: YES)
2022-10-25 16:29:54 36 [Warning] Access denied for user 'securitynik'@'localhost' (using password: YES)
2022-10-25 16:29:54 37 [Warning] Access denied for user 'securitynik'@'localhost' (using password: YES)
2022-10-25 16:29:56 38 [Warning] Access denied for user 'securitynik'@'localhost' (using password: YES)
2022-10-25 16:29:57 39 [Warning] Access denied for user 'securitynik'@'localhost' (using password: YES)
The above is expected as the password is incorrect. Trying again with the correct password.
┌──(kali㉿securitynik)-[~]
└─$ mysql --user=securitynik --password='Testing1' --verbose
ERROR 4151 (HY000): Access denied, this account is locked
This is expected. When this account was created it was created ACCOUNT LOCK
Let's now alter that account so it can authenticate.
MariaDB [securitynik_db]> ALTER USER 'securitynik' ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.001 sec)
With the above change, we now have successful authentication.
┌──(kali㉿securitynik)-[~]
└─$ mysql --user=securitynik --password='Testing1' --verbose
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.6.8-MariaDB-1 Debian buildd-unstable
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Reading history-file /home/kali/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Confirm the securitynik default role.
MariaDB [(none)]> SELECT CURRENT_ROLE();
--------------
SELECT CURRENT_ROLE()
--------------
+----------------------+
| CURRENT_ROLE() |
+----------------------+
| securitynik_db_users |
+----------------------+
1 row in set (0.000 sec)
With this in place let's set a new password for the securitynik user.
MariaDB [securitynik_db]> SET PASSWORD FOR 'securitynik' = PASSWORD('WelcomeWagon');
Query OK, 0 rows affected (0.009 sec)
Confirm the user can login with the new password. First try the old password.
┌──(kali㉿securitynik)-[~]
└─$ mysql --user=securitynik --password='Testing1' --verbose
ERROR 1045 (28000): Access denied for user 'securitynik'@'localhost' (using password: YES)
Failed as expected. Trying new password ...
┌──(kali㉿securitynik)-[~]
└─$ mysql --user=securitynik --password='WelcomeWagon' --verbose
MariaDB [(none)]>
With this in place, let's tidy up.
MariaDB [securitynik_db]> SELECT user,host,password,default_role FROM mysql.user;
+-----------------------+-----------+-------------------------------------------+----------------------+
| User | Host | Password | default_role |
+-----------------------+-----------+-------------------------------------------+----------------------+
| mariadb.sys | localhost | | |
| root | localhost | invalid | |
| mysql | localhost | invalid | |
| securitynik_db_users | | | |
| securitynik_db_admins | | | |
| admin | % | *A7300B6D1322C0CFCE601E16B138D8139A4E07B7 | |
| securitynik | % | *1D1F0D40B7E2B350296DAF7183062FBA4C8B81C9 | securitynik_db_users |
| nakia | localhost | *A7300B6D1322C0CFCE601E16B138D8139A4E07B7 | |
| neysa@localhost | % | *A7300B6D1322C0CFCE601E16B138D8139A4E07B7 | |
+-----------------------+-----------+-------------------------------------------+----------------------+
9 rows in set (0.001 sec)
Removing user securitynik from the default role securitynik_db_users;
MariaDB [securitynik_db]> SET DEFAULT ROLE NONE FOR securitynik;
Query OK, 0 rows affected (0.001 sec)
MariaDB [securitynik_db]> SELECT user,host,password,default_role FROM mysql.user WHERE user='securitynik';
+-------------+------+-------------------------------------------+--------------+
| User | Host | Password | default_role |
+-------------+------+-------------------------------------------+--------------+
| securitynik | % | *1D1F0D40B7E2B350296DAF7183062FBA4C8B81C9 | |
+-------------+------+-------------------------------------------+--------------+
1 row in set (0.001 sec)
With the default roles removed from securitynik, DROP the roles. Verify the roles still exists and remove any permission previously issued to securitynik_db_users and securitynik_db_admins by removing these roles.
MariaDB [securitynik_db]> SELECT user,is_role FROM mysql.user WHERE user='securitynik_db_users' or user='securitynik_db_admins';
+-----------------------+---------+
| User | is_role |
+-----------------------+---------+
| securitynik_db_users | Y |
| securitynik_db_admins | Y |
+-----------------------+---------+
2 rows in set (0.001 sec)
Drop the roles and verify they no longer exists
MariaDB [securitynik_db]> DROP ROLE securitynik_db_users,securitynik_db_admins;
Query OK, 0 rows affected (0.011 sec)
MariaDB [securitynik_db]> SELECT user,is_role FROM mysql.user WHERE user='securitynik_db_users' or user='securitynik_db_admins';
Empty set (0.001 sec)
This also removed the grants assigned to those roles as is expected.
MariaDB [securitynik_db]> SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Review the data in the interesting_data table
MariaDB [securitynik_db]> SELECT * FROM interesting_data;
+---------+-----------------------------+--------------------+-------+-----------------------+-----------------------------+-----------------------+
| file_no | owner | password | admin | comments | blogs | delete_me |
+---------+-----------------------------+--------------------+-------+-----------------------+-----------------------------+-----------------------+
| 1 | securitynik | Testing1 | Y | Systems Administrator | https://www.securitynik.com | you are to be deleted |
| 2 | booo | Testing1 | Y | Guest User | https://www.example.local | NULL |
| 3 | owner_updated_YouKnowWhoIAm | You changed me :-( | Y | some user | https://www.blah.local | NULL |
| 4 | hello | world | Y | hi | securitynik.local | NULL |
+---------+-----------------------------+--------------------+-------+-----------------------+-----------------------------+-----------------------+
4 rows in set (0.000 sec)
Drop a few rows from the interesting_data table
MariaDB [securitynik_db]> DELETE FROM interesting_data WHERE file_no=1 or file_no=2 or file_no=3;
Verify the records no longer exists.
Query OK, 3 rows affected (0.010 sec)
MariaDB [securitynik_db]> SELECT * FROM interesting_data;
+---------+-------+----------+-------+----------+-------------------+-----------+
| file_no | owner | password | admin | comments | blogs | delete_me |
+---------+-------+----------+-------+----------+-------------------+-----------+
| 4 | hello | world | Y | hi | securitynik.local | NULL |
+---------+-------+----------+-------+----------+-------------------+-----------+
1 row in set (0.002 sec)
Realistically, I could have just dropped the table
MariaDB [securitynik_db]> DROP TABLE interesting_data;
Query OK, 0 rows affected (0.022 sec)
MariaDB [securitynik_db]> SHOW TABLES;
Empty set (0.000 sec)
Time to drop the database and close up shop.
MariaDB [securitynik_db]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| securitynik_db |
| sys |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [securitynik_db]> DROP DATABASE securitynik_db;
Query OK, 0 rows affected (0.013 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.000 sec)
That's it!
References:
https://dev.mysql.com/doc/refman/8.0/en/
https://www.mysqltutorial.org/
https://www.w3schools.com/mysql/mysql_create_table.asp
https://www.mysqltutorial.org/mysql-show-columns/
https://www.rosehosting.com/blog/mysql-show-users/
https://www.softwaretestinghelp.com/mysql-create-user/
https://mariadb.com/kb/en/create-user/
https://mariadb.com/kb/en/error-log/
https://mariadb.com/kb/en/mysqluser-table/
