Index: kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupService.java =================================================================== --- kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupService.java (revision 109324) +++ kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupService.java (working copy) @@ -245,7 +245,7 @@ name = name.intern(); // check the cache to see if the role name already exists - if (m_roleNameCache.contains(name)) return; + if (getRealmRoleKey(name) != null) return; // see if we have it in the db String statement = dbAuthzGroupSql.getCountRealmRoleSql(); @@ -284,7 +284,21 @@ synchronized (m_roleNameCache) { - m_roleNameCache.add(name); + //Get realm role Key + statement = dbAuthzGroupSql.getSelectRealmRoleKeySql(); + results = sqlService().dbRead(statement, fields, new SqlReader() { + public Object readSqlResultRecord(ResultSet result) { + try { + String name = result.getString(1); + String key = result.getString(2); + RealmRole realmRole = new RealmRole(name, key); + m_roleNameCache.add(realmRole); + } + catch (SQLException ignore) { + } + return null; + } + }); } } @@ -303,7 +317,9 @@ try { String name = result.getString(1); - m_roleNameCache.add(name); + String key = result.getString(2); + RealmRole realmRole = new RealmRole(name, key); + m_roleNameCache.add(realmRole); } catch (SQLException ignore) { @@ -1491,7 +1507,7 @@ if (M_log.isDebugEnabled()) M_log.debug("isAllowed: auth=" + auth + " userId=" + userId + " lock=" + lock + " realm=" + realmId); - String statement = dbAuthzGroupSql.getCountRealmRoleFunctionSql(ANON_ROLE, AUTH_ROLE, auth); + String statement = dbAuthzGroupSql.getCountRealmRoleFunctionSql(getRealmRoleKey(ANON_ROLE), getRealmRoleKey(AUTH_ROLE), auth); Object[] fields = new Object[3]; fields[0] = userId; fields[1] = lock; @@ -1556,7 +1572,7 @@ String inClause = orInClause(realms.size(), "SAKAI_REALM.REALM_ID"); // any of the grant or role realms - String statement = dbAuthzGroupSql.getCountRealmRoleFunctionSql(ANON_ROLE, AUTH_ROLE, auth, inClause); + String statement = dbAuthzGroupSql.getCountRealmRoleFunctionSql(getRealmRoleKey(ANON_ROLE), getRealmRoleKey(AUTH_ROLE), auth, inClause); Object[] fields = new Object[2 + (2 * realms.size())]; int pos = 0; @@ -1564,6 +1580,13 @@ String userSiteRef = null; String siteRef = null; + // oracle query has different order of parameters + String dbAuthzGroupSqlClassName=dbAuthzGroupSql.getClass().getName(); + + if(dbAuthzGroupSqlClassName.equals("org.sakaiproject.authz.impl.DbAuthzGroupSqlOracle")) { + fields[pos++] = userId; + } + // populate values for fields for (String realmId : realms) { @@ -1581,7 +1604,9 @@ fields[pos++] = realmId; } fields[pos++] = lock; - fields[pos++] = userId; + if(!dbAuthzGroupSqlClassName.equals("org.sakaiproject.authz.impl.DbAuthzGroupSqlOracle")) { + fields[pos++] = userId; + } for (String realmId : realms) { fields[pos++] = realmId; @@ -2643,4 +2668,49 @@ return (result.size() > 0 ? result.get(0) : null); } } + + private String getRealmRoleKey(String roleName) { + Iterator itr = m_roleNameCache.iterator(); + while (itr.hasNext()) { + RealmRole realmRole = (RealmRole) itr.next(); + if (realmRole != null && realmRole.getName().equals(roleName)) { + return realmRole.getKey(); + } + } + return null; + } + + class RealmRole implements Comparable{ + private String name; + private String key; + + RealmRole(String name) { + this.name = name; + } + + RealmRole(String name, String key) { + this.name = name; + this.key = key; + } + + public String getName() { + return name; + } + + public void setName(String name) { + this.name = name; + } + + public String getKey() { + return key; + } + + public void setKey(String key) { + this.key = key; + } + + public int compareTo(RealmRole realmRole) { + return this.name.compareToIgnoreCase(realmRole.name); + } + } } Index: kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlDefault.java =================================================================== --- kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlDefault.java (revision 109324) +++ kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlDefault.java (working copy) @@ -33,35 +33,34 @@ return "select count(1) from SAKAI_REALM_FUNCTION where FUNCTION_NAME = ?"; } - public String getCountRealmRoleFunctionEndSql(String anonymousRole, String authorizationRole, boolean authorized, String inClause) + public String getCountRealmRoleFunctionEndSql(String anonymousRoleKey, String authorizationRoleKey, boolean authorized, String inClause) { + String roleKeys = authorized? authorizationRoleKey + "," + anonymousRoleKey : anonymousRoleKey; return " and FUNCTION_KEY in (select FUNCTION_KEY from SAKAI_REALM_FUNCTION where FUNCTION_NAME = ?) " + " and (ROLE_KEY in (select ROLE_KEY from SAKAI_REALM_RL_GR where ACTIVE = '1' and USER_ID = ? " + // granted in any of the grant or role realms " and REALM_KEY in (select REALM_KEY from SAKAI_REALM where " + inClause + ")) " - + " or ROLE_KEY in (select ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = '" + anonymousRole + "') " - + (authorized ? "or ROLE_KEY in (select ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = '" + authorizationRole + "') " : "") + ")"; + + " or ROLE_KEY in (" + roleKeys + ") " + + ")"; } - public String getCountRealmRoleFunctionSql(String anonymousRole, String authorizationRole, boolean authorized) + public String getCountRealmRoleFunctionSql(String anonymousRoleKey, String authorizationRoleKey, boolean authorized) { + String roleKeys = authorized? authorizationRoleKey + "," + anonymousRoleKey : anonymousRoleKey; return "select count(1) " + "from SAKAI_REALM_RL_FN MAINTABLE " + " LEFT JOIN SAKAI_REALM_RL_GR GRANTED_ROLES ON (MAINTABLE.REALM_KEY = GRANTED_ROLES.REALM_KEY AND " - + " MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS, SAKAI_REALM_ROLE ROLES, SAKAI_REALM_FUNCTION FUNCTIONS " + + " MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS, SAKAI_REALM_FUNCTION FUNCTIONS " + "where " - + - // our criteria - " (ROLES.ROLE_NAME in('" + anonymousRole + "'" + (authorized ? ",'" + authorizationRole + "'" : "") + ") or " - + " (GRANTED_ROLES.USER_ID = ? AND GRANTED_ROLES.ACTIVE = 1)) AND FUNCTIONS.FUNCTION_NAME = ? AND REALMS.REALM_ID in (?) " + + + " (MAINTABLE.ROLE_KEY in(" + roleKeys + ") or (GRANTED_ROLES.USER_ID = ? AND GRANTED_ROLES.ACTIVE = 1)) AND FUNCTIONS.FUNCTION_NAME = ? AND REALMS.REALM_ID in (?) " + // for the join - " AND MAINTABLE.REALM_KEY = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY AND MAINTABLE.ROLE_KEY = ROLES.ROLE_KEY "; + " AND MAINTABLE.REALM_KEY = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY"; } - public String getCountRealmRoleFunctionSql(String anonymousRole, String authorizationRole, boolean authorized, String inClause) + public String getCountRealmRoleFunctionSql(String anonymousRoleKey, String authorizationRoleKey, boolean authorized, String inClause) { - return "select count(1) from SAKAI_REALM_RL_FN " + "where REALM_KEY in (select REALM_KEY from SAKAI_REALM where " + inClause + ")" - + getCountRealmRoleFunctionEndSql(anonymousRole, authorizationRole, authorized, inClause); + return "select count(1) from SAKAI_REALM_RL_FN " + "where REALM_KEY in (select REALM_KEY from SAKAI_REALM where " + inClause + ")" + + getCountRealmRoleFunctionEndSql(anonymousRoleKey, authorizationRoleKey, authorized, inClause); } public String getCountRealmRoleSql() @@ -487,9 +486,14 @@ public String getSelectRealmRoleSql() { - return "select ROLE_NAME from SAKAI_REALM_ROLE"; + return "select ROLE_NAME, ROLE_KEY from SAKAI_REALM_ROLE"; } + public String getSelectRealmRoleKeySql() + { + return "select ROLE_NAME, ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = ?"; + } + public String getSelectRealmSize() { return "select COUNT(REALM_KEY) from SAKAI_REALM_RL_GR where REALM_KEY = ?"; Index: kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlMySql.java =================================================================== --- kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlMySql.java (revision 109324) +++ kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlMySql.java (working copy) @@ -108,11 +108,11 @@ return "DELETE SAKAI_REALM_ROLE_DESC FROM SAKAI_REALM_ROLE_DESC INNER JOIN SAKAI_REALM ON SAKAI_REALM_ROLE_DESC.REALM_KEY = SAKAI_REALM.REALM_KEY AND SAKAI_REALM.REALM_ID = ?"; } - public String getCountRealmRoleFunctionSql(String anonymousRole, String authorizationRole, boolean authorized, String inClause) + public String getCountRealmRoleFunctionSql(String anonymousRoleKey, String authorizationRoleKey, boolean authorized, String inClause) { return "select count(1) from SAKAI_REALM_RL_FN,SAKAI_REALM force index " + "(AK_SAKAI_REALM_ID) where SAKAI_REALM_RL_FN.REALM_KEY = SAKAI_REALM.REALM_KEY " + "and " + inClause - + getCountRealmRoleFunctionEndSql(anonymousRole, authorizationRole, authorized, inClause); + + getCountRealmRoleFunctionEndSql(anonymousRoleKey, authorizationRoleKey, authorized, inClause); } public String getSelectRealmRoleGroupUserIdSql(String inClause1, String inClause2) Index: kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlOracle.java =================================================================== --- kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlOracle.java (revision 109324) +++ kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlOracle.java (working copy) @@ -41,4 +41,11 @@ { return "insert into SAKAI_REALM_ROLE (ROLE_KEY, ROLE_NAME) values (SAKAI_REALM_ROLE_SEQ.NEXTVAL, ?)"; } + + public String getCountRealmRoleFunctionSql(String anonymousRoleKey, String authorizationRoleKey, boolean authorized, String inClause) + { + String roleKeys = authorized? authorizationRoleKey + "," + anonymousRoleKey : anonymousRoleKey; + return "SELECT 1 FROM SAKAI_REALM_RL_FN srrf, SAKAI_REALM_FUNCTION srf, (select realm_key, role_key from SAKAI_REALM_RL_GR where ACTIVE = '1' and USER_ID = ? union select -1 as realm_key, -1 as role_key from dual) srrg WHERE rownum = 1 AND srrf.realm_key in (select realm_key from SAKAI_REALM where " + inClause + ") AND srrf.function_key = srf.function_key AND srf.function_name = ? AND ((srrf.role_key = srrg.role_key AND srrg.realm_key in (select realm_key from SAKAI_REALM where " + inClause + ")) OR srrf.role_key in (" + roleKeys + "))"; + } + } Index: kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlDb2.java =================================================================== --- kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlDb2.java (revision 109324) +++ kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSqlDb2.java (working copy) @@ -36,17 +36,15 @@ return "insert into SAKAI_REALM_ROLE (ROLE_NAME) values(?)"; } - public String getCountRealmRoleFunctionSql(String anonymousRole, String authorizationRole, boolean authorized) + public String getCountRealmRoleFunctionSql(String anonymousRoleKey, String authorizationRoleKey, boolean authorized) { - return "select count(1) " + "from SAKAI_REALM_RL_FN MAINTABLE " + String roleKeys = authorized? authorizationRoleKey + "," + anonymousRoleKey : anonymousRoleKey; + return "select count(1) " + "from SAKAI_REALM_RL_FN MAINTABLE " + " LEFT JOIN SAKAI_REALM_RL_GR GRANTED_ROLES ON (MAINTABLE.REALM_KEY = GRANTED_ROLES.REALM_KEY AND " - + " MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS, SAKAI_REALM_ROLE ROLES, SAKAI_REALM_FUNCTION FUNCTIONS " + + " MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS, SAKAI_REALM_FUNCTION FUNCTIONS " + "where " - + - // our criteria - " (ROLES.ROLE_NAME in('" + anonymousRole + "'" + (authorized ? ",'" + authorizationRole + "'" : "") + ") or " + " (GRANTED_ROLES.USER_ID = ? AND GRANTED_ROLES.ACTIVE = '1')) AND FUNCTIONS.FUNCTION_NAME = ? AND REALMS.REALM_ID in (?) " + // for the join - " AND MAINTABLE.REALM_KEY = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY AND MAINTABLE.ROLE_KEY = ROLES.ROLE_KEY "; + " AND MAINTABLE.REALM_KEY = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY AND MAINTABLE.ROLE_KEY in (" + roleKeys + ")"; } } Index: kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSql.java =================================================================== --- kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSql.java (revision 109324) +++ kernel-impl/src/main/java/org/sakaiproject/authz/impl/DbAuthzGroupSql.java (working copy) @@ -31,11 +31,11 @@ { String getCountRealmFunctionSql(); - String getCountRealmRoleFunctionEndSql(String anonymousRole, String authorizationRole, boolean authorized, String inClause); + String getCountRealmRoleFunctionEndSql(String anonymousRoleKey, String authorizationRoleKey, boolean authorized, String inClause); - String getCountRealmRoleFunctionSql(String anonymousRole, String authorizationRole, boolean authorized); + String getCountRealmRoleFunctionSql(String anonymousRoleKey, String authorizationRoleKey, boolean authorized); - String getCountRealmRoleFunctionSql(String anonymousRole, String authorizationRole, boolean authorized, String inClause); + String getCountRealmRoleFunctionSql(String anonymousRoleKey, String authorizationRoleKey, boolean authorized, String inClause); String getCountRealmRoleSql(); @@ -140,6 +140,8 @@ String getSelectRealmRoleNameSql(); String getSelectRealmRoleSql(); + + String getSelectRealmRoleKeySql(); String getSelectRealmRolesSql(String inClause); Index: api/src/main/java/org/sakaiproject/memory/api/ehcache.xml =================================================================== --- api/src/main/java/org/sakaiproject/memory/api/ehcache.xml (revision 109324) +++ api/src/main/java/org/sakaiproject/memory/api/ehcache.xml (working copy) @@ -90,4 +90,13 @@ timeToLiveSeconds="7200" overflowToDisk="false" /> + + +