73

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.

Teun Zengerink
  • 197
Clutch
  • 980

3 Answers3

119

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:

Manuel Jordan
  • 133
Warner
  • 24,174
  • 2
  • 63
  • 69
0

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'
a.k
  • 101
0

My solution was to use ansible mysql_user module. It maintains the user permissions without revoking everything.
Grant a few permissions in test db on testtable table:
playbook.yaml content:

---
- name:
  hosts: localhost
  vars:
    ansible_python_interpreter: /usr/bin/python3
  tasks:
  - name: Test user
    community.mysql.mysql_user:
      name: testuser
      password: testpassword
      priv:
        'test.testtable': SELECT,INSERT,ALTER
      login_host: 127.0.0.1
      login_user: root
      login_password: root
      state: present

Run ansible-playbook playbook.yaml
Then revoke ALTER by removing from priv:

'test.testtable': SELECT,INSERT

run again ansible-playbook playbook.yaml
I checked mysql logs to make sure it does not revoke all, before grant:

2022-10-10T14:34:54.911222Z      1632 Query     SHOW GRANTS FOR 'testuser'@'localhost'
2022-10-10T14:34:54.911797Z      1632 Query     REVOKE ALTER ON `test`.`testtable` FROM 'testuser'@'localhost'
2022-10-10T14:34:54.947049Z      1632 Query     FLUSH PRIVILEGES
2022-10-10T14:34:54.951583Z      1632 Query     SHOW GRANTS FOR 'testuser'@'localhost'
daniel_impl
  • 1