potential problem with extend user and ACL

[Blake Day <bl...@charter.net>]

I have (successfully?) extended the TurbineUser by adding more columns to
the TURBINE_USER table and actually removing the LOGIN_NAME column.  I want
to use the EMAIL column for usernames.

I have no problems until I start trying to use ACLs.  Somewhere in the code,
the LOGIN_NAME column is mentioned, because the generated query to fetch the
roles tries to use it.  The following snippet of code from
org.apache.turbine.om.security.peer.RolePeer generates the incorrect query:

     public static RoleSet retrieveSet( User user, Group group )
        throws Exception
    {
        Criteria criteria = new Criteria();

        /*
         * Peer specific methods should absolutely NOT be part
         * of any of the generic interfaces in the security system.
         * this is not good.
         *
         * UserPeer up = TurbineSecurity.getUserPeerInstance();
         */

        UserPeer up = ((DBSecurityService)TurbineSecurity.getService())
            .getUserPeerInstance();

        criteria.add(up.getFullColumnName(UserPeer.USERNAME),
                     user.getUserName());
        criteria.add(UserGroupRolePeer.GROUP_ID,
                     ((Persistent)group).getPrimaryKey());

        criteria.addJoin(up.getFullColumnName(UserPeer.USER_ID),
                         UserGroupRolePeer.USER_ID);
        criteria.addJoin(UserGroupRolePeer.ROLE_ID, RolePeer.ROLE_ID);
        return retrieveSet(criteria);
    }

The query is:

SELECT TURBINE_ROLE.ROLE_ID, TURBINE_ROLE.ROLE_NAME, TURBINE_ROLE.OBJECTDATA
FROM TURBINE_ROLE, TURBINE_USER, TURBINE_USER_GROUP_ROLE WHERE
(TURBINE_USER.LOGIN_NAME='blake@artistrystudios.net') AND
(TURBINE_USER_GROUP_ROLE.GROUP_ID=1) AND
TURBINE_USER.USER_ID=TURBINE_USER_GROUP_ROLE.USER_ID AND
TURBINE_USER_GROUP_ROLE.ROLE_ID=TURBINE_ROLE.ROLE_ID ORDER BY
UPPER(TURBINE_ROLE.ROLE_NAME) ASC


Now, I would be SOL if there was no way around adding the UserPeer.USERNAME
column to the criteria; however, the UserPeer.USERNAME column is not even
necessary.  In fact, it's inefficient as it leads to an extraneous table
join. Why aren't we using the UserPeer.USER_ID column to narrow down the
returned TURBINE_USER rows?  We could remove the join to TURBINE_USER
altogether and the query would become as follows:

SELECT TURBINE_ROLE.ROLE_ID, TURBINE_ROLE.ROLE_NAME, TURBINE_ROLE.OBJECTDATA
FROM TURBINE_ROLE, TURBINE_USER_GROUP_ROLE WHERE
(TURBINE_USER_GROUP_ROLE.USER_ID=x) AND (TURBINE_USER_GROUP_ROLE.GROUP_ID=1)
AND TURBINE_USER_GROUP_ROLE.ROLE_ID=TURBINE_ROLE.ROLE_ID ORDER BY
UPPER(TURBINE_ROLE.ROLE_NAME) ASC



Michael Blake Day
Artistry Studios - e-commerce design, implementation and hosting
email: mday@artistrystudios.net


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>