Critique my auth system DB schema?

Question

I'm designing a RESTful Web app that will provide an authentication system for several other apps. The other apps will query this app through HTTP and get back XML describing authenticated users.

The authentication app needs to keep track of which users are allowed to do what on which applications.

I'm working out the DB schema. Below is my initial design. (Assume each table has an id column.)

applications  # The various client apps that will query this auth system.
------------
name

users         # Table simplified for discussion
-----
username
password
email

roles
-----
name
application_id

roles_users
-----------
role_id
user_id

The idea is say someone tried to perform an administrative function in the the "Equipment Inventory" app. So "Equipment Inventory" would say to the auth system "get the user with username xxx and password yyy." Then it would look at the returned (via ActiveResource) User object and check whether its roles Array contains a Role with a name of "ADMIN" that itself belongs to an Application object with a name of "Equipment Inventory".

Or perhaps it would be better to eliminate the applications table and have many more roles, e.g., "equipment_inventory_admin", "equipment_inventory_readonly", "job_tracker_admin", etc.

What's more important, normalizing the Application entity or simplifying the table structure? Perhaps after all that typing I've just answered my own question, but comments or suggestions would be most welcome.

Answer 1

Personally I tend to err on the side of normalization. At least in my experience some kind of additional modules get added down the line. Much easier to add an additional line in application and brand new tables as necessary then editing the existing schema and having to update all relevant Data Access code.

Edit: On a second look you could merge the roles table and the roles_users table. It could be one place that defines the roles and how the users can access them for each application.

Answer 2

The schema looks sane, You would send

<login><username>abc</username><password>xyz</password><app>51</app></login>

and you get back

<auth> <user> <username>abc</a> <lastlogin>123456464</lastlogin> </user> <app> <name>Equipment Inventory</name> <version>3.1.5e</version> </app> <roles> <role>admin</role> <role>manager</role> <role>dataentry</role> </roles> </auth>

or

<auth><error type="1"></auth>

Answer 3

Definitely separate authentication from authorization. (looks like you're doing that; the "user" table falls into authentication, the rest + user.id fall into authorization)

Passwords: you're not storing them in the clear, are you? Storing MD5 hashes (+ salt to prevent attacks) would be more secure.

Would Kerberos be a possibility? (perhaps it could be adapted to work over HTTP as a transport layer)