MySQL — Users

Create User

CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123';

Grant Privileges

-- All privileges on a database
GRANT ALL PRIVILEGES ON mydb.* TO 'john'@'localhost';

-- Specific privileges
GRANT SELECT, INSERT ON mydb.customers TO 'john'@'localhost';

-- Read-only access
GRANT SELECT ON mydb.* TO 'reader'@'%';

View Privileges

SHOW GRANTS FOR 'john'@'localhost';

Revoke Privileges

REVOKE INSERT ON mydb.* FROM 'john'@'localhost';

Privilege Types

Privilege	Description
SELECT	Read data
INSERT	Add rows
UPDATE	Modify rows
DELETE	Remove rows
CREATE	Create databases/tables
DROP	Drop databases/tables
ALL	Everything

Drop User

DROP USER 'john'@'localhost';

Change Password

ALTER USER 'john'@'localhost' IDENTIFIED BY 'new_password';

Password Expiration

ALTER USER 'john'@'localhost' PASSWORD EXPIRE;

Public Key Authentication

CREATE USER 'secure_user'@'%'
    IDENTIFIED WITH caching_sha2_password
    BY 'strong_password';

Host Patterns

Pattern	Matches
localhost	Same machine only
%	Any host
192.168.1.%	Subnet range
%.example.com	Domain pattern

Mini Practice

Write SQL code that:

1. Creates a new user
2. Grants SELECT and INSERT privileges
3. Shows the user's grants
4. Revokes a privilege and drops the user

Up Next

Continue with Backup — protecting your data with backups.