design security, does spring security can be used?

[collinm]

hi

i try to design the security of an application. It's a project management software.

An user can be assigner on many project.
An user can have many role.
an role can have many permission.

An user can have different role in function of a project.
An role can have different permission in function of a project.

An schema i thought

    create table project (  
        id       int            GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not null,  
        name varchar(50) not null,  
        primary key(id)  
    ) ;  
      
    create table account (  
        id       int            GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not null,  
        username varchar(50) not null,  
      primary key(id)  
    ) ;  
      
    create table role (  
        id       int            GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not null,  
        name varchar(50) not null,  
        primary key(id)  
    ) ;  
      
    create table permission (  
        id       int            GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not null,  
        name varchar(50) not null,  
        primary key(id)  
    ) ;  
      
    create table permissionscheme (  
        id       int            GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not null,  
        name varchar(50) not null,  
        primary key(id)  
    ) ;  
      
    create table schemepermissions (  
        id       int            GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not null,  
        permissionscheme_id int  not null,  
        permission_id int  not null,  
        role_id int  not null,  
        foreign key (permissionscheme_id) references permissionscheme (id),  
        foreign key (permission_id) references permission (id),  
        foreign key (role_id) references role (id),  
        primary key(id)  
    ) ;  
      
    create table account_role_project (  
        id       int            GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not null,  
        project_id int  not null,  
        account_id int  not null,  
        role_id int  not null,  
        foreign key (project_id) references project (id),  
        foreign key (account_id) references account (id),  
        foreign key (role_id) references role (id),  
        primary key(id)  
    ) ;

idea comment, tip...?

does spring security can be used for that?

thanks

[Rob Winch]

Take a look at the contacts sample application