How do I remove a MySQL user from a database?
To remove a MySQL user from a database, you can use theREVOKE
statement. TheREVOKE
statement is used to revoke privileges granted to a MySQL user account. Here’s a step-by-step guide:
Using Command Line:
- Open a Command Prompt or Terminal:
- On Windows, you can open the Command Prompt.
- On macOS or Linux, use the Terminal.
- Log in to MySQL:
- Enter the following command and provide the MySQL root user password when prompted:
bash
mysql -u root -p
Revoke Privileges:
- Use the
REVOKE
statement to revoke the privileges from the user. Replaceusername
with the actual username,database_name
with the database name, andprivilege
with the specific privilege to revoke.
sql
REVOKE privilege ON database_name.* FROM ‘username’@’hostname’;
- For example, to revoke all privileges on a database:
sql
REVOKE ALL PRIVILEGES ON database_name.* FROM ‘username’@’hostname’;
- Replace
'hostname'
with the specific hostname or ‘%’ for any host.
Flush Privileges:
- After revoking privileges, it’s a good practice to flush the privileges to apply the changes immediately:
sql
FLUSH PRIVILEGES;
- Exit MySQL:
- Type
exit
and press Enter to exit the MySQL shell.
- Type
Using a MySQL Client (e.g., phpMyAdmin):
- Access phpMyAdmin:
- Log in to your phpMyAdmin interface through your web browser.
- Select Database and User:
- Click on the database on the left-hand navigation panel.
- Navigate to the “User accounts” section.
- Revoke Privileges:
- Locate the user account you want to remove.
- Click on the “Edit Privileges” icon or navigate to the “Privileges” tab for that user.
- Find the privileges you want to revoke and uncheck them.
- Apply Changes:
- Scroll down and click the “Go” or “Apply” button to save the changes.
Keep in mind that removing a user from a database does not delete the user account itself; it only revokes the privileges for that specific database. If you want to remove the user account entirely, you may use theDROP USER
statement.
Using Command Line (for User Removal):
To remove the user account:
sql
DROP USER ‘username’@’hostname’;
Replace'username'@'hostname'
with the actual username and hostname.
Always exercise caution when modifying user privileges to avoid unintended consequences.