I have set up user, group and role tables to be used with JAAS.
I want the possibility to add groups to users and roles to users.
This means 3 link tables between user, group and role.
DatabaseServerLoginmodule:
The tables are:
SYS_USER
------------
ID VARCHAR(64)
PASSWORD VARCHAR(64)
NAME VARCHAR(50)
NOTES VARCHAR(255)
SYS_GROUP
--------------
ID VARCHAR64
NAME VARCHAR(50)
NOTES VARCHAR(255)
SYS_ROLE
------------
ID VARCHAR(64)
JAAS_GROUP VARCHAR(64)
NAME VARCHAR(50)
NOTES VARCHAR(255)
SYS_USER_GROUP_LINK
-----------------------------
SYS_USER_ID VARCHAR(64)
SYS_GROUP_ID VARCHAR(64)
SYS_USER_ROLE_LINK
---------------------------
SYS_USER_ID VARCHAR(64)
SYS_ROLE_ID VARCHAR(64)
SYS_GROUP_ROLE_LINK
-----------------------------
SYS_GROUP_ID VARCHAR(64)
SYS_ROLE_ID VARCHAR(64)
My first attempt to solve this was to use the DatabaseServerLoginmodule with the following queries:
principalsQuery="SELECT PASSWORD AS Password FROM SYS_USER WHERE ID=?"
rolesQuery="SELECT DISTINCT A.ID AS Roles, A.JAAS_GROUP AS RoleGroups FROM SYS_ROLE A, SYS_USER_GROUP_LINK B, SYS_GROUP_ROLE_LINK C, SYS_USER_ROLE_LINK D WHERE (A.ID = D.SYS_ROLE_ID AND D.SYS_USER_ID = ?) OR (B.SYS_GROUP_ID = C.SYS_GROUP_ID AND B.SYS_USER_ID = ? AND A.ID = C.SYS_ROLE_ID)"
This would probably work fine if it wasn't for the fact that I need to give the principal twice in the rolesQuery. Is there a better query to sort this, or do I have to write my own DatabaseServerLoginmodule? (my strong side is not SQL i'm afraid)
I am using Oracle.
If I have to write my own module it is just a very minor change of existing DatabaseServerLoginModule, could this change be done in an easy way by extending the module?
All suggestions and tips are welcome on how to get this very flexible user, group, role scenario to work.
Thanks in advance.
Goran
