In this lesson, you will learn how to secure your database by applying the "Principle of Least Privilege" to ensure users only have the access they absolutely need.
The Principle of Least Privilege
In a database, not everyone needs to see or change everything. If a junior analyst only needs to generate monthly reports, they should not have permission to DROP tables or modify payroll records. The Principle of Least Privilege is a security best practice where you grant users the bare minimum level of access required to perform their specific job functions.
By restricting access, you prevent accidental data loss (like an employee mistakenly deleting a table) and limit the potential damage if a user account is compromised.
Managing Access with Grant and Revoke
In SQL, we manage this access using the GRANT and REVOKE commands. Instead of giving everyone "Admin" access, you selectively provide permissions on specific tables or views.
For example, if you want a user named analyst_user to only be able to view data in the sales table, you would grant them the SELECT permission:
-- Grant read-only access to a specific table
GRANT SELECT ON sales TO analyst_user;
-- Prevent that user from modifying any data
REVOKE INSERT, UPDATE, DELETE ON sales FROM analyst_user;
Why Use Views for Security?
Often, you don't want a user to see an entire table, especially if it contains sensitive columns like social security numbers or private contact info. You can combine security with the Views we discussed previously. By creating a view that excludes sensitive columns, you can grant the user access to the view instead of the base table.
-- Create a view that hides sensitive data
CREATE VIEW public_sales_summary AS
SELECT product_name, quantity_sold
FROM sales;
-- Grant access only to the safe view
GRANT SELECT ON public_sales_summary TO analyst_user;
Even if the user tries to query the underlying sales table, their request will be denied because you have strictly limited their permissions to only the public_sales_summary view. By controlling who can do what, you ensure your data remains accurate and secure.
Key Takeaway: Always apply the Principle of Least Privilege by granting only the minimum permissions necessary for a user to perform their specific tasks.