Charper 8: Is the tbl_project_user_role table really needed?

[thefsb (Fsb)]

This has been discussed before but not in a way that answers my question.

As jefftulsa put it:

1. This biz rule allows us to add an extra dimension to using RBAC to check authorization access. We need to assign users to roles, and that is handled by the Yii RBAC implementation (AuthAssignment table in this case), but the thing is, the role is only applicable within the context of a specific project. The user can be an "owner" of Project A, just a "member" of Project B, and yet again only a "reader" of Project C. The AuthAssignment table just makes the association between the role and the user, i.e. it will just say User 1 is an "owner". The business rule makes the extra check to ensure that role does, indeed, apply to the context of the project they are currently within. This allows us to ask: "Okay, great, User 1 is an owner, BUT is user 1 really an owner of Project A?"

Yes. With you 100%.

But why does this necessitate the [font="Courier New"]tbl_project_user_role[/font] table? It seems to me that our bizrules can query the original 4 TrakStar tables to answer the questions: “Is user [font="Courier New"]U [/font] an owner of project [font="Courier New"]P[/font] ?” “Was issue [font="Courier New"]I [/font] requested by user [font="Courier New"]U[/font] ?” and the rest.

In other words, can’t we write a perfectly good [font=“Courier New”]function isUserInRole($role){}[/font] without querying [font=“Courier New”]tbl_project_user_role[/font]?

What’s data is missing from [font=“Courier New”]tbl_project[/font], [font=“Courier New”]tbl_issue[/font] and the three CDbAuthManager tables that prevents us from doing the same job that Chapter 8 does?

[este (Esteban Almiron)]

There are 2 tables that determine the relations between Users, Roles and Projects:

1. tbl_project_user_role (project_id, user_id, role)

2. tbl_project_user_assignment (project_id, user_id)

I also see a repetition there: users and projects are associated in both tables.

We surely need an association project + user + role (defined by tbl_project_user_role), but it could also be defined through the AuthAssign table, i guess…

Actually, it seems that we can then get rid of one associative table: tbl_project_user_assignment or tbl_user_role.

[brian.s (Bseymouremail)]

Actually, it seems that we can then get rid of one associative table: tbl_project_user_assignment or tbl_user_role.

I am evaluating this myself right now and do not understand why two tables are needed (or used by the author). It seems to me that one table should suffice:

tbl_project_user

• project_id

• user_id

• role (role_id)

• create_time

• create_user_id

• update_time

• update_user_id

I suppose the question to be addressed is whether a project user can have more than one role? For my needs, each project user should have exactly one role always, especially since the RBAC can be designed so that higher roles (such as Owner) can inherit "abilities" from lower roles (such as Member or Reader).

[brian.s (Bseymouremail)]

I suppose the question to be addressed is whether a project user can have more than one role?

Thinking on this a bit more, the proposed table structure seems to work even if a project user could have more than one role, as the table’s composite primary key could be modified from:

PRIMARY KEY (`project_id`,`user_id`)

to:

PRIMARY KEY (`project_id`,`user_id`,`role`)