Deleted Roles Still Assigned to Users

Anonymoose

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.

peregrine

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.

peregrine

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.

Anonymoose

@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

peregrine

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

it is proprietary and already addressed.

R_J

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.

Anonymoose

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.

peregrine

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.

R_J

@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.

hgtonight

@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.

Anonymoose

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

Awesome!

Anonymoose

@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

R_J

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

Anonymoose

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

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