Tuesday, January 3, 2023

Understanding NMAP's scan techniques: -sS/sT/sA/sW/sM: TCP SYN/Connect()/ACK/Window/Maimon scans

A member of the Toronto Metropolitan University/Rogers Cybersecure Catalyst program, a program I'm currently a mentor for, was using Nmap and could not really see the difference when using the -sW and -sM scan techniques. To help that student and others using Nmap, I thought I should put together a quick blog post.

Before getting into the -sW and -sM, let's take a look at some other TCP scan options. This is important as when we run these tools, it is important that we understand what they are doing in the background.

Staring with Nmap SYN Scan (-sS), we get

┌──(kali㉿securitynik)-[~]
└─$ sudo nmap -sS 10.0.0.106 --reason -p 445 --send-ip --reason -Pn
Starting Nmap 7.92 ( https://nmap.org ) at 2023-01-03 16:04 EST
Nmap scan report for 10.0.0.106
Host is up, received user-set (0.00046s latency).

PORT    STATE SERVICE      REASON
445/tcp open  microsoft-ds syn-ack ttl 128
MAC Address: 08:00:27:88:B8:34 (Oracle VirtualBox virtual NIC)

Nmap done: 1 IP address (1 host up) scanned in 5.65 seconds

Looking at this scan activity under the hood, we see ...

┌──(kali㉿securitynik)-[~]
└─$ sudo tcpdump -nn --interface eth0 'port 445'
tcpdump: verbose output suppressed, use -v[v]... for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), snapshot length 262144 bytes

16:04:18.309853 IP 10.0.0.108.37698 > 10.0.0.106.445: Flags [S], seq 3556209128, win 1024, options [mss 1460], length 0
16:04:18.310294 IP 10.0.0.106.445 > 10.0.0.108.37698: Flags [S.], seq 2634056672, ack 3556209129, win 64240, options [mss 1460], length 0
16:04:18.310312 IP 10.0.0.108.37698 > 10.0.0.106.445: Flags [R], seq 3556209129, win 0, length 0
 

From above, we see 3 packets. The first represents the stimulus. Nmap sends a SYN [S] packet and receive a SYN/ACK [S.]. This "[S.]", tells Nmap, this port is "open". Hence the Nmap result above states "open" and the reason states "syn-ack". On a side note, do you know why there is a 3rd packet? The one in red with "[R]"? Leave a comment in the chat if you do. Getting into the 3rd packet is not important for this post but would love to know your comment if you have one.

Looking at the connect scan (-sT)

┌──(kali㉿securitynik)-[~]
└─$ sudo nmap -sT 10.0.0.106 --reason -p 445 --send-ip --reason -Pn
Starting Nmap 7.92 ( https://nmap.org ) at 2023-01-03 16:04 EST
Nmap scan report for 10.0.0.106
Host is up, received user-set (0.00047s latency).

PORT    STATE SERVICE      REASON
445/tcp open  microsoft-ds syn-ack

Nmap done: 1 IP address (1 host up) scanned in 5.56 seconds

Looking at the output, it does not look any different from the Syn Scan (-sS). Keep in mind, both of these scans so far, returned a state of "open" and reason "syn-ack".

Looking under the hood, we are able to immediately see a difference between the two scans

┌──(kali㉿securitynik)-[~]
└─$ sudo tcpdump -nn --interface eth0 'port 445'
tcpdump: verbose output suppressed, use -v[v]... for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), snapshot length 262144 bytes
16:05:04.611618 IP 10.0.0.108.39368 > 10.0.0.106.445: Flags [S], seq 1022019330, win 64240, options [mss 1460,sackOK,TS val 322962533 ecr 0,nop,wscale 7], length 0
16:05:04.612014 IP 10.0.0.106.445 > 10.0.0.108.39368: Flags [S.], seq 486308284, ack 1022019331, win 65535, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0
16:05:04.612030 IP 10.0.0.108.39368 > 10.0.0.106.445: Flags [.], ack 1, win 502, length 0
16:05:04.612054 IP 10.0.0.108.39368 > 10.0.0.106.445: Flags [R.], seq 1, ack 1, win 502, length 0

From a simplistic view, the Connect Scan resulted in 4 packets, while the Syn Scan resulted in 3. The first 3 packets, represent the complete TCP 3-way handshake. However, there is a fourth packet. Similar to above, it is not important for what we are interested in. However, if you have the answer, why the "[R.]" packet is there, do leave a comment ;-).

If we look at the two outputs from Nmap so far, there is really little to no difference. Continuing with the simplistic view of only looking at the Nmap output, only tells you this host is alive.

Running the ACK Scan

┌──(kali㉿securitynik)-[~]
└─$ sudo nmap -sA 10.0.0.106 --reason -p 445 --send-ip --reason -Pn
Starting Nmap 7.92 ( https://nmap.org ) at 2023-01-03 16:09 EST
Nmap scan report for 10.0.0.106
Host is up, received user-set (0.00030s latency).

PORT    STATE      SERVICE      REASON
445/tcp unfiltered microsoft-ds reset ttl 128
MAC Address: 08:00:27:88:B8:34 (Oracle VirtualBox virtual NIC)

Nmap done: 1 IP address (1 host up) scanned in 5.64 seconds

Once again, the output looks basically the same so far as we saw in the earlier two. However, if you pay close attention, you see the state is "unfiltered". Why unfiltered?


Looking under the hood, we see two packets this time.

┌──(kali㉿securitynik)-[~]
└─$ sudo tcpdump -nn --interface eth0 'port 445'
tcpdump: verbose output suppressed, use -v[v]... for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), snapshot length 262144 bytes
16:09:21.794159 IP 10.0.0.108.50010 > 10.0.0.106.445: Flags [.], ack 2598207558, win 1024, length 0
16:09:21.794442 IP 10.0.0.106.445 > 10.0.0.108.50010: Flags [R], seq 2598207558, win 0, length 0

So far, the output from the first two are basically the same, having a state of "open" and this 3rd "unfiltered". The real difference in how they operate can be seen from under the hood. While the two previous examples had a stimulus of "[S]", this example has a stimulus of "[.]". Notice also the Nmap output states "unfiltered". Nmap knows the port is unfiltered, because it received the "[R]". We can confirm this, by looking at the Nmap output, where the reason shows "reset" besides "unfiltered".

Now to the two scan techniques the user was really concerned about. -sW and -sM. Starting with -sW

┌──(kali㉿securitynik)-[~]
└─$ sudo nmap -sW 10.0.0.106 --reason -p 445 --send-ip --reason -Pn                                                                                           
Starting Nmap 7.92 ( https://nmap.org ) at 2023-01-03 16:13 EST
Nmap scan report for 10.0.0.106
Host is up, received user-set (0.00038s latency).

PORT    STATE  SERVICE      REASON
445/tcp closed microsoft-ds reset ttl 128
MAC Address: 08:00:27:88:B8:34 (Oracle VirtualBox virtual NIC)

Nmap done: 1 IP address (1 host up) scanned in 5.64 seconds

Notice the state is "closed". How does Nmap know it is closed? We should now have an understanding of where to find the answer. 

Looking under the hood, we see two packets.

┌──(kali㉿securitynik)-[~]
└─$ sudo tcpdump -nn --interface eth0 'port 445'
tcpdump: verbose output suppressed, use -v[v]... for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), snapshot length 262144 bytes
16:13:20.105897 IP 10.0.0.108.46454 > 10.0.0.106.445: Flags [.], ack 3050815645, win 1024, length 0
16:13:20.106254 IP 10.0.0.106.445 > 10.0.0.108.46454: Flags [R], seq 3050815645, win 0, length 0

This is much the same as was seen in the ACK scan. This is also because this scan is exactly the same as the ACK scan, difference being that it examines the TCP Windows field when a "[R]" packet is returned.

Wrapping this up with a Maimon scan (-sM)

┌──(kali㉿securitynik)-[~]
└─$ sudo nmap -sM 10.0.0.106 --reason -p 445 --send-ip --reason -Pn
Starting Nmap 7.92 ( https://nmap.org ) at 2023-01-03 16:17 EST
Nmap scan report for 10.0.0.106
Host is up, received user-set (0.00034s latency).

PORT    STATE  SERVICE      REASON
445/tcp closed microsoft-ds reset ttl 128
MAC Address: 08:00:27:88:B8:34 (Oracle VirtualBox virtual NIC)

Nmap done: 1 IP address (1 host up) scanned in 5.61 seconds

Once again, the state is "closed". The output between -sW and -sM looks basically the same.  

Looking under the hood ...

┌──(kali㉿securitynik)-[~]
└─$ sudo tcpdump -nn --interface eth0 'port 445'
tcpdump: verbose output suppressed, use -v[v]... for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), snapshot length 262144 bytes
16:17:06.538048 IP 10.0.0.108.45642 > 10.0.0.106.445: Flags [F.], seq 0, ack 925611209, win 1024, length 0
16:17:06.538370 IP 10.0.0.106.445 > 10.0.0.108.45642: Flags [R], seq 925611209, win 0, length 0

... We have two packets. Notice the difference in the flags? The -sW has flags "[.]" and "[R]". The -sM have flags "[F.]" and "[R]".

You might have noticed, the last 3 outputs where there is the "[R]" RST flag, I did not put them in red. This is because this behavior was expected. The other two above, they are generated from your system's TCP/IP stack because it was not aware of the initial "[S]" packet being sent.

If you are reading this and wonder why I did not go more into what the different flags mean, then come hang out with us at one of the upcoming SANS SEC503: Network Monitoring and Threat Detection in Depthwhere we go deep into packets learn more.

Hopefully this clear up any concerns users of Nmap  have, as it relates to not seeing any difference in the outputs.


Reference:
https://linux.die.net/man/1/nmap

Wednesday, November 2, 2022

Beginning Integer Overflow/Underflow - Signed and Unsigned integers

Still on the journey providing mentorship to the SANS/Ryerson/Rogers Cyber Secure Catalyst Program.

In this post, the ask was to explain integer overflow/underflow. 

Keeping it simple! The basic ideas, in the case of a 32-bit system or code compiled as 32 bits, an integer signed or unsigned, will occupy 32 bits. If we calculate 2**32 we get 4,294,967,296 possible values. In the case of unsigned int, this means, we should be able to get 0 to 4,294,967,295 as the possible values, as unsigned int only allows for positive values. Signed int on the other hand, allows for negative numbers and have a range of −2,147,483,648 to 2,147,483,647. Note, this problem is not only about 32-bit integer the idea is basically applying a value larger to a datatype than it was designed to accommodate. Int is just the example used in this case but it could have been something else.

Let's define a small program to learn more:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
#include <stdio.h>

int main()
    {	  
	  // Define a variable
        unsigned int my_num = 429496725;

        // Get the size of an integer for this compiled code
        printf("Size of my_num is :%d bytes or %d bits \n", sizeof(my_num), sizeof(my_num) * 8);

        // Print the value of my number to the screen
        printf("Current value for my_num is: %u \n", my_num);
        
        return 0;
    }

Let's compile and run this small program

1
2
3
4
┌──(kalisecuritynik)-[~]
└─$ gcc intOverflow.c -o intOverflow -m32 && ./intOverflow 
Size of my_num is :4 bytes or 32 bits 
Current value for my_num is: 429496725 

Above we see the size of the my_num integer is 4 bytes or 32 bits. We also see the current value is set to the maximum value possible for a 32-bit unsigned int. 

What would happen if we add 1 to this program. 

Here is the updated code.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
#include <stdio.h>

int main()
    {
	  // Define a variable
        int  my_num = 4294967295;

        // Get the size of an integer for this compiled code
        printf("Size of my_num is :%d bytes or %d bits \n", sizeof(my_num), sizeof(my_num) * 8);

        // Print the value of my number to the screen
        printf("%u + 1 = %u \n", my_num, my_num + 1);
        
        return 0;
    }

When compiled and run, we get:

1
2
3
4
┌──(kalisecuritynik)-[~]
└─$ gcc intOverflow.c -o intOverflow -m32 && ./intOverflow 
Size of my_num is :4 bytes or 32 bits 
4294967295 + 1 = 0 

Realistically, that value should have been 4294967295 + 1 = 4294967296.

It looks like we hit the overflow.

What would happen if we update the code to add 10. Here we see:

1
2
3
4
┌──(kalisecuritynik)-[~]
└─$ gcc intOverflow.c -o intOverflow -m32 && ./intOverflow 
Size of my_num is :4 bytes or 32 bits 
4294967295 + 10 = 9 

From above, we see we are wrapping around. Previously when we went over by 1 the number did 1-1 which gave a result of 0. When we went over by 10, our answer is 9. 

Anyhow that's it for the unsigned int overflow.


Signed int

As discussed above, in both the signed and unsigned int perspective, the size is 4 bytes or 32 bits. The difference between signed and unsigned, is that signed can accommodate negative numbers. While the range for unsigned is 0 to 4,294,967,295 signed values are from −2,147,483,648 to 2,147,483,647. Let's take the max and - 0 from it.

Here is the code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
#include <stdio.h>

int main()
    {
   // Define a signed integer. Notice the "-"
        signed int  my_num_signed = -2147483648;

        // Get the size of an signed integer for this compiled code
        printf("Size of my_num_signed is :%d bytes or %d bits \n", sizeof(my_num_signed), sizeof(my_num_signed) * 8);

        // Print the value of my number to the screen
        printf("%d - 0 = %d \n", my_num_signed, my_num_signed -0);
        
        return 0;
    }

When compiled, here is what we have:

1
2
3
4
┌──(kalisecuritynik)-[~]
└─$ gcc intOverflow.c -o intOverflow -m32 && ./intOverflow 
Size of my_num_signed is :4 bytes or 32 bits 
-2147483648 - 0 = -2147483648

The above is expected as -2147483648 - 0 = -2147483648.

Let's modify the code to -1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
#include <stdio.h>

int main()
    {
        signed int  my_num_signed = -2147483648;
        // Get the size of an signed integer for this compiled code
        printf("Size of my_num_signed is :%d bytes or %d bits \n", sizeof(my_num_signed), sizeof(my_num_signed) * 8);

        // Print the value of my number to the screen
        printf("%d - 1 = %d \n", my_num_signed, my_num_signed -1);
        
        return 0;
    }

When this is compiled we see 

1
2
3
4
┌──(kalisecuritynik)-[~]
└─$ gcc intOverflow.c -o intOverflow -m32 && ./intOverflow 
Size of my_num_signed is :4 bytes or 32 bits 
-2147483648 - 1 = 2147483647

Oh! oh! oh! oh! What we expect is that -2147483648 -1 should equal -2,147,483,649. Therefore, above resulted in an underflow.

Similarly, if we -10 we see:

1
2
3
4
┌──(kalisecuritynik)-[~]
└─$ gcc intOverflow.c -o intOverflow -m32 && ./intOverflow 
Size of my_num_signed is :4 bytes or 32 bits 
-2147483648 - 10 = 2147483638

Let's wrap up with one more. This time, taking the max number on the positive side of the signed values.

Here is the code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
#include <stdio.h>

int main()
    {
        signed int  my_num_signed = 2147483647;
        // Get the size of an signed integer for this compiled code
        printf("Size of my_num_signed is :%d bytes or %d bits \n", sizeof(my_num_signed), sizeof(my_num_signed) * 8);

        // Print the value of my number to the screen
        printf("%d + 1 = %d \n", my_num_signed, my_num_signed + 1);
        
        return 0;
    }

When compiled and run we see ...

1
2
3
4
┌──(kalisecuritynik)-[~]
└─$ gcc intOverflow.c -o intOverflow -m32 && ./intOverflow 
Size of my_num_signed is :4 bytes or 32 bits 
2147483647 + 1 = -2147483648 

Tuesday, October 25, 2022

Beginning MariaDB / MySQL - Basic Administration Stuff

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)

Alternatively

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_admins

MariaDB [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

Which resulted in:

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/