MySQL - GRANT

1. GRANT 

The GRANT statement assigns specific privileges to a user.

Syntax:

GRANT privilege_list
ON database_name.table_name
TO 'username'@'host'
[IDENTIFIED BY 'password'];

Common privileges:

  • ALL PRIVILEGES → all permissions.

  • SELECT → read data.

  • INSERT → insert data.

  • UPDATE → modify data.

  • DELETE → delete rows.

  • CREATE → create new databases/tables.

  • DROP → drop databases/tables.

  • ALTER → alter tables.

  • GRANT OPTION → allow user to grant/revoke privileges to others.

Examples:

-- Give all privileges on all databases/tables to a user
GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost';

-- Give read-only access to a specific database
GRANT SELECT ON sales.* TO 'bob'@'%';

-- Give insert and update access on one table
GRANT INSERT, UPDATE ON sales.customers TO 'carol'@'192.168.1.100';

2. REVOKE – Remove privileges

The REVOKE statement removes previously granted privileges.

Syntax:

REVOKE privilege_list
ON database_name.table_name
FROM 'username'@'host';

Examples:

-- Remove INSERT and UPDATE from one table
REVOKE INSERT, UPDATE ON sales.customers FROM 'carol'@'192.168.1.100';

-- Remove all privileges from a user
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alice'@'localhost';

3. Apply changes

After using GRANT or REVOKE, run:

FLUSH PRIVILEGES;

This reloads the privilege tables immediately.

4. Check privileges

You can see a user’s privileges with:

SHOW GRANTS FOR 'username'@'host';