I'm late for the party it seems.
Anyway, here's my take, use a simplified version of how Oracle have set up their own permission system.
User and groups belongs in the same self referencing table with just a field setting that defines if it's a group or a user.
You need to add logics that only allows a user or a group to have a group as a parent. But a group can have another group as it's parent.
You also need to add a check that you don't get a circular reference.
Same is valid for Permissions and Roles.
Projects can of course be a part of a larger project. Also here check for circular references.
You also need to add a possibility to Allow or Deny a Permission.
Here's a suggestion
Relational_1_-_SubView_1.png (25.5 KB)
And the DDL:
CREATE TABLE PERMISSIONS
(
PERMISSIONID INTEGER NOT NULL ,
PARENTID INTEGER ,
ISROLE NUMBER (1) NOT NULL ,
NAME NVARCHAR2 (64)
) ;
ALTER TABLE PERMISSIONS ADD CHECK ( ISROLE IN (0, 1)) ;
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_PK PRIMARY KEY ( PERMISSIONID ) ;
CREATE TABLE PERMISSIONTYPE
(
PERMISSIONTYPEID NUMBER (1) NOT NULL ,
NAME VARCHAR2 (5 CHAR) NOT NULL
) ;
ALTER TABLE PERMISSIONTYPE ADD CONSTRAINT PERMISSIONTYPE_PK PRIMARY KEY ( PERMISSIONTYPEID ) ;
CREATE TABLE PROJECTS
(
PROJECTID INTEGER NOT NULL ,
PARENTID INTEGER ,
NAME NVARCHAR2 (64) NOT NULL
) ;
ALTER TABLE PROJECTS ADD CONSTRAINT PROJECTS_PK PRIMARY KEY ( PROJECTID ) ;
CREATE TABLE USERPROJECTPERMISSIONS
(
USERID INTEGER CONSTRAINT NNC_UPP_USERS_USERID NOT NULL ,
PERMISSIONID INTEGER CONSTRAINT NNC_UPP_PERMISSIONS_ID NOT NULL ,
PROJECTID INTEGER CONSTRAINT NNC_UPP_PROJECTS_PROJECTID NOT NULL ,
PERMISSIONTYPEID NUMBER (1) NOT NULL
) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT USERPROJECTPERMISSIONS_PK PRIMARY KEY ( USERID, PERMISSIONID, PROJECTID ) ;
CREATE TABLE USERS
(
USERID INTEGER CONSTRAINT NNC_USERS_USERID NOT NULL ,
PARENTID INTEGER ,
ISGROUP NUMBER (1) CONSTRAINT NNC_USERS_ISUSERGROUP NOT NULL ,
NAME NVARCHAR2 (64)
) ;
ALTER TABLE USERS ADD CHECK ( ISGROUP IN (0, 1)) ;
ALTER TABLE USERS ADD CONSTRAINT USERS_PK PRIMARY KEY ( USERID ) ;
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_PERMISSIONS_FK FOREIGN KEY ( PARENTID ) REFERENCES PERMISSIONS ( PERMISSIONID ) ;
ALTER TABLE PROJECTS ADD CONSTRAINT PROJECTS_PROJECTS_FK FOREIGN KEY ( PARENTID ) REFERENCES PROJECTS ( PROJECTID ) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT UPP_PERMISSIONS_FK FOREIGN KEY ( PERMISSIONID ) REFERENCES PERMISSIONS ( PERMISSIONID ) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT UPP_PERMISSIONTYPE_FK FOREIGN KEY ( PERMISSIONTYPEID ) REFERENCES PERMISSIONTYPE ( PERMISSIONTYPEID ) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT UPP_PROJECTS_FK FOREIGN KEY ( PROJECTID ) REFERENCES PROJECTS ( PROJECTID ) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT UPP_USERS_FK FOREIGN KEY ( USERID ) REFERENCES USERS ( USERID ) ;
ALTER TABLE USERS ADD CONSTRAINT USERS_USERS_FK FOREIGN KEY ( PARENTID ) REFERENCES USERS ( USERID ) ;