3 Replies Latest reply on Oct 30, 2007 11:10 AM by Peter Johnson

    USERS and ROLES - why in seperate tables?

    Kosta Kontos Newbie

      Hi

      With users.properties and roles.properties:

      It makes sense to have username=password in the former, and username=Role1,Role2,...,RoleN in the latter, because Java's property lookup mechanism works like that.

      ie: we cannot have username=password=Role1,Role2,...,RoleN

      But now with regards to DatabaseServerLoginModule - why do we need two separate tables, one for USERS and one for ROLES? That's what the wiki / docs recommend and I don't understand why.

      I'm repeating usernames in both tables and there is no clear reason for the tables to be separate. I could just as easily have a single table with fields for USERNAME, PASSWORD and ROLES all in one table.

      Am I overlooking something? Thanks.

        • 1. Re: USERS and ROLES - why in seperate tables?
          Peter Johnson Master

          You could put them all in one table, but that breaks some relational database rule (first-normal form?). Usually, if a user has multiple roles, in the Roles table you have one row per role, with each row having user name and role columns. Thus 'select role from roles where user=xxx' actually returns multiple records.

          • 2. Re: USERS and ROLES - why in seperate tables?
            Kosta Kontos Newbie

            Thanks for the reply.

            Your post makes total sense: sticking to First-Normal form is the ideal, and thus I should have a row in my ROLES table for each username->Role mapping.

            What is weird though is that in my current application-policy (which uses DatabaseServerLoginModule) my module-option for "rolesQuery" has the following:

            <module-option name = "rolesQuery">SELECT role_name, role_group FROM __user_roles WHERE user_id=?</module-option>


            ... and in my __user_roles table I have just one row for each username, and under the role_name column I have a comma-separated list of Roles.

            I am using this because that's what the jboss docs advised. I guess what is confusing to me is the fact that this still works.

            So bearing in mind that the comma-separate roles approach works just fine, are there any real benefits to sticking to First-Normal form in this case?

            • 3. Re: USERS and ROLES - why in seperate tables?
              Peter Johnson Master

              The only benefit I can think of is ease of maintenance.