Ad

Critique My Auth System DB Schema?

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.

Ad

Answer

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>

Ad
source: stackoverflow.com
Ad