• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to List Users and Privileges in MySQL

by admin

The information about the users is stored in the user system table in the mysql database.

A user consists of two parts: the user name and the host the user connected from, so to get the list of users, it is necessary to include both the user and host columns from the user table:

mysql> SELECT User, Host, CONCAT(User, '@', Host) AS Username
  FROM mysql.user;

You may also run the following command, that checks the information_schema.USER_PRIVILEGES table instead:

shell> mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ', GRANTEE, ';') FROM information_schema.USER_PRIVILEGES" > show-grants.sql
Note: Note that the usage of -N option will skip displaying column names in the output.

To get a list of privileges for a given user, the SHOW GRANTS command can be used, for example to get the privileges for the root user when logged in from localhost use:

SHOW GRANTS FOR 'root'@'localhost';

To get a list of users with grant over certain schema/s, the following query can be used (note that permissions on schemas are maintained after schema is dropped):

SELECT User FROM mysql.db WHERE Db ="[schema]";

Filed Under: mysql

Some more articles you might also be interested in …

  1. How to find location of MySQL configuration file(s)
  2. What is the meaning of the TRX_TABLES_LOCKED column in the information_schema.INNODB_TRX MySQL table
  3. MySQL Backup stuck at “Starting to lock all the tables”
  4. MySQL Cluster requirements to avoid Single Point of Failure
  5. Understanding MySQL Query Cache
  6. MySQL Cluster Point-In-Time Recovery (PITR)
  7. How to take Logical Backups on a MySQL Replication Slave using shell script
  8. What is the MySQL Enterprise Monitor?
  9. How to Troubleshoot InnoDB Lock Issues
  10. Beginners Guide to Management of MySQL Cluster Log Files

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright