🛡️ Lesson 19: User Management & Permissions

Why User Management Matters

Consider these scenarios:

• A junior developer accidentally runs DELETE FROM customers without a WHERE clause — every customer record gone
• A reporting dashboard only needs to read data, but its database account can also write and delete
• An intern with root access drops the production database
• A web application's database credentials are compromised — the attacker has full admin access

All of these are preventable with proper user management. Each user or application gets only the permissions required for their job.

Creating Users

Basic Syntax

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
                

A MySQL user is identified by two parts: the username and the host they connect from. The same username from different hosts is treated as different accounts.

Common Host Patterns

Examples

-- User that can only connect locally
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'SecurePass123!';

-- User that can connect from any host
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'RemotePass456!';

-- User locked to a specific IP
CREATE USER 'api_service'@'10.0.0.50' IDENTIFIED BY 'ApiKey789!';
                

See All Users

-- List all user accounts
SELECT User, Host FROM mysql.user;
                

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | localhost |
| mysql.sys        | localhost |
| app_user         | localhost |
| remote_user      | %         |
+------------------+-----------+
                    

Granting Privileges

Creating a user gives them zero permissions — they can connect but can't see or do anything. You must explicitly grant what they need.

Basic Syntax

GRANT privilege_list ON database.table TO 'user'@'host';
                

Common Privileges

Examples

-- Read-only access to the bookstore database
GRANT SELECT ON bookstore.* TO 'report_user'@'localhost';

-- Full CRUD access to bookstore
GRANT SELECT, INSERT, UPDATE, DELETE ON bookstore.* TO 'app_user'@'localhost';

-- All privileges on bookstore (but not on other databases)
GRANT ALL PRIVILEGES ON bookstore.* TO 'admin_user'@'localhost';

-- Read access to a specific table only
GRANT SELECT ON bookstore.books TO 'catalog_viewer'@'localhost';

-- Apply changes (required in older MySQL versions)
FLUSH PRIVILEGES;
                

GRANT with Grant Option

If you want a user to be able to grant their own privileges to others:

-- admin_user can grant privileges to other users
GRANT ALL PRIVILEGES ON bookstore.*
TO 'admin_user'@'localhost'
WITH GRANT OPTION;
                

Privilege Levels

Privileges can be scoped at different levels — from "everything on the server" to "one column in one table."

Examples at Each Level

-- Global: SELECT on every database (e.g., monitoring tool)
GRANT SELECT ON *.* TO 'monitor'@'localhost';

-- Database: Full access to bookstore only
GRANT ALL PRIVILEGES ON bookstore.* TO 'admin_user'@'localhost';

-- Table: Read only the books table
GRANT SELECT ON bookstore.books TO 'catalog_viewer'@'localhost';

-- Column: See customer names but not emails or phones
GRANT SELECT (id, first_name, last_name) ON bookstore.customers
TO 'limited_viewer'@'localhost';

-- Routine: Run a specific stored procedure
GRANT EXECUTE ON PROCEDURE bookstore.place_order TO 'app_user'@'localhost';
                

Viewing Privileges

-- See your own privileges
SHOW GRANTS;

-- See another user's privileges
SHOW GRANTS FOR 'app_user'@'localhost';
                

+-----------------------------------------------------------------------+
| Grants for app_user@localhost                                         |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost`                          |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `bookstore`.* TO `app_user`@… |
+-----------------------------------------------------------------------+
                    

USAGE means "no privileges" at the global level — it's the default grant that says "this account exists and can connect, but has no global permissions." The real permissions come from the second line.

Checking Current User

-- Who am I?
SELECT CURRENT_USER();

-- What's my connection info?
SELECT USER();
                

Revoking Privileges

Remove privileges with REVOKE — the syntax mirrors GRANT:

-- Remove DELETE privilege (keep SELECT, INSERT, UPDATE)
REVOKE DELETE ON bookstore.* FROM 'app_user'@'localhost';

-- Remove all privileges on bookstore
REVOKE ALL PRIVILEGES ON bookstore.* FROM 'app_user'@'localhost';

-- Remove the grant option
REVOKE GRANT OPTION ON bookstore.* FROM 'admin_user'@'localhost';
                

Verify After Revoking

-- Always verify the change took effect
SHOW GRANTS FOR 'app_user'@'localhost';
                

Modifying & Deleting Users

Change a Password

-- Change another user's password (as root)
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewSecurePass789!';

-- Change your own password
ALTER USER CURRENT_USER() IDENTIFIED BY 'MyNewPassword!';

-- Older syntax (still works):
SET PASSWORD FOR 'app_user'@'localhost' = 'NewSecurePass789!';
                

Rename a User

RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';
                

Lock/Unlock an Account

-- Temporarily disable an account (MySQL 5.7.6+)
ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;

-- Re-enable it
ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;
                

Delete a User

-- Remove a user entirely (account + all privileges)
DROP USER 'app_user'@'localhost';

-- Safe version (no error if user doesn't exist)
DROP USER IF EXISTS 'app_user'@'localhost';
                

Roles (MySQL 8+)

A role is a named collection of privileges. Instead of granting the same set of privileges to 20 users individually, you define a role once and assign it to users. Change the role's privileges and all assigned users are updated automatically.

Create and Use a Role

-- Step 1: Create the role
CREATE ROLE 'bookstore_reader';

-- Step 2: Grant privileges to the role
GRANT SELECT ON bookstore.* TO 'bookstore_reader';

-- Step 3: Assign the role to users
GRANT 'bookstore_reader' TO 'alice'@'localhost';
GRANT 'bookstore_reader' TO 'bob'@'localhost';
GRANT 'bookstore_reader' TO 'carol'@'localhost';

-- Step 4: Activate the role for a user's session
SET DEFAULT ROLE 'bookstore_reader' TO 'alice'@'localhost';
-- Or for all roles:
SET DEFAULT ROLE ALL TO 'alice'@'localhost';
                

Multiple Roles

-- Define roles for different access levels
CREATE ROLE 'bookstore_reader', 'bookstore_writer', 'bookstore_admin';

GRANT SELECT ON bookstore.* TO 'bookstore_reader';
GRANT SELECT, INSERT, UPDATE, DELETE ON bookstore.* TO 'bookstore_writer';
GRANT ALL PRIVILEGES ON bookstore.* TO 'bookstore_admin';

-- A user can have multiple roles
GRANT 'bookstore_reader', 'bookstore_writer' TO 'app_user'@'localhost';
SET DEFAULT ROLE ALL TO 'app_user'@'localhost';
                

Drop a Role

DROP ROLE 'bookstore_reader';
                

The Principle of Least Privilege

The principle of least privilege (PoLP) states: every user and application should have only the minimum permissions needed to do their job — nothing more.

Why It Matters

• Limits damage from mistakes: A user with only SELECT can't accidentally delete data
• Limits damage from breaches: If credentials are compromised, the attacker can only do what the account allows
• Creates accountability: Each account's purpose is clear and auditable
• Meets compliance requirements: Many security standards (SOC 2, HIPAA, PCI DSS) require least privilege

Applying It in Practice

Common User Setups

Here's a complete setup for a typical bookstore application:

-- 1. Application user: reads and writes data, runs procedures
CREATE USER 'bookstore_app'@'localhost' IDENTIFIED BY 'AppPass!2026';
GRANT SELECT, INSERT, UPDATE, DELETE ON bookstore.* TO 'bookstore_app'@'localhost';
GRANT EXECUTE ON bookstore.* TO 'bookstore_app'@'localhost';

-- 2. Reporting user: read-only access
CREATE USER 'bookstore_reports'@'localhost' IDENTIFIED BY 'ReportPass!2026';
GRANT SELECT ON bookstore.* TO 'bookstore_reports'@'localhost';

-- 3. Admin user: full database management (not server-wide)
CREATE USER 'bookstore_admin'@'localhost' IDENTIFIED BY 'AdminPass!2026';
GRANT ALL PRIVILEGES ON bookstore.* TO 'bookstore_admin'@'localhost';

-- 4. Backup user: read-only + lock for consistent dumps
CREATE USER 'bookstore_backup'@'localhost' IDENTIFIED BY 'BackupPass!2026';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON bookstore.*
TO 'bookstore_backup'@'localhost';

-- 5. Support team: limited view of customers only
CREATE USER 'bookstore_support'@'localhost' IDENTIFIED BY 'SupportPass!2026';
GRANT SELECT (id, first_name, last_name, email) ON bookstore.customers
TO 'bookstore_support'@'localhost';

-- Verify all setups:
SHOW GRANTS FOR 'bookstore_app'@'localhost';
SHOW GRANTS FOR 'bookstore_reports'@'localhost';
SHOW GRANTS FOR 'bookstore_admin'@'localhost';
SHOW GRANTS FOR 'bookstore_backup'@'localhost';
SHOW GRANTS FOR 'bookstore_support'@'localhost';
                

-- In a terminal:
-- mysql -u bookstore_reports -p bookstore
-- Then try:
DELETE FROM books WHERE id = 1;
-- ERROR 1142: DELETE command denied to user 'bookstore_reports'@'localhost'
                    

Exercises

-- 1
CREATE USER 'inventory_mgr'@'localhost' IDENTIFIED BY 'InvPass!2026';

-- 2
GRANT SELECT, INSERT, UPDATE ON bookstore.books
TO 'inventory_mgr'@'localhost';

-- 3
CREATE USER 'public_api'@'%' IDENTIFIED BY 'ApiPass!2026';

-- 4
GRANT SELECT ON bookstore.books TO 'public_api'@'%';
GRANT SELECT ON bookstore.authors TO 'public_api'@'%';
                        

-- 1
SHOW GRANTS FOR 'inventory_mgr'@'localhost';

-- 2
REVOKE UPDATE ON bookstore.books FROM 'inventory_mgr'@'localhost';

-- 3
SHOW GRANTS FOR 'inventory_mgr'@'localhost';
-- Should show SELECT, INSERT only

-- 4
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'public_api'@'%';
DROP USER 'public_api'@'%';
                        

-- Teacher: view students/courses, manage grades
CREATE USER 'teacher'@'localhost' IDENTIFIED BY 'TeachPass!2026';
GRANT SELECT ON school.students TO 'teacher'@'localhost';
GRANT SELECT ON school.courses TO 'teacher'@'localhost';
GRANT SELECT ON school.enrollments TO 'teacher'@'localhost';
GRANT SELECT, INSERT, UPDATE ON school.grades TO 'teacher'@'localhost';

-- Registrar: manage students/courses/enrollments, no grades
CREATE USER 'registrar'@'localhost' IDENTIFIED BY 'RegPass!2026';
GRANT SELECT, INSERT, UPDATE, DELETE ON school.students TO 'registrar'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON school.courses TO 'registrar'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON school.enrollments TO 'registrar'@'localhost';

-- Student portal: read-only on courses and grades
CREATE USER 'student_portal'@'localhost' IDENTIFIED BY 'PortalPass!2026';
GRANT SELECT ON school.courses TO 'student_portal'@'localhost';
GRANT SELECT ON school.grades TO 'student_portal'@'localhost';
-- Note: Filtering by student ID would be done in the application layer
                        

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — CREATE USER
• MySQL Docs — GRANT Statement
• MySQL Docs — REVOKE Statement
• MySQL Docs — Using Roles
• MySQL Docs — Privileges Provided by MySQL

🚀 What's Next?

That completes Module 6: Advanced Features! You've built reusable logic (procedures/functions), protected data integrity (transactions), and locked down access (users/permissions). In the final module, Lesson 20: Importing & Exporting Data covers the essential real-world skill of moving data in and out of MySQL — backups with mysqldump, CSV imports, and more.