SQL User Management and Permissions - Controlling Access to Your Database

Introduction

Imagine a school where everyone had keys to every room — from classrooms to the principal’s office. It would be chaos. The same goes for databases. User management and permissions allow you to assign just the right access to each user — no more, no less — ensuring both security and accountability.

Why User Management Matters

• Protect sensitive data (like student marks)
• Enforce roles (teacher vs admin vs auditor)
• Track and log who did what

Creating a New User

-- Syntax (MySQL):
CREATE USER 'teacher1'@'localhost' IDENTIFIED BY 'SecurePass@123';

This creates a user named teacher1 who can connect from the local machine.

Granting Permissions

Give read-only access to students table:

GRANT SELECT ON school.students TO 'teacher1'@'localhost';

Give full access to marks table:

GRANT SELECT, INSERT, UPDATE ON school.marks TO 'teacher1'@'localhost';

Apply changes:

FLUSH PRIVILEGES;

Viewing User Privileges

SHOW GRANTS FOR 'teacher1'@'localhost';

GRANT SELECT ON school.students TO 'teacher1'@'localhost';
GRANT SELECT, INSERT, UPDATE ON school.marks TO 'teacher1'@'localhost';

Revoking Permissions

REVOKE INSERT, UPDATE ON school.marks FROM 'teacher1'@'localhost';

This removes data modification rights but retains read-only access.

Dropping a User

DROP USER 'teacher1'@'localhost';

This permanently removes the user from the system.

Permission Scope Explained

Permission	Description
SELECT	Read rows
INSERT	Add new rows
UPDATE	Modify existing rows
DELETE	Remove rows
ALL	Full access to a table or DB

Real-World School Use Cases

• Teachers: Read/write access to marks, no access to admin tables
• Principal: Full access to all tables
• Auditors: Read-only access to financial and academic data
• IT Admins: ALL privileges + permission to create/drop users

Security Best Practices

• Never use root for applications
• Create separate users for read vs write operations
• Change default passwords immediately
• Use REVOKE when permissions are no longer needed
• Review SHOW GRANTS regularly

Summary

Just like in a school, not everyone needs access to every file cabinet. Managing SQL users and their permissions helps enforce clear boundaries. Done right, it protects your data, clarifies roles, and supports system stability.