MySQL Grant Privilege

The MySQL GRANT statement is used to give specific privileges to users or roles for specific database objects (e.g., tables, databases). The GRANT statement is typically used by database administrators to control access to MySQL databases and to define what actions (privileges) users or roles can perform.

The basic syntax of the GRANT statement is as follows:

GRANT privileges
ON database.object
TO 'user'@'host';

Here’s a breakdown of each part of the statement:

• privileges: Specifies the specific privileges you want to grant. These can include privileges like SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and many others. You can also use ALL PRIVILEGES to grant all available privileges.
• database.object: Identifies the specific database or database object to which you want to grant the privileges. This could be a specific database, table, or a wildcard character like *.* to represent all databases and objects.
• user@host: Specifies the user or role you want to grant the privileges to and the host from which they are allowed to connect. The user and host are typically specified as ‘username’@’hostname’, where the hostname can include wildcard characters for more flexibility.

Here are some examples of how to use the GRANT statement in MySQL:

1. Grant SELECT and INSERT privileges on the employees table in the hr database to a user named ‘john’ connecting from the localhost:

   GRANT SELECT, INSERT ON hr.employees TO 'john'@'localhost';

2. Grant all privileges on all tables in the sales database to a user named ‘manager’ connecting from any host:

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

3. Grant the ability to create databases and tables to a user named ‘developer’ connecting from the localhost:

   GRANT CREATE, CREATE TABLE ON *.* TO 'developer'@'localhost';

After executing the GRANT statement, the specified user or role will have the defined privileges for the specified database object. The changes will take effect immediately, and the user or role can perform the granted actions on the designated database or table.

It’s important to note that the GRANT statement should be executed by a user with the necessary administrative privileges, such as GRANT OPTION, to be able to grant privileges to others. Additionally, you may need to execute a FLUSH PRIVILEGES statement for the changes to take effect immediately.