MySQL GRANT

MySQL GRANT Statement

The GRANT statement in MySQL is used to assign specific privileges to users. These privileges determine what actions the user can perform on the database or specific tables. The GRANT command is essential for managing database security and user access control.

Syntax

GRANT privilege_type [(column_list)] 
ON {database_name.table_name | *.* | database_name.*} 
TO 'username'@'host' 
[IDENTIFIED BY 'password'] 
[WITH GRANT OPTION];

privilege_type: Specifies the type of privilege to grant (e.g., SELECT, INSERT, UPDATE, ALL PRIVILEGES).
database_name.table_name: Specifies the database and table the privilege applies to.
- Use *.* for all databases and tables.
- Use database_name.* for all tables in a specific database.
username: The name of the user receiving the privilege.
host: Specifies the host from which the user can connect (% allows access from any host).
IDENTIFIED BY: Optionally sets or updates the user's password.
WITH GRANT OPTION: Allows the user to grant privileges to other users.

Examples

1. Grant All Privileges to a User

Grant all privileges on all databases and tables:

GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost';

2. Grant Privileges on a Specific Database

Grant all privileges on a specific database:

GRANT ALL PRIVILEGES ON sales.* TO 'emma'@'%';

3. Grant Specific Privileges

Grant SELECT and INSERT privileges on a specific table:

GRANT SELECT, INSERT ON inventory.products TO 'dave'@'localhost';

4. Grant Privileges with a Password

Create a new user and assign privileges with a password:

GRANT SELECT, UPDATE ON employees.* TO 'alice'@'%' IDENTIFIED BY 'secure_password';

5. Grant Privileges with Grant Option

Allow the user to grant privileges to others:

GRANT ALL PRIVILEGES ON sales.* TO 'manager'@'localhost' WITH GRANT OPTION;

6. Grant Privileges on Columns

Grant privileges on specific columns in a table:

GRANT SELECT (name, email), UPDATE (email) ON customers TO 'support'@'%';

Revoking Privileges

To revoke privileges from a user:

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

Example:

Revoke INSERT privileges from a user:

REVOKE INSERT ON sales.orders FROM 'emma'@'%';

Viewing Privileges

1. Check Privileges for the Current User

SHOW GRANTS;

2. Check Privileges for a Specific User

SHOW GRANTS FOR 'username'@'host';

3. List All Users

SELECT user, host FROM mysql.user;

Common Privileges

Privilege	Description
`ALL PRIVILEGES`	Grants all privileges except `GRANT OPTION`.
`SELECT`	Allows reading data.
`INSERT`	Allows inserting rows.
`UPDATE`	Allows modifying rows.
`DELETE`	Allows deleting rows.
`CREATE`	Allows creating databases and tables.
`DROP`	Allows dropping databases and tables.
`ALTER`	Allows altering table structure.
`EXECUTE`	Allows executing stored procedures and functions.
`INDEX`	Allows creating and dropping indexes.

Best Practices

Grant Minimal Privileges: Follow the principle of least privilege by granting only the necessary permissions.
Use Strong Passwords: Ensure users have strong passwords when using the IDENTIFIED BY clause.
Avoid Using GRANT OPTION Unnecessarily: Only allow users to grant privileges if necessary.
Audit Privileges: Regularly review and revoke unnecessary privileges.
Limit Host Access: Specify a host or IP address to restrict where users can connect from.

Let me know if you need help setting up or managing MySQL user privileges!

Souy Soeng