MySQL | Grant/Revoke Privileges

Managing user privileges is a fundamental aspect of MySQL administration. You can control which users have access to specific databases, tables, and even columns, as well as what actions they can perform on those resources.

1. GRANT:

The GRANT statement in MySQL allows you to grant privileges to MySQL user accounts.

Syntax:

GRANT privilege_type [(column_list)]
  ON [object_type] privilege_level
  TO user [IDENTIFIED BY 'password']
  [REQUIRE {NONE | tls_option}]
  [WITH {GRANT OPTION | resource_option}]

Examples:

Grant All Privileges on a Database:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';

Grant Specific Privileges on a Table:

GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'hostname';

Grant All Privileges with Grant Option (to grant further privileges to others):

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname' WITH GRANT OPTION;

2. REVOKE:

The REVOKE statement is used to remove privileges from MySQL user accounts.

Syntax:

REVOKE privilege_type [(column_list)]
  ON [object_type] privilege_level
  FROM user

Examples:

Revoke All Privileges on a Database:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'hostname';

Revoke Specific Privileges on a Table:

REVOKE SELECT, INSERT ON database_name.table_name FROM 'username'@'hostname';

Important Notes:

After executing GRANT or REVOKE, you should run the FLUSH PRIVILEGES command to ensure that the changes are applied immediately.
```
FLUSH PRIVILEGES;
```
The GRANT and REVOKE commands only modify the privileges in the MySQL grant tables. They don't modify the actual databases or tables in any way.
Use the SHOW GRANTS command to see which privileges an account has:
```
SHOW GRANTS FOR 'username'@'hostname';
```
Always be cautious when granting the GRANT OPTION because a user with this privilege can give their privileges to other users.
Be deliberate and careful when assigning privileges. Make sure to provide only the necessary privileges to maintain a secure database environment. Limiting access can prevent unintended data modification and help mitigate potential security issues.

Regularly reviewing and managing user privileges is a best practice in database administration. It ensures that access is restricted only to those who need it, maintaining data integrity and security.

How to revoke privileges in MySQL:
- To revoke privileges in MySQL, use the REVOKE statement followed by the specific privileges and user.
```
REVOKE SELECT, INSERT ON database.* FROM 'username'@'host';
```
Granting specific privileges to a user in MySQL:
- Use the GRANT statement to give specific privileges to a user.
```
GRANT SELECT, INSERT ON database.* TO 'username'@'host';
```
MySQL REVOKE command for revoking access:
- The REVOKE command is used to remove specific privileges previously granted.
```
REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'host';
```
Granting privileges on tables in MySQL:
- Granting privileges on specific tables in MySQL.
```
GRANT SELECT, INSERT ON database.table TO 'username'@'host';
```
Granting SELECT, INSERT, UPDATE privileges in MySQL:
- Granting multiple privileges to a user.
```
GRANT SELECT, INSERT, UPDATE ON database.* TO 'username'@'host';
```
Revoking all privileges for a user in MySQL:
- Use the REVOKE statement with the ALL PRIVILEGES option to remove all privileges.
```
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';
```
MySQL GRANT OPTION and WITH GRANT OPTION:
- Granting the ability to grant privileges to other users.
```
GRANT SELECT ON database.* TO 'username'@'host' WITH GRANT OPTION;
```