Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @ 219:4572f0694fde

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved DatabaseMetaData methods getTablePrivileges() and getColumnPrivileges() by returning also any combination of privileges for the table or column in the PRIVILEGE result column. Previously only single privileges (SELECT or UPDATE or INSERT or DELETE or EXECUTE or GRANT) would be returned.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 26 Apr 2018 18:43:55 +0200 (2018-04-26)
parents 5cc7101c5c8d
children 34f9ddd1a4e5
comparison
equal deleted inserted replaced
218:5cc7101c5c8d 219:4572f0694fde
2304 String schemaPattern, 2304 String schemaPattern,
2305 String tableNamePattern, 2305 String tableNamePattern,
2306 String columnNamePattern 2306 String columnNamePattern
2307 ) throws SQLException 2307 ) throws SQLException
2308 { 2308 {
2309 boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists();
2309 StringBuilder query = new StringBuilder(1100); 2310 StringBuilder query = new StringBuilder(1100);
2310 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 2311 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2311 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + 2312 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
2312 "\"tables\".\"name\" AS \"TABLE_NAME\", " + 2313 "\"tables\".\"name\" AS \"TABLE_NAME\", " +
2313 "\"columns\".\"name\" AS \"COLUMN_NAME\", " + 2314 "\"columns\".\"name\" AS \"COLUMN_NAME\", " +
2314 "\"grantors\".\"name\" AS \"GRANTOR\", " + 2315 "\"grantors\".\"name\" AS \"GRANTOR\", " +
2315 "\"grantees\".\"name\" AS \"GRANTEE\", " + 2316 "\"grantees\".\"name\" AS \"GRANTEE\", ")
2317 .append(usePrivilege_codesTable ? "\"privilege_codes\".\"privilege_code_name\"" :
2316 "cast(CASE \"privileges\".\"privileges\" " + 2318 "cast(CASE \"privileges\".\"privileges\" " +
2317 "WHEN 1 THEN 'SELECT' " + 2319 "WHEN 1 THEN 'SELECT' " +
2318 "WHEN 2 THEN 'UPDATE' " + 2320 "WHEN 2 THEN 'UPDATE' " +
2319 "WHEN 4 THEN 'INSERT' " + 2321 "WHEN 4 THEN 'INSERT' " +
2320 "WHEN 8 THEN 'DELETE' " + 2322 "WHEN 8 THEN 'DELETE' " +
2321 "WHEN 16 THEN 'EXECUTE' " + 2323 "WHEN 16 THEN 'EXECUTE' " +
2322 "WHEN 32 THEN 'GRANT' " + 2324 "WHEN 32 THEN 'GRANT' " +
2323 "ELSE NULL " + 2325 "ELSE NULL " +
2324 "END AS varchar(7)) AS \"PRIVILEGE\", " + 2326 "END AS varchar(7))").append(" AS \"PRIVILEGE\", " +
2325 "cast(CASE \"privileges\".\"grantable\" " + 2327 "cast(CASE \"privileges\".\"grantable\" " +
2326 "WHEN 0 THEN 'NO' " + 2328 "WHEN 0 THEN 'NO' " +
2327 "WHEN 1 THEN 'YES' " + 2329 "WHEN 1 THEN 'YES' " +
2328 "ELSE NULL " + 2330 "ELSE NULL " +
2329 "END AS varchar(3)) AS \"IS_GRANTABLE\" " + 2331 "END AS varchar(3)) AS \"IS_GRANTABLE\" " +
2330 "FROM \"sys\".\"privileges\", " + 2332 "FROM \"sys\".\"privileges\", " +
2331 "\"sys\".\"tables\", " + 2333 "\"sys\".\"tables\", " +
2332 "\"sys\".\"schemas\", " + 2334 "\"sys\".\"schemas\", " +
2333 "\"sys\".\"columns\", " + 2335 "\"sys\".\"columns\", " +
2334 "\"sys\".\"auths\" AS \"grantors\", " + 2336 "\"sys\".\"auths\" AS \"grantors\", " +
2335 "\"sys\".\"auths\" AS \"grantees\" " + 2337 "\"sys\".\"auths\" AS \"grantees\" ");
2336 "WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " + 2338 if (usePrivilege_codesTable) {
2339 query.append(", \"sys\".\"privilege_codes\" ");
2340 }
2341 query.append("WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " +
2337 "AND \"columns\".\"table_id\" = \"tables\".\"id\" " + 2342 "AND \"columns\".\"table_id\" = \"tables\".\"id\" " +
2338 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + 2343 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2339 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " + 2344 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
2340 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\""); 2345 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
2346 if (usePrivilege_codesTable) {
2347 query.append(" AND \"privileges\".\"privileges\" = \"privilege_codes\".\"privilege_code_id\"");
2348 }
2341 2349
2342 if (catalog != null && !catalog.isEmpty()) { 2350 if (catalog != null && !catalog.isEmpty()) {
2343 // none empty catalog selection. 2351 // none empty catalog selection.
2344 // as we do not support catalogs this always results in no rows returned 2352 // as we do not support catalogs this always results in no rows returned
2345 query.append(" AND 1 = 0"); 2353 query.append(" AND 1 = 0");
2393 String catalog, 2401 String catalog,
2394 String schemaPattern, 2402 String schemaPattern,
2395 String tableNamePattern 2403 String tableNamePattern
2396 ) throws SQLException 2404 ) throws SQLException
2397 { 2405 {
2406 boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists();
2398 StringBuilder query = new StringBuilder(1000); 2407 StringBuilder query = new StringBuilder(1000);
2399 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 2408 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2400 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + 2409 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
2401 "\"tables\".\"name\" AS \"TABLE_NAME\", " + 2410 "\"tables\".\"name\" AS \"TABLE_NAME\", " +
2402 "\"grantors\".\"name\" AS \"GRANTOR\", " + 2411 "\"grantors\".\"name\" AS \"GRANTOR\", " +
2403 "\"grantees\".\"name\" AS \"GRANTEE\", " + 2412 "\"grantees\".\"name\" AS \"GRANTEE\", ")
2413 .append(usePrivilege_codesTable ? "\"privilege_codes\".\"privilege_code_name\"" :
2404 "cast(CASE \"privileges\".\"privileges\" " + 2414 "cast(CASE \"privileges\".\"privileges\" " +
2405 "WHEN 1 THEN 'SELECT' " + 2415 "WHEN 1 THEN 'SELECT' " +
2406 "WHEN 2 THEN 'UPDATE' " + 2416 "WHEN 2 THEN 'UPDATE' " +
2407 "WHEN 4 THEN 'INSERT' " + 2417 "WHEN 4 THEN 'INSERT' " +
2408 "WHEN 8 THEN 'DELETE' " + 2418 "WHEN 8 THEN 'DELETE' " +
2409 "WHEN 16 THEN 'EXECUTE' " + 2419 "WHEN 16 THEN 'EXECUTE' " +
2410 "WHEN 32 THEN 'GRANT' " + 2420 "WHEN 32 THEN 'GRANT' " +
2411 "ELSE NULL " + 2421 "ELSE NULL " +
2412 "END AS varchar(7)) AS \"PRIVILEGE\", " + 2422 "END AS varchar(7))").append(" AS \"PRIVILEGE\", " +
2413 "cast(CASE \"privileges\".\"grantable\" " + 2423 "cast(CASE \"privileges\".\"grantable\" " +
2414 "WHEN 0 THEN 'NO' " + 2424 "WHEN 0 THEN 'NO' " +
2415 "WHEN 1 THEN 'YES' " + 2425 "WHEN 1 THEN 'YES' " +
2416 "ELSE NULL " + 2426 "ELSE NULL " +
2417 "END AS varchar(3)) AS \"IS_GRANTABLE\" " + 2427 "END AS varchar(3)) AS \"IS_GRANTABLE\" " +
2418 "FROM \"sys\".\"privileges\", " + 2428 "FROM \"sys\".\"privileges\", " +
2419 "\"sys\".\"tables\", " + 2429 "\"sys\".\"tables\", " +
2420 "\"sys\".\"schemas\", " + 2430 "\"sys\".\"schemas\", " +
2421 "\"sys\".\"auths\" AS \"grantors\", " + 2431 "\"sys\".\"auths\" AS \"grantors\", " +
2422 "\"sys\".\"auths\" AS \"grantees\" " + 2432 "\"sys\".\"auths\" AS \"grantees\" ");
2423 "WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " + 2433 if (usePrivilege_codesTable) {
2434 query.append(", \"sys\".\"privilege_codes\" ");
2435 }
2436 query.append("WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " +
2424 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + 2437 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2425 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " + 2438 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
2426 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\""); 2439 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
2440 if (usePrivilege_codesTable) {
2441 query.append(" AND \"privileges\".\"privileges\" = \"privilege_codes\".\"privilege_code_id\"");
2442 }
2427 2443
2428 if (catalog != null && !catalog.isEmpty()) { 2444 if (catalog != null && !catalog.isEmpty()) {
2429 // none empty catalog selection. 2445 // none empty catalog selection.
2430 // as we do not support catalogs this always results in no rows returned 2446 // as we do not support catalogs this always results in no rows returned
2431 query.append(" AND 1 = 0"); 2447 query.append(" AND 1 = 0");