Chapter 18: Users, Privileges & Security
This is the chapter that turns your MySQL database from a “free-for-all playground” into a secure, professional system! In real life, you never want everyone to have full access — imagine if every student could delete all enrollment records or change fees!
Today we’ll learn how to:
- Create new users
- Grant (give) and Revoke (take away) specific permissions
- Check what privileges a user has with SHOW GRANTS
- Change passwords safely
Let’s pretend we’re running a real coaching institute database and we need different types of users:
- admin → full control
- teacher → can read and update student marks/notes
- receptionist → can only INSERT new students and read basic info
- student → can only see their own details
1. CREATE USER – Adding New Users
Syntax:
|
0 1 2 3 4 5 6 |
CREATE USER 'username'@'host' IDENTIFIED BY 'strong_password'; |
host can be:
- ‘localhost’ → only from the same machine
- ‘%’ → from anywhere (dangerous — use only for trusted remote access!)
- ‘192.168.1.%’ → from a specific IP range
Best practice: Always use strong passwords (at least 12 characters, mix of letters/numbers/symbols).
Examples:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Create admin user (full access, only from localhost) CREATE USER 'admin'@'localhost' IDENTIFIED BY 'Admin@2026Secure!'; -- Create teacher user (can connect from anywhere) CREATE USER 'teacher'@'%' IDENTIFIED BY 'Teach@123Strong'; -- Create receptionist user CREATE USER 'reception'@'localhost' IDENTIFIED BY 'Recep@456!'; -- Create a student user (limited access) CREATE USER 'student'@'localhost' IDENTIFIED BY 'Stud@789!'; |
Tip: After creating users, always flush privileges (though usually automatic):
|
0 1 2 3 4 5 6 |
FLUSH PRIVILEGES; |
2. GRANT / REVOKE Privileges – Giving & Taking Permissions
GRANT gives permissions. REVOKE takes them away.
Common privileges:
| Privilege | What it allows |
|---|---|
| ALL PRIVILEGES | Everything (dangerous!) |
| SELECT | Read data (SELECT queries) |
| INSERT | Add new rows |
| UPDATE | Change existing rows |
| DELETE | Remove rows |
| CREATE | Create tables/databases |
| DROP | Delete tables/databases |
| EXECUTE | Run stored procedures/functions |
| GRANT OPTION | Can give privileges to others |
Syntax:
|
0 1 2 3 4 5 6 7 8 |
GRANT privilege_list ON database.table TO 'user'@'host'; REVOKE privilege_list ON database.table FROM 'user'@'host'; |
Example 1 – Give full access to admin
|
0 1 2 3 4 5 6 7 |
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES; |
Example 2 – Give teacher SELECT + UPDATE on students table
|
0 1 2 3 4 5 6 7 8 |
GRANT SELECT, UPDATE ON my_coaching.students TO 'teacher'@'%'; GRANT SELECT ON my_coaching.courses TO 'teacher'@'%'; FLUSH PRIVILEGES; |
Example 3 – Give receptionist only INSERT and SELECT on students
|
0 1 2 3 4 5 6 7 |
GRANT SELECT, INSERT ON my_coaching.students TO 'reception'@'localhost'; FLUSH PRIVILEGES; |
Example 4 – Give student only SELECT on their own data (using views) First create a view for student’s own data:
|
0 1 2 3 4 5 6 7 8 9 |
CREATE VIEW my_info AS SELECT full_name, email, join_date FROM students WHERE email = CURRENT_USER(); |
Then:
|
0 1 2 3 4 5 6 7 |
GRANT SELECT ON my_coaching.my_info TO 'student'@'localhost'; FLUSH PRIVILEGES; |
Now student can only see their own info:
|
0 1 2 3 4 5 6 7 |
-- Login as student user SELECT * FROM my_info; |
Revoke example – Take away UPDATE from teacher
|
0 1 2 3 4 5 6 7 |
REVOKE UPDATE ON my_coaching.students FROM 'teacher'@'%'; FLUSH PRIVILEGES; |
3. SHOW GRANTS – Check What Privileges a User Has
Syntax:
|
0 1 2 3 4 5 6 |
SHOW GRANTS FOR 'user'@'host'; |
Example:
|
0 1 2 3 4 5 6 |
SHOW GRANTS FOR 'teacher'@'%'; |
Sample output:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
+-----------------------------------------------------------------------+ | Grants for teacher@% | +-----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `teacher`@`%` | | GRANT SELECT, UPDATE ON `my_coaching`.`students` TO `teacher`@`%` | | GRANT SELECT ON `my_coaching`.`courses` TO `teacher`@`%` | +-----------------------------------------------------------------------+ |
For current user:
|
0 1 2 3 4 5 6 |
SHOW GRANTS; |
4. Changing Passwords – Safely Update Credentials
Syntax:
|
0 1 2 3 4 5 6 |
ALTER USER 'user'@'host' IDENTIFIED BY 'new_password'; |
Example – Change admin password
|
0 1 2 3 4 5 6 7 |
ALTER USER 'admin'@'localhost' IDENTIFIED BY 'NewSuperSecure@2026!'; FLUSH PRIVILEGES; |
Important tips for passwords:
- Never use simple passwords like ‘123456’ or ‘admin’
- Use password policies (MySQL 8.0+ supports validate_password plugin)
- Change default root password immediately after installation!
- For root:
|
0 1 2 3 4 5 6 |
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@Strong2026!'; |
5. Bonus: Best Security Practices (Real-World Tips)
- Never use root for applications — create dedicated users with minimal privileges
- Use ‘%’ host only when really needed — prefer specific IPs or localhost
- Enable validate_password plugin (for strong passwords)
|
0 1 2 3 4 5 6 |
INSTALL PLUGIN validate_password SONAME 'validate_password.so'; |
- Use SSL for remote connections
- Regularly audit privileges with SHOW GRANTS
- Backup before major privilege changes
That’s it for Chapter 18! 🎉 You now know how to secure your database like a real DBA — only the right people can see or change the right things!
Homework for today (do it right now – very important!) Run these commands and paste the output of the final SHOW GRANTS:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- 1. Create a new user CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'Report@2026Secure!'; -- 2. Give read-only access to students and courses GRANT SELECT ON my_coaching.students TO 'report_user'@'localhost'; GRANT SELECT ON my_coaching.courses TO 'report_user'@'localhost'; FLUSH PRIVILEGES; -- 3. Check privileges SHOW GRANTS FOR 'report_user'@'localhost'; -- 4. Change password ALTER USER 'report_user'@'localhost' IDENTIFIED BY 'NewReport@2026!'; FLUSH PRIVILEGES; -- 5. Check grants again SHOW GRANTS FOR 'report_user'@'localhost'; |
Paste the two SHOW GRANTS outputs — I’ll check them!
