Spring Security Combat Series Spring Security Combat (iii)

Spring Security in Combat (ii) Describes the default database with spring security to store users and permissions data, but spring security provides the table structure by default is too simple, in fact, even if the default table structure is complex, It is not necessarily possible to meet the requirements of the project to manage user information and authority information. Then the next step is to explain how to customize the database to implement the management of user information and permissions information. A custom table structureThe MySQL database is still used here, so the Pom.xml files are not modified. Just create a new three-sheet table, User table, Role table, User_role table. Where the user table, role table is the main table that holds the user permission data, User_role is the associated table. User tables, role roles tables are many-to-many relationships, meaning that a user can have multiple roles. To build a table statement and insert data:

--Role CREATE TABLE role (ID bigint, ' name ' varchar (), DESCN varchar (200));  
ALTER TABLE role add constraint Pk_role primary key (ID); 
  
--ALTER TABLE role ALTER COLUMN ID int generated by default as identity (1, 1); --User CREATE table ' user ' (ID bigint, username varchar (), ' password ' varchar, ' status ' in  
Teger, DESCN varchar (200));  
ALTER TABLE ' user ' Add constraint Pk_user primary key (ID);  
  
--ALTER TABLE ' USER ' alter column ID bigint generated by default as identity (start with 1);  
--User Role Connection table CREATE table User_role (user_id bigint, role_id bigint);  
ALTER TABLE User_role ADD constraint Pk_user_role primary key (user_id, role_id);  
ALTER TABLE User_role add constraint fk_user_role_user foreign key (user_id) References ' user ' (ID);

ALTER TABLE User_role add constraint fk_user_role_role foreign key (role_id) references role (ID); --Inserting data insert into user (ID,USERNAME,PASSWORD,STATUS,DESCN) vAlues (1, ' admin ', ' admin ', 1, ' admin ');  
  
Insert into User (ID,USERNAME,PASSWORD,STATUS,DESCN) VALUES (2, ' user ', ' username ', 1, ' users ');  
Insert into role (ID,NAME,DESCN) VALUES (1, ' role_admin ', ' Administrator role ');  
  
Insert into role (ID,NAME,DESCN) VALUES (2, ' role_user ', ' user roles ');  
Insert into User_role (user_id,role_id) values (+);  
Insert into User_role (user_id,role_id) values;  Insert into User_role (user_id,role_id) values (2,2);

Two modifying the Spring Security configuration file (Applicationcontext-security.xml)Now we want to use the data structure based on the Spring security,spring Security needs to do is to deal with two cases, one is to determine whether the login user is legitimate, and the second is to determine whether the logged on users have access to protected system resources. So the work we have to do is to provide these two kinds of data to spring security on the basis of the existing data structures. There are two properties in the Jdbc-user-service tag: 1. Users-by-username-query to find the user based on the user name, the system queries the current user's login name, password, and whether the status is disabled by the incoming user name. 2.authorities-by-username-query to find permissions based on the user name, the system queries the incoming user name for all permissions that the current user has been granted. So the Users-by-username-query property is to query the user name, password, and availability through username, Authorities-by-username-query property is username to query user permissions. So we modify the SQL statement on the basis of our custom table structure and get the following configuration:

<!--default database storage for users Spring security requires two tables, user tables, and permission tables by default. -
    <authentication-manager>
        <authentication-provider>
            <jdbc-user-service data-source-ref= "Mysqldatasource"
                  users-by-username-query= "select username, ' Password ', ' status ' as enabled from ' user ' where username =? "
                  authorities-by-username-query= "Select ' User ' Username,role. ' name ' from ' user ', role,user_role where ' user '. Id=user_ role.user_id and User_role.role_id=role.id and ' user '. Username =? "/>

        </authentication-provider>
    </authentication-manager>

The resulting configuration file is as follows:

<?xml version= "1.0" encoding= "UTF-8"?> <beans:beans xmlns= "http://www.springframework.org/schema/security "Xmlns:beans=" Http://www.springframework.org/schema/beans "xmlns:xsi=" Http://www.w3.org/2001/XM Lschema-instance "xmlns:sec=" http://www.springframework.org/schema/security "xsi:schemalocation= "Http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-be Ans-3.0.xsd Http://www.springframework.org/schema/context Http://www.sprin
                        Gframework.org/schema/context/spring-context-3.1.xsd HTTP://WWW.SPRINGFRAMEWORK.ORG/SCHEMA/TX Http://www.springframework.org/schema/tx/spring-tx-3.0.xsd HTTP://WWW.SPRINGFR Amework.org/schema/security http://www.springframework.org/schema/security/spring-security.xsd "


    ; <!--Configure non-filtered resources (static resources and loginsRelated). is to ignore the interception of certain resources, mainly for static resources-- 

and configuration (such as Applicationcontext.xml, Pplicationcontext-datasource.xml, Logback.xml, datasource.properties) and the previous Combat II (Spring Security Combat (b) Exactly the same, please refer to the previous combat. three results because just change the user information and permissions information to save the way, the other has not changed, the effect of the effect is the same as the actual combat