MySQL query in spring-security.xml for authorization

Question

Q1. > How do I check whether the following two queries is executed or not in spring-security.xml ?

 <jdbc-user-service data-source-ref="dataSource"
          users-by-username-query=
            "select user_login_name as username,user_password as password,user_type_id,role_id from sox_audit.sox_users where user_login_name=? and user_password=?"

           authorities-by-username-query=
            "select user_login_name as username,role_id as authority from sox_audit.sox_users where user_login_name ='[email protected]' and user_password='12345' "/>

I can able to login with hardcoded values and give authorities as per the user type. But I cannot By selecting the records from DB.

Can anyone help me out in this.

Your queries are wrong, plain wrong. The users-by-username one should return 3 values, username,password and enabled nothing more, nothing less. It should also query only on username not password (i.e. just a single parameter). Your authorities by username has the same flaw it should only have a username as the parameter not password. So basically your queries are wrong. See the reference guide on what to return and what is used to query on. — M. Deinum
– M. Deinum, Commented May 12, 2015 at 8:46

isah · Accepted Answer · 2015-05-12 08:44:49Z

1

Enable logs for JdbcDaoImpl which implements UserDetailsService. something like:

log4j.logger.org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl=DEBUG

If that doesn't work, add some breakpoints on this class and debug it on runtime.

answered May 12, 2015 at 8:44

isah

5,3613 gold badges29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh · Accepted Answer · 2015-05-12 12:27:58Z

0

I solved it. Thing is query is wrong. users-by-username-query will take only 3 parameters. It should be username, password and enabled from database. Also, authorities-by-username-query will take 2 parameters.

 users-by-username-query=
            "select user_login_name as username,user_password as password,enabled from sox_audit.sox_users where user_login_name=?"

           authorities-by-username-query=
            "select user_login_name as username,authority from sox_audit.sox_users where user_login_name =?"/>

Then control the users in HomeController. This will work perfectly.

answered May 12, 2015 at 12:27

Yogesh

756 bronze badges

Collectives™ on Stack Overflow

MySQL query in spring-security.xml for authorization

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related