SQL Security
Databases store some of the most valuable assets of an application user data, financial records, business secrets, and operational information. While writing correct queries is important, protecting data from unauthorized access is equally critical.
In real-world systems, not every user should be allowed to:
- View all tables
- Modify sensitive data
- Delete records
- Perform administrative operations
To handle this safely, SQL provides a built-in security and access control mechanism. This mechanism is based on users, roles, and privileges, managed using the GRANT and REVOKE commands.
Understanding SQL Security Basics
SQL security follows a simple principle:
Give users only the permissions they need, nothing more.
This concept is known as the Principle of Least Privilege.
Users in SQL
A user represents a person, application, or service that connects to the database.
Each user:
- Has login credentials
- Connects to the database independently
- Can be assigned specific permissions
Example: Creating a User
CREATE USER report_user IDENTIFIED BY report123;This creates a database user with authentication details.
Why Multiple Users Are Needed
Different users exist for different purposes:
- Admin users
- Application users
- Reporting users
- Read-only users
Each user should have limited and appropriate access.
Roles in SQL
A role is a collection of privileges that can be assigned to users.
Instead of granting permissions individually:
- Create a role
- Assign permissions to the role
- Assign the role to users
This makes security easier to manage and scale.
Example: Creating a Role
CREATE ROLE readonly_role;Granting Permissions to a Role
GRANT SELECT ON employees TO readonly_role;Assigning Role to User
GRANT readonly_role TO report_user;Now the user inherits all permissions of the role.
Benefits of Using Roles
- Centralized permission management
- Easy onboarding and offboarding
- Reduced security mistakes
- Cleaner administration
Roles are a best practice in professional environments.
GRANT Command
The GRANT command is used to give permissions to users or roles.
Permissions can include:
- SELECT
- INSERT
- UPDATE
- DELETE
- EXECUTE
- ALL
Example: Granting Privileges
GRANT SELECT, INSERT ON employees TO app_user;This allows:
- Reading data
- Inserting new records
- No update or delete access
Granting ALL Privileges
GRANT ALL ON employees TO admin_user;Use this cautiously.
REVOKE Command
The REVOKE command is used to remove permissions that were previously granted.
It is essential for:
- Security tightening
- Role changes
- User deactivation
Example: Revoking Privileges
REVOKE INSERT ON employees FROM app_user;After this:
- User can no longer insert records
- Other privileges remain intact
Granting Permissions on Different Objects
Permissions can be granted on:
- Tables
- Views
- Stored procedures
- Functions
- Schemas
This allows fine-grained access control.
Real-World Security Scenario
Consider an HR system:
- HR staff can update employee data
- Managers can only view data
- Auditors can access reports
Using:
- Separate users
- Dedicated roles
- Controlled GRANT and REVOKE
The system remains secure and auditable.
Common Security Mistakes
- Granting ALL privileges unnecessarily
- Not using roles
- Sharing database credentials
- Forgetting to revoke unused access
These mistakes often lead to data breaches.
Two Minute Drill
- Users represent database access
- Role group permissions
- GRANT give access
- REVOKE removes access
- Least privilege is key
- Security must be proactive