SQL Security Best Practices: Protecting Data and Access
Database security is about more than a strong password. Here are the practices I implement on every database to limit damage and prevent breaches. Database security is the set of practices that prevent unauthorized access, data leaks, and destructive mistakes. After investigating a few incidents caused by overly permissive accounts and SQL injection, I developed a checklist of security practices that I apply to every database. These are not exotic techniques. They are the fundamentals that most breaches exploit when they are missing. Principle of Least Privilege Every database account should have the minimum permissions it needs to do its job and nothing more. The application account does not need to create tables. The reporting account does not need to delete rows. The backup account does not need to read every column. I create roles for each function and grant only the necessary privileges. . Create a role for the application CREATE ROLE app_readwrite; GRANT SELECT, INSERT, UPDATE ON orders TO app_readwrite; GRANT SELECT, INSERT, UPDATE ON customers TO app_readwrite; GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_readwrite; . The application connects as this role, not as root CREATE USER app_service WITH PASSWORD 'strong_password';…