SQL Security, Backup & Restoration:

Now that we have our employees and departments tables with stored procedures, let’s explore security, backup, and restoration concepts.

User Privileges (Restricting Access): Instead of giving full access to every user, restrict privileges using GRANT statements.

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'securepassword';

GRANT SELECT ON company.* TO 'readonly_user'@'localhost';

This user can only run SELECT queries on the company database (assuming our tables are in company). The user cannot modify or delete data.

User with only insert access:

CREATE USER 'data_entry_user'@'localhost' IDENTIFIED BY 'password123';
GRANT INSERT ON learningschema.employees TO 'data_entry_user'@'localhost';
FLUSH PRIVILEGES;

This user can only add new employees but cannot delete or update records.

Revoking privileges:

REVOKE INPRIMARYSERT ON learningschema.employees FROM 'data_entry_user'@'localhost';

Backup strategies: Full Database Backup (Using mysqldump):

mysqldump -u root -p company > company_backup.sql

Table-Specific Backup: Take only employee table

mysqldump -u root -p company employees > employees_backup.sql

Data Restoration: Restoring the Entire Database If the database is lost or corrupted, restore it using:

mysql -u root -p company < company_backup.sql

Restoring a Single Table:

mysql -u root -p company < employees_backup.sql

Prevention against SQL injection:

PREPARE stmt FROM 'SELECT * FROM employees WHERE name = ?';
SET @name = 'John Doe';
EXECUTE stmt USING @name;

This ensures user input is properly escaped, preventing SQL injection.

GRANT SELECT, INSERT ON learningschema.* TO 'secure_user'@'192.168.1.100';

Restrict Users to Specific IP Addresses:

Showing the privileges of current user:

SHOW GRANTS FOR CURRENT_USER()