? GRANT Permissions in MySQL
? Quick Overview
The GRANT statement in MySQL is used to give specific privileges to database users. These privileges control what actions a user can perform on databases, tables, or other database objects.
? Key Concepts
- Permissions define allowed database operations
- Privileges can be global, database-level, or table-level
- Users must exist before permissions are granted
- GRANT works together with REVOKE
? Syntax / Theory
MySQL permissions are assigned using the GRANT keyword followed by privileges, object scope, and user identification.
- SELECT Read data
- INSERT Add new rows
- UPDATE Modify existing data
- DELETE Remove data
- ALL Full access
? Code Example(s)
? View Code Example
GRANT SELECT ON school_db.* TO 'student'@'localhost';
? View Code Example
GRANT SELECT, INSERT, UPDATE ON school_db.marks TO 'teacher'@'localhost';
? View Code Example
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
? Live Output / Explanation
What Happens After GRANT?
- The user can immediately perform allowed operations
- No server restart is required
- Privileges are stored in MySQL system tables
- Use
SHOW GRANTS to verify permissions
? Tips & Best Practices
- Grant minimum required privileges only
- Avoid using
ALL PRIVILEGES for normal users
- Use database-level grants instead of global when possible
- Regularly review user permissions
? Try It Yourself
- Create a new user using
CREATE USER
- Grant SELECT and INSERT permissions
- Test access by logging in as that user
- View permissions using
SHOW GRANTS