How to Modify User Privileges in MySQL Databases

Here this article will explain how to Modify User Privileges in MySQL Databases in mysql mode. You have to login to server with SSH root privileges.

MySQL is an open source, object-relational database built with speed and reliability in mind. Its large and active developer community has created many third-party applications, tools, and libraries that expand MySQL’s functionality.

The privileges granted to a MySQL user determine what operations that user can perform. MySQL privileges are organized accordingly:

• Administrative privileges allow users to manage the operations of the MySQL server itself, including the privileges of other users. Also known as global privileges.
• Database privileges allow users to manage a specific database and all the objects within that database. These can be granted globally or only for specific databases.
• Database object privileges allow users to manage specific objects within databases. These privileges can be granted for specific objects within a database, for an entire database, or globally.

Modifying user privileges in MySQL involves granting or revoking specific permissions for users on databases or tables.

Here’s a step-by-step guide on how to do it: Modify User Privileges in MySQL Databases

1. Access MySQL

First, log into MySQL as the root user (or any user with sufficient privileges) from the terminal:

mysql -u root -p

Enter your password when prompted.

2. View User Privileges in MySQL Databases

To check what privileges a specific user has, run:

SHOW GRANTS FOR 'username'@'host';

Replace username with the user’s MySQL username and host with the host (e.g., localhost).

3. Grant Privileges in MySQL Databases

To grant privileges, use the following syntax:

GRANT privilege_type ON database_name.table_name TO 'username'@'host';

• privilege_type: The type of permission to grant, e.g., SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES, etc.
• database_name.table_name: The specific database and table for the privilege (use *.* for all databases and tables).
• username and host: Specify the user and host.

Example:

Grant all privileges on a specific database to a user:

GRANT ALL PRIVILEGES ON my_database.* TO 'username'@'localhost';

Granting additional privileges to a user does not remove any existing privileges. To remove existing privileges, use the REVOKE command.

4. Revoke Privileges in MySQL Databases

To remove privileges, use the following syntax:

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

Example:

To revoke INSERT and UPDATE privileges from a user:

REVOKE INSERT, UPDATE ON my_database.* FROM 'username'@'localhost';

5. Apply Changes

After granting or revoking privileges, apply the changes with:

FLUSH PRIVILEGES;

6. Confirm Changes

To verify the user’s privileges, use:

SHOW GRANTS FOR 'username'@'host';

Common Privilege Types

• ALL PRIVILEGES: All permissions.
• SELECT: Read data.
• INSERT: Add data.
• UPDATE: Modify data.
• DELETE: Remove data.
• CREATE: Create databases/tables.
• DROP: Delete databases/tables.
• GRANT OPTION: Allow user to grant privileges.

From the above you have learn how to modify /Revoke user privileges in MySQL Databases. I hope it will helpful to you.