Effective permission management in MySQL and MariaDB is crucial for maintaining the security of your database. Administrators must frequently audit user access and remove unnecessary privileges to prevent unauthorized data access and potential security breaches. Both MySQL and MariaDB rely on a privilege system to manage user rights.

Over time, users may accumulate privileges that are no longer needed, or a role change may require reducing their access. The REVOKE command is used to revoke specific privileges from a user or remove all their access rights. Additionally, running the FLUSH PRIVILEGES command is essential to ensure that any changes made to user permissions take immediate effect.

Consistently managing and auditing user access rights helps to maintain a secure environment. Using the correct syntax for revoking privileges ensures that users only have the access they need, and no more. This helps to mitigate security risks and protect the integrity of your database.

Steps to revoke permissions from MySQL or MariaDB users:

  1. Log in to MySQL or MariaDB as an administrator.
    $ mysql -u root -p
    Enter password:
    mysql>

    Use 'root' or any administrative user account for login.

  2. View the privileges assigned to the user.
    mysql> SHOW GRANTS FOR 'username'@'host';
    +-----------------------------------------------------------+
    | Grants for username@host                                  |
    +-----------------------------------------------------------+
    | GRANT SELECT, INSERT, UPDATE ON `database_name`.* TO 'username'@'host' |
    +-----------------------------------------------------------+
    1 row in set (0.00 sec)

    Replace 'username' and 'host' with the actual username and host details.

  3. Revoke specific privileges from the user.
    mysql> REVOKE SELECT ON database_name.* FROM 'username'@'host';
    Query OK, 0 rows affected (0.00 sec)

    This removes the SELECT privilege from the user for the specified database.

  4. Revoke all privileges from the user if necessary.
    mysql> REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
    Query OK, 0 rows affected (0.00 sec)

    This command removes all access to the database for the specified user.

  5. Flush the privileges to apply the changes.
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)

    Always run FLUSH PRIVILEGES after making changes to ensure they are immediately applied.

  6. Verify that the permissions were revoked.
    mysql> SHOW GRANTS FOR 'username'@'host';
    +-------------------------------------+
    | Grants for username@host            |
    +-------------------------------------+
    | GRANT USAGE ON *.* TO 'username'@'host' |
    +-------------------------------------+
    1 row in set (0.00 sec)
  7. Exit the database system.
    mysql> exit;
    Bye
  8. Optionally, test the user’s access to ensure that permissions have been correctly revoked.
Discuss the article:

Comment anonymously. Login not required.