This series of posts will expand the authorization model for our application. At a high level, we are going to add unique permissions to all of our controller class methods and service class methods. This modification will be modeled after the authorization structure (chapter 7) in
Spring In Practice (2013). The revised security table structure is shown below, and supersedes the structure in the post
Another Data Model Update.
 |
| Trading System V2 Security - ERM Diagram Update |
1. The first step in this update is to create the table structure. The foreign key and unique index constraints were discussed in the post where we first added roles to the application. These constraints are required for our link tables to work correctly.
DROP TABLE IF EXISTS USER_ROLES;
DROP TABLE IF EXISTS ROLE_PERMISSIONS;
DROP TABLE IF EXISTS USERS;
DROP TABLE IF EXISTS ROLES;
DROP TABLE IF EXISTS PERMISSIONS;
CREATE TABLE USERS (
ID INT(6) NOT NULL AUTO_INCREMENT,
USERNAME VARCHAR(50) NOT NULL,
PASSWORD VARCHAR(50) NOT NULL,
ENABLED TINYINT(1) NOT NULL,
PRIMARY KEY (ID),
UNIQUE INDEX USERNAME (USERNAME)
) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1;
CREATE TABLE ROLES (
ID INT(6) NOT NULL AUTO_INCREMENT,
ROLENAME VARCHAR(50) NOT NULL,
PRIMARY KEY (ID),
UNIQUE INDEX ROLENAME (ROLENAME)
) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1;
CREATE TABLE USER_ROLES (
USER_ID int(6) NOT NULL,
ROLE_ID int(6) NOT NULL,
KEY USER (USER_ID),
KEY ROLE (ROLE_ID),
CONSTRAINT USER FOREIGN KEY (USER_ID) REFERENCES USERS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT ROLE FOREIGN KEY (ROLE_ID) REFERENCES ROLES (ID) ON DELETE CASCADE ON UPDATE CASCADE
) COLLATE='utf8_general_ci' ENGINE=InnoDB;
CREATE TABLE PERMISSIONS (
ID INT(6) NOT NULL AUTO_INCREMENT,
PERMISSIONNAME VARCHAR(50) NOT NULL,
PRIMARY KEY(ID),
UNIQUE INDEX PERMISSIONNAME (PERMISSIONNAME)
) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1;
CREATE TABLE ROLE_PERMISSIONS (
ROLE_ID INT(6) NOT NULL,
PERMISSION_ID INT(6) NOT NULL,
FOREIGN KEY (ROLE_ID) REFERENCES ROLES (ID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (PERMISSION_ID) REFERENCES PERMISSIONS (ID) ON DELETE CASCADE ON UPDATE CASCADE
) COLLATE='utf8_general_ci' ENGINE=InnoDB;
2. To ease the creation of data, we will create stored procedures to load the data.
delimiter //
DROP PROCEDURE IF EXISTS createPermission;
DROP PROCEDURE IF EXISTS createRole;
DROP PROCEDURE IF EXISTS createRoleHasPermission;
DROP PROCEDURE IF EXISTS createUser;
DROP PROCEDURE IF EXISTS createUserHasRole;
create procedure createPermission($name varchar(50))
begin
insert into permissions (permissionname) values ($name);
end //
create procedure createRole($name varchar(50), out $id int)
begin
insert into roles (rolename) values ($name);
set $id := last_insert_id();
end //
create procedure createRoleHasPermission($role_id smallint, $perm_name varchar(50))
begin
declare _perm_id int;
select id from permissions where permissionname = $perm_name into _perm_id;
insert into role_permissions (role_id, permission_id) values ($role_id, _perm_id);
end //
create procedure createUserEntry($name varchar(50), out $id int)
begin
insert into users (username, password, enabled) values ($name, 'password', 1);
set $id := last_insert_id();
end //
create procedure createUserHasRole($user_id int, $role_id smallint)
begin
insert into user_roles (user_id, role_id) values ($user_id, $role_id);
end //
delimiter ;
3. Finally, we will load our sample data. We've created permissions that will be used in our strategy controller. I will use the convention of CTRL as a prefix for permissions associated with Spring Controllers, and the SVC prefix for permissions associated with Spring Services. The class name comes next, followed by the method name, followed by the HTTP method for controllers. As we add methods to our application, we will need to add method permissions to the permissions table, and then map these permissions to roles.
-- Create permissions
call createPermission('CTRL_STRATEGY_LIST_GET');
call createPermission('CTRL_STRATEGY_ADD_POST');
call createPermission('CTRL_STRATEGY_EDIT_GET');
call createPermission('CTRL_STRATEGY_EDIT_POST');
call createPermission('CTRL_STRATEGY_DELETE_GET');
-- Create roles
call createRole('ROLE_ADMIN', @role_admin);
call createRoleHasPermission(@role_admin, 'CTRL_STRATEGY_LIST_GET');
call createRoleHasPermission(@role_admin, 'CTRL_STRATEGY_ADD_POST');
call createRoleHasPermission(@role_admin, 'CTRL_STRATEGY_EDIT_GET');
call createRoleHasPermission(@role_admin, 'CTRL_STRATEGY_EDIT_POST');
call createRoleHasPermission(@role_admin, 'CTRL_STRATEGY_DELETE_GET');
call createRole('ROLE_TRADER', @role_trader);
call createRole('ROLE_USER', @role_user);
-- Create accounts
call createUserEntry('admin', @admin);
call createUserHasRole(@admin, @role_admin);
call createUserEntry('trader', @trader);
call createUserHasRole(@trader, @role_trader);
call createUserEntry('user', @user);
call createUserHasRole(@user, @role_user);
4. The entity to store our permissions is shown below. Note that this class implements the GrantedAuthority interface, so each instance/record will be considered an authority from the perspective of Spring Security. Also note the lines highlighted in blue correspond to messages contained in the ValidationMessages.properties file under the resources directory.
package com.dtr.oas.model;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import org.hibernate.validator.constraints.NotEmpty;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.security.core.GrantedAuthority;
import com.google.common.base.Objects;
@Entity
@Table(name = "PERMISSIONS")
public class Permission extends BaseEntity implements GrantedAuthority {
private static final long serialVersionUID = -5404269148967698143L;
static Logger logger = LoggerFactory.getLogger(Permission.class);
@NotNull(message = "{error.permission.permissionname.null}")
@NotEmpty(message = "{error.permission.permissionname.empty}")
@Size(max = 50, message = "{permission.permissionname.role.max}")
@Column(name = "permissionname", length = 50)
private String permissionname;
@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name = "role_permissions",
joinColumns = {@JoinColumn(name = "permission_id", referencedColumnName = "id")},
inverseJoinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "id")}
)
private Set<Role> permRoles;
public String getPermissionname() {
return permissionname;
}
public void setPermissionname(String permissionname) {
this.permissionname = permissionname;
}
@Override
public String getAuthority() {
return permissionname;
}
public Set<Role> getPermRoles() {
return permRoles;
}
public void setPermRoles(Set<Role> permRoles) {
this.permRoles = permRoles;
}
@Override
public String toString() {
return String.format("%s(id=%d, permissionname='%s')",
this.getClass().getSimpleName(),
this.getId(), this.getPermissionname());
}
@Override
public boolean equals(Object o) {
if (this == o)
return true;
if (o == null)
return false;
if (o instanceof Role) {
final Permission other = (Permission) o;
return Objects.equal(getId(), other.getId())
&& Objects.equal(getPermissionname(), other.getPermissionname());
}
return false;
}
@Override
public int hashCode() {
return Objects.hashCode(getId(), getPermissionname());
}
}
5. Our role entity will need to be updated to reference the permissions in the permissions entity. There are a few changes to note that are highlighted in blue below:
- This class now extends GrantedAuthority so that a role and a permission will both be considered authorities by Spring Security.
- After noticing some issues during unit testing, FetchType.EAGER was added to the user_roles @OneToMany annotation.
- A @OneToMany entry was added for the relationship between this Role class and the Permission class.
- Getters and setters were added for the permissions instance variable.
- A getAuthority() method was added to meet the requirement of the GrantedAuthority interface implementation.
package com.dtr.oas.model;
import java.io.Serializable;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinTable;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import org.hibernate.validator.constraints.NotEmpty;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.security.core.GrantedAuthority;
import com.google.common.base.Objects;
@Entity
@Table(name = "ROLES")
public class Role extends BaseEntity implements Serializable, GrantedAuthority {
private static final long serialVersionUID = 6874667425302308430L;
static Logger logger = LoggerFactory.getLogger(Role.class);
@NotNull(message = "{error.roles.role.null}")
@NotEmpty(message = "{error.roles.role.empty}")
@Size(max = 50, message = "{error.roles.role.max}")
@Column(name = "rolename", length = 50)
private String rolename;
//@OneToMany(cascade = CascadeType.ALL)
@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name = "user_roles",
joinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "id")},
inverseJoinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")}
)
private Set<User> userRoles;
@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name = "role_permissions",
joinColumns = { @JoinColumn(name = "role_id", referencedColumnName = "id") },
inverseJoinColumns = { @JoinColumn(name = "permission_id", referencedColumnName = "id") }
)
private Set<Permission> permissions;
public String getRolename() {
return rolename;
}
public void setRolename(String rolename) {
this.rolename = rolename;
}
public Set<User> getUserRoles() {
return userRoles;
}
public void setUserRoles(Set<User> userRoles) {
this.userRoles = userRoles;
}
public Set<Permission> getPermissions() {
return permissions;
}
public void setPermissions(Set<Permission> permissions) {
this.permissions = permissions;
}
@Override
public String toString() {
return String.format("%s(id=%d, rolename='%s')",
this.getClass().getSimpleName(),
this.getId(), this.getRolename());
}
@Override
public boolean equals(Object o) {
if (this == o)
return true;
if (o == null)
return false;
if (o instanceof Role) {
final Role other = (Role) o;
return Objects.equal(getId(), other.getId())
&& Objects.equal(getRolename(), other.getRolename());
}
return false;
}
@Override
public int hashCode() {
return Objects.hashCode(getId(), getRolename());
}
@Override
public String getAuthority() {
return getRolename();
}
}
6. The user entity will also need to be updated as shown below. As with the Role class, the changes are highlighted in blue.
- After noticing some issues during unit testing, FetchType.EAGER was added to the user_roles @OneToMany annotation.
- The instance variable role was changed to they type Set and renamed roles. A user has been updated to contain multiple roles if required.
- The associated getter and setter have been updated.
- The toString() method has been updated to not include the role.
- A getPermissions() method has been added to retrieve all of the permissions associated with all of the roles associated with a given user.
- The grantedAuthority() method has been updated to return all of the roles and all of the permissions for a given user.
package com.dtr.oas.model;
import java.util.Collection;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinTable;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Transient;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import org.hibernate.validator.constraints.NotEmpty;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;
import com.google.common.base.Objects;
@Entity
@Table(name="USERS")
public class User extends BaseEntity implements UserDetails {
private static final long serialVersionUID = 6311364761937265306L;
static Logger logger = LoggerFactory.getLogger(User.class);
@NotNull(message = "{error.user.username.null}")
@NotEmpty(message = "{error.user.username.empty}")
@Size(max = 50, message = "{error.user.username.max}")
@Column(name = "username", length = 50)
private String username;
@NotNull(message = "{error.user.password.null}")
@NotEmpty(message = "{error.user.password.empty}")
@Size(max = 50, message = "{error.user.password.max}")
@Column(name = "password", length = 50)
private String password;
@Column(name = "enabled")
private boolean enabled;
@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name = "user_roles",
joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")},
inverseJoinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "id")}
)
private Set<Role> roles;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public boolean getEnabled() {
return enabled;
}
public void setEnabled(boolean enabled) {
this.enabled = enabled;
}
public Set<Role> getRoles() {
return roles;
}
public void setRoles(Set<Role> roles) {
this.roles = roles;
}
@Override
public String toString() {
return String.format("%s(id=%d, username=%s, password=%s, enabled=%b)",
this.getClass().getSimpleName(),
this.getId(),
this.getUsername(),
this.getPassword(),
this.getEnabled());
}
@Override
public boolean equals(Object o) {
if (this == o)
return true;
if (o == null)
return false;
if (o instanceof User) {
final User other = (User) o;
return Objects.equal(getId(), other.getId())
&& Objects.equal(getUsername(), other.getUsername())
&& Objects.equal(getPassword(), other.getPassword())
&& Objects.equal(getEnabled(), other.getEnabled());
}
return false;
}
@Override
public int hashCode() {
return Objects.hashCode(getId(), getUsername(), getPassword(), getEnabled());
}
@Transient
public Set<Permission> getPermissions() {
Set<Permission> perms = new HashSet<Permission>();
for (Role role : roles) {
perms.addAll(role.getPermissions());
}
return perms;
}
@Override
@Transient
public Collection<GrantedAuthority> getAuthorities() {
Set<GrantedAuthority> authorities = new HashSet<GrantedAuthority>();
authorities.addAll(getRoles());
authorities.addAll(getPermissions());
return authorities;
}
@Override
public boolean isAccountNonExpired() {
//return true = account is valid / not expired
return true;
}
@Override
public boolean isAccountNonLocked() {
//return true = account is not locked
return true;
}
@Override
public boolean isCredentialsNonExpired() {
//return true = password is valid / not expired
return true;
}
@Override
public boolean isEnabled() {
return this.getEnabled();
}
}
In the next post, we will add/update the DAO layer
