What's the difference between authentication and authorization at the database level?
Quick Answer
Authentication answers "who are you" — verifying an identity via credentials (password, certificate, IAM token) before allowing any connection at all. Authorization answers "what are you allowed to do" — once connected as a verified identity, which specific operations (SELECT, INSERT, DROP) on which specific objects (tables, columns, schemas) that identity's granted privileges permit. A database always performs authentication first (to establish identity) and then authorization on every subsequent operation (to check permissions for that identity).
Detailed Answer
Authentication — establishing identity
Client connects: "I am app_user, here's my password/certificate/token"
Database: verifies the credential -- if valid, the connection is established
as that specific, verified identity. If invalid, connection is rejected outright.
Common authentication mechanisms: password-based (the simplest, and weakest if not paired with strong password policies and TLS), certificate-based (mutual TLS, where the client presents a certificate the server trusts), and identity-federation/IAM-based (the database trusts tokens issued by an external identity provider — e.g., AWS RDS IAM authentication, or Kerberos/Active Directory integration in enterprise environments).
Authorization — establishing permissions
Once connected as a verified identity, every subsequent operation is checked against that identity's granted privileges:
-- Connected as app_user (already authenticated)
SELECT * FROM orders; -- allowed, if app_user was GRANTed SELECT on orders
DROP TABLE orders; -- rejected: "permission denied", if app_user lacks DROP privilege
SELECT * FROM admin_secrets; -- rejected: "permission denied", if app_user has no grant on this table
Authorization is enforced via the granted privilege model (GRANT/REVOKE, roles — see the least-privilege question) and is checked per operation, not just once at connection time — a successfully authenticated connection doesn't mean unrestricted access; it means access exactly as broad as that identity's granted permissions.
Why the distinction matters practically
A connection can be successfully authenticated (the password/certificate was valid) but still have every actual operation denied by authorization (the identity has no useful privileges granted) — these are two entirely separate failure modes with different causes and fixes. "Authentication failed" means the credential itself was wrong/invalid; "permission denied" means the credential was valid, but that identity simply isn't allowed to do the specific thing it tried to do. Confusing the two when debugging a production access issue wastes time looking in the wrong place (resetting a password won't fix a missing GRANT, and vice versa).
How this maps to the broader security picture
- Authentication is the front door — verifying you're really who you claim to be before letting you in at all.
- Authorization is what happens at every room inside the house once you're in — just because you got through the front door doesn't mean every room is unlocked to you.
Both are necessary and distinct layers: strong authentication with overly broad authorization (everyone who can log in has full access to everything) still leaves you exposed to any authenticated-but-compromised or malicious identity; conversely, tight authorization is useless if authentication itself can be trivially bypassed or credentials are easily stolen/guessed. A secure system needs both layers implemented deliberately and independently.