Starts of a feature plugin for global user roles.

Functions

Create table
Remove Table
Activate
Uninstall

gistfile1.txt

/**
 * Useful actions
 * do_action( 'add_user_role', $this->ID, $role );
 * do_action( 'remove_user_role', $this->ID, $role );
 * do_action( 'set_user_role', $this->ID, $role, $old_roles );
 *
 * do_action( 'add_user_to_blog', $user_id, $role, $blog_id );
 * do_action( 'remove_user_from_blog', $user_id, $blog_id );
 *
 * do_action( 'deleted_user', $id, $reassign );
 * do_action( 'wpmu_delete_user', $id );
 *
 * do_action( 'revoke_super_admin', $user_id );
 * do_action( 'granted_super_admin', $user_id );
 *
 * apply_filters( 'populate_network_meta', $sitemeta, $network_id )
 *
 * apply_filters_ref_array( 'users_pre_query', array( null, &$this ) );
 * apply_filters( 'pre_count_users', null, $strategy, $site_id );
 *
 *
 * Multi network support 
 * do_action( 'add_network', $new_network_id, $r );
 * do_action( 'delete_network', $network );
 * do_action( 'move_site', $site_id, $site->network_id, $new_network_id );
 *
 * do_action( 'wp_update_site', $new_site, $old_site );
 * do_action( 'wp_delete_site', $old_site );
 * 
 */

gistfile2.txt

<?php


class my_test
{
    protected $blog_id = 0;

    protected $role__in = array();
    protected $role__not_in = array();

    function setup(){
        add_action('pre_get_users', [$this, 'pre_get_users']);
        add_action('pre_user_query', [$this, 'pre_user_query']);
    }

    function pre_get_users($current){
        if(isset($current->query_vars['blog_id'])){
            $this->blog_id = $current->query_vars['blog_id'];
            $role = array();
            if ( isset( $current->query_vars['role'] ) ) {
                if ( is_array( $current->query_vars['role'] ) ) {
                    $role = $current->query_vars['role'];
                } elseif ( is_string( $current->query_vars['role'] ) && ! empty( $current->query_vars['role'] ) ) {
                    $role = array_map( 'trim', explode( ',', $current->query_vars['role'] ) );
                }
            }

            if ( isset( $current->query_vars['role__in'] ) ) {
                $role__in = (array) $current->query_vars['role__in'];
                $this->role__in = array_merge($role, $role__in);
            }

            if ( isset( $current->query_vars['role__not_in'] ) ) {
                $this->role__not_in = (array) $current->query_vars['role__not_in'];
            }
            $current->query_vars['blog_id'] = 0;

        }
        return $current;
    }

    function pre_user_query($current){
        global $wpdb;

        if(!$this->blog_id){
            return $current;
        }

        if ( $current->query_vars['has_published_posts'] ) {
            if ( true === $current->query_vars['has_published_posts'] ) {
                $post_types = get_post_types( array( 'public' => true ) );
            } else {
                $post_types = (array) $current->query_vars['has_published_posts'];
            }
            foreach ( $post_types as &$post_type ) {
                $post_type = $wpdb->prepare( '%s', $post_type );
            }
            $posts_table        = $wpdb->get_blog_prefix( $this->blog_id ) . 'posts';
            $current->query_where .= " AND $wpdb->users.ID IN ( SELECT DISTINCT $posts_table.post_author FROM $posts_table WHERE $posts_table.post_status = 'publish' AND $posts_table.post_type IN ( " . join( ', ', $post_types ) . ' ) )';
        }

        $current->query_from .= ' INNER JOIN wp_user_role ON ( wp_users.ID = wp_user_role.user_id )';

        if ( isset( $current->query_vars['who'] ) && 'authors' == $current->query_vars['who'] ) {
            $current->query_where .= ' AND ( wp_user_role.level != 0 ) )';
        }

        $current->query_where .= ' AND ( wp_user_role.blog_id = '.$this->blog_id.' )';

        if($this->role__in) {
            $current->query_where .= ' AND ( wp_user_role.role IN ("'.implode('","', $this->role__in).'"") )';
        }

        if($this->role__not_in) {
            $current->query_where .= ' AND ( wp_user_role.role IN ("'.implode('","', $this->role__not_in).'"") )';
        }
    }

}

$test = new my_test();
$test->setup();

Other notes

/**
 * Structure
 *
 * crud
 *  add_user_role($user_id, $role)
 *  remove_user_role($user_id)
 *  set_user_role($user_id, $role)
 *   get_user_roles($args)
 *   get_user_role($id)
 *   get_use_role_by_user($user_id)
 * actions
 * query
 * register
 *  register global table
 *  register global cache
 */

Table structure

wp_role 
id int primary key
user_id int index
blog_id int index default 1
site_id int index default 1
role index varchar default ''

WP CLI commands

Commands
Create table
Remove table
Migrate users --include-super-admin=true