Authentication and Authorization

Authentication and authorization are two of the most fundamental pillars of database security. Although the two terms are often used together—and are deeply interrelated—they describe distinct processes. Authentication is the act of verifying that a user, application, or system component is who or what it claims to be. Authorization is what happens next: once identity has been confirmed, the database determines what that verified identity is actually permitted to do. A failure in either layer can lead to unauthorized data access, data corruption, regulatory violations, and reputational damage. Understanding how databases implement both processes—and how to configure them correctly—is essential for any database administrator, developer, or security professional.

Authentication: Verifying User Identity

Before a database grants access to any data, it must establish trust in the connecting party. Modern database systems support several authentication mechanisms, each with different security profiles, operational requirements, and use cases.

Username and password authentication is the most widely understood method. A client supplies a username and a secret password; the database compares the supplied credential against a stored representation (typically a hashed value) and either accepts or rejects the connection. While simple to implement, password-based authentication carries inherent risks—passwords can be guessed, reused across systems, or leaked in data breaches—so it must always be combined with strong password policies and account lockout controls.

Operating system (OS) level authentication delegates identity verification to the underlying operating system rather than maintaining a separate credential store inside the database. PostgreSQL's peer authentication method, for example, trusts the OS-reported username for local socket connections. Oracle Database supports OS authentication through the OPS$ prefix convention. This approach is convenient for administrative accounts on a local server but is generally not appropriate for remote application connections where the OS identity of the connecting process may not be meaningful or trustworthy.

Certificate-based authentication uses Public Key Infrastructure (PKI). The client presents a digital certificate signed by a trusted Certificate Authority (CA); the database verifies the certificate's signature and optionally maps the certificate's subject name to a database account. This method is highly resistant to credential-stuffing and phishing attacks because there is no reusable secret string to steal, and it is commonly used for service-to-service connections in environments that already operate a PKI.

Many enterprise environments integrate their databases with external identity providers such as LDAP (Lightweight Directory Access Protocol), Microsoft Active Directory, Kerberos, or OAuth 2.0 / OpenID Connect services. Integration with these systems centralizes identity management: when an employee leaves the organization and their Active Directory account is disabled, their database access is revoked automatically without requiring a separate action inside the database. Kerberos, in particular, provides mutual authentication—both the client and the server verify each other's identity—and issues time-limited tickets rather than transmitting passwords over the network.

Regardless of the authentication method chosen, failed authentication attempts must be logged and monitored. A sudden spike in failed logins for a single account is a classic indicator of a brute-force attack. A large number of failed attempts spread across many accounts from a single IP address suggests a credential-stuffing attack, where attackers try username/password pairs harvested from unrelated data breaches. Database audit logs and SIEM (Security Information and Event Management) tools should be configured to alert on these patterns so that response can begin before an attacker succeeds.

Password Policies and Credential Management

Even when databases support modern authentication alternatives, passwords remain prevalent, making well-defined password policies critical. A robust policy typically enforces several properties simultaneously:

• Minimum length: Current guidance from NIST (SP 800-63B) recommends at least 12–15 characters for human-managed passwords. Longer passwords dramatically increase the computational work required for offline cracking.
• Complexity requirements: Requiring a mix of uppercase letters, lowercase letters, digits, and special characters increases the search space an attacker must explore. However, complexity requirements alone are insufficient if users compensate by making passwords predictable (e.g., Password1!).
• Expiration intervals: Periodic forced rotation limits the window during which a compromised credential remains valid. Many organizations set database account passwords to expire every 90 days, though NIST now recommends against arbitrary rotation unless there is evidence of compromise, arguing it encourages weaker password choices.
• History enforcement: Preventing reuse of a configurable number of previous passwords stops users from cycling back to a known-compromised credential.
• Account lockout: After a defined number of consecutive failed attempts (commonly 5–10), the account should be locked and require administrative or automated reset, directly countering brute-force attacks.

How the database stores credentials is equally important as the policy governing them. Storing passwords in plain text is never acceptable: a single database breach would immediately expose every user's credential. Reversible encryption is nearly as dangerous—it simply adds a decryption step for an attacker who obtains the key. The correct approach is a strong, salted hashing algorithm designed specifically for password storage, such as bcrypt, scrypt, or Argon2. These functions are intentionally slow (computationally expensive) to compute, which makes offline dictionary attacks impractical even when an attacker obtains the hashed values. A salt—a random value unique to each credential—ensures that two users with identical passwords produce different hash outputs, preventing the use of precomputed rainbow tables.

A frequently overlooked but critical step during any database deployment is changing default credentials. Database software ships with well-known default accounts and passwords (for example, Oracle's historical sys/change_on_install, MySQL's empty root password on some installations, or Microsoft SQL Server's sa account). These defaults are documented publicly and are the first targets attackers try. Automated scanning tools used by attackers can locate and compromise an unprotected database within minutes of it being exposed to the internet. Changing all default credentials, disabling unused default accounts, and verifying these steps during a post-installation security checklist is non-negotiable.

Authorization: Controlling What Users Can Do

Once a user or application has been authenticated, the database enforces authorization—a continuous, per-operation evaluation of whether the authenticated identity has permission to perform the requested action. This evaluation happens for every SQL statement executed, not just at connection time.

Authorization is expressed through privileges and permissions that can be scoped to different levels of the database object hierarchy:

The principle of least privilege is the governing philosophy behind sound authorization design. It states that every user, application, and process should receive only the minimum set of permissions necessary to fulfill its specific function—nothing more. A reporting user who only needs to read summary sales data should have SELECT on the relevant tables or views, but not INSERT, UPDATE, DELETE, or any schema-level permissions. An application that inserts customer orders should have INSERT on the orders table, but not DROP TABLE or access to financial records. Overly permissive accounts dramatically expand the blast radius of a compromise: if an attacker exploits an application vulnerability to run arbitrary SQL, least-privilege limits what they can do even after gaining a foothold.

Permissions can be granted or revoked dynamically using SQL statements:

-- Grant read access to the reporting user
GRANT SELECT ON sales.quarterly_summary TO reporting_user;

-- Revoke write access previously given
REVOKE INSERT, UPDATE ON sales.quarterly_summary FROM reporting_user;

This dynamic nature means that access adjustments—for example, temporarily elevating a developer's permissions to diagnose a production issue—can be made precisely and then reversed without any application code changes. Maintaining an audit trail of all GRANT and REVOKE operations is a best practice that supports both security reviews and regulatory compliance.

Role-Based Access Control (RBAC)

Managing permissions individually for every user in a large organization quickly becomes unmanageable and error-prone. Role-Based Access Control addresses this by introducing an intermediate layer: the role. A role is a named collection of privileges. Users are assigned to roles, and they inherit all of the privileges associated with those roles. When a job function changes—say, a set of users now needs access to a new table—the administrator grants the privilege to the role once, and all members instantly inherit the change.

Common role archetypes in database environments include:

• Read-only (reporting): SELECT on designated tables or views. Suitable for analysts, report consumers, and monitoring tools.
• Read-write (application): SELECT, INSERT, UPDATE, and sometimes DELETE on application-owned tables. Scoped to exactly the tables the application needs.
• Schema owner / developer: CREATE, ALTER, and DROP within specific schemas. Used for deployment pipelines and database developers, but should be absent from production application accounts.
• Database administrator (DBA): Superuser or SYSADMIN-level privileges. Should be assigned to as few accounts as possible and used only when genuinely needed; day-to-day administration should use less-privileged accounts.

For example, in PostgreSQL:

-- Create a read-only role
CREATE ROLE readonly_role;
GRANT CONNECT ON DATABASE production_db TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

-- Create a user and assign the role
CREATE USER analyst_alice WITH PASSWORD 'securepassword';
GRANT readonly_role TO analyst_alice;

Many database systems support role nesting—assigning one role to another role to create a hierarchy. For instance, a readwrite_role might itself be granted readonly_role, inheriting its privileges while adding write permissions on top. This reduces redundancy but demands careful design. Deep or circular role hierarchies can make it difficult to reason about the effective permissions of any given user, creating a risk of privilege escalation by inheritance—where a user accumulates more access than intended because of an indirect role chain. Documenting the role hierarchy and auditing effective permissions regularly mitigates this risk.

Applying Authorization Rules at the Database Level

A critical advantage of enforcing authorization inside the database—rather than relying solely on application-layer checks—is that the rules apply universally to every connection. Whether a query is submitted by the web application, a direct SQL client used by an administrator, an ETL pipeline, or a reporting tool, the same permissions are evaluated. Application-layer access control can be bypassed by a sufficiently privileged attacker, a misconfigured tool, or a future integration that the original developers did not anticipate. Database-level rules cannot be circumvented without first obtaining sufficient database credentials.

Views and stored procedures provide an additional authorization boundary. A view is a named query that can be granted permissions independently of the underlying tables. By granting users access to a view but not the base table, administrators expose exactly the data that is appropriate for that user class:

-- Create a view that hides salary information
CREATE VIEW hr.employee_public AS
  SELECT employee_id, first_name, last_name, department
  FROM hr.employees;

-- Grant access to the view only; revoke base table access
GRANT SELECT ON hr.employee_public TO hr_staff_role;
REVOKE SELECT ON hr.employees FROM hr_staff_role;

Stored procedures take this a step further: with definer's rights execution (called SECURITY DEFINER in PostgreSQL and standard SQL), a procedure runs with the privileges of the user who defined it rather than the user who called it. This allows applications to perform narrowly scoped operations—such as inserting a new customer record with all required validation logic—without granting the application any direct table-level permissions at all.

Row-Level Security (RLS) is a powerful feature available in databases such as PostgreSQL, SQL Server, and Oracle that allows the database to automatically filter rows in query results based on the identity or attributes of the querying user. Rather than relying on application code to append a WHERE clause that limits results, the database enforces the filter transparently and consistently:

-- PostgreSQL RLS example: users can only see their own orders
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_isolation_policy ON orders
  USING (customer_id = current_user_id());

-- Even a SELECT * returns only the user's own rows

RLS is particularly valuable in multi-tenant SaaS applications, where a single database table might contain data for thousands of customers and the system must guarantee that no customer can ever see another's data—even if an application bug constructs an incorrect query.

Authentication and Authorization for Application Accounts

Applications connecting to databases represent a special and especially important class of authentication and authorization subject. Unlike human users who log in interactively and can respond to prompts, applications authenticate with static credentials that must be managed carefully. The key principles are:

Scope permissions to exactly what the application needs. Map out every database operation the application performs: which tables it reads, which it writes, which stored procedures it calls. Create a dedicated service account with precisely those permissions and nothing more. If the application never deletes records, it should not have DELETE. If it never modifies the schema, it should not have ALTER TABLE. This constrains the damage possible if the application is exploited through SQL injection or other vulnerabilities.

Never embed credentials in source code. Hardcoded database passwords are among the most common and severe security mistakes found in code repositories. When source code is shared, checked into version control, or accidentally made public, embedded credentials are immediately compromised. Credentials belong in external, access-controlled locations:

• Environment variables: Injected at runtime by the deployment platform; the credential never appears in the codebase.
• Secrets managers: Dedicated services such as HashiCorp Vault, AWS Secrets Manager, or Azure Key Vault store credentials encrypted, control access via IAM policies, and support automatic rotation with notifications to consuming applications.
• Encrypted configuration files: If file-based configuration is used, the file must be encrypted at rest, excluded from version control (via .gitignore), and its encryption key stored separately.

Rotate credentials periodically and after any suspected compromise. Even well-protected credentials can be leaked through log files, memory dumps, network captures, or insider threats. Regularly scheduled rotation—supported by automated secrets management tools—limits the window during which a leaked credential remains valid. When a compromise is suspected or confirmed, immediate rotation is essential. Modern secrets managers can rotate credentials automatically, updating the database account password and notifying or directly updating consuming applications without manual intervention, making this practice far less operationally disruptive than it once was.

Together, authentication and authorization form a defense-in-depth strategy for database security. Authentication ensures that only known, verified identities can establish connections. Authorization ensures that even verified identities can access only the data and operations they are explicitly permitted to use. Layering both—with strong authentication methods, rigorous password policies, least-privilege role design, database-enforced access rules, and secure credential management for applications—creates multiple barriers that an attacker must overcome, significantly reducing the likelihood and impact of a data breach.