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
3
  • 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, 2016 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, 2016 at 14:21
  • 1
    to address the data management concern, use an ID in the user roles table.
    – ztk
    May 16, 2016 at 14:24

1 Answer 1

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.

1
  • 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, 2016 at 14:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .