This is a continuation of my posts on relational databases, started here. Previous posts on this theme, “Data Modeling“, “Let’s Talk About Database Schema“, “The Exasperating Topic of Database Indexes“, “The Keys & Relationships of Relational Databases“, “Relational Database Query Optimization“, “Normalization in Relational Databases“, and “Database (DB) Caching and DB Tertiary Caching“.

Basic Security Architecture

Relational Database Management Systems (RDBMS) like SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL are designed with robust security architectures. The core components typically include:

1. Authentication: Verifying the identity of users accessing the database.
2. Authorization: Determining what authenticated users are allowed to do.
3. Access Control: Implementing permissions and roles to manage user access.
4. Encryption: Protecting data at rest and in transit.
5. Auditing and Logging: Tracking access and changes to the database for security and compliance.

Authentication Mechanisms

Authentication is the first line of defense in database security. It ensures that only legitimate users can access the database. The primary methods include:

• Username and Password: The most common method, where users provide credentials that are verified against the database’s security system.
• Integrated Security: Leveraging the operating system or network security (like Windows Authentication in SQL Server) for user validation.
• Certificates and Keys: Using digital certificates or cryptographic keys for more secure authentication.

Database-Specific Security and Authentication

SQL Server

• Windows Authentication: Integrates with Windows user accounts, providing a seamless and secure authentication mechanism.
• SQL Server Authentication: Involves creating specific database users and passwords.
• Roles and Permissions: SQL Server offers fixed server roles, fixed database roles, and user-defined roles for granular access control.
• SSO Integration: Supports integration with Active Directory for single sign-on capabilities.

Oracle

• Oracle Database Authentication: Users are authenticated by the database itself.
• OS Authentication: Oracle can use credentials from the operating system.
• Roles and Privileges: Oracle has a sophisticated role-based access control system, with predefined roles like DBA and user-defined roles.
• Oracle Wallet: For storing and managing credentials, enhancing security for automated login processes.

MariaDB/MySQL

• Standard Authentication: Username and password-based, with the option of using SHA256 for password encryption.
• Pluggable Authentication: Supports external authentication methods like PAM (Pluggable Authentication Modules) or Windows native authentication.
• Role-Based Access Control: Introduced in later versions, allowing for more flexible and manageable permissions.
• SSL/TLS Encryption: For securing connections between the client and the server.

PostgreSQL

• Role-Based Authentication: PostgreSQL uses roles to handle both authentication and authorization.
• Password Authentication: Supports MD5 or SCRAM-SHA-256 for password encryption.
• Peer Authentication: For local connections, relying on OS user credentials.
• SSO Integration: Can integrate with external authentication systems like Kerberos.

Continue reading “Security and Authentication in Relational Databases” →