Java web application authentication - account design

Question

I am working on a web project, backend is Java & Mysql, the client include web(html5) and app(IOS/Android), I have some doubt in design the account of the system.

There are 3 different types of account:

• Shop, shop account will have its own website,
• Customer, customer access shop/commodity via app(IOS/Android),
• Admin, manage everything of the system.

My basic idea of authentication:

There will be account / role / permission table for sure, because both admin & customer will have quite complex user permission issue, customer also have different permission due to their history behavior.

I have kind decided to use Apache Shiro, due to its simplicity & distributed session.

My question is:

(1) Should I create a single account table or 3 individual account tables.

(2) Any advise on design of 3 tables: account / role / permission ?

Answer 1

I suggest you to consider delegating all your user-management needs to Stormpath. With Stormpaht, you do not need to worry about such low-level concerns, all your data is securely managed and stored. Stormpath provides:

• User management API with different SDKs: node.js, express, java, rest, python, flask.
• Off the shelf Hosted Login: login, registration, and password reset.
• Off the shelf ID Site to power Single Sign-On across your applications
• API keys for your users, secured with HTTP Basic Auth or OAuth2
• Social Login: Facebook, Google, LinkedIn, Github
• Integration with Shiro and Spring Security
• Integration with Active Directory and LDAP

With Stormpath you will only need to create Groups which will represent your roles. Inside your groups and accounts, you can also create finer-grained concepts like permissions using our flexible Custom Data concept.

As mentioned above, we also support Shiro integration, where you can model all your security needs with Shiro while using Stormpath as the authentication and authorization data provider. Please take a look at our Stormpath Shiro plugin and at our Sample Shiro Web App.

Disclaimer, I am an active Stormpath contributor.

Answer 2

If in your first question you're asking how to design a database schema for three very distinct entities (admin user, customer user and shop owner), I suggest you don't combine them into a single table, because they are different concepts and will likely have different features.
You kind of answered your own question, since "ease of programming" rarely trumps business rules/logic.

Your decision to use an existing security framework, or to roll your own, should be independent of the data model for your core business entities.

If you don't want to use a managed solution like Stormpath, and haven't settled on Shiro yet, check out OACC, an open-source permission-based security framework for Java with support for hierarchical security domains, super users, permission inheritance and impersonation.

It might be a good fit for your project because:

• you won't need to clutter your database design with authorization-related aspects
• OACC was designed for multi-tenancy application architectures (like your project's "shops")
• it allows for impersonation, which is a powerful feature if you need to support customer service representatives without giving them "admin" privileges

[Disclaimer: I am a maintainer and co-developer of OACC]

Answer 3

To be short: you don't need role / permission tables :)

I would decide first do you really need RBAC security model? Your application looks like a perfect use case for hexagonal architecture with 3 separate isolated front-end parts: Consumer, Shop, Admin. Then I would advise to build separate authentication/authorization mechanism for each of these front-ends. In this case you are flexible to choose the best tool for the purpose (OAuth2, OpenID, LDAP whatever) and follow least common mechanism security principle. Your application doesn't look like the one which needs authorization on method level, thus you don't need RBAC.

Answer 4

I would design each Java object depending on its individual needs. Get clear about what you have to create. Are there overlappings in the account types? Shall a Shop account be able to buy something like (that is. extend) a Customer, or shall an Admin implement both a Shop and a Customer? Shall the address of a Customer, living in the same street as a Shop is located, change if the Shop reports the street got renamed? Does the phone number’s area code depend on the city?

If your Java objects do their job properly, think about the O/R mapping in a second step. Perhaps it will be even very different from what you may think now (just think of carrier codes in telephone numbers that require inline replacement, Packstation boths, Shops with multiple persons of contact, different address layouts in different countries …).

In general, make sure your address fields properly support UTF-8 for diacritics, greek, cyrillic or arabic addresses.

Answer 5

(1) Should I create a single account table or 3 individual account tables.

Yes, I think you should design a single one, as an account is going to have similar data for all 3 types.

(2) Any advise on design of 3 tables: account / role / permission ?

account: PK account_id int, FK role_id int

role: PK role_id int, account_permission enum(admin [0], customer [1])

You do not need a permission table, you may handle your permission levels in your application code, using composite design pattern, where you can have multiple hierarchical levels of admin or customer permissions. Reason for this is it's better to declare your business logic in your model code rather than database design, database is there to persist data with as optimised and normalised state as possible. I suppose you can then use dependency injection to your composite permission hierarchy depending on customer behaviour, which needs to be held in the database under a table, ie named customer_behaviour, with certain columns "ticked" as they behave certain ways.

Hope this helps.