-- -----------------------------------------------------------------------
-- Filename:   my_user_role.sql
-- Purpose:    Extracts MySQL users grouped by access level
-- Date:       01-Jan-2018
-- Author:     meo
--
-- Updated By  meo
-- Description Tricky script to find roles (roles will be available in MySQL 8.0)
-- -----------------------------------------------------------------------


-- Admin Users
select '10-Admin' Role, concat(user,'@',host) User
  from mysql.user
 where insert_priv='Y' or delete_priv='Y'
union
-- Operations
select '12-Oper', concat(user,'@',host)
  from mysql.user
 where select_priv='Y'
   and concat(user,'@',host) not in (
	select concat(user,'@',host)
	  from mysql.user
	 where insert_priv='Y' or delete_priv='Y')
union
-- Schema Owners
select '20-Owner', concat(user,'@',host)
  from mysql.db
 where create_priv='Y'
union
-- Application Users
select '22-CRUD', concat(user,'@',host)
  from mysql.db
 where insert_priv='Y'
   and concat(user,'@',host) not in (
	select concat(user,'@',host)
	  from mysql.db
	 where create_priv='Y')
union
-- Schema Read Only Users 
select '24-RO', concat(user,'@',host)
  from mysql.db
 where select_priv='Y'
   and concat(user,'@',host) not in (
	select concat(user,'@',host)
	  from mysql.db
	 where insert_priv='Y')
union
-- Other Users
select '30-Other', concat(user,'@',host)
  from mysql.user
 where concat(user,'@',host) not in (
	select concat(user,'@',host)
	  from mysql.db
	 where select_priv='Y')
   and concat(user,'@',host) not in (
	select concat(user,'@',host)
	  from mysql.user
	 where select_priv='Y')
 order by 1,2;