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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | -- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 | 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
4 comments:
A very detailed and well written article. I also had to build roles as a set of permissions and do this in a fully dynamic way. The basic issue was to dynamically configure the right to execute methods (usually service layer methods). I followed a different approach than yours though, in fact I figured out that the ACL module itself has a viable model to define roles as set of permissions. I don’t know if it is supposed to be used that way, anyway it does the trick and I don’t see any particular drawbacks. If you look at the ACL model the SID entity can play the role of a Principal but also of a Granted Authority. The Granted Authority can be arbitrarily seen as a role and then I can define ACL objects as, for instance, MethodExecution objects, link a custom ‘execution’ permission with them, and associate this set of objects and related permissions to the Granted Authority (i.e as ACE instances). If you’re interested I have written an article on this subject at the following link (it explains the basics and it does not contain a full example, though, I hope to have the time to do it in the near future):
Dynamically Securing Method Execution with Spring Security .
Very Detailed and well explained article
Thank you Kunal...I appreciate your feedback!
Facing Error in BaseEntity Class. Where is this BaseEntity class present
Post a Comment