Granting privileges in MySQL

Introduction

Granting privileges lets you give users permission to do specific actions in the database. This keeps your data safe and organized.

When you create a new user and want them to access certain tables.

When you want to allow a user to add or change data in the database.

When you want to let a user run special commands like creating or deleting tables.

When you want to control who can see or change sensitive information.

When you want to give temporary access to a user for a specific task.

Syntax

MySQL

GRANT privilege_type ON database_name.table_name TO 'username'@'host';

privilege_type can be SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES, etc.

database_name.table_name specifies where the privilege applies. Use * for all databases or tables.

Examples

Gives user 'alice' permission to read data from all tables in the 'mydb' database.

MySQL

GRANT SELECT ON mydb.* TO 'alice'@'localhost';

Gives user 'bob' all permissions on all databases and tables from any host.

MySQL

GRANT ALL PRIVILEGES ON *.* TO 'bob'@'%';

Allows 'carol' to add and change data in the 'products' table of the 'shop' database from a specific IP.

MySQL

GRANT INSERT, UPDATE ON shop.products TO 'carol'@'192.168.1.10';

Sample Program

This creates a new user 'dave' with a password, grants him permission to read and add data in the 'orders' table of 'testdb', and then shows his privileges.

MySQL

CREATE USER 'dave'@'localhost' IDENTIFIED BY 'password123';
GRANT SELECT, INSERT ON testdb.orders TO 'dave'@'localhost';
SHOW GRANTS FOR 'dave'@'localhost';

Important Notes

After granting privileges, sometimes you need to run FLUSH PRIVILEGES; to apply changes immediately.

Be careful granting ALL PRIVILEGES; it gives full control which can be risky.

Use specific privileges to follow the principle of least privilege for better security.

Summary

Granting privileges controls what users can do in the database.

You specify the user, the allowed actions, and where those actions apply.

Always grant only the permissions needed to keep data safe.