Users, Privileges & Backups

A database holds a business's most valuable data: customers, orders, salaries. Up to now you have probably used the all-powerful root account for everything. That is fine on your own laptop, but a real application needs separate accounts with limited rights, protection against attackers who type SQL into your forms, and backups for the day something goes wrong.

In this lesson you will create MySQL users, give them only the permissions they need, see how SQL injection works and how prepared statements stop it, and back up and restore a database with mysqldump. User and privilege commands run only on a real MySQL server, not in the browser.

MySQL accounts

A MySQL account has two parts: a user name and the host it may connect from, written as 'user'@'host':

CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Long-Random-Passw0rd-Here';
CREATE USER 'reports'@'10.0.0.%' IDENTIFIED BY 'Another-Long-Passw0rd';

• 'localhost' allows connections only from the same machine as the server. This is right for a website whose PHP code runs on the same server as MySQL.
• '10.0.0.%' allows any address starting with 10.0.0., such as an office network. % is a wildcard.
• '%' alone means "from anywhere". Avoid it for important accounts.

New accounts use the caching_sha2_password authentication method, the default since MySQL 8.0. The older mysql_native_password method is switched off by default in MySQL 8.4 and was removed in the 9.x series, so very old client libraries may need updating before they can connect.

Change a password with ALTER USER 'shop_app'@'localhost' IDENTIFIED BY 'New-Long-Passw0rd';, and remove an account with DROP USER 'reports'@'10.0.0.%';.

GRANT: least privilege

A new account can log in but can do almost nothing. You add permissions, called privileges, with GRANT. Follow the principle of least privilege: give each account only what its job needs, nothing more.

The website needs to read and write rows in the shop database, but it never needs to drop tables or create users:

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';

SHOW GRANTS FOR 'shop_app'@'localhost';

Grants for shop_app@localhost
--------------------------------------------------------------------------
GRANT USAGE ON *.* TO `shop_app`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `shop_app`@`localhost`
(2 rows)

USAGE means "may connect, nothing else"; every account has it. shop.* means every table in the shop database. You can be more precise:

The reporting account should only read, and should not see salaries. Grant it read access to the tables it needs and to the employee_directory view from the Views lesson, but not to employees itself:

GRANT SELECT ON shop.orders TO 'reports'@'10.0.0.%';
GRANT SELECT ON shop.order_items TO 'reports'@'10.0.0.%';
GRANT SELECT ON shop.employee_directory TO 'reports'@'10.0.0.%';

REVOKE SELECT ON shop.order_items FROM 'reports'@'10.0.0.%';   -- take one back

Why bother? If an attacker steals the website's password, they can still damage rows, but they cannot drop the database, read other databases or create new admin accounts. Least privilege limits how bad a bad day can get.

Roles: privileges for a whole team

When several people need the same rights, create a role, grant privileges to the role, and grant the role to people:

CREATE ROLE 'sales_read';
GRANT SELECT ON shop.orders TO 'sales_read';
GRANT SELECT ON shop.customers TO 'sales_read';

CREATE USER 'simran'@'localhost' IDENTIFIED BY 'Simran-Long-Passw0rd';
GRANT 'sales_read' TO 'simran'@'localhost';
SET DEFAULT ROLE ALL TO 'simran'@'localhost';

SET DEFAULT ROLE makes the role active automatically when Simran logs in. When the sales team's needs change, you change the role once instead of every account.

SQL injection

SQL injection is one of the most common and damaging attacks on websites. It happens when a program builds a query by gluing user input into the SQL text. Imagine a login form whose code builds this query from what the user typed:

SELECT ... WHERE email = '<what the user typed>' AND last_name = '<what the user typed>'

An honest user types an email and a surname. An attacker types ' OR '1' = '1 as the surname. The quotes they typed close your quote early and add a condition that is always true. Compare the two queries the program would build:

SELECT COUNT(*) AS matching_customers
FROM customers
WHERE email = 'priya.verma@example.com' AND last_name = 'Sharma';

SELECT COUNT(*) AS matching_customers
FROM customers
WHERE email = 'priya.verma@example.com' AND last_name = '' OR '1' = '1';

matching_customers
------------------
0
(1 row)

matching_customers
------------------
12
(1 row)

The honest query with a wrong surname matches nobody. The injected query matches every customer, because AND is evaluated before OR, and '1' = '1' is always true. A login built this way lets the attacker in. Worse attacks read other tables or change data.

The fix is not to "clean" the input with your own code. The fix is prepared statements: you send the SQL with placeholders, and send the values separately. The database never treats the values as SQL, whatever characters they contain. In PHP with PDO:

$stmt = $pdo->prepare('SELECT customer_id, first_name FROM customers WHERE email = ? AND last_name = ?');
$stmt->execute([$email, $lastName]);
$customer = $stmt->fetch();

You will build complete pages this way in CRUD with Prepared Statements. MySQL also supports prepared statements in plain SQL, which shows the same idea:

PREPARE find_customer FROM 'SELECT first_name FROM customers WHERE email = ?';
SET @email = 'priya.verma@example.com';
EXECUTE find_customer USING @email;
DEALLOCATE PREPARE find_customer;

Least privilege is your second line of defence: an injection through shop_app cannot run DROP DATABASE, because that account has no such privilege.

Backups with mysqldump

mysqldump is a command-line tool that writes a database out as a .sql file full of CREATE TABLE and INSERT statements. Run it in a terminal, not inside MySQL:

mysqldump -u root -p --single-transaction --routines shop > shop-2025-06-10.sql

• -p makes it ask for the password, so the password does not appear on screen or in your command history.
• --single-transaction takes a consistent snapshot of InnoDB tables without locking them, so the website keeps working during the backup.
• --routines adds stored procedures and functions, which are left out otherwise. Triggers are included by default.

To restore, create an empty database and feed the file to the mysql client:

mysql -u root -p -e "CREATE DATABASE shop_restored CHARACTER SET utf8mb4"
mysql -u root -p shop_restored < shop-2025-06-10.sql

On Windows with XAMPP, these tools live in C:\xampp\mysql\bin. XAMPP's root account has no password by default, which is acceptable only on your own computer.

A backup plan is more than one command:

1. Automate it. Run the dump every night with a scheduled task (Task Scheduler on Windows, cron on Linux).
2. Keep copies elsewhere. A backup on the same disk as the database dies with that disk. Copy it to another machine or to cloud storage.
3. Keep several days. If a mistake is found a week later, last night's backup already contains it.
4. Test restores. A backup you have never restored is only a hope. Restore into a test database regularly and check the row counts.

For large databases, MySQL Shell has faster dump and load utilities, and the server's binary log lets you replay changes up to a moment just before an accident. Those are tools to explore once the basics are in place.

Key points

• MySQL accounts are 'user'@'host'; create them with CREATE USER and remove them with DROP USER.
• Use GRANT to give only the privileges an account needs, on only the databases, tables or views it needs.
• Roles group privileges for teams; SHOW GRANTS shows what an account can do.
• SQL injection happens when user input is glued into SQL text; prepared statements stop it.
• mysqldump --single-transaction backs up InnoDB databases; restore with the mysql client.
• Automate backups, store them off the server, and test restores.

CREATE USER 'tanvi'@'localhost' IDENTIFIED BY 'Tanvi-Long-Random-Passw0rd';

GRANT SELECT ON shop.customers TO 'tanvi'@'localhost';
GRANT SELECT ON shop.orders TO 'tanvi'@'localhost';
GRANT UPDATE (status) ON shop.orders TO 'tanvi'@'localhost';

SHOW GRANTS FOR 'tanvi'@'localhost';