Why SQL security awareness matters - See It in Action

Why SQL Security Awareness Matters

📖 Scenario: You are working as a junior database administrator for a small company. Your manager wants you to understand why SQL security is important to protect sensitive company data from unauthorized access and misuse.

🎯 Goal: Build a simple SQL example that shows how to create a user table, add a user with a password, and then demonstrate how to restrict access to this sensitive data using SQL commands.

📋 What You'll Learn

Create a table called users with columns id, username, and password

Insert one user with username 'admin' and password 'secure123'

Create a role called read_only that can only select data from the users table

Grant the read_only role SELECT permission on the users table

💡 Why This Matters

🌍 Real World

Companies use SQL security to protect sensitive data like user passwords and personal information from unauthorized access.

💼 Career

Database administrators and developers must understand SQL security to keep data safe and comply with privacy laws.

1

Create the users table

Write a SQL statement to create a table called users with three columns: id as an integer primary key, username as a text field, and password as a text field.

SQL

-- Create the users table with id, username, and password columns
-- Your code here

Need a hint?

Use CREATE TABLE followed by the table name and define each column with its data type. Mark id as the primary key.

2

Insert a user into the users table

Write a SQL statement to insert a user with id 1, username 'admin', and password 'secure123' into the users table.

SQL

CREATE TABLE users (
  id INT PRIMARY KEY,
  username TEXT,
  password TEXT
);
-- Insert the admin user
-- Your code here

Need a hint?

Use INSERT INTO with the column names and VALUES to add the user data.

3

Create a read_only role

Write a SQL statement to create a role called read_only that will be used to restrict access to the users table.

SQL

CREATE TABLE users (
  id INT PRIMARY KEY,
  username TEXT,
  password TEXT
);

INSERT INTO users (id, username, password) VALUES (1, 'admin', 'secure123');
-- Create the read_only role
-- Your code here

Need a hint?

Use CREATE ROLE followed by the role name to create a new role.

4

Grant SELECT permission to the read_only role

Write a SQL statement to grant SELECT permission on the users table to the read_only role.

SQL

CREATE TABLE users (
  id INT PRIMARY KEY,
  username TEXT,
  password TEXT
);

INSERT INTO users (id, username, password) VALUES (1, 'admin', 'secure123');

CREATE ROLE read_only;
-- Grant SELECT permission to read_only role
-- Your code here

Need a hint?

Use GRANT SELECT ON followed by the table name and TO the role name.