Announcement Announcement Module
Collapse
No announcement yet.
Custom user/role tables Page Title Module
Move Remove Collapse
This topic is closed
X
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Custom user/role tables

    Hello all,

    I'm using a normalized structure to store my user/role data. The tables are defined as follows:

    user:
    +---------------+--------------+------+-----+---------------------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------------+--------------+------+-----+---------------------+-------+
    | id | int(11) | NO | PRI | | |
    | username | varchar(128) | NO | UNI | | |
    | password | varchar(128) | NO | | | |
    | email_address | varchar(128) | NO | UNI | | |
    | enabled | tinyint(1) | NO | | | |
    | creation_date | timestamp | YES | | CURRENT_TIMESTAMP | |
    | last_visit | timestamp | YES | | 0000-00-00 00:00:00 | |
    +---------------+--------------+------+-----+---------------------+-------+

    authority:
    +-------------+---------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------------+---------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | | |
    | authority | varchar(128) | NO | | | |
    | description | varchar(1024) | YES | | NULL | |
    +-------------+---------------+------+-----+---------+-------+

    granted_authority:
    +--------------+---------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +--------------+---------+------+-----+---------+-------+
    | user_id | int(11) | NO | MUL | | |
    | authority_id | int(11) | NO | MUL | | |
    +--------------+---------+------+-----+---------+-------+

    I understand that I need to customize my usersByUsernameQuery and my authoritiesByUsernameQuery.

    The usersByUsernameQuery is working and is as follows:
    SELECT username,password,enabled FROM user WHERE username = ?

    However, I cannot make the authoritiesByUsernameQuery work and I'm looking for some help. The following query works, but only returns the authorities and I think that Acegi SS expects both username and authority to be returned. How can I combine the date from the user table with the table from the authority table so that Acegi SS can work with the data returned?

    SELECT authority FROM authority WHERE id IN (SELECT authority_id FROM granted_authority WHERE user_id IN (SELECT id FROM user WHERE username = ?))

    I'm using Spring 2.0.3 with Acegi 1.0.3 and with MySQL 5.0.24a.

    Many thanks,
    James

  • #2
    Check out the Acegi class JdbcDaoImpl's JavaDoc. the original DEF_AUTHORITIES_BY_USERNAME_QUERY "SELECT username,authority FROM authorities WHERE username = ?" which means you can override it with a simple join.

    HTH

    Comment


    • #3
      The JdbcDaoImpl returns a username and authority, the query only read the second parameter though. Not quite sure, maybe it's a legacy thing. To get yours to work just ensure it's the second parameter.
      Code:
      SELECT username,authority FROM authorities WHERE username = ?
      e.g.
      Code:
      SELECT id, authority FROM authority WHERE id IN (SELECT authority_id FROM granted_authority WHERE user_id IN (SELECT id FROM user WHERE username = ?))

      Comment


      • #4
        [Solved] Custom user/role tables

        Originally posted by karldmoore View Post
        The JdbcDaoImpl returns a username and authority, the query only read the second parameter though. Not quite sure, maybe it's a legacy thing. To get yours to work just ensure it's the second parameter.
        Code:
        SELECT username,authority FROM authorities WHERE username = ?
        e.g.
        Code:
        SELECT id, authority FROM authority WHERE id IN (SELECT authority_id FROM granted_authority WHERE user_id IN (SELECT id FROM user WHERE username = ?))
        Worked like a charm Many thanks!

        Comment


        • #5
          Hi

          This is probably going to be a silly question, but if i configure the userdetails to use a jdbc dao and provide the above sql statements to retrive user creditionals and access rights, do i need to do any mapping between what the user submits on the form and the dao query? Or does the filter automatically handle that for you?

          Comment


          • #6
            I don't know what you mean by "mapping". If you are using form passed authentication with the JdbcDaoImpl you just need to ensure there are the correct return values and input parameters. If you look at the style of query used by default, it's easy to see what you need to do.

            Comment


            • #7
              What i meant was that my form has j_username and j_password, do i need anything to map those fields with the '?' in the SQL statement below

              Code:
              SELECT id, authority FROM authority WHERE id IN (SELECT authority_id FROM granted_authority WHERE user_id IN (SELECT id FROM user WHERE username = ?))
              I presume as long as i have the j_username and j_password set in my html form the filter will take those and apply them to teh SQL in the JDBC impl.

              Comment


              • #8
                That's all handled for you, you don't have to worry about that. As long as you return the right information and you have the correct number of ?'s everything will be fine!

                Comment


                • #9
                  Excellent! By the way just want to say thanks Karldmoore...you've been a major help and I'm sure i'm gonna be back here to ask more questions!

                  Comment


                  • #10
                    Not a problem, I'm glad to have helped. Just remember before you reach for the forum, the reference manual has lots of answers and the forums has already answered lots more. There's only so many hours in the day .

                    Comment

                    Working...
                    X