9. Security

Overview

By the end of this chapter, you will be able to understand the need for access control. We will learn how to create users and user roles in MySQL. We will also learn how to grant and revoke permissions to user roles.

Introduction

In the previous chapter, we learned about SQL programming. We saw that SQL stored procedures, functions, and triggers help us automate database queries and integrate web and desktop applications with SQL database resources. We can now approach database tasks, problems, and questions with confidence. However, we have only worked in sandboxes, or safe, isolated environments. We haven't had to worry about security threats and hazards. Unfortunately, real-world scenarios potentially involve major security threats and hazards, which can lead to the theft of and damage to valuable data.

Database resources are valuable, and bad actors have plenty of motivation to steal, damage, and/or destroy data. Databases that house data involving national security information, financial information, medical histories, and personal employment histories are subject to malicious attacks from hostile actors. Hackers have successfully attacked government and corporate databases from the...

Access Control (Authorization)

So far, we've had full rights and control over our database resources because we created them as operating system (OS) account administrators. We could make any changes we wanted. Therefore, for modern databases, security focuses on who can do what to specific database resources in a granular way.

First, the database needs to identify or authenticate every user who wishes to access the database resources. A user will typically see a prompt to supply a login string (username) and a password string, to enter the system. MySQL has tools that define users with those login and password strings. Next, the database needs to authorize, or allow, each user one or more specific actions on one or more database components.

For a modern database, a user must supply a login string and a password string to gain entry to the system. Then, we can use those tools to give users permission to view, create, delete, and/or update all the database resources within...

Summary

In this chapter, we saw that MySQL offers flexible tools that allow the fine-grained security configuration of virtually all database resources. We have a responsibility to research the security needs of organization stakeholders, age lever these security tools to protect the database resources entrusted to us and defend the organizations that rely on those resources.

In the next chapter, we'll return back to programming and see some of the methods that will be used to provide statistical data from the database.