11. MySQL Part 2: UPDATE, ALTER, DELETE, Keys, Constraints and Users

11.7 Users, GRANT, REVOKE and Least Privilege

By Ravindra Bagale Last updated: 27 September 2026

Pratyek application sathi vegla MySQL user, fakt tyachya database var, fakt garjeche privileges – hach least privilege.

CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'Str0ng#Pass2026';
GRANT SELECT, INSERT, UPDATE, DELETE ON cybercourse.* TO 'webapp'@'localhost';

CREATE USER 'report'@'localhost' IDENTIFIED BY 'R3port#Only';
GRANT SELECT ON cybercourse.students TO 'report'@'localhost';        -- one table, read-only

CREATE USER 'webapp'@'10.0.1.%' IDENTIFIED BY 'Str0ng#Pass2026';    -- app servers in a private subnet
GRANT SELECT, INSERT, UPDATE, DELETE ON cybercourse.* TO 'webapp'@'10.0.1.%';

SHOW GRANTS FOR 'webapp'@'localhost';
REVOKE DELETE ON cybercourse.* FROM 'webapp'@'localhost';
ALTER USER 'report'@'localhost' IDENTIFIED BY 'N3w#Pass';
SELECT user, host FROM mysql.user;
DROP USER 'report'@'localhost';

Host part	Who can connect
`'localhost'`	Only from the same server
`'10.0.1.%'`	Any machine in 10.0.1.0/24
`'%'`	From anywhere – avoid

Backup and restore:

mysqldump -u root -p cybercourse > cybercourse-$(date +%F).sql     # Ubuntu: sudo mysqldump cybercourse > ...
mysql -u root -p cybercourse < cybercourse-2026-09-27.sql

Why this matters for security

A web app that connects as root turns every SQL injection into full database (and sometimes file system) compromise. With a limited user, sqlmap may still read that app's tables, but it cannot drop databases, read other schemas or write files with INTO OUTFILE (needs the FILE privilege).

Ravindra Bagale's Tip

GRANT ALL PRIVILEGES ON *.* TO 'app'@'%' – this one line shows up in many students' projects because "it works". This line means handing the whole database to the world. Only on that database (dbname.*), only the privileges needed, only from the right host. Remember this!

Ravindra Bagale's Tip – मराठी

GRANT ALL PRIVILEGES ON *.* TO 'app'@'%' – ही एक line बऱ्याच students च्या projects मध्ये दिसते कारण "चालते". ही line म्हणजे सगळा database जगाला देणं. फक्त त्या database वर (dbname.*), फक्त गरजेचे privileges, फक्त योग्य host. लक्षात ठेवा!

Ravindra Bagale's Tip – हिंदी

GRANT ALL PRIVILEGES ON *.* TO 'app'@'%' – यह एक line बहुत से students के projects में दिखती है क्योंकि "चलती है". इस line का मतलब है पूरा database दुनिया को दे देना. सिर्फ़ उसी database पर (dbname.*), सिर्फ़ ज़रूरी privileges, सिर्फ़ सही host से. याद रखो!

Lab

Create webapp with DML rights on cybercourse only. Log in as webapp and confirm that SELECT works, DROP TABLE courses; is denied, and SHOW DATABASES; lists only the databases it may use. Take a mysqldump backup.

Thodkyaat sangaycha tar

Always SELECT with the same WHERE before UPDATE or DELETE.
Safe update mode blocks UPDATE/DELETE without a key in WHERE (error 1175); use transactions and ROLLBACK as an undo.
ALTER: ADD, MODIFY, CHANGE, RENAME COLUMN, DROP COLUMN, RENAME TO.
DELETE (DML, rows, rollback possible) vs TRUNCATE (DDL, all rows, resets AUTO_INCREMENT) vs DROP (removes table).
Keys: super → candidate (minimal) → primary (chosen) → alternate (rest); unique, composite, foreign.
Constraints: NOT NULL, DEFAULT, CHECK, AUTO_INCREMENT.
One user per app, only its database, only needed privileges; never root, never '%' without reason.

Samjla ka? MySQL che basics aata tumche aahet – SQL injection samjayla tumhi tayar aahat. Part 5 sampla. Aata pudhe jaauya domains aani HTTPS kade!