MySQL GRANT and REVOKE

After creating a user account in MySQL, privileges must be assigned before the user can perform any meaningful operations. GRANT assigns specific permissions to a user. REVOKE removes those permissions. Together, they form the access control system of MySQL.

Types of Privileges

GRANT Syntax

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

Scope Options

• *.* — All databases and all tables (global)
• database_name.* — All tables in a specific database
• database_name.table_name — A specific table

Example: Grant SELECT on One Table

GRANT SELECT ON school_db.students TO 'ravi'@'localhost';

Ravi can now only read the students table in the school_db database.

Example: Grant Multiple Privileges on a Database

GRANT SELECT, INSERT, UPDATE ON school_db.* TO 'sneha'@'localhost';

Sneha can read, insert, and update any table in school_db but cannot delete or create tables.

Example: Grant All Privileges on a Database

GRANT ALL PRIVILEGES ON school_db.* TO 'admin_user'@'localhost';

GRANT WITH GRANT OPTION

Adding WITH GRANT OPTION allows the user to grant their own privileges to other users.

GRANT SELECT ON school_db.* TO 'team_lead'@'localhost' WITH GRANT OPTION;

Applying Privilege Changes

FLUSH PRIVILEGES;

This reloads the privilege tables from disk. It is required when privileges are modified directly in the mysql system tables. When using GRANT and REVOKE directly, MySQL applies changes immediately and FLUSH PRIVILEGES is optional.

Viewing Grants for a User

SHOW GRANTS FOR 'ravi'@'localhost';

REVOKE Syntax

REVOKE privilege_list ON database.table FROM 'username'@'host';

Example: Revoke a Specific Privilege

REVOKE DELETE ON school_db.* FROM 'sneha'@'localhost';

Sneha can no longer delete rows, but her SELECT, INSERT, and UPDATE privileges remain.

Example: Revoke All Privileges

REVOKE ALL PRIVILEGES ON school_db.* FROM 'ravi'@'localhost';

Full Workflow Example

-- Create user
CREATE USER 'pooja'@'localhost' IDENTIFIED BY 'Safe@Pass2024';

-- Grant permissions
GRANT SELECT, INSERT ON company_db.employees TO 'pooja'@'localhost';

-- Verify
SHOW GRANTS FOR 'pooja'@'localhost';

-- Remove INSERT permission later
REVOKE INSERT ON company_db.employees FROM 'pooja'@'localhost';

-- Drop user when no longer needed
DROP USER 'pooja'@'localhost';

Key Points

• GRANT assigns one or more privileges to a user on a specific scope.
• REVOKE removes previously granted privileges.
• Privileges can be granted at global, database, or table level.
• WITH GRANT OPTION allows the user to pass their privileges to other users.
• Use SHOW GRANTS FOR to inspect what a user is currently allowed to do.