CODEWITHSUNDEEP
mysql
Saleem
Saleem

Reputation: 75

Mysql: How do I find all the users with ALTER permissions or any permissions?

I have lot of users in mysql db. How do I filter users based on a certain permission?

Like I need to find all users that have CREATE or ALTER permissions.

Upvotes: 2

Views: 169

Answers (2)

Krushi Raj
Krushi Raj

Reputation: 538

MySQL store all the privileges in mysql schema and there are various tables based on what kind of privileges you would want to look for. Below are tables in which different privileges are stored.

  • tables_priv
  • columnss_priv
  • procs_priv

You can have a look at what privileges you can find in each of these tables over here

Upvotes: 0

Naktibalda
Naktibalda

Reputation: 14110

Mysql stores use privileges in the schema named mysql.

If you want to select users who have those privileges at global or database level, the query you are looking for is:

SELECT user, host FROM mysql.user WHERE Alter_priv = 1 OR Create_priv = 1
UNION
SELECT user, host FROM mysql.db WHERE Alter_priv = 1 OR Create_priv = 1;

Upvotes: 1

Related Questions