Google Cybersecurity Certificate Course 4 - Tools of the Trade - Linux and SQL

This is the fourth course of the Google Cybersecurity Certificate Course on Coursera. Again it’s a mix of my notes and definitions from the course.

My notes start to tail off here because the material is so familiar.

Here is a link to my main page for the course.

Course 4 Overview

• Operating Systems
• Linux
• SQL

Module 1: Intro to operating systems

Power button -> BIOS / UEFI -> Bootloader -> OS

User -> Application -> OS -> Hardware

CLI is like making a meal from scratch. GUI is like ordering food from a restaurant.

Module 2: The Linux operating system

User, that’s you.

Shell, the CLI.

FHS.

Kernel: communicates with hardware. Allocates resources. Manages processes and memory. Most important component of the Linux OS.

Shout out Kali. They say Kali should be used on a VM.

apt list --installed 

The shell helps humans and computers communicate with each other. It is the command-line interpreter and helps humans communicate with the operating system through the command line.

echo hello

After a user types a command into the shell, the shell can return either standard output or standard error. Standard output is information returned by the OS through the shell. Standard error contains error messages returned by the OS through the shell.

expr 3500 * 12

Module 3: Linux commands in the Bash shell

Configure users and groups.

Bash is the default for most distros.

Dollar sign means enter a command.

Argument is information needed by a command.

Root. Think trees. Root directory is a single slash.

man hier

head, tail, less.

grep

grep OS updates.txt

Piping. e.g. ls /home/reports | grep users

read write execute rwx

user group other

d [directory] rwx [permission for user] rwx [permission for group] rwx [permission for other]

World writable means anyone can edit it.

Change permissions with chmod

u / g / o

chmod g+w,o-r access.txt

Reading permissions

In Linux, permissions are represented with a 10-character string. Permissions include:

read: for files, this is the ability to read the file contents; for directories, this is the ability to read all contents in the directory including both files and subdirectories

write: for files, this is the ability to make modifications on the file contents; for directories, this is the ability to create new files in the directory

execute: for files, this is the ability to execute the file if it’s a program; for directories, this is the ability to enter the directory and access its files

These permissions are given to these types of owners:

user: the owner of the file

group: a larger group that the owner is a part of

other: all other users on the system

Problems with logging in as root

• Security risks
• Irreversible mistakes
• Accountability

The purpose of sudo is to temporarily grant elevated permissions to specific users.

useradd
userdel

How to Manage File Permissions in Linux

Linux resources
Google
Stack Overflow
man pages
whatis
apropos

Module 4: Databases and SQL

SQL is an important tool in the world of cybersecurity and is essential when querying databases

SQL query There are two essential keywords in any SQL query: SELECT and FROM.

mysql>SELECT employee_id, device_id
-> FROM employees;

An asterisk instructs SQL to return all columns from the specified table.

mysql> SELECT *
    -> FROM employees;

SELECT customerid, city, country
FROM customers
ORDER BY city DESC;

What is filtering in SQL?
Selecting data that match a certain condition

You are working with the Chinook database and want to return the firstname, lastname, and phone of all employees:

SELECT firstname, lastname, phone
FROM employees;

A security analyst wants to filter the log_in_attempts table for records where the value in the country column is ‘Canada’. What is a valid query for this?

SELECT *
FROM log_in_attempts
WHERE country = 'Canada';

Which pattern matches with any string that starts with the character ‘A’?
'A%'

Activity Retrieve data for login attempts made after 2022-05-09:

SELECT *
FROM log_in_attempts
WHERE login_date > '2022-05-09';

In this case, 125 attempts were made after that date.

Now, include 2022-05-09 in the query:

SELECT *
FROM log_in_attempts
WHERE login_date >= '2022-05-09';

That brings the number up to 165 attempts.

Now query attempts between dates:

SELECT *
FROM log_in_attempts
WHERE login_date
BETWEEN '2022-05-09' AND '2022-05-11';

This returns a value of 123 attempts.

Next, check for login attempts at certain times. In this case, before 0700:

SELECT *
FROM log_in_attempts
WHERE login_time < '07:00:00';

Now check login attempts between 0600 and 0700:

SELECT *
FROM log_in_attempts
WHERE login_time
BETWEEN '06:00:00' AND '07:00:00';

Query for login attempts with event_id greater than or equal to 100:

SELECT *
FROM log_in_attempts
WHERE event_id >= 100;

Narrow it down to return only login attempts with event_id between 100 and 150:

SELECT *
FROM log_in_attempts
WHERE event_id
BETWEEN 100 AND 150;

Filters with AND, OR, and NOT AND, OR, and NOT allow query filtering to return specific information. They are all logical operators.

AND

SELECT *
FROM machines
WHERE operating_system = 'OS 1' AND email_client = 'Email Client 1';

OR

SELECT *
FROM machines
WHERE operating_system = 'OS 1' OR operating_system = 'OS 3';

NOT

SELECT *
FROM machines
WHERE NOT operating_system = 'OS 3';

Combine logical operators:

SELECT firstname, lastname, email, country
FROM customers
WHERE NOT country = 'Canada' AND NOT country = 'USA';

Questions

1. Which filter outputs all records with values in the date column between ‘01-01-2015’ (January 1, 2015) and ‘01-04-2015’ (April 1, 2015)?

WHERE date BETWEEN '01-01-2015' AND '01-04-2015';

1. Which operator is most efficient at returning all records with a status other than ‘successful’?

NOT

1. You are working with the Chinook database. You want to find the first and last names of customers who have a value in the country column of either ‘Brazil’ or ‘Argentina’. Replace –??? with the missing information to complete the query.

SELECT firstname, lastname, country
FROM customers
WHERE country = 'Brazil' OR country = 'Argentina';

Join tables in SQL table.columnName

INNER JOIN Returns rows matching on a specified column that exists in more than one table.

SELECT username, office, operating_system
FROM employees
INNER JOIN machines ON employees.employee_id = machines.employee.id;

Types of joins

• LEFT JOIN
  • Returns all of the records of the first table, but only returns rows of the second table that match on a specified column.
• RIGHT JOIN
  • Returns all of the records of the second table, but only returns rows of the first table that match on a specified column.
• FULL OUTER JOIN
  • Returns all records from all tables.

What is the difference between an inner join and an outer join? Inner joins only return rows that match on a specified column, but outer joins also return rows that don’t match on the specified column.