SQL Tutorial › Security › GRANT

SQL GRANT Statement

Q: How do I see the privileges of a MySQL user?

SHOW GRANTS FOR 'user'@'host'; For your own account, SHOW GRANTS;.

Q: Do I need FLUSH PRIVILEGES after GRANT?

No. GRANT, REVOKE, CREATE USER and similar statements take effect immediately. FLUSH PRIVILEGES is only needed after editing the grant tables directly, which you should not do.

IntermediateLesson 67 of 6910 min read7 queries run on MySQL 8.4

GRANT gives an account (or a role) permission to do something: read a table, insert rows, create tables, run a procedure. It is the core of DCL (Data Control Language). Granting exactly what each account needs, and nothing more, is the most effective database security measure you can take.

In this lesson

Grant privileges at global, database, table and column level
Inspect privileges with SHOW GRANTS
Grant through views for row and column security
Understand WITH GRANT OPTION and ALL PRIVILEGES

GRANT syntax

MySQL

GRANT privilege [, privilege ...]ON levelTO 'user'@'host' [, ...][WITH GRANT OPTION];

Level	Written as	Example
Global	`.`	every database on the server
Database	`company.*`	every table in `company`
Table	`company.orders`	one table
Column	`SELECT (col1, col2) ON company.employees`	some columns of one table
Routine	`EXECUTE ON PROCEDURE company.place_order`	one procedure

Common privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, EXECUTE, CREATE VIEW, TRIGGER, and ALL PRIVILEGES.

Database-level grant

MySQL

CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p#App2026';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'shop_app'@'localhost';SHOW GRANTS FOR 'shop_app'@'localhost';

ResultMySQL 8.4

mysql> CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p

Query OK, 0 rows affected

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'shop_app'@'local…

Query OK, 0 rows affected

mysql> SHOW GRANTS FOR 'shop_app'@'localhost'

Grants for shop_app@localhost
GRANT USAGE ON . TO `shop_app`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `company`.* TO `shop_app`@`localhost`

2 rows in set

How this query works, step by step

The account can read and change rows in every table of company.
It cannot create, alter or drop tables, and it cannot touch other databases.
SHOW GRANTS lists exactly what the account may do.

Table-level grant

A reporting account that may read only the shop tables:

MySQL

CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt#2026';GRANT SELECT ON company.orders TO 'report_reader'@'localhost';GRANT SELECT ON company.order_items TO 'report_reader'@'localhost';GRANT SELECT ON company.products TO 'report_reader'@'localhost';SHOW GRANTS FOR 'report_reader'@'localhost';

ResultMySQL 8.4

mysql> CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt

Query OK, 0 rows affected

mysql> GRANT SELECT ON company.orders TO 'report_reader'@'localhost'

Query OK, 0 rows affected

mysql> GRANT SELECT ON company.order_items TO 'report_reader'@'localhost'

Query OK, 0 rows affected

mysql> GRANT SELECT ON company.products TO 'report_reader'@'localhost'

Query OK, 0 rows affected

mysql> SHOW GRANTS FOR 'report_reader'@'localhost'

Grants for report_reader@localhost
GRANT USAGE ON . TO `report_reader`@`localhost`
GRANT SELECT ON `company`.`order_items` TO `report_reader`@`localhost`
GRANT SELECT ON `company`.`orders` TO `report_reader`@`localhost`
GRANT SELECT ON `company`.`products` TO `report_reader`@`localhost`

4 rows in set

Column-level grant

HR assistants may see employee names and departments, but not salaries:

MySQL

CREATE USER 'hr_assistant'@'localhost' IDENTIFIED BY 'Hr#Asst2026';GRANT SELECT (emp_id, first_name, last_name, dept_id, job_title) ON company.employees TO 'hr_assistant'@'localhost';GRANT UPDATE (job_title) ON company.employees TO 'hr_assistant'@'localhost';SHOW GRANTS FOR 'hr_assistant'@'localhost';

ResultMySQL 8.4

mysql> CREATE USER 'hr_assistant'@'localhost' IDENTIFIED BY 'Hr

Query OK, 0 rows affected

mysql> GRANT SELECT (emp_id, first_name, last_name, dept_id, job_title) ON co…

Query OK, 0 rows affected

mysql> GRANT UPDATE (job_title) ON company.employees TO 'hr_assistant'@'local…

Query OK, 0 rows affected

mysql> SHOW GRANTS FOR 'hr_assistant'@'localhost'

Grants for hr_assistant@localhost
GRANT USAGE ON . TO `hr_assistant`@`localhost`
GRANT SELECT (`dept_id`, `emp_id`, `first_name`, `job_title`, `last_name`), UPDATE (`job_title`) ON `company`.`employees` TO `hr_assistant`@`localhost`

2 rows in set

How this query works, step by step

SELECT (columns) allows reading only the listed columns; SELECT salary FROM employees would fail for this account.
UPDATE (job_title) allows changing that one column only.

Security through views

Column grants cannot filter rows. A view can: give a regional manager only Mumbai employees:

MySQL

CREATE VIEW mumbai_staff ASSELECT emp_id, first_name, last_name, job_title FROM employees WHERE city = 'Mumbai';CREATE USER 'mumbai_mgr'@'localhost' IDENTIFIED BY 'Mumb@i#2026';GRANT SELECT ON company.mumbai_staff TO 'mumbai_mgr'@'localhost';SHOW GRANTS FOR 'mumbai_mgr'@'localhost';

ResultMySQL 8.4

mysql> CREATE VIEW mumbai_staff AS

Query OK, 0 rows affected

mysql> CREATE USER 'mumbai_mgr'@'localhost' IDENTIFIED BY 'Mumb@i

Query OK, 0 rows affected

mysql> GRANT SELECT ON company.mumbai_staff TO 'mumbai_mgr'@'localhost'

Query OK, 0 rows affected

mysql> SHOW GRANTS FOR 'mumbai_mgr'@'localhost'

Grants for mumbai_mgr@localhost
GRANT USAGE ON . TO `mumbai_mgr`@`localhost`
GRANT SELECT ON `company`.`mumbai_staff` TO `mumbai_mgr`@`localhost`

2 rows in set

The account can read the view but has no privilege on employees itself (Views).

Grant to a role

MySQL

CREATE ROLE 'qa_tester';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'qa_tester';CREATE USER 'arjun'@'localhost' IDENTIFIED BY 'Arj#un2026';GRANT 'qa_tester' TO 'arjun'@'localhost';SHOW GRANTS FOR 'arjun'@'localhost';

ResultMySQL 8.4

mysql> CREATE ROLE 'qa_tester'

Query OK, 0 rows affected

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'qa_tester'

Query OK, 0 rows affected

mysql> CREATE USER 'arjun'@'localhost' IDENTIFIED BY 'Arj

Query OK, 0 rows affected

mysql> GRANT 'qa_tester' TO 'arjun'@'localhost'

Query OK, 0 rows affected

mysql> SHOW GRANTS FOR 'arjun'@'localhost'

Grants for arjun@localhost
GRANT USAGE ON . TO `arjun`@`localhost`
GRANT `qa_tester`@`%` TO `arjun`@`localhost`

2 rows in set

See Users & Roles.

WITH GRANT OPTION and ALL PRIVILEGES

WITH GRANT OPTION lets the account pass its own privileges on to others. Give it only to administrators.
GRANT ALL PRIVILEGES ON company.* TO ... gives every database-level privilege, including DROP. Convenient for a developer’s own test database, dangerous for an application account.

MySQL

CREATE USER 'team_lead'@'localhost' IDENTIFIED BY 'L3ad#2026';GRANT ALL PRIVILEGES ON company.* TO 'team_lead'@'localhost' WITH GRANT OPTION;SHOW GRANTS FOR 'team_lead'@'localhost';

ResultMySQL 8.4

mysql> CREATE USER 'team_lead'@'localhost' IDENTIFIED BY 'L3ad

Query OK, 0 rows affected

mysql> GRANT ALL PRIVILEGES ON company.* TO 'team_lead'@'localhost' WITH GRAN…

Query OK, 0 rows affected

mysql> SHOW GRANTS FOR 'team_lead'@'localhost'

Grants for team_lead@localhost
GRANT USAGE ON . TO `team_lead`@`localhost`
GRANT ALL PRIVILEGES ON `company`.* TO `team_lead`@`localhost` WITH GRANT OPTION

2 rows in set

Errors

Granting on an account that does not exist fails in MySQL 8 (older versions silently created it):

MySQL

GRANT SELECT ON company.* TO 'ghost'@'localhost';

ResultMySQL 8.4

ERROR 1410 (42000): You are not allowed to create a user with GRANT

Quick quiz

1. What does ON company.* mean?

db.* is the database level.

2. How do you let an account read only some columns of a table?

3. Which tool restricts which rows an account can see?

4. What does WITH GRANT OPTION allow?

GRANT FAQ

What is the GRANT command in SQL?

A Data Control Language statement that gives privileges, such as SELECT or INSERT, on databases, tables, columns or routines to accounts or roles.

How do I see the privileges of a MySQL user?

SHOW GRANTS FOR 'user'@'host'; For your own account, SHOW GRANTS;.

Do I need FLUSH PRIVILEGES after GRANT?

No. GRANT, REVOKE, CREATE USER and similar statements take effect immediately. FLUSH PRIVILEGES is only needed after editing the grant tables directly, which you should not do.