Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Options

Deleted Roles Still Assigned to Users

AnonymooseAnonymoose ✭✭
edited January 2015 in Vanilla 2.0 - 2.8

After admin deletes some roles, they are still assigned to users. This is especially the case after upgrades or imports. There is no way to access those "deleted roles" and remove them from users.

These "deleted roles" remain invisible in the dashboard, but are still assigned. The only way to access them is to do manual database manipulation.

However, when new roles are created, sometimes they are assigned the same IDs, and then users with those role IDs previously assigned to them in the past (before upgrading, importing, etc.) are now given the new roles.

There should be a utility to purge old roles from users.

Comments

  • Options
    peregrineperegrine MVP
    edited January 2015

    However, when new roles are created, sometimes they are assigned the same IDs,

    this should not be the case. the role table auto increments roleid. and does not reuse old ids AFAIK

    when you delete a role it says move users to another role.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • Options
    peregrineperegrine MVP
    edited January 2015

    set your autoincrement number to an id that is higher than you largest roleid number. and you won't be re-using roleids in role table.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • Options
    AnonymooseAnonymoose ✭✭
    edited January 2015

    @R_J said:
    Looking at hgtonight s profile, I see roles "Moderator, , Developer" and I read that as "Moderator, SomeMysteriousAndSecretRoleThatNoOneElseCanSee, Developer" because some other mods, like Shadowdare, don't have that additional comma, denoting a hidden role!!!

    I wonder what else is going on behind the scenes... ;)

    http://vanillaforums.org/discussion/27383/i-wonder-what-that-secret-role-is-that-you-ve-got

  • Options

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • Options
    R_JR_J Ex-Fanboy Munich Admin

    Nope, that's really an issue!
    1. Create role
    2. Assign user to role
    3. Delete role
    4. Create new role

    The role that is created in step 4 has the same ID as the role from step 1!
    If you haven't chosen a replacement role in step 3, there is still an entry in UserRole for that formerly deleted RoleID which in this case is obviously wrong. I'll file it on GitHub.

  • Options
    AnonymooseAnonymoose ✭✭
    edited January 2015

    Thanks. It would be nice to have a utility that loops through each user and checks for nonexistent roles to clean things up. Kind of like the structure tool.

  • Options
    peregrineperegrine MVP
    edited January 2015

    If you haven't chosen a replacement role in step 3,

    you are correct. I stand corrected. just tested.

    even if you check replace role when removing. it still adds the new role back to the user that had the previous role and does re-use role id.

    sometimes moose are correct.

    and you are right as well r_j.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • Options
    R_JR_J Ex-Fanboy Munich Admin

    @Anonymoose said:
    Thanks. It would be nice to have a utility that loops through each user and checks for nonexistent roles to clean things up. Kind of like the structure tool.

    DELETE *
    FROM GDN_UserRole
    WHERE RoleID NOT IN (
        SELECT RoleID
        FROM GDN_Role
    )
    

    But I think that it is really an issue that must be solved: a deleted role must be deleted from table UserRole.

  • Options
    hgtonighthgtonight ∞ · New MVP
    edited January 2015

    @Anonymoose Let's build one together!

    Create a folder in the /plugins folder called UserRoleCleaner or something more imaginative. Then create an empty file called class.userrolecleaner.plugin.php in that new folder. Lets add the following to set up the basic information:

    <?php if (!defined('APPLICATION')) exit();
    /*Copyright 2015 Zachary Doll */
    $PluginInfo['UserRoleCleaner'] = array(
      'Name' => 'User Role Cleaner',
      'Description' => 'Removes records from the UserRole table that point to non-existent roles.',
      'Version' => '0.1',
      'RequiredApplications' => array('Vanilla' => '2.1.6'),
      'MobileFriendly' => TRUE,
      'Author' => 'Zachary Doll',
      'AuthorEmail' => 'hgtonight@daklutz.com',
      'AuthorUrl' => 'http://www.daklutz.com',
      'License' => 'GPLv3'
    );
    
    class UserRoleCleaner extends Gdn_Plugin {
    
      public function Structure() {
    
      }
    
      public function Setup() {
        $this->Structure();
      }
    }
    

    The plugin info array should all be self-explanatory. The UserRoleCleaner::Setup() method will be called whenever the plugin is enabled. The UserRoleCleaner::Structure() method will be called if the plugin is enabled and UtilityController::Structure() is executed (generally from /utility/structure).

    SO this plugin, upon enabling calls it's own Structure() method. Which does absolutely bupkis at this point.

    So what do we want to do? We want to find any RoleIDs that exist in the UserRole table but not in the Role table. I came up with the following SQL that does this through some simple trial and error:

    SELECT ur.`RoleID` FROM `GDN_UserRole` as ur
    LEFT JOIN `GDN_Role` as r on r.`RoleID` = ur.`RoleID`
    WHERE r.`RoleID` IS NULL
    GROUP BY ur.`RoleID`
    

    Once we get the non-existent role IDs, we need to remove those records from the UserRole table. That is as easy as looping through the array of RoleIDs and running the following SQL:

    DELETE FROM `GDN_UserRole` WHERE `RoleID` = :RoleID
    

    Put into Vanilla terms, it might look like this:

    $RoleIDs = Gdn::SQL()
            ->Select('ur.RoleID')
            ->From('UserRole ur')
            ->Join('Role r', 'r.RoleID = ur.RoleID', 'LEFT')
            ->Where('r.RoleID')
            ->GroupBy('ur.RoleID')
            ->Get()
            ->Result();
    
    foreach($RoleIDs as $Row) {
      Gdn::SQL()->Delete('UserRole', array('RoleID' => $Row->RoleID));
    }
    

    Pop that into your Structure() method and you should be good to go!

    @R_J what the heck man! Too fast. :|

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

  • Options
    AnonymooseAnonymoose ✭✭
    edited January 2015

    Pop that into your Structure() method and you should be good to go!

    Awesome!

  • Options
    AnonymooseAnonymoose ✭✭
    edited January 2015

    @R_J said:

    Failed to execute SQL : SQL DELETE * FROM GDN_UserRole WHERE RoleID NOT IN ( SELECT RoleID FROM GDN_Role ) failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM GDN_UserRole WHERE RoleID NOT IN ( SELECT RoleID FROM GDN_Role )' at line 1

  • Options
    R_JR_J Ex-Fanboy Munich Admin
    DELETE
    FROM GDN_UserRole
    WHERE RoleID NOT IN (
        SELECT RoleID
        FROM GDN_Role
    )
    

    I'm always adding that bloody "*" to DELETE queries...
    Also make sure that you have also the table prefix "GDN_" simply by looking at the db structure

  • Options
    AnonymooseAnonymoose ✭✭
    edited January 2015

    @hgtonight said:
    Anonymoose Let's build one together!

    Thank you. I'm not that good at php, but I'm trying to learn.

Sign In or Register to comment.