Author: sohil.shah(a)jboss.com Date: 2009-12-09 17:44:43 -0500 (Wed, 09 Dec 2009) New Revision: 13854 Added: modules/cms/trunk/cms-jackrabbit/src/main/resources/findPermissionsByUser.sql Modified: modules/cms/trunk/.classpath modules/cms/trunk/cms-jackrabbit/src/main/java/org/jboss/portal/cms/security/AuthorizationProviderImpl.java Log: JBEPP-117 - CMS identity queries with case sensitive DB fails Modified: modules/cms/trunk/.classpath =================================================================== --- modules/cms/trunk/.classpath 2009-12-09 15:40:03 UTC (rev 13853) +++ modules/cms/trunk/.classpath 2009-12-09 22:44:43 UTC (rev 13854) @@ -13,14 +13,12 @@ <classpathentry kind="var" path="M2_REPO/org/apache/ant/ant-junit/1.7.0/ant-junit-1.7.0.jar"/> <classpathentry kind="var" path="M2_REPO/ant/ant-launcher/1.6.5/ant-launcher-1.6.5.jar"/> <classpathentry kind="var" path="M2_REPO/org/apache/ant/ant-launcher/1.7.0/ant-launcher-1.7.0.jar"/> - <classpathentry kind="var" path="M2_REPO/ant/ant-nodeps/1.6.5/ant-nodeps-1.6.5.jar"/> <classpathentry kind="var" path="M2_REPO/antlr/antlr/2.7.7/antlr-2.7.7.jar"/> <classpathentry kind="var" path="M2_REPO/asm/asm/1.5.3/asm-1.5.3.jar"/> <classpathentry kind="var" path="M2_REPO/backport-util-concurrent/backport-util-concurrent/3.1/backport-util-concurrent-3.1.jar"/> <classpathentry kind="var" path="M2_REPO/bsh/bsh/1.3.0/bsh-1.3.0.jar"/> <classpathentry kind="var" path="M2_REPO/org/codehaus/cargo/cargo-core-uberjar/0.8/cargo-core-uberjar-0.8.jar"/> <classpathentry kind="var" path="M2_REPO/cglib/cglib/2.1_3/cglib-2.1_3.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/portal/common/common-common/1.2.0/common-common-1.2.0.jar"/> <classpathentry kind="var" path="M2_REPO/commons-codec/commons-codec/1.2/commons-codec-1.2.jar"/> <classpathentry kind="var" path="M2_REPO/commons-collections/commons-collections/3.2/commons-collections-3.2.jar"/> <classpathentry kind="var" path="M2_REPO/apache-httpclient/commons-httpclient/2.0.2/commons-httpclient-2.0.2.jar"/> @@ -28,14 +26,12 @@ <classpathentry kind="var" path="M2_REPO/commons-logging/commons-logging/1.1.1/commons-logging-1.1.1.jar"/> <classpathentry kind="var" path="M2_REPO/concurrent/concurrent/1.3.4/concurrent-1.3.4.jar"/> <classpathentry kind="var" path="M2_REPO/oswego-concurrent/concurrent/1.3.4/concurrent-1.3.4.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/portal/core/core/2.7.0.CR1/core-2.7.0.CR1.jar"/> <classpathentry kind="var" path="M2_REPO/org/apache/derby/derby/10.2.1.6/derby-10.2.1.6.jar"/> <classpathentry kind="var" path="M2_REPO/dom4j/dom4j/1.6.1/dom4j-1.6.1.jar"/> <classpathentry kind="var" path="M2_REPO/wutka-dtdparser/dtdparser121/1.2.1/dtdparser121-1.2.1.jar"/> <classpathentry kind="var" path="M2_REPO/net/sf/ehcache/ehcache/1.4.1/ehcache-1.4.1.jar"/> <classpathentry kind="var" path="M2_REPO/hibernate/hibernate3/3.2.4.SP1/hibernate3-3.2.4.SP1.jar"/> <classpathentry kind="var" path="M2_REPO/hsqldb/hsqldb/1.8.0.2/hsqldb-1.8.0.2.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/portal/identity/identity-identity/1.0.2/identity-identity-1.0.2.jar"/> <classpathentry kind="var" path="M2_REPO/org/apache/jackrabbit/jackrabbit-api/1.4/jackrabbit-api-1.4.jar"/> <classpathentry kind="var" path="M2_REPO/org/apache/jackrabbit/jackrabbit-core/1.4.5/jackrabbit-core-1.4.5.jar"/> <classpathentry kind="var" path="M2_REPO/org/apache/jackrabbit/jackrabbit-jcr-commons/1.4/jackrabbit-jcr-commons-1.4.jar"/> @@ -47,15 +43,12 @@ <classpathentry kind="var" path="M2_REPO/jboss/jbossas/core-libs/jboss/4.0.4.GA/jboss-4.0.4.GA.jar"/> <classpathentry kind="var" path="M2_REPO/org/jboss/aop/jboss-aop/2.0.0.CR1/jboss-aop-2.0.0.CR1.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jboss-common-core/2.0.2.GA/jboss-common-core-2.0.2.GA.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/jboss-common-core/2.2.3.GA/jboss-common-core-2.2.3.GA.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jbossas/core-libs/jboss-common-jdbc-wrapper/4.0.4.GA/jboss-common-jdbc-wrapper-4.0.4.GA.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jboss-common-logging-log4j/2.0.4.GA/jboss-common-logging-log4j-2.0.4.GA.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jboss-common-logging-spi/2.0.4.GA/jboss-common-logging-spi-2.0.4.GA.jar"/> <classpathentry kind="var" path="M2_REPO/org/jboss/microcontainer/jboss-container/2.0.0.Beta9/jboss-container-2.0.0.Beta9.jar"/> <classpathentry kind="var" path="M2_REPO/org/jboss/microcontainer/jboss-dependency/2.0.0.Beta9/jboss-dependency-2.0.0.Beta9.jar"/> - <classpathentry kind="var" path="M2_REPO/jboss/jboss-j2ee/4.2.0.GA/jboss-j2ee-4.2.0.GA.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jbossas/core-libs/jboss-jca/4.0.4.GA/jboss-jca-4.0.4.GA.jar"/> - <classpathentry kind="var" path="M2_REPO/jboss/jboss-jmx/4.2.0.GA/jboss-jmx-4.2.0.GA.jar"/> <classpathentry kind="var" path="M2_REPO/org/jboss/microcontainer/jboss-kernel/2.0.0.Beta9/jboss-kernel-2.0.0.Beta9.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jbossas/core-libs/jboss-local-jdbc/4.2.2.GA/jboss-local-jdbc-4.2.2.GA.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jboss-logging-jdk/2.0.2.GA/jboss-logging-jdk-2.0.2.GA.jar"/> @@ -67,10 +60,6 @@ <classpathentry kind="var" path="M2_REPO/jboss/jboss-system/4.2.2.GA/jboss-system-4.2.2.GA.jar"/> <classpathentry kind="var" path="M2_REPO/org/jboss/jboss-test/1.0.4.GA/jboss-test-1.0.4.GA.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jbossas/core-libs/jboss-transaction/4.0.4.GA/jboss-transaction-4.0.4.GA.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/unit/jboss-unit/1.2.1/jboss-unit-1.2.1.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/unit/jboss-unit-mc/1.2.1/jboss-unit-mc-1.2.1.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/unit/jboss-unit-tooling-ant/1.2.1/jboss-unit-tooling-ant-1.2.1.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/unit/jboss-unit-tooling-core/1.2.1/jboss-unit-tooling-core-1.2.1.jar"/> <classpathentry kind="var" path="M2_REPO/org/jboss/cache/jbosscache-core/1.4.1.SP3/jbosscache-core-1.4.1.SP3.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jbossha/4.2.2.GA/jbossha-4.2.2.GA.jar"/> <classpathentry kind="var" path="M2_REPO/org/jboss/jbossxb/2.0.0.CR4/jbossxb-2.0.0.CR4.jar"/> @@ -78,8 +67,6 @@ <classpathentry kind="var" path="M2_REPO/org/slf4j/jcl104-over-slf4j/1.3.0/jcl104-over-slf4j-1.3.0.jar"/> <classpathentry kind="var" path="M2_REPO/javax/jcr/jcr/1.0/jcr-1.0.jar"/> <classpathentry kind="var" path="M2_REPO/sleepycat/je/3.0.12/je-3.0.12.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/portal/core/jems/2.7.0.CR1/jems-2.7.0.CR1.jar"/> - <classpathentry kind="var" path="M2_REPO/jgroups/jgroups/2.4.1/jgroups-2.4.1.jar"/> <classpathentry kind="var" path="M2_REPO/jboss/jbossas/core-libs/jnpserver/4.0.4.GA/jnpserver-4.0.4.GA.jar"/> <classpathentry kind="var" path="M2_REPO/net/sf/jsr107cache/jsr107cache/1.0/jsr107cache-1.0.jar"/> <classpathentry kind="var" path="M2_REPO/junit/junit/3.8.2/junit-3.8.2.jar"/> @@ -90,18 +77,13 @@ <classpathentry kind="var" path="M2_REPO/sun-opends/opends-core/legacy/opends-core-legacy.jar"/> <classpathentry kind="var" path="M2_REPO/pdfbox/pdfbox/0.6.4/pdfbox-0.6.4.jar"/> <classpathentry kind="var" path="M2_REPO/poi/poi/2.5.1-final-20040804/poi-2.5.1-final-20040804.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/unit/portal-test/1.2.1/portal-test-1.2.1.jar"/> <classpathentry kind="var" path="M2_REPO/qdox/qdox/1.6/qdox-1.6.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/portal/core/search/2.7.0.CR1/search-2.7.0.CR1.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/portal/core/security/2.7.0.CR1/security-2.7.0.CR1.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/portal/core/server/2.7.0.CR1/server-2.7.0.CR1.jar"/> <classpathentry kind="var" path="M2_REPO/javax/servlet/servlet-api/2.4/servlet-api-2.4.jar"/> <classpathentry kind="var" path="M2_REPO/org/slf4j/slf4j-api/1.3.0/slf4j-api-1.3.0.jar"/> <classpathentry kind="var" path="M2_REPO/org/slf4j/slf4j-log4j12/1.3.0/slf4j-log4j12-1.3.0.jar"/> <classpathentry kind="var" path="M2_REPO/org/textmining/tm-extractors/0.4/tm-extractors-0.4.jar"/> <classpathentry kind="var" path="M2_REPO/trove/trove/2.1.1/trove-2.1.1.jar"/> <classpathentry kind="var" path="M2_REPO/apache-slide/webdavlib/2.0/webdavlib-2.0.jar"/> - <classpathentry kind="var" path="M2_REPO/org/jboss/portal/core/workflow/2.7.0.CR1/workflow-2.7.0.CR1.jar"/> <classpathentry kind="var" path="M2_REPO/apache-xerces/xercesImpl/2.7.1/xercesImpl-2.7.1.jar"/> <classpathentry kind="var" path="M2_REPO/xerces/xercesImpl/2.8.1/xercesImpl-2.8.1.jar"/> <classpathentry kind="var" path="M2_REPO/apache-xerces/xml-apis/2.7.1/xml-apis-2.7.1.jar"/> Modified: modules/cms/trunk/cms-jackrabbit/src/main/java/org/jboss/portal/cms/security/AuthorizationProviderImpl.java =================================================================== --- modules/cms/trunk/cms-jackrabbit/src/main/java/org/jboss/portal/cms/security/AuthorizationProviderImpl.java 2009-12-09 15:40:03 UTC (rev 13853) +++ modules/cms/trunk/cms-jackrabbit/src/main/java/org/jboss/portal/cms/security/AuthorizationProviderImpl.java 2009-12-09 22:44:43 UTC (rev 13854) @@ -40,17 +40,20 @@ import org.jboss.portal.identity.db.HibernateUserModuleImpl; import org.jboss.portal.jems.as.system.AbstractJBossService; import org.jboss.portal.jems.hibernate.HibernateProvider; -import org.jboss.portal.jems.hibernate.SessionFactoryBinder; import org.jboss.portal.security.spi.provider.DomainConfigurator; import org.jboss.portal.security.spi.provider.PermissionFactory; import org.jboss.portal.security.spi.provider.PermissionRepository; import org.jboss.portal.security.spi.provider.SecurityConfigurationException; import org.jboss.portal.security.impl.jacc.JACCPortalPrincipal; +import org.jboss.portal.common.io.IOTools; +import java.io.InputStream; import javax.management.ObjectName; import javax.security.auth.Subject; import javax.security.jacc.PolicyContext; import javax.security.jacc.PolicyContextException; + +import java.text.MessageFormat; import java.util.Collection; import java.util.HashSet; import java.util.Iterator; @@ -79,6 +82,8 @@ private String schemaName = null; + private String nativePermissionQuery; + /** * */ @@ -108,6 +113,25 @@ // Will require a rework once fully based on MC. log.warn("Can't access \"portal:service=Hibernate,type=Instance\" MBean"); } + + //Read the externalized native query (using a native query for better performance) + InputStream is = null; + try + { + is = Thread.currentThread().getContextClassLoader().getResourceAsStream("findPermissionsByUser.sql"); + byte[] bytes = IOTools.getBytes(is); + String nativeSql = new String(bytes).trim(); + + String schemaPrefix = (schemaName!=null ? schemaName+"." : ""); + this.nativePermissionQuery = MessageFormat.format(nativeSql, new Object[]{schemaPrefix}); + } + finally + { + if(is != null) + { + try{is.close();}catch(Exception ignoreMe){} + } + } } // ------AuthorizationDomain @@ -461,20 +485,9 @@ permissions.addAll(userQuery.list()); if (this.userModule instanceof HibernateUserModuleImpl) - { - String schemaPrefix = (schemaName!=null ? schemaName+"." : ""); - - // in case of a database version, use the cache optimized way to - // lookup the permissions - String lookupByRole = "SELECT p.* from " + schemaPrefix + "jbp_cms_perm p," + schemaPrefix + "jbp_cms_perm_role r," + schemaPrefix + "jbp_role_membership m," + schemaPrefix + "jbp_roles roles," + schemaPrefix + "jbp_users users WHERE " - + "p.id=r.cms_perm_id AND " - + "r.role_id=roles.jbp_name AND " - + "m.jbp_rid=roles.jbp_rid AND " - + "m.jbp_uid=users.jbp_uid AND " + "users.jbp_uname=?"; - - // perform lookup based on role membership - Query roleQuery = session.createSQLQuery(lookupByRole).addEntity( - Permission.class); + { + Query roleQuery = session.createSQLQuery(this.nativePermissionQuery).addEntity( + Permission.class); roleQuery.setString(0, userId); roleQuery.setCacheable(true); permissions.addAll(roleQuery.list()); Added: modules/cms/trunk/cms-jackrabbit/src/main/resources/findPermissionsByUser.sql =================================================================== --- modules/cms/trunk/cms-jackrabbit/src/main/resources/findPermissionsByUser.sql (rev 0) +++ modules/cms/trunk/cms-jackrabbit/src/main/resources/findPermissionsByUser.sql 2009-12-09 22:44:43 UTC (rev 13854) @@ -0,0 +1 @@ +SELECT p.* from {0}jbp_cms_perm p,{0}jbp_cms_perm_role r,{0}jbp_role_membership m,{0}jbp_roles roles,{0}jbp_users users WHERE p.id=r.cms_perm_id AND r.role_id=roles.jbp_name AND m.jbp_rid=roles.jbp_rid AND m.jbp_uid=users.jbp_uid AND users.jbp_uname=? \ No newline at end of file