1. Authentication and authorization
Authentication and Authorization in Databases
Database security depends on authentication (verifying user identity) and authorization (controlling user access). Both mechanisms protect data from unauthorized access, leaks, or corruption.
1. Authentication: Verifying User Identity
Authentication ensures that only legitimate users can access the database.
1.1 Common Authentication Methods
✅ Username & Password: Traditional method where users log in with credentials.
✅ LDAP (Lightweight Directory Access Protocol): Centralized authentication using Active Directory or OpenLDAP.
✅ Kerberos: Secure ticket-based authentication system used in enterprise environments.
✅ OAuth & OpenID Connect: Token-based authentication for cloud-based databases.
✅ Certificate-Based Authentication: Uses SSL/TLS certificates instead of passwords.
✅ Multi-Factor Authentication (MFA): Requires an additional verification step, such as OTPs or biometrics.
1.2 Example: Creating a Database User (PostgreSQL & MySQL)
🔹 PostgreSQL:
CREATE ROLE db_user WITH LOGIN PASSWORD 'securepass';
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'securepass';
2. Authorization: Controlling Access
Authorization defines what users can do once authenticated.
2.1 Database Access Control Models
🔹 Role-Based Access Control (RBAC): Assigns roles with specific permissions (e.g., admin, developer, read-only).
🔹 Discretionary Access Control (DAC): Users have control over their objects (e.g., tables, views).
🔹 Mandatory Access Control (MAC): Enforces strict rules based on security classifications (used in military or high-security environments).
2.2 Granting Permissions
✅ PostgreSQL Example:
GRANT SELECT, INSERT ON customers TO db_user;
REVOKE DELETE ON customers FROM db_user;
GRANT SELECT, INSERT ON mydb.customers TO 'db_user'@'localhost';
REVOKE DELETE ON mydb.customers FROM 'db_user'@'localhost';
GRANT EXECUTE ON procedure_name TO db_user;
DENY DELETE ON employees TO db_user;
3. Advanced Security Features
🔹 Row-Level Security (RLS): Restricts access to specific rows in a table based on user roles.
🔹 Column-Level Security: Hides or restricts access to specific columns (e.g., hiding SSNs from regular users).
🔹 Data Masking: Obscures sensitive data from unauthorized users.
🔹 Encryption: Ensures stored and transmitted data remains confidential.
PostgreSQL Row-Level Security Example
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_orders ON orders FOR SELECT
USING (user_id = current_user);
4. Best Practices for Authentication & Authorization
✅ Use strong authentication mechanisms (MFA, OAuth, Kerberos).
✅ Follow the principle of least privilege (grant only necessary permissions).
✅ Regularly review and audit user permissions.
✅ Implement role-based access control (RBAC) for better security.
✅ Use encrypted connections (SSL/TLS) to protect credentials.
✅ Enable logging and monitoring to detect unauthorized access.
By enforcing strong authentication and well-defined authorization rules, databases can be protected against unauthorized access and security threats.