• 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 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 find location of MySQL configuration file(s)
  2. CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
  3. How to Restore a Cluster Slave Using its Own Backups
  4. How To Restore an NDBCluster Backup Using ndb_restore
  5. How to List and Set SELinux Context for MySQL Server
  6. Managing MySQL Using Systemd As A Non Root User
  7. How to Restart a MySQL Cluster without downtime
  8. MySQL Error “Too many connections” and how to resolve it
  9. How to configure resource groups for MySQL Server running on Linux
  10. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged

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