MySQL - Revoke Privileges

MySQL - REVOKE Statement

The REVOKE statement is a critical Data Control Language (DCL) command used to remove privileges from a MySQL user account or role. It is the counterpart to the GRANT statement and is essential for implementing the security principle of least privilege, which states that a user should only have the exact permissions necessary to perform their job, and no more.

The MySQL REVOKE Statement

You can revoke specific privileges on different levels: global (*.*), database (database.*), table (database.table), or even column level.

Syntax

REVOKE privilege_type [(column_list)]
ON object_type
FROM user_or_role;

For example:

REVOKE SELECT, INSERT ON my_app_db.products FROM 'app_user'@'localhost';

Example: Revoking a Specific Privilege

First, let’s create a user and grant them some privileges on a specific table.

-- Step 1: Create a user and a database/table for the example
CREATE DATABASE IF NOT EXISTS my_app_db;
CREATE TABLE IF NOT EXISTS my_app_db.products (id INT, name VARCHAR(100));
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password123';

-- Step 2: Grant SELECT and UPDATE privileges
GRANT SELECT, UPDATE ON my_app_db.products TO 'test_user'@'localhost';

-- Step 3: Verify the granted privileges
SHOW GRANTS FOR 'test_user'@'localhost';

The output of SHOW GRANTS will include: GRANT SELECT, UPDATE ON my_app_db.productsTOtest_user@localhost`.

Now, let’s revoke the UPDATE privilege but leave the SELECT privilege intact.

REVOKE UPDATE ON my_app_db.products FROM 'test_user'@'localhost';

A Query OK response indicates success. No FLUSH PRIVILEGES is necessary.

Verification

To confirm the privilege was removed, we check the grants again.

SHOW GRANTS FOR 'test_user'@'localhost';

The new output will now only show: GRANT SELECT ON my_app_db.productsTOtest_user@localhost`.

Revoking All Privileges

To remove all privileges from a user, you can use REVOKE ALL PRIVILEGES. This is often followed by DROP USER if the account is being decommissioned.

-- This revokes all privileges granted to the user.
-- The GRANT OPTION allows the user to grant their own privileges to others.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'test_user'@'localhost';

After running this, SHOW GRANTS for the user will only show GRANT USAGE ON *.* ..., which means the user can connect but has no permissions on any database objects.

Revoking Roles

Roles are a powerful feature for managing permissions. Instead of granting many individual privileges to users, you grant them to a role, and then grant the role to users. Revoking a role is just as easy as revoking a privilege.

Example

-- Step 1: Create a role and grant it permissions
CREATE ROLE 'read_only_role';
GRANT SELECT ON my_app_db.* TO 'read_only_role';

-- Step 2: Grant the role to a user
CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'analytics_pass';
GRANT 'read_only_role' TO 'analyst'@'localhost';

-- Step 3: Now, revoke the role from the user
REVOKE 'read_only_role' FROM 'analyst'@'localhost';

The user ‘analyst’ no longer has the SELECT permissions that were provided by the ‘read_only_role’.

Revoking Privileges Using a Client Program

When revoking privileges from an application, always use the REVOKE statement with parameterized queries to ensure security and prevent errors.

Example (Python with mysql-connector-python)

import mysql.connector

def revoke_privileges(config, username, hostname, privilege, database):
    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor() as cursor:
                # Use format to construct the query string safely for DCL
                # Note: DCL statements often don't support parameter substitution for identifiers.
                # Sanitize inputs carefully if they come from external sources.
                query = f"REVOKE {privilege} ON `{database}`.* FROM %s@%s"
                cursor.execute(query, (username, hostname))
                connection.commit()
                print(f"Successfully revoked {privilege} from '{username}'@'{hostname}' on database '{database}'.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")

if __name__ == "__main__":
    db_config = {
        'host': 'localhost',
        'user': 'root',
        'password': 'your_root_password'
    }

    # Example: Revoke INSERT privilege from test_user on the my_app_db database
    revoke_privileges(db_config, 'test_user', 'localhost', 'INSERT', 'my_app_db')