Associating Permissions with oauth_user_id

[pejeio]

Question: Currently, permissions are associated with the id field in the users table. However, I'm considering whether it would be safer to associate permissions with the oauth_user_id field, which stores the GitHub User ID. I'd like to hear your thoughts on this.

Pros of Associating with oauth_user_id:

• Increased security: GitHub User IDs are less likely to change than internal id values.
• Better tracking: It provides a clear link between permissions and external authentication.

Cons of Associating with oauth_user_id:

• Potential complications: It may require additional logic to handle users without GitHub authentication.
• Dependencies: This approach relies on external authentication sources.

Important Note: Our application allows for both local accounts and external authentication via GitHub. Consequently, the 'oauth_user_id' column may not always be filled in for every user.

I'd love to hear the community's opinions and any potential pitfalls or advantages I might not have considered. What's your take on this?

Simplified Table Schema:

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `oauth_provider` varchar(255) DEFAULT NULL, # For example, 'github'
  `oauth_user_id` varchar(255) DEFAULT NULL, # The Github User ID
  `email` varchar(255) NOT NULL,
  -- Other fields...
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_oauth_user_id_unique` (`oauth_user_id`)
);

[drbyte]

Currently, permissions are associated with the id field in the users table. However, I'm considering whether it would be safer to associate permissions with the oauth_user_id field, which stores the GitHub User ID.
Important Note: Our application allows for both local accounts and external authentication via GitHub. Consequently, the 'oauth_user_id' column may not always be filled in for every user.

I'd think that if you continue to base your application around user id, it'll give you maximum flexibility. That will allow you to support having users who don't have github connected (yet or expired), and it will let you grow in future.
Sure, you probably need to have some additional "ability" checks defined in your app (perhaps in AuthServiceProvider) to limit things if a user's oauth_user_id is blank. And probably a helper method in the User model like isGithub(): bool like { return filled($this->oauth_user_id) && filled($this->oauth_provider); } so you can easily assess suitable capabilities.

Increased security: GitHub User IDs are less likely to change than internal id values.

I'm not sure why you'd have internal id values changing. There's no need for that: once a record is created it never needs to change.