GRANT and REVOKE in MySQL

Use GRANT and REVOKE as Authorization Changes

GRANT assigns privileges or roles; REVOKE removes them. They do not replace authentication, network controls or application authorization. In current MySQL, create the account first:

CREATE USER 'teacher_app'@'10.20.30.%'
  IDENTIFIED BY RANDOM PASSWORD REQUIRE SSL;

GRANT SELECT ON school_app.students
TO 'teacher_app'@'10.20.30.%';

REVOKE SELECT ON school_app.students
FROM 'teacher_app'@'10.20.30.%';

Quote user and host separately and write the exact host in every statement. 'teacher_app'@'localhost' is not the same account as 'teacher_app'@'10.20.30.%'.

Build a Safe Least-Privilege Lab

CREATE DATABASE IF NOT EXISTS school_app;
CREATE TABLE IF NOT EXISTS school_app.attendance (
  attendance_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  attendance_date DATE NOT NULL,
  status ENUM('P','A','L') NOT NULL,
  UNIQUE KEY uq_attendance(student_id,attendance_date)
);

CREATE USER 'attendance_demo'@'localhost'
  IDENTIFIED BY RANDOM PASSWORD REQUIRE SSL;

GRANT SELECT,INSERT,UPDATE
ON school_app.attendance
TO 'attendance_demo'@'localhost';

Expected: the account can read and maintain attendance rows, but cannot delete the table, create users, read unrelated schemas or use server files. Run labs on an isolated server; do not create tutorial accounts in production.

SHOW GRANTS FOR 'attendance_demo'@'localhost';

Save the initial grants as the baseline. Every later change should have an intentional diff.

Choose the Correct Privilege and Scope

-- Database scope
GRANT SELECT ON school_app.* TO 'reporter'@'localhost';

-- Table scope
GRANT SELECT,INSERT ON school_app.attendance
TO 'attendance_demo'@'localhost';

-- Column scope
GRANT SELECT(student_id,attendance_date,status)
ON school_app.attendance TO 'auditor'@'localhost';

-- Stored procedure execution
GRANT EXECUTE ON PROCEDURE school_app.close_attendance_day
TO 'operator'@'localhost';

Dynamic administrative privileges and ON *.* require special review. Grant only operations the workload demonstrably needs.

Grant Privileges to Roles, Then Roles to Users

CREATE ROLE IF NOT EXISTS
  'attendance_read'@'%', 'attendance_write'@'%';

GRANT SELECT ON school_app.attendance
TO 'attendance_read'@'%';
GRANT SELECT,INSERT,UPDATE ON school_app.attendance
TO 'attendance_write'@'%';

GRANT 'attendance_write'@'%'
TO 'attendance_demo'@'localhost';

SET DEFAULT ROLE 'attendance_write'@'%'
TO 'attendance_demo'@'localhost';

A privilege grant contains ON; a role grant does not. MySQL does not allow one GRANT statement to mix privileges and roles. Set the default role for future sessions or use SET ROLE in the current session.

Roles reduce duplication and make reviews clearer: change the role once, then verify every dependent workload. Avoid deep role graphs that become hard to reason about.

Verify Direct, Role-Derived and Effective Access

SHOW GRANTS FOR 'attendance_demo'@'localhost';
SHOW GRANTS FOR 'attendance_write'@'%';

-- Run after connecting as the demo account
SELECT USER(),CURRENT_USER(),CURRENT_ROLE();
SET ROLE DEFAULT;
SELECT CURRENT_ROLE();

Then perform both sides of the acceptance test:

-- Must succeed
SELECT COUNT(*) FROM school_app.attendance;

-- Must fail for this runtime account
DROP TABLE school_app.attendance;

A successful allowed test proves useful access; a denied destructive test proves the boundary. Test through the same connector, host, TLS settings and default-role behavior as production. Views, routines and DEFINER/INVOKER security can introduce additional access paths.

Revoke the Exact Assignment Safely

-- Remove table writes from a role
REVOKE INSERT,UPDATE ON school_app.attendance
FROM 'attendance_write'@'%';

-- Remove a role assignment from one account
REVOKE 'attendance_write'@'%'
FROM 'attendance_demo'@'localhost';

-- Emergency containment: stop login
ALTER USER 'attendance_demo'@'localhost' ACCOUNT LOCK;

Revoking a role does not remove the role itself or direct grants. Revoking a privilege from one role does not remove the same privilege inherited through another role. Re-run SHOW GRANTS on the user and every applicable role.

For complete deprovisioning after dependency review:

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM 'legacy_app'@'localhost';
DROP USER 'legacy_app'@'localhost';

Treat GRANT OPTION and ADMIN OPTION as High Risk

-- Powerful privilege delegation
GRANT SELECT ON school_app.*
TO 'data_lead'@'localhost' WITH GRANT OPTION;

-- Powerful role delegation
GRANT 'attendance_read'@'%'
TO 'team_lead'@'localhost' WITH ADMIN OPTION;

WITH GRANT OPTION permits privilege delegation. WITH ADMIN OPTION permits role delegation. Neither belongs in normal application accounts. Delegation can expand access beyond the original review and complicate incident containment.

• Require a named administrator and documented business reason.
• Limit scope; never add it to broad global grants casually.
• Review who can delegate, not only who can read/write.
• Test revocation and downstream assignments in a nonproduction clone.

Production Authorization Workflow

1. Identify exact account, host, workload owner and expiry.
2. List required SQL actions and objects from observed use cases.
3. Prefer an existing reviewed role; otherwise create a capability role.
4. Generate SQL and a reverse REVOKE/GRANT rollback.
5. Peer-review wildcards, global privileges and delegation options.
6. Apply with a controlled admin account over verified TLS.
7. Compare SHOW GRANTS before/after.
8. Reconnect and test an allowed and denied action.
9. Monitor errors and business flow during rollout.
10. Review periodically and revoke stale access.

FLUSH PRIVILEGES is not required after GRANT/REVOKE. Do not directly edit grant tables. Continue with users and roles and database security.

Official References

• MySQL 8.4: GRANT Statement
• MySQL 8.4: REVOKE Statement
• MySQL 8.4: SHOW GRANTS

Syntax, scope, role assignment and delegation behavior were verified against the official MySQL 8.4 manual.