1

I want to create a user roles SQL table which stores information about users permissions and roles (Such as Admin, Worker, User)

I need 2 columns in the table one which identifies which user it is and the other is the level of permission the user has.

I was wondering, Is there any security concerns identifying users with their Username rather than their ID #. It would be a lot easier to manage user permissions with usernames and I'm willing to lose a bit of security if its not that bad.

Notes:

  1. My user table and user roles table are in the same database
  2. My user table has a ID column and a Username column
  • There is a data management concern. If you have the passwords in another table, you will want a user table linking each user to an ID, which will then be used in the password and roles tables. This avoids data duplication, keeping the DB easy to manage. – ztk May 16 '16 at 14:19
  • My users table HAS a id column AND username column I'm wondering if the user roles table should have either a Username column or ID column – mateos May 16 '16 at 14:21
  • 1
    to address the data management concern, use an ID in the user roles table. – ztk May 16 '16 at 14:24
3

This isn't a security issue, it's a data management issue.

Usernames can, and do change. That's why you use a meaningless identifier and foreign key relationship to ensure database integrity when you have a role table.

Any UI that adds or manages roles should hide the complexity of the ID vs the username inside it.

  • Okay thank you. I can't upvote your answer since I'm a new user but I will if i get more points in the future haha! – mateos May 16 '16 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.