2

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?

Improve this question

1 Answer 1

Reset to default
3

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.).

Improve this answer
6
  • Hi Alex, thanks for the reply. I actually don't need the n:n option with respects to users and accounts. Users will login with their email, so this will be the key to a specific account. The user cannot have multiple accounts with the same email.
    – user43855
    Dec 27, 2011 at 21:23
  • The other issue which I was most concerned with, and which was not answered, is with help in understanding how the records in the account will be private from other accounts. I suppose I can have an account_id (as foreign key) in all tables, and only allow records to be displayed to users who have gained access to the account. Can you describe / discuss patterns for this type of architecture?
    – user43855
    Dec 27, 2011 at 21:27
  • ah, sorry, I thought your main concern was with the schema, I'll update my answer
    – alex
    Dec 27, 2011 at 21:45
  • Hi Alex, thanks for all the great info. I'm just starting to program, so some of this is beyond me at the moment. I'm interested in the DAO service you mentioned and will research that. I am wondering though if you can provide a bit more beginner level thinking about the DB structure in a system like this (if any should be considered). As I've written, my thoughts were to add a related field (foreign key) to each table that maps over to the user's account_id. This means that each SQL query I write will need to check this first. Is this proper thinking regarding how to approach this?
    – user43855
    Dec 27, 2011 at 22:36
  • 1
    DAO/Services are often associated with entrerprise-ish platforms (i.e. Java EE). They are also often extraneous. If you are learning, you might want to consider two approaches: 1) try to do stuff your way without thinking/researching too much about doing things "right", but realize that you are probably to get it wrong [and once you have been "wrong", learn the "right" way 2) research a lot about the right way. I don't think you can truly learn to program well if you don't make mistakes and suffer them though...
    – alex
    Dec 27, 2011 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.