• 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 gather information on the MySQL 8 Roles and Privileges assigned to a user without using SHOW GRANTS

by admin

List the active and inactive roles available on the system

Active Role: a roll that has at least 1 user account assigned to the role:

mysql> SELECT DISTINCT u.User 'Role Name', if(e.from_user is NULL,0, 1) Active
  FROM mysql.user u
    LEFT JOIN role_edges e ON e.from_user=u.user
  WHERE u.account_locked='Y' AND u.password_expired='Y' AND u.authentication_string='';
+-----------+--------+
| Role Name | Active |
+-----------+--------+
| myrole    |    1   |
| myrole2   |    0   |
+-----------+--------+
2 rows in set (0.00 sec)

Roles assigned to a user

You can use the mysql.role_edges table to see which roles are assigned to which users:

mysql> SELECT * FROM mysql.role_edges;
+-----------+-----------+-----------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST   | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+-----------+---------+-------------------+
| %         | myrole    | localhost | myuser  | N                 |
+-----------+-----------+-----------+---------+-------------------+
1 row in set (0.00 sec)

Global Privileges for a user/role

To find out what specific GLOBAL privileges a user/role has, you can access the INFORMATION_SCHEMA.USER_PRIVILEGES table:

mysql> select * from information_schema.user_privileges where GRANTEE='\'mysql.infoschema\'@\'localhost\''; 
+--------------------------------+---------------+----------------+--------------+ 
| GRANTEE                        | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | 
+--------------------------------+---------------+----------------+--------------+ 
| 'mysql.infoschema'@'localhost' | def           | SELECT         | NO           | 
+--------------------------------+---------------+----------------+--------------+ 
1 row in set (0.00 sec)

Privileges for a user/role

For the actual permissions associate with a role/user you will want to look at the underlying system tables associated with the CREATE USER, GRANT and REVOKE commands.You must understand that the CREATE USER, GRANT and REVOKE commands are the recommended method of interacting with these system tables for privileges and access. MySQL DOES NOT recommend direct modification of the underlying system tables. If you choose to modify the underlying system tables anyway – IT IS DONE AT YOUR OWN RISK!

Filed Under: mysql

Some more articles you might also be interested in …

  1. How To Reset MySQL 8.0 Root Password On Windows
  2. How to configure resource groups for MySQL Server running on Linux
  3. How to obtain MySQL metadata (metadata access methods)
  4. Beginners Guide to Management of MySQL Cluster Log Files
  5. Configuring mysqld to log slow queries
  6. How to backup and restore MySQL database
  7. MySQL Interview Questions : Multi-source Replication in MySQL Server
  8. How to find the size of a MySQL database
  9. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  10. How to find location of MySQL configuration file(s)

You May Also Like

Primary Sidebar

Recent Posts

  • powertop Command Examples in Linux
  • powertop: command not found
  • powerstat: command not found
  • powerstat Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright