zidance's avatar

How to design database for user with different role can login with different method?

Should I separate it from user table to avoid nullable column in the table? Or I should just stick with user table and add the necessary column to it?

For example, user with admin/staff role login with email and password, while customer can use social account/phone number to login.

Any better approaches to design the database table when in this kind of situation?

0 likes
8 replies
martinbean's avatar

@zidance A user is a user. There’s no need to create separate tables for the same information. Just use a role to designate what type of user each one is, and then authorization to determine what a user can and cannot do based on their role(s).

If a user can connect social accounts to their profile, then store these connections in their own table. Authenticating with a third-party site is usually done via OAuth. Sites like Facebook and Twitter will give you an ID for that user on that site, so you can let a user connect say, a Twitter account in their account settings on your site. You can save the Twitter ID and your user ID so that the next time the user uses Twitter to log in, you can look up the corresponding account on your site and authenticate them:

class TwitterController extends Controller
{
    public function handleProviderCallback()
    {
        $twitterUser = Socialite::driver('twitter')->user();

        // Find user associated with this Twitter account
        $accessToken = AccessToken::where(function ($query) use ($twitterUser) {
            $query->where('provider_type', '=', 'twitter');
            $query->where('provider_id', '=', $twitterUser->getId());
        })->first();

        if ($accessToken) {
            // Update row with fresh token and secret, and authenticate user
            $accessToken->update([
                'access_token' => $twitterUser->token,
                'access_token_secret' => $twitterUser->token_secret,
            ]);

            Auth::loginUsingId($accessToken->user_id);

            return redirect()->intended('/');
        }

        // This Twitter account does not belong to any user
        // Show error message instead
        return redirect()
            ->to('/login')
            ->with('error', 'This Twitter account does not belong to a user account');
    }
}
zidance's avatar

@martinbean Will take a look on it. My concern more on better way to design when a user have multiple authentication mechanism, should I separate them or stick to user table will do by adding the necessary column.

jlrdw's avatar

@ronvanderheijden you said:

no unnecessary checks for a user's type to check a permission

In a setup like that, any user can edit your information. You would still need authorization, just like the forum here. Otherwise I would be able to edit your answer.

@martinbean is right, but just my opinion.

@zidance I have written many apps where users might have more than one role. That's where authorization and the use of a good query scope comes in.

martinbean's avatar

So to stay in your example, I don't think that laracast backend admins are in the same table as we are.

@ronvanderheijden I’d bet $10 that Jeffrey doesn’t have a separate “admins” table and instead just has an is_admin column or role column in his users table.

jlrdw's avatar

@zidance Another user deleted their reply.

But an example I have given before:

  • Bob is an admin

  • Suzy is admin and does bookkeeping

  • Mary is a bookkeeper only

  • If Bob is logged in, Bob can only do admin stuff and all access to user stuff. But Bob cannot mess with bookkeeping.

  • If Suzy is logged in she can access admin stuff and bookkeeping and accounting stuff.

  • If Mary is logged in she cannot mess with admin stuff, but has access to bookkeeping and accounting stuff.

So I just check at method level if the logged in users role can or cannot access that method / function.

And use query scopes to let a user edit / view their own data or an admin can access all users data.

Each app will be different as to who can do what.

So in pseudocode:

public function makeInvoice()
    {
        if (a required role of bkeep is not true here) {   // bkeep = bookkeeper
            return redirect('somewhere'); // whereever you redirect to if not authorized
        }
        // Rest of method here is accomplished if 
        // the logged in user has the required role of 'bkeep'.
    }

Again just examples.

Also a Spatie example I saw:

public function update(Request $request, Post $post) {
    if ($post->author !== auth()->user()->id || auth()->user()->cannot('edit posts'))
        abort(404);// or redirect, or whatever action 
    }
    //rest of method if all okay
}

In summary RBAC is at least 4 main steps:

  • A login required
  • An authorization implementation to determine what the logged in person with role can or cannot do
  • Protection of URL and parameters, checking that the logged in users id matches the id used in a query
  • How the query is done to ensure correct user is editing only their data, etc

Each application will require unique tweaks in RBAC, no two apps are exactly the same.

So if a user has more than one role, you check if the method matches the required role. You could have a user with 4 roles, but that doesn't matter when authorization is implemented.

Edit:

Here on laracasts @jeffreyway has free lessons on how to setup all this stuff, authentication and authorization.

2 likes

Please or to participate in this conversation.