001/* 002 * (C) Copyright 2006-2011 Nuxeo SA (http://nuxeo.com/) and others. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 * 016 * Contributors: 017 * Florent Guillaume 018 */ 019 020package org.nuxeo.ecm.core.storage.sql.db; 021 022import java.io.Serializable; 023import java.sql.Connection; 024import java.sql.DriverManager; 025import java.sql.PreparedStatement; 026import java.sql.ResultSet; 027import java.sql.SQLException; 028import java.util.ArrayList; 029import java.util.Collection; 030import java.util.Collections; 031import java.util.HashSet; 032import java.util.List; 033import java.util.Set; 034import java.util.regex.Pattern; 035 036/** 037 * Functions used as stored procedures for H2. 038 * 039 * @author Florent Guillaume 040 */ 041public class EmbeddedFunctions { 042 043 protected static final String READ = "Read"; 044 045 protected static final String READ_VERSION = "ReadVersion"; 046 047 // for debug 048 private static boolean isLogEnabled() { 049 return false; 050 // return log.isTraceEnabled(); 051 } 052 053 // for debug 054 private static void logDebug(String message) { 055 // log.trace(message); 056 } 057 058 /** 059 * Checks if an id is a (strict) descendant of a given base id. 060 * 061 * @param id the id to check for 062 * @param baseId the base id 063 */ 064 public static boolean isInTree(Serializable id, Serializable baseId) throws SQLException { 065 try (Connection conn = DriverManager.getConnection("jdbc:default:connection")) { 066 return isInTree(conn, id, baseId); 067 } 068 } 069 070 /** 071 * Checks if an id is a (strict) descendant of a given base id. 072 * 073 * @param conn the connection to the database 074 * @param id the id to check for 075 * @param baseId the base id 076 */ 077 public static boolean isInTree(Connection conn, Serializable id, Serializable baseId) throws SQLException { 078 if (baseId == null || id == null || baseId.equals(id)) { 079 // containment check is strict 080 return false; 081 } 082 try (PreparedStatement ps = conn.prepareStatement("SELECT PARENTID, ISPROPERTY FROM HIERARCHY WHERE ID = ?")) { 083 do { 084 ps.setObject(1, id); 085 try (ResultSet rs = ps.executeQuery()) { 086 if (!rs.next()) { 087 // no such id 088 return false; 089 } 090 if (id instanceof String) { 091 id = rs.getString(1); 092 } else { 093 id = Long.valueOf(rs.getLong(1)); 094 } 095 if (rs.wasNull()) { 096 id = null; 097 } 098 boolean isProperty = rs.getBoolean(2); 099 if (isProperty) { 100 // a complex property is never in-tree 101 return false; 102 } 103 } 104 if (baseId.equals(id)) { 105 // found a match 106 return true; 107 } 108 } while (id != null); 109 // got to the root 110 return false; 111 } 112 } 113 114 /** 115 * Checks if access to a document is allowed. 116 * <p> 117 * This implements in SQL the ACL-based security policy logic. 118 * 119 * @param id the id of the document 120 * @param principals the allowed identities 121 * @param permissions the allowed permissions 122 * @deprecated since 11.3, unused 123 */ 124 @Deprecated 125 public static boolean isAccessAllowed(Serializable id, Set<String> principals, Set<String> permissions) 126 throws SQLException { 127 try (Connection conn = DriverManager.getConnection("jdbc:default:connection")) { 128 return isAccessAllowed(conn, id, principals, permissions); 129 } 130 } 131 132 /** 133 * @deprecated since 11.3, use other signature 134 */ 135 @Deprecated 136 public static boolean isAccessAllowed(Connection conn, Serializable id, Set<String> principals, 137 Set<String> permissions) throws SQLException { 138 return isAccessAllowed(conn, id, principals, permissions, true, true); 139 } 140 141 /** 142 * Checks if access to a document is allowed. 143 * <p> 144 * This implements in SQL the ACL-based security policy logic. 145 * 146 * @param conn the database connection 147 * @param id the id of the document 148 * @param principals the allowed identities 149 * @param permissions the allowed permissions 150 * @param disableVersionACL whether ACLs on a version are disabled 151 * @param disableReadVersionPermission whether the ReadVersion permission is disabled 152 * @since 11.3 153 */ 154 public static boolean isAccessAllowed(Connection conn, Serializable id, Set<String> principals, 155 Set<String> permissions, boolean disableVersionACL, boolean disableReadVersionPermission) 156 throws SQLException { 157 try (PreparedStatement psAcl = conn.prepareStatement( 158 "SELECT \"GRANT\", \"PERMISSION\", \"USER\" FROM \"ACLS\" WHERE ID = ? AND (STATUS IS NULL OR STATUS = 1) ORDER BY POS"); 159 PreparedStatement psHier = conn.prepareStatement( 160 "SELECT PARENTID, ISVERSION FROM HIERARCHY WHERE ID = ?"); 161 PreparedStatement psVer = conn.prepareStatement("SELECT VERSIONABLEID FROM VERSIONS WHERE ID = ?")) { 162 boolean replaceReadVersionPermission = false; 163 RowInfo rowInfo = null; // info about the row for the current id 164 if (disableVersionACL) { 165 // if it's a version, ignore its ACL and find the live doc 166 rowInfo = getRowInfo(psHier, psVer, id); 167 if (rowInfo.isVersion) { 168 replaceReadVersionPermission = !disableReadVersionPermission; 169 id = rowInfo.versionableId; 170 if (id == null) { 171 return false; 172 } 173 rowInfo = null; 174 } 175 } 176 do { 177 // check permissions at this level 178 Boolean access = getAccess(psAcl, id, principals, permissions, replaceReadVersionPermission); 179 if (access != null) { 180 return access; 181 } 182 // nothing conclusive found, repeat on the parent 183 if (rowInfo == null) { 184 rowInfo = getRowInfo(psHier, psVer, id); 185 } 186 if (rowInfo.isVersion) { 187 replaceReadVersionPermission = !disableReadVersionPermission; 188 id = rowInfo.versionableId; 189 } else { 190 id = rowInfo.parentId; 191 } 192 rowInfo = null; 193 } while (id != null); 194 // we've reached the root or a placeless document, deny access 195 return false; 196 } 197 } 198 199 protected static class RowInfo { 200 201 public Serializable parentId; 202 203 public boolean isVersion; 204 205 public Serializable versionableId; 206 } 207 208 protected static RowInfo getRowInfo(PreparedStatement psHier, PreparedStatement psVer, Serializable id) 209 throws SQLException { 210 psHier.setObject(1, id); 211 try (ResultSet rs = psHier.executeQuery()) { 212 RowInfo rowInfo = new RowInfo(); 213 if (rs.next()) { 214 rowInfo.parentId = (Serializable) rs.getObject(1); 215 rowInfo.isVersion = rs.getBoolean(2); 216 if (rowInfo.isVersion) { 217 psVer.setObject(1, id); 218 try (ResultSet rs2 = psVer.executeQuery()) { 219 if (rs2.next()) { 220 rowInfo.versionableId = (Serializable) rs2.getObject(1); 221 } 222 } 223 } 224 } 225 return rowInfo; 226 } 227 } 228 229 protected static Boolean getAccess(PreparedStatement psAcl, Serializable id, Set<String> principals, 230 Set<String> permissions, boolean replaceReadVersionPermission) throws SQLException { 231 psAcl.setObject(1, id); 232 try (ResultSet rs = psAcl.executeQuery()) { 233 while (rs.next()) { 234 boolean grant = rs.getShort(1) != 0; 235 String permission = rs.getString(2); 236 String user = rs.getString(3); 237 if (replaceReadVersionPermission && READ_VERSION.equals(permission)) { 238 permission = READ; 239 } 240 if (principals.contains(user) && permissions.contains(permission)) { 241 return grant; 242 } 243 } 244 return null; // no grant nor deny -- NOSONAR 245 } 246 } 247 248 /** 249 * Extracts the words from a string for simple fulltext indexing. 250 * 251 * @param string1 the first string 252 * @param string2 the second string 253 * @return a string with extracted words 254 * @deprecated unused 255 */ 256 @Deprecated(since = "11.1") 257 public static String parseFullText(String string1, String string2) { 258 Set<String> set = new HashSet<>(); 259 set.addAll(parseFullText(string1)); 260 set.addAll(parseFullText(string2)); 261 List<String> words = new ArrayList<>(set); 262 Collections.sort(words); 263 return join(words, ' '); 264 } 265 266 /** @deprecated unused */ 267 @Deprecated(since = "11.1") 268 protected static Set<String> parseFullText(String string) { 269 if (string == null) { 270 return Collections.emptySet(); 271 } 272 Set<String> set = new HashSet<>(); 273 for (String word : wordPattern.split(string)) { 274 String w = parseWord(word); 275 if (w != null) { 276 set.add(w); 277 } 278 } 279 return set; 280 } 281 282 /** 283 * Checks if the passed query expression matches the fulltext. 284 * 285 * @param fulltext the fulltext, space-separated words 286 * @param query a list of space-separated words 287 * @return {@code true} if all the words are in the fulltext 288 * @deprecated unused 289 */ 290 @Deprecated(since = "11.1") 291 protected static boolean matchesFullText(String fulltext, String query) { 292 if (fulltext == null || query == null) { 293 return false; 294 } 295 Set<String> words = split(query.toLowerCase(), ' '); 296 Set<String> filtered = new HashSet<>(); 297 for (String word : words) { 298 if (!wordPattern.matcher(word).matches()) { 299 filtered.add(word); 300 } 301 } 302 words = filtered; 303 if (words.isEmpty()) { 304 return false; 305 } 306 Set<String> fulltextWords = split(fulltext.toLowerCase(), ' '); 307 for (String word : words) { 308 if (word.endsWith("*") || word.endsWith("%")) { 309 // prefix match 310 String prefix = word.substring(0, word.length() - 2); 311 boolean match = false; 312 for (String candidate : fulltextWords) { 313 if (candidate.startsWith(prefix)) { 314 match = true; 315 break; 316 } 317 } 318 if (!match) { 319 return false; 320 } 321 } else { 322 if (!fulltextWords.contains(word)) { 323 return false; 324 } 325 } 326 } 327 return true; 328 } 329 330 // ----- simple parsing, don't try to be exhaustive ----- 331 332 private static final Pattern wordPattern = Pattern.compile("[\\s\\p{Punct}]+"); 333 334 private static final String UNACCENTED = "aaaaaaaceeeeiiii\u00f0nooooo\u00f7ouuuuy\u00fey"; 335 336 private static final String STOPWORDS = "a an are and as at be by for from how " 337 + "i in is it of on or that the this to was what when where who will with " 338 + "car donc est il ils je la le les mais ni nous or ou pour tu un une vous " + "www com net org"; 339 340 private static final Set<String> stopWords = new HashSet<>(split(STOPWORDS, ' ')); 341 342 /** @deprecated unused */ 343 @Deprecated(since = "11.1") 344 public static final String parseWord(String string) { 345 int len = string.length(); 346 if (len < 3) { 347 return null; 348 } 349 StringBuilder sb = new StringBuilder(len); 350 for (int i = 0; i < len; i++) { 351 char c = Character.toLowerCase(string.charAt(i)); 352 if (c == '\u00e6') { 353 sb.append("ae"); 354 } else if (c >= '\u00e0' && c <= '\u00ff') { 355 sb.append(UNACCENTED.charAt((c) - 0xe0)); 356 } else if (c == '\u0153') { 357 sb.append("oe"); 358 } else { 359 sb.append(c); 360 } 361 } 362 // simple heuristic to remove plurals 363 int l = sb.length(); 364 if (l > 3 && sb.charAt(l - 1) == 's') { 365 sb.setLength(l - 1); 366 } 367 String word = sb.toString(); 368 if (stopWords.contains(word)) { 369 return null; 370 } 371 return word; 372 } 373 374 // ----- utility functions ----- 375 376 public static Set<String> split(String string) { 377 return split(string, '|'); 378 } 379 380 public static Set<String> split(String string, char sep) { 381 int len = string.length(); 382 if (len == 0) { 383 return Collections.emptySet(); 384 } 385 int end = string.indexOf(sep); 386 if (end == -1) { 387 return Collections.singleton(string); 388 } 389 Set<String> set = new HashSet<>(); 390 int start = 0; 391 do { 392 String segment = string.substring(start, end); 393 set.add(segment); 394 start = end + 1; 395 end = string.indexOf(sep, start); 396 } while (end != -1); 397 if (start < len) { 398 set.add(string.substring(start)); 399 } else { 400 set.add(""); 401 } 402 return set; 403 } 404 405 /** @deprecated unused */ 406 @Deprecated(since = "11.1") 407 private static final String join(Collection<String> strings, char sep) { 408 if (strings == null || strings.isEmpty()) { 409 return ""; 410 } 411 int size = 0; 412 for (String word : strings) { 413 size += word.length() + 1; 414 } 415 StringBuilder sb = new StringBuilder(size); 416 for (String word : strings) { 417 sb.append(word); 418 sb.append(sep); 419 } 420 sb.setLength(size - 1); 421 return sb.toString(); 422 } 423 424}