0

I am working on a database design to implement user roles in Oracle 11g database. The relationship is many-to-many but what is different in this setup is that a user can either have a specific user type (admin, user, etc) with a pre-determined set of access rights or user type is custom and will have a list of access rights specific to this user only. I've tried many approaches which I'll list briefly but none seem to have a complete and robust solution.

Another thing is I would like to minimize the data in user_responsibilities so if 1million users have usertype 'user' and there are 10 responsibilities for 'user', I don't want them to have 5million records, just the 5.

Approach 1:

users (username, usertype)
user_responsibilites (usertype, username, responsibility_description) // username is null if usertype is not 'custom'

Example: USERS:
         username       usertype
         --------       --------
         user1          admin
         user2          custom
         user3          admin

         USER_RESPONSIBILITIES:
         usertype       username       responsibility_description
         --------       --------       --------------------------
         admin          null           create_user
         admin          null           delete_user
         admin          null           update_user 
         custom         user2          create_user
         custom         user2          add_responsibility

But using this approach, I can't link the two tables because user_responsibilities doesn't have a primary or unique key.

I also looked into the classic Junction Table approach:

Approach 2:

users (username, usertype)
user_responsibilites (username, responsibility_description)
responsibilities (responsibility_description)

But this does not take into consideration if usertype is 'admin' and thus already has pre-defined responsibilities. I would have to use a record for each user/responsiblity_desc in user_responsibilites. (The 1million users issue).

Approach 3:

users (username, usertype)
user_responsibilites (usertype, responsibility_description)

This doesn't take into consideration the possibility of having users with 'custom' role.

So are any of these approaches close to how the design should be? Or am I going into this the wrong way?

RMK
  • 456
  • 2
  • 9
  • 19
  • Aren't you partially reinventing how Oracle has privileges granted via a role or directly to a user? I'm not sure if a user can have more than one role in your design. But I'd look at [how Oracle does it](http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm) and see if you can adapt that model. You might also want to think about it from the other end - how you'll authorise a particular access or action for a user. – Alex Poole Oct 22 '14 at 10:12
  • Not reinventing but more like figuring out how to translate a similar approach into an effective table design; the user doesn't have more than one role and the roles are actually for web interface access, not actual database roles. Thanks for the link, I think I've come across it but I will take a look again. – RMK Oct 23 '14 at 06:18

1 Answers1

0

The ERP program with which I work (Priority) has something similar: users can either inherit their permissions from a group, or they are a group by themselves. Extrapolating this, in the users table you would need a field with a name like 'permission group' which would be a foreign key to the 'permissions groups' table which in turn links to a 'permissions' table.

So in your case, it would seem that you have the following data/structure:

PERMISSION_GROUP
id         | name
1            Admin
2            user2  

USERS
username   | pergroup
user1        1
user2        2
user3        1

PERMISSIONS
pergroup      | action
1            create_user
1            delete_user
1            read access to 'orders' table
1            write access to 'orders' table
2            read access to 'orders' table

etc.

No'am Newman
  • 6,395
  • 5
  • 38
  • 50
  • This actually might be exactly what I need. It was the username as usertype idea that wasn't being completed in my mind. One thing though, table Users will have FK to Permission_Group but the link between Permission_Group and Permissions is actually Permissions having an FK to Permission_Group right? Because otherwise I'd end up with the same problem as Approach1 where Permissions doesn't have a Unique key so I can't link another table's FK to it. – RMK Oct 23 '14 at 06:22
  • @RML: You're right. I think that I described things backwards: the primary key in 'permissions' is pergroup + action. Pergroup is a foreign key to the 'permission_group' table, both in 'permissions' and in 'users'. – No'am Newman Oct 23 '14 at 10:05
  • Okay thanks for that. Unless other future issues come up, I will be implementing this approach. Thanks for the reply! – RMK Oct 23 '14 at 11:04