Storage model for various user setting and attributes in database?

Question

I'm currently trying to upgrade a user management system for one web application. This web application is used to provide remote access to various networking equipment for educational purposes. All equipment is assigned to various pods, which users can book for periods of time.

The current system is very simple - just 2 user types: administrators and students. All their security and other attributes are mostly hardcoded.

I want to change this to something like the following model: user <--> (1..n)profile <---> (1..n) attributes.

I.e. user can be assigned several profiles and each profile can have multiple attributes. At runtime all profiles and attributes are merged into single active profile.

Some examples of attributes i'm planning to implement:

EXPIRATION_DATE - single value, value type: date, specifies when user account will expire;

ACCESS_POD - single value, value type: ref to object of Pod class, specifies which pod the user is allowed to book, user profile can have multiple such attributes with different values;

TIME_QUOTA - single value, value type: integer, specifies maximum length of time for which student can reserve equipment.

CREDIT_CHARGING - multi valued, specifies how much credits will be assigned to user over period of time. (Reservation of devices will cost credits, which will regenerate over time);

Security permissions and user preferences can end up as profile or user attributes too:

i.e CAN_CREATE_USERS, CAN_POST_NEWS, CAN_EDIT_DEVICES, FONT_SIZE, etc..

This way i could have, for example: students of course A will have profiles STUDENT (with basic attributes) and PROFILE A (wich grants acces to pod A).
Students of course B will have profiles: STUDENT, PROFILE B(wich grants to pod B and have increased time quotas).

I'm using Spring and Hibernate frameworks for this application and MySQL for database. For this web application i would like to stay within boundaries of these tools.

The problem is, that i can't figure out how to best represent all these attributes in database. I also want to create some kind of unified way of retrieveing these attributes and their values.

Here is the model i've come up with.
Base classes.

public abstract class Attribute{
    private Long id;
    Attribute() {}

    abstract public String getName();

    public Long getId() {return id; }
    void setId(Long id) {this.id = id;}
}

public abstract class SimpleAttribute extends Attribute{
    public abstract Serializable getValue();
    abstract void setValue(Serializable s);

    @Override
    public boolean equals(Object obj) { ... }

    @Override
    public int hashCode() { ... }
}

Simple attributes can have only one value of any type (including object and enum).

Here are more specific attributes:

public abstract class IntAttribute extends SimpleAttribute {
    private Integer value;

    public Integer getValue() { return value; }

    void setValue(Integer value) {  this.value = value;}    
    void setValue(Serializable s) { setValue((Integer)s); }
}

public class MaxOrdersAttribute extends IntAttribute {
    public String getName() {
        return "Maximum outstanding orders";
    }
}

public final class CreditRateAttribute extends IntAttribute {
    public String getName() {
        return "Credit Regeneration Rate";
    }
}

All attributes stored stored using Hibenate variant "table per class hierarchy". Mapping:

<class name="ru.mirea.rea.model.abac.Attribute" table="ATTRIBUTES" abstract="true" >
    <id name="id" column="id">
        <generator class="increment" />
    </id>
    <discriminator column="attributeType" type="string"/>
    <subclass name="ru.mirea.rea.model.abac.SimpleAttribute" abstract="true">
        <subclass name="ru.mirea.rea.model.abac.IntAttribute" abstract="true" >
            <property name="value" column="intVal" type="integer"/>
            <subclass name="ru.mirea.rea.model.abac.CreditRateAttribute" discriminator-value="CreditRate" />
            <subclass name="ru.mirea.rea.model.abac.MaxOrdersAttribute" discriminator-value="MaxOrders" />
        </subclass>
        <subclass name="ru.mirea.rea.model.abac.DateAttribute" abstract="true" >
            <property name="value" column="dateVal" type="timestamp"/>
            <subclass name="ru.mirea.rea.model.abac.ExpirationDateAttribute" discriminator-value="ExpirationDate" />
        </subclass>
        <subclass name="ru.mirea.rea.model.abac.PodAttribute" abstract="true" >
            <many-to-one name="value" column="podVal" class="ru.mirea.rea.model.pods.Pod"/>
            <subclass name="ru.mirea.rea.model.abac.PodAccessAttribute" discriminator-value="PodAccess" lazy="false"/>
        </subclass>
        <subclass name="ru.mirea.rea.model.abac.SecurityPermissionAttribute" discriminator-value="SecurityPermission" lazy="false">
            <property name="value" column="spVal" type="ru.mirea.rea.db.hibernate.customTypes.SecurityPermissionType"/>
        </subclass>
    </subclass>
</class>

SecurityPermissionAttribute uses enumeration of various permissions as it's value.
Several types of attributes imlement GrantedAuthority interface and can be used with Spring Security for authentication and authorization.

Attributes can be created like this:

public final class AttributeManager {
    public <T extends SimpleAttribute> T createSimpleAttribute(Class<T> c, Serializable value) {
        Session session = HibernateUtil.getCurrentSession();
        T att = null;
            ...
            att =  c.newInstance();
            att.setValue(value);
            session.save(att); 
            session.flush();
            ...
        return att;
   }
   public <T  extends SimpleAttribute> List<T> findSimpleAttributes(Class<T> c) {
       List<T> result = new ArrayList<T>();
       Session session = HibernateUtil.getCurrentSession();
       List<T> temp =  session.createCriteria(c).list();
       result.addAll(temp);
       return result;
   }
}

And retrieved through User Profiles to which they are assigned.

I do not expect that there would be very large amount of rows in the ATTRIBUTES table, but are there any serious drawbacks of such design?

Answer 1

I know this is slightly besides your question, but authentication and authorization patterns are pretty classic, there are libraries to abstract it away in a standardized fashion. Should

For exemple, in your case, have you considered using Spring Security ? Even if the whole feature set is overkill for your current use cases, you can still plug it in and use the subset of features you want. The heavy work of the database design will be done for you already, you can then extend it for your custom attributes.

Edit

From your examples it feels like your "profile" model is the same as the role model in spring security.

Here is how I understand your needs (user) ---- (role) | | (user_attr) (role_attr)

In user_attr you would have the user expiration date as a timestamp, actually in the examples you mentionned it looks like this is the only user specific attribute, I would consider adding it directly on the user table.

The attributes can ACCESS_POD, CAN_CREATE_USERS, CAN_POST_NEWS, CAN_EDIT_DEVICES are best described with ACLs : Role Class_B has access to POD B Role UserCreator or Administrator can create users Role Editor or Author or Administrator can post news Role Administrator can edit devices

The credit charging sounds more like a role attribute The time quota would be an attribute for a device or for a role etc.

The idea afterwards is to use a custom authentication-provider which checks for the combination of attributes you want.