How to GRANT Permissions in MySQL

Galaxy Glossary

How do I grant permissions in MySQL?

GRANT assigns specific database privileges to MySQL users or roles, controlling what operations they can perform.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

What does the GRANT command do?

GRANT lets you specify exactly which databases, tables, or columns a MySQL user can read, write, or administer. It writes these rules to the mysql.user and related privilege tables.

Which privileges can I grant?

You can grant global, database, table, column, or routine-level privileges such as SELECT, INSERT, UPDATE, DELETE, EXECUTE, and administrative rights like CREATE USER or RELOAD.

How do I grant privileges to a new user?

Use GRANT with ON and TO clauses. Always follow with FLUSH PRIVILEGES on older MySQL (<5.7) if you modify privilege tables directly.

Example: Read-only access to all product data

GRANT SELECT ON ecommerce.Products TO 'analytics'@'%' IDENTIFIED BY 'S3cure!';

This lets the analytics user query, but not change, product records from any host.

How do I grant multiple privileges at once?

List privileges comma-separated.
GRANT SELECT, INSERT, UPDATE ON ecommerce.Orders TO 'ops'@'localhost';

How do I grant privileges on all future tables?

Use the *.* or db_name.* wildcard.
GRANT ALL PRIVILEGES ON ecommerce.* TO 'admin'@'%';

How do I revoke or change privileges?

Run REVOKE or GRANT with new privileges. MySQL updates immediately; no restart needed.

Best practices

Grant least privilege, use roles in MySQL 8.0+, and store credentials in a secrets manager. Always use strong passwords or auth plugins.

Common mistakes and fixes

Mixing GRANT with manually editing privilege tables can create inconsistency—always use GRANT/REVOKE. Forgetting WITH GRANT OPTION prevents a user from passing rights along—add it only when necessary.

Why How to GRANT Permissions in MySQL is important

How to GRANT Permissions in MySQL Example Usage


-- Allow customer-service staff to update order totals but not product pricing
GRANT UPDATE(order_date, total_amount)
ON ecommerce.Orders
TO 'support'@'internal.company.com'
IDENTIFIED BY 'C$agent42';

How to GRANT Permissions in MySQL Syntax


GRANT [privilege [, privilege] ...] ON [object_type] priv_level
TO user [IDENTIFIED BY 'password']
    [REQUIRE tls_option]
    [WITH GRANT OPTION]

priv_level:
    *.*                      -- all databases
    db_name.*                -- all tables in database
    db_name.tbl_name         -- specific table
    tbl_name                 -- table in current db

Example (ecommerce context):
GRANT SELECT, INSERT ON ecommerce.OrderItems TO 'warehouse_app'@'10.9.%' IDENTIFIED BY 'Str0ng!';

Common Mistakes

Granting ALL PRIVILEGES when only SELECT is needed. This overexposes data. Fix by explicitly listing required privileges, e.g., GRANT SELECT ON db.* TO 'user'@'%';
Forgetting to limit host scope. Using 'user'@'%' lets anyone with credentials connect. Specify trusted hosts like 'user'@'192.168.1.%'.

Frequently Asked Questions (FAQs)

Can I grant privileges and create a user in one step?

Yes. Add IDENTIFIED BY 'pwd' in the GRANT statement. MySQL creates the user automatically.

Do I need to run FLUSH PRIVILEGES?

No for GRANT/REVOKE. Only needed if you manually edit the privilege tables.

How do I list a user's privileges?

Execute SHOW GRANTS FOR 'user'@'host'; The server returns the GRANT statements that define current rights.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.