Implementing Table relationships where there are both pre-determined and custom user roles in db

Question

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?

Answer 1

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.