Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Try Vanilla Forums Cloud product
Vanilla 2.6 is here! It includes security fixes and requires PHP 7.0. We have therefore ALSO released Vanilla 2.5.2 with security patches if you are still on PHP 5.6 to give you additional time to upgrade.
Please upgrade to 2.3 here. The 2.2 and earlier branches are no longer being updated.

Assigning a role via SQL query works but doesn't work

edited December 2017 in Vanilla 2.0 - 2.3

I have setup an automated SQL query to assign members a role that allows them to see a private forum. This is done via GDN_UserRole table.

On the surface it appears to work - looking at dashboard/users it shows the role assigned to the member. However, the member doesn't see the private forum unless the role is manually assigned via the dashboard by an admin.

Is there another modification needed in the database other than GDN_UserRole for a role to be fully assigned?


  • this is why you have APIs. databases these days to not give all the clues to the model, and you haven't future proofed by doing it his way.

    grep is your friend.

  • edited December 2017

    I did do a lot of Googling into a jsConnect method but just couldn't figure it out. I would love some help if you know a better way to do this.

    For now though, is it true then that there are more SQL references needed for this to work?

  • LincLinc Director of Development Detroit Vanilla Staff

    My guess is either you have caching enabled which is delaying the user seeing the category (probably until next full login), or you don't have the permissions for the role set up correctly.

  • edited December 2017

    Logging out and logging back in didn't work.

    The role itself is setup correctly because if I apply it to a user manually via dashboard, it works fine and instantly.

  • you best bet is look at the role model, that is what is there for.

    grep is your friend.

  • I agree with linc regards cache. You will need to clear memcached if you are running it. That is wha the model does with the setUserRoles method.

    Anyway you could use eh api to do this sever to server. It is never a great idea to to this low level data insertions, on a framework, it is and it is not supposed to be the easy way, on the contrary.

    Aslo are there muliple roles invovled? roles are additive.

    grep is your friend.

  • it is actually the usermodel saveRoles that is relevant. However using the api would be better. Or getting jsConnect set up correctly.

    grep is your friend.

  • a user permissions re stashed for easy access, which is likely why you have this problem. Best work with the framwork rather than try an pull its strings.

    grep is your friend.

  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    @w1ckedsick said:
    I did do a lot of Googling into a jsConnect method but just couldn't figure it out. I would love some help if you know a better way to do this.

    For now though, is it true then that there are more SQL references needed for this to work?

    Create a plugin. This is just a draft, not tested.

    $PluginInfo['SaveUserRoleHelper'] = [
        // Your work...
    class SaveUserRoleHelperPlugin extends Gdn_Plugin {
         * Create an endpoint for setting roles
         * Must be called like that:
         * Your software must save two lines to GDN_UserMeta:
         * UserID: Vanilla User ID
         * Name: Plugin.SaveUserRoleHelper.Key
         * Value: Some long and random string which should be url safe
         * UserID: Vanilla User ID
         * Name: Plugin.SaveUserRoleHelper.RoleID
         * Value: The ID of the role to add
        public function pluginController_saveUserRoleHelper_create($sender, $args) {
            // /
            $userID = intval(val(0, $args, 0));
            $secretKey = val(1, $args, 0);
            $storedKey = $this->getUserMeta($userID, 'SaveUserRoleHelper.Key');
            if ($secretKey !== $storedKey) {
                $wrongTries = $this->getUserMeta($userID, 'SaveUserRoleHelper.WrongTries');
                if ($wrongTries > 5) {
                    // Prevent brute force hacks.
                    $this->setUserMeta($userID, 'SaveUserRoleHelper.%', null);
                $this->setUserMeta($userID, 'SaveUserRoleHelper.WrongTries' , $wrongTries + 1);
            $targetRoleID = $this->getUserMeta($userID, 'SaveUserRoleHelper.RoleID');
            $currentRoleIDs = Gdn::userModel->getRoles($userID);
            $newRoleIDs = $currentRoleIDs;
            $newRoleIDs[] = $targetRoleID;
            Gdn::userModel()->saveRoles($userID, $newRoleIDs);
    1. You have to find out what to write in the plugin info array
    2. You have to look up how setUsetMeta could clean up the entries from the UserMeta table so that you could clean up after the role is set or if there were too many wrong tries
    3. Check if the handling of the roles is what you like

    Afterwards instead of setting a role with SQL you would have to add two lines to the USerMeta table like described in the comment and afterwards call that endpoint. Afterwards the role will be properly set by the UserModel. That would be the approach I would recommend

  • Thanks a lot for getting me started!

  • topcheesetopcheese Montreal Moderator

    If you are dead set on using a SQL query to assign a role I suggest also emptying the Permissions field of the user in question, this way on refresh/login the new set of permissions will be attributed to the user making the private forum available to him without manually assigning the role to him.

    I do agree with x00 using the API is a better solution for what you are trying to accomplish.

Sign In or Register to comment.