Home / server / Questions / 115950
Accepted
Clutch
Asked: 2010-02-24 11:56:54 +0800 CST

How do I change the privileges for MySQL user that is already created?

  • 772

I understand how to create a new user with privileges, but what is the correct way to change privileges for users that are already created?

We are running a DB audit and some of the users have way more access then is needed. Plus I don't know the passwords for most of these MySQL users, so I don't want to delete them and create new ones.

mysql

2 Answers

  1. Best Answer

    To list users:

    select user,host from mysql.user;

    To show privileges:

    show grants for 'user'@'host';

    To change privileges, first revoke. Such as:

    revoke all privileges on *.* from 'user'@'host';

    Then grant the appropriate privileges as desired:

    grant SELECT,INSERT,UPDATE,DELETE ON `db`.* TO 'user'@'host';

    Finally, flush:

    flush privileges;

    The MySQL documentation is excellent:

    https://dev.mysql.com/doc/refman/8.0/en/access-control.html

    • 117

  2. without downtime you can do this (example for granting ALTER command):

    UPDATE mysql.db
set Alter_priv='Y'
WHERE User='USER'

    for any other privilege, see current user privileges and right columns to change:

    SELECT *
FROM mysql.db
WHERE User='USER'
    • 0