Spring Security3 Chapter 4 Part 2 (Advanced Configuration of JdbcDaoImpl)

 

Advanced Configuration of JdbcDaoImpl

JdbcDaoImpl has many configurable options that can be used in an existing schema or make more complex adjustments to its functions. In many scenarios, it is very likely that we only need to adjust the configuration of the built-in UserDetailsService class without writing our own code.

 

An important feature is to add an isolation layer (a level of indirection -- no better translation can be found) between the User and the GrantedAuthority ), this is achieved by dividing GrantedAuthority into a set (group) by logic. The user can be assigned to one or more groups, and the group members are assigned a series of GrantedAuthority statements.

 

As described in the figure, the middle isolation layer allows us to assign roles in the same set to many people, and this only requires specifying new users to an existing group. In contrast to our previous practice, GrantedAuthority is directly allocated to a single user.

 

This method of permission packaging may be used in the following scenarios:

 

 

L users should be divided into different groups, and some roles in the groups overlap;

L you want to modify the permissions of a class of users globally. For example, if you have a "supplier" group and you want to modify the settings of whether they can access a specific area of the application;

L has a large number of users, and you do not need user-level authorization configuration.

 

Unless you have a limited number of application users, you may need to use group-based access control. The simplicity and scalability of this management method bring much more value than the slightly increased complexity. This technology of Putting user permissions in A Group is usually called Group-Based Access Control (GBAC ).

[Group-based access control is available in almost any secure operating systems and software packages on the market. Microsoft's Active Directory (AD) is a typical Implementation of GBAC in a wide range. It includes AD users into a group and grants group permissions. By using GBAC, You can simplify permission management for a large number of AD-based organizations exponentially. Think about the security features of the software you are using-How are users, groups, and permissions managed? What are the advantages and disadvantages of writing security functions in this way ?]

 

Let's add an abstraction to JBCP Pets and apply the group-based authorization concept to this site.

Configure group-based authorization

We will add two groups for the site-common Users (we call them "Users") and Administrators (we call them "Administrators "). By modifying the SQL script used to start the database, you can allocate existing guest and admin accounts to appropriate groups.

Configure JdbcDaoImpl to use user groups

First, we need to set attributes for the custom implementation class of JdbcDaoImpl to enable the Group function and disable the function of directly authorizing users. Add the following bean declaration in the dogstore-base.xml:

 

Xml Code

<Bean id = "jdbcUserService"

Class = "com. packtpub. springsecurity. security. CustomJdbcDaoImpl">

<Property name = "dataSource" ref = "dataSource"/>

<Property name = "enableGroups" value = "true"/>

<Property name = "enableAuthorities" value = "false"/>

</Bean>

Note that if you have followed our example and used the JdbcUserManager code and configuration, Please modify it, because we will use CustomJdbcDaoImpl for the rest of this chapter.

Modify the initially loaded SQL script

We need to simply modify the SQL statement for building the database:

L define our group information;

L specify GrantedAuthority to declare it to the group;

L specify the user to the group.

 

For simplicity, we declare a new SQL script named test-users-groups-data. SQL.

First, add the group:

 

SQL code

Insert into groups (group_name) values ('users ');

Insert into groups (group_name) values ('administrators ');

 

Next, specify the role to the group:

 

SQL code

Insert into group_authorities (group_id, authority) select id, 'Role _

User' from groups where group_name = 'users ';

Insert into group_authorities (group_id, authority) select id, 'Role _

USER 'from groups where group_name = 'administrators ';

Insert into group_authorities (group_id, authority) select id, 'Role _

ADMIN 'from groups where group_name = 'administrators ';

Next, create a user:

 

SQL code

Insert into users (username, password, enabled) values

('Admin', 'admin', true );

Insert into users (username, password, enabled) values

('Guest ', 'Guest', true );

Finally, specify the user to the group:

 

SQL code

Insert into group_members (group_id, username) select id, 'Guest 'from

Groups where group_name = 'users ';

Insert into group_members (group_id, username) select id, 'admin' from

Groups where group_name = 'administrators ';

 

Modify the embedded database creation statement

We need to update the creation configuration of the embedded HSQL database to point to this script, instead of an existing test-data. SQL script:

 

Xml Code

<Jdbc: embedded-database id = "dataSource" type = "HSQL">

<Jdbc: script location = "classpath: security-schema. SQL"/>

<Jdbc: script location = "classpath: test-users-groups-data. SQL"/>

</Jdbc: embedded-database>

Note that the security-schema. SQL script already contains table declarations that support the Group feature, so we don't need to modify this script.

 

Here, you can restart the JBCP Pets site, which will be exactly the same as the previous performance, however, the abstraction layer we add between users and permissions makes it easier for us to develop and develop complex user management functions.

 

Let's leave the JBCP Pets scenario for the moment to understand a more important configuration in this regard.

Database-based authentication using legacy or user-defined schame

Generally, new users of Spring Security may need to adapt users, groups, and roles to existing database schemas. Although the legacy database does not match the schema required by Spring Security, we can configure JdbcDaoImpl to match it.

Assume that we have a legacy database schema as shown in, and implement Spring Security based on it:

 

We can easily modify the JdbcDaoImpl configuration to use this schema and rewrite the default Spring Security table definitions and columns we use in JBCP Pets.

Determine the correct jdbc SQL query

JdbcDaoImpl has three SQL queries, which have well-defined parameters and a set of returned columns. We must take the opportunity of the functions they provide to determine the SQL statements for each query. Every SQL query in JdbcDaoImpl uses the username provided during logon as the unique parameter.

 

Query name	Description	Expected SQL Column
UsersByUsernameQuery	Returns one or more users that match the user name. Only the first user returned is used.	Username (string) Password (string) Enabled (Boolean)
AuthoritiesByUsernameQuery	Returns the permissions directly granted to the user. It is generally used when GBAC is disabled.	Username (string) Granted Authority (String)
GroupAuthoritiesByUsernameQuery	Returns the permissions and group details granted to the user as a member of the group. It is used when the GBAC function is enabled.	Group Primary Key (Any) Group Name (any) Granted Authority (String)

 

Note that in some scenarios, the returned columns are not used in the default JdbcDaoImpl implementation, but we still need to return these values. Before entering the next chapter, take a moment to try to write a query statement based on the previous database chart.

Configure JdbcDaoImpl to use custom SQL queries

For non-standard databases to use custom SQL queries, we need to modify the attributes of JdbcDaoImpl in the Spring Bean configuration file. To configure JDBC queries for JdbcDaoImpl, you cannot use the <jdbc-user-service> statement. It is necessary to explicitly instantiate this bean, as we did when customizing JdbcDaoImpl implementation:

 

Xml Code

<Bean id = "jdbcUserService"

Class = "com. packtpub. springsecurity. security. CustomJdbcDaoImpl">

<Property name = "dataSource" ref = "dataSource"/>

<Property name = "enableGroups" value = "true"/>

<Property name = "enableAuthorities" value = "false"/>

<Property name = "usersByUsernameQuery">

<Value> select login, PASSWORD,

1 FROM USER_INFO where login =?

</Value>

</Property>

<Property name = "groupAuthoritiesByUsernameQuery">

<Value> select g. GROUP_ID, G. GROUP_NAME, P. NAME

FROM USER_INFO U

JOIN USER_GROUP UG on U. USER_INFO_ID = UG. USER_INFO_ID

Join group g on ug. GROUP_ID = G. GROUP_ID

JOIN GROUP_PERMISSION gp on g. GROUP_ID = GP. GROUP_ID

Join permission p on gp. PERMISSION_ID = P. PERMISSION_ID

Where u. LOGIN =?

</Value>

</Property>

</Bean>

This is the only configuration that Spring Security requires when reading settings from an existing database that does not conform to the default schema. Note that when using an existing schema, you usually need to extend JdbcDaoImpl to support changing passwords, renaming user accounts, and other user management functions.

 

If you use JdbcUserDetailsManager to complete user management tasks, this class uses about 20 configurable SQL queries. Refer to Javadoc or source code to learn about the default query used by JdbcUserDetailsManager.