What's the best way to design a data and php model for a multi-account system?

Question

I'm doing some high-level sketching of a system that will require users to register, when they do they get an account and can invite others to that account.

To be clear, an account would be similar to a private workspace, and the users will belong to that account and cannot exist outside of that account. The users will be identified by a unique username (likely their email) and this will give them access to the account on a one (account) to many (emails / users) basis.

Each record generated in that account is visible only to those users who belong to that account.

Is there a good pattern for modeling this type of system?

Being extremely new to programming, I'm imagining a field in each table that references the account #, but this makes virtually every entity a one to many.

Is there a better way?

Answer 1

n users - 1 account (1 user can belong to just one account, n users can belong to 1 account, where n is 0..infinity):

 accounts (
   account_id   primary key
 )

 users (
   user_id      primary key,
   account_id   not null references accounts(account_id)
 )

n users - m accounts; which is probably what you need (a user can belong to m accounts, an account might have n users)

 accounts (
   account_id   primary key
 )

 users (
   user_id      primary key
 )

 user_accounts (
   user_id      not null references accounts(user_id)
   account_id   not null references accounts(account_id)
   unique(user_id, account_id)
 )

in order to ensure that no information is leaked, several approaches exist. Some databases and frameworks might allow you to specify this requirement "declaratively", which will give you maximum security (or security as good as the implementation- which in many cases will be better than you can achieve- as the people developing the database/framework will probably have more resources).

Unfortunately, complete and pure RDBMS solutions are not implemented in widely available systems, and even if they existed, I'm guessing they would be tied to the RDBMS role system; that is, you could grant select privileges over certain rows to certain database roles, but that might certainly not be applicable to your problem (i.e. you'd have to create roles per user in your app and execute queries as the appropriate user; as far as I know this is not practical in any platform I know of).

If you are using an ORM to access your database, you might have better luck. I know Hibernate implements something where you can check conditions before executing any operation ( http://docs.jboss.org/hibernate/core/3.3/reference/en/html/events.html#objectstate-decl-security ) or filter data, and I believe Yii's ORM allows for something similar.

If those are not available, or not suitable for your particular case, you will have to work further up your stack. A traditional solution is to mandate all data access to go through a layer which enforces security- actually this is one of the best reasons to implement a DAO/Service layer separation; DAOs contain "raw" data access and the service layer checks privileges. If you avoid accessing the DAO outside the Services, you have all your security logic in a predictable place (i.e. the same class/classes) and thus it is easier to check.

Another common solution is to access your database through stored procedures which enforce security. This often has the drawback that stored procedures are considered less nice to write (i.e. PL/SQL is unfriendlier than Java), although that's debatable. On the other hand, if you have several applications accessing the database, and esp. if they use different languages, you won't have to duplicate all the access check stuff everywhere.

Also, this might be an appropriate problem for aspect oriented programming.

Finally, please note that all of the above only give security "outwards" (i.e. ensure that the application can not act insecurely due to user action). If attackers can tamper "inside" the application, it's game over- this means you should adhere to standard safe coding (i.e. avoid buffer overruns, XSS, etc.).