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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. MySQL Interview Questions : Multi-source Replication in MySQL Server
  2. How to configure resource groups for MySQL Server running on Linux
  3. How to backup and restore MySQL database
  4. How to List Tables Per Tablespace In MySQL Cluster
  5. How to find Which Process Is Killing mysqld With SIGKILL or SIGTERM on Linux
  6. MySQL Backup stuck at “Starting to lock all the tables”
  7. MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB
  8. Beginners Guide to MySQL Data Types
  9. Configuring mysqld to log slow queries
  10. Beginners Guide to Management of MySQL Cluster Log Files

You May Also Like

Primary Sidebar

Recent Posts

  • SQL script to find tables that are fragmented
  • TRUNCATE TABLE not releasing space from tablespace
  • How to reclaim entire space of an oracle database table with “Truncate Table” statement
  • Oracle SQL Script to Report Tablespace Free and Fragmentation
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary