Tri 3: Tech Talk week 8: Many to Many Database Relationships

Build a Many-to-Many Database Relationship

Persistent data for a user is a big key to any Web Application. This tech talk is focused on building a relational table for User roles.

• Goals form this Tech Talk
  • Define and build your own database and tables for your project
  • Figure out your data relationships
  • Accomplish objectives #1-#4 by Tuesday Tech Talk

Man-to-Many relationships

Remember Trimester 2 we discussed database relationships in Tables using Person and Scrum Team POJOs. This example will show a Many-to-Many relationship using Person and Roles tables. This is 1st step to helping Teams build User Accounts and Roles to support Authentication (who you are) and Authorization (level of access).

• Many-to-Many

  • Each Person can have 0, 1, to Many Roles
  • Each Role can be associated with 0, 1, to Many Persons

• This lectures goes beyond CB, but is very consistent with enhancing Computer Science students understanding of Data Structures.

• There are many resources for Many-to-Many relationship, however this one was found by Charlie Zhu and will assist with the final 3 Tech Talks given in class: https://www.youtube.com/watch?v=VVn9OG9nfH0&t=6350s

Objective 1: Review Setup of the Person POJO and JPA file

• Key point on setting up a relationship between POJO (ultimately SQL tables) is the Annotation "@ManyToMany" and the reference to Role within the Collection in the Person POJO. The fetch = Eager will fetch and load the associated Role(s) when the Person is accessed.

 @ManyToMany(fetch = EAGER)
    private Collection<Role> roles = new ArrayList<>();

Objective 2: Introduce the Roles POJO and JPA file

• Role is setup as an independent Table within the Database. This enables a Person to have 0, 1 or many Role(s) references from the Role Collection in Person to the Role table. In this example we have ROLE_STUDENT, ROLE_TEACHER, ROLE_ADMIN as unique rows with the Role table. A Person can have 0 or all of the Roles with the Collection.

Objective 3: Adding Roles using Sqlite3 terminal tools

MacBook-Pro-3:nighthawk_csa3 johnmortensen$ sqlite3 sqlite.db 
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> .tables
hibernate_sequence  person_roles        scrum             
person              role              
sqlite> select * from role;
0|ROLE_STUDENT
1|ROLE_TEACHER
2|ROLE_ADMIN
sqlite> .schema role
CREATE TABLE role (id bigint not null, name varchar, primary key (id));
sqlite> INSERT INTO role (id, name) values (3, "ROLE_TESTER");
sqlite> select * from role;
0|ROLE_STUDENT
1|ROLE_TEACHER
2|ROLE_ADMIN
3|ROLE_TESTER
sqlite> 

Objective 4: CommandLineRunner used to add, correct, and show Data

• This ModelInit, CommandLineRunner is checked when application is loaded. This method is used to validate checks on data, by making sure data expectations are met, prior to user interaction with the system.
  • Role data is setup here as CRUD UI has not been constructed
  • With addition of Encryption, checks are made on Person Password to make sure records are encrypted
  • With addition of Roles, every Person is checked for STUDENT_ROLE
  • Teacher is check for additional roles.

    @Component // Scans Application for ModelInit Bean, this detects CommandLineRunner
public class ModelInit {
    // Inject repositories
    @Autowired RoleJpaRepository roleJpaRepository;
    @Autowired ModelRepository modelRepository;

    @Bean
    CommandLineRunner run() {  // The run() method will be executed after the application starts
        return args -> {
            // Fail safe data validations

            // make sure Role database is populated with defaults
            String[] roles = {"ROLE_STUDENT", "ROLE_TEACHER", "ROLE_ADMIN", "ROLE_TESTER"};
            for (String role : roles) {
                if (roleJpaRepository.findByName(role) == null)
                    roleJpaRepository.save(new Role(null, role));
            }

            // make sure every record added has a Default encrypted password and ROLE_STUDENT
            modelRepository.defaults("123querty", "ROLE_STUDENT");

            // make sure privileged roles exist for Teacher
            modelRepository.addRoleToPerson("jmort1021@gmail.com", "ROLE_TEACHER");
            modelRepository.addRoleToPerson("jmort1021@gmail.com", "ROLE_ADMIN");

            // review/validate/test by performing output to console
            System.out.println(modelRepository.listAll());
            System.out.println(modelRepository.listAllRoles());
        };
    }
}

• To see if the database is actually being populated by the according roles, passwords, and users a couple of output statements are added to the CommandLineRunner (listAll(), listAllRoles()). Observe Password Encryption or Roles in the IntelliJ run window as illustrated ...

Future Objectives: Building http security and user authentication

protected void configure(HttpSecurity http) throws Exception { //THis is going to be altered to use the JWT
        // security rules
        http
            .authorizeRequests()
                .antMatchers(POST, "/api/person/post/**").hasAnyAuthority("ROLE_STUDENT")
                .antMatchers(DELETE, "/api/person/delete/**").hasAnyAuthority("ROLE_ADMIN")
                .antMatchers("/database/personupdate/**").hasAnyAuthority("ROLE_STUDENT")
                .antMatchers("/database/persondelete/**").hasAnyAuthority("ROLE_ADMIN")
                .antMatchers( "/api/person/**").permitAll()
                .antMatchers( "/api/refresh/token/**").permitAll()
                .antMatchers("/", "/starters/**", "/frontend/**", "/mvc/**", "/database/person/**", "/database/personcreate", "/database/scrum/**", "/course/**").permitAll()
                .anyRequest().authenticated()
                .and()
            .formLogin()
                .loginPage("/login")
                .permitAll()
                .and()
            .logout()
                .logoutRequestMatcher(new AntPathRequestMatcher("/logout"))
                .logoutSuccessUrl("/database/person")
                .permitAll()
        ;
        // Cross-Site Request Forgery needs to be disabled to allow activation of JS Fetch URIs
        http.csrf().disable();
    }

• Here we see examples of role restrictions through http auth requests
• ROLE_STUDENT can be seen with post authority, while delete authority is reserved for ROLE_ADMIN

 /* UserDetailsService Overrides and maps Person & Roles POJO into Spring Security */
    @Override
    public org.springframework.security.core.userdetails.UserDetails loadUserByUsername(String email) throws UsernameNotFoundException {
        Person person = personJpaRepository.findByEmail(email); // setting variable user equal to the method finding the username in the database
        if(person==null){
            throw new UsernameNotFoundException("User not found in database");
        }
        Collection<SimpleGrantedAuthority> authorities = new ArrayList<>();
        person.getRoles().forEach(role -> { //loop through roles
            authorities.add(new SimpleGrantedAuthority(role.getName())); //create a SimpleGrantedAuthority by passed in role, adding it all to the authorities list, list of roles gets past in for spring security
        });
        return new org.springframework.security.core.userdetails.User(person.getEmail(), person.getPassword(), authorities);
    }

• role.getName() is used to iterate through all accounts with "role" and are given SimpleGrantedAuthority
• EX: Role "ADMIN" gets authentication while role "USER" does not