Database Authorization & Access Control | Complete Guide to User Permissions & Security

Authorization and Access Control

Authorization is the process that determines what an authenticated user is allowed to do within the database. It defines permissions and access levels for different users or roles.

Access Control enforces these permissions by restricting database actions like reading, writing, or deleting data.

Purpose

• To protect database objects (tables, views, schemas, etc.).
• To ensure users perform only authorized operations.
• To implement least privilege principle, minimizing damage from accidental misuse or attacks.

Types of Access Control

Type	Description
Discretionary Access Control (DAC)	Access rights are granted or revoked by the data owner using commands like GRANT or REVOKE.
Mandatory Access Control (MAC)	Access is controlled by a central authority based on predefined policies (e.g., government-level databases).
Role-Based Access Control (RBAC)	Permissions are assigned to roles, and users are assigned roles (common in enterprise DBMS).

SQL Authorization Commands

1. GRANT: To assign privileges.

   GRANT SELECT, INSERT ON student TO 'teacher'@'localhost';

2. REVOKE: To remove privileges.

   REVOKE INSERT ON student FROM 'teacher'@'localhost';

Common Privileges

Privilege	Description
SELECT	Read data from a table/view.
INSERT	Add new records.
UPDATE	Modify existing records.
DELETE	Remove records.
CREATE / DROP	Create or delete database objects.
EXECUTE	Run stored procedures or functions.

Access Control Levels

1. User Level: Access to database or schema.
2. Object Level: Access to specific tables, views, or functions.
3. Column Level: Restrict visibility to specific columns (useful for sensitive data like salary or password).
4. Row Level: Access limited to certain rows based on user identity (via WHERE clauses or policies).

---

Relationship Between Authentication and Authorization

Aspect	Authentication	Authorization
Purpose	Verifies who the user is.	Determines what the user can do.
Stage	Performed before authorization.	Follows successful authentication.
Based on	Identity verification (passwords, tokens).	Access policies, roles, and privileges.
Outcome	Grants or denies login.	Grants or denies specific actions.

---

DBMS Example Scenario

Suppose a banking database has two roles:

• Admin: Can view and modify all customer data.
• Teller: Can only view customer balances.

Implementation:

CREATE ROLE teller;
CREATE ROLE admin;

GRANT SELECT ON accounts TO teller;
GRANT ALL PRIVILEGES ON accounts TO admin;

CREATE USER 'rahul'@'localhost' IDENTIFIED BY 'pass123';
GRANT teller TO 'rahul'@'localhost';

Here, authentication verifies Rahul’s identity, and authorization restricts his access only to viewing data.

---

Summary Table

Concept	Definition	Key Mechanism	Example
Authentication	Verifies user identity	Passwords, MFA, Tokens	CREATE USER
Authorization	Determines access level	Roles, GRANT, REVOKE	GRANT SELECT
Access Control	Enforces authorization rules	DAC, MAC, RBAC	Role-based privileges