|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import org.apache.derby.tools.ij;
25 import org.apache.derbyTesting.functionTests.util.TestUtil;
26 import org.apache.derby.tools.JDBCDisplayUtil;
27 import org.apache.derby.iapi.services.io.FormatableBitSet;
28
29 import java.sql.*;
30
31 import java.util.ArrayList  ;
32 import java.util.HashMap ;
33
34 public class grantRevoke
35 {
36 private static final User[] users = { new User( "DAN", "MakeItFaster"),
37 new User( "KREG", "visualWhat?"),
38 new User( "JEFF", "HomeRun61"),
39 new User( "AMES", "AnyVolunteer?"),
40 new User( "JERRY", "SacreBleu"),
41 new User( "HOWARDR", "IamBetterAtTennis"),
42 new User( "FRANCOIS", "paceesalute"),
43 new User( "JAMIE", "MrNamePlates")};
44 private static final User owner = new User( "OWNER", "BigCheese");
45 private static final User publicUser = new User( "PUBLIC", null);
46 private DatabaseMetaData dbmd;
47 private static boolean routineCalled = false;
48 private int errorCount = 0;
49
50 public static void main(String [] args)
51 {
52 grantRevoke tester = new grantRevoke();
53 tester.doIt( args);
54 }
55
56 private void doIt( String [] args)
57 {
58 try
59 {
60 ij.getPropertyArg(args);
63
64 System.setProperty("ij.password", "BigCheese");
66 owner.setConnection( ij.startJBMS());
67 dbmd = owner.getConnection().getMetaData();
68
69 runGrantTests();
70 if( errorCount == 0)
72 runRevokeTests( );
73
74 System.out.println( "Error cases.");
75 testErrors( stdErrorCases);
76 }
77 catch (SQLException sqle) {
78 unexpectedException( null, sqle);
79 }
80 catch (Throwable t) {
81 errorCount++;
82 t.printStackTrace(System.out);
83 }
84 if( errorCount == 0)
85 System.out.println( "PASSED.");
86 else
87 System.out.println( "FAILED. " + errorCount + ((errorCount > 1) ? " errors" : " error"));
88 System.exit( 0);
89 }
91 private void runGrantTests( ) throws SQLException
92 {
93 setup( grantTestSetupSQL);
94
95 testOneStatement( "Grant",
97 "grant select on s1.t1 to " + users[0].name,
98 new PrivCheck[] { new SelectPrivCheck( true, false, users[0], "S1", "T1", null),
99 new SelectPrivCheck( false, false, users[1], "S1", "T1", null)},
100 "simple grant");
101 owner.stmt.executeUpdate( "set schema s2");
103 testOneStatement( "Grant",
104 "grant all privileges on t1 to " + users[1].name + "," + users[2].name,
105 new PrivCheck[] { new SelectPrivCheck( true, false, users[1], "S2", "T1", null),
106 new DeletePrivCheck( true, false, users[1], "S2", "T1"),
107 new InsertPrivCheck( true, false, users[1], "S2", "T1"),
108 new UpdatePrivCheck( true, false, users[1], "S2", "T1", null),
109 new ReferencesPrivCheck( true, false, users[1], "S2", "T1", null),
110 new TriggerPrivCheck( true, false, users[1], "S2", "T1"),
111
112 new SelectPrivCheck( true, false, users[2], "S2", "T1", null),
113 new DeletePrivCheck( true, false, users[2], "S2", "T1"),
114 new InsertPrivCheck( true, false, users[2], "S2", "T1"),
115 new UpdatePrivCheck( true, false, users[2], "S2", "T1", null),
116 new ReferencesPrivCheck( true, false, users[2], "S2", "T1", null),
117 new TriggerPrivCheck( true, false, users[2], "S2", "T1"),
118
119 new SelectPrivCheck( false, false, users[0], "S2", "T1", null),
120 new DeletePrivCheck( false, false, users[0], "S2", "T1"),
121 new InsertPrivCheck( false, false, users[0], "S2", "T1"),
122 new UpdatePrivCheck( false, false, users[0], "S2", "T1", null),
123 new ReferencesPrivCheck( false, false, users[0], "S2", "T1", null),
124 new TriggerPrivCheck( false, false, users[0], "S2", "T1"),
125
126 new SelectPrivCheck( false, false, users[1], "S1", "T1", null),
127 new SelectPrivCheck( false, false, users[1], "S2", "T2", null),
128 },
129 "all privileges, multiple users (2)");
130 testOneStatement( "Grant",
132 "grant select(c1),update(c3,c2),references(c3,c1,c2) on s1.t1 to " + users[3].name,
133 new PrivCheck[] { new SelectPrivCheck( true, false, users[3], "S1", "T1",
134 new String [] {"C1"}),
135 new SelectPrivCheck( false, false, users[3], "S1", "T1",
136 new String [] {"C2"}),
137 new SelectPrivCheck( false, false, users[3], "S1", "T1",
138 new String [] {"C3"}),
139 new SelectPrivCheck( false, false, users[3], "S1", "T1", null),
140 new UpdatePrivCheck( true, false, users[3], "S1", "T1",
141 new String [] {"C2","C3"}),
142 new UpdatePrivCheck( false, false, users[3], "S1", "T1",
143 new String [] {"C1"}),
144 new ReferencesPrivCheck( true, false, users[3], "S1", "T1",
145 new String [] {"C1","C2","C3"}),
146 new ReferencesPrivCheck( false, false, users[3], "S1", "T1", null)
147 },
148 "Column privileges");
149 testOneStatement( "Grant",
151 "grant execute on function s1.f1 to " + users[0].name,
152 new PrivCheck[] { new ExecutePrivCheck( true, false, users[0], "S1", "F1", true),
153 new ExecutePrivCheck( false, false, users[0], "S1", "F1", false),
154 new ExecutePrivCheck( false, false, users[1], "S1", "F1", true),
155 },
156 "execute on function with like named procedure");
157 testOneStatement( "Grant",
159 "grant execute on procedure s1.p1 to " + users[0].name,
160 new PrivCheck[] { new ExecutePrivCheck( true, false, users[0], "S1", "P1", false),
161 new ExecutePrivCheck( false, false, users[1], "S1", "P1", false),
162 },
163 "execute on procedure");
164
165 testOneStatement( "Grant",
167 "grant select, references(c1) on table s2.t2 to public",
168 new PrivCheck[] { new SelectPrivCheck( true, true, publicUser, "S2", "T2", null),
169 new SelectPrivCheck( false, true, users[1], "S2", "T2", null),
170 new SelectPrivCheck( false, false, publicUser, "S2", "NOPERMS", null),
171 new UpdatePrivCheck( false, false, publicUser, "S2", "T2", null),
172 new ReferencesPrivCheck( true, true, publicUser, "S2", "T2",
173 new String [] {"C1"}),
174 new ReferencesPrivCheck( false, false, publicUser, "S2", "T2", null)
175 },
176 "PUBLIC table privileges");
177 testOneStatement( "Grant",
178 "grant execute on procedure s1.p1 to Public",
179 new PrivCheck[] { new ExecutePrivCheck( true, true, publicUser, "S1", "P1", false),
180 new ExecutePrivCheck( true, true, users[0], "S1", "P1", false),
182 new ExecutePrivCheck( false, true, users[1], "S1", "P1", false)
184 },
185 "PUBLIC routine privileges");
186
187 testGrantRollbackAndCommit( );
188
189 System.out.println( "Test metadata supports methods.");
190 if( dbmd.supportsCatalogsInPrivilegeDefinitions())
191 reportFailure( "DatabaseMetaData.supportsCatalogsInPrivilegeDefinitions returned true.");
192 if( ! dbmd.supportsSchemasInPrivilegeDefinitions())
193 reportFailure( "DatabaseMetaData.supportsSchemasInPrivilegeDefinitions returned false.");
194 }
196 private void testOneStatement( String stmtName,
197 String sql,
198 PrivCheck[] checks,
199 String testLabel)
200 {
201 testOneStatement( stmtName, sql, checks, true, testLabel);
202 }
203
204 private void testOneStatement( String stmtName,
205 String sql,
206 PrivCheck[] checks,
207 boolean runStatements,
208 String testLabel)
209 {
210 System.out.println( stmtName + " test: " + testLabel);
211 try
212 {
213 owner.stmt.executeUpdate( sql);
214 runChecks( checks, runStatements, false, testLabel);
215 }
216 catch( SQLException sqle)
217 {
218 unexpectedException( testLabel, sqle);
219 }
220 }
222 private void setup( String [] setupSQL) throws SQLException
223 {
224 boolean autoCommit = owner.getConnection().getAutoCommit();
225 owner.getConnection().setAutoCommit( false);
226 for( int i = 0; i < setupSQL.length; i++)
227 owner.stmt.executeUpdate( setupSQL[i]);
228 owner.getConnection().commit();
229 owner.getConnection().setAutoCommit( autoCommit);
230 }
232 private void testGrantRollbackAndCommit( )
233 {
234 System.out.println( "Test grant rollback and commit");
235 PrivCheck[] preExistingPrivChecks =
236 new PrivCheck[] { new SelectPrivCheck( true, true, publicUser, "S2", "T2", null),
237 new UpdatePrivCheck( false, false, publicUser, "S2", "T2", null)
238
239 };
240 PrivCheck[] tableChecks1 =
241 new PrivCheck[] { new SelectPrivCheck( true, false, users[0], "S2", "T3", new String [] {"C2"}),
242 new DeletePrivCheck( true, false, users[0], "S2", "T3")
243 };
244 PrivCheck[] tableChecks2 =
245 new PrivCheck[] { new TriggerPrivCheck( true, true, publicUser, "S2", "T2")
246 };
247 PrivCheck[] routineChecks =
248 new PrivCheck[] { new ExecutePrivCheck( true, false, users[0], "S2", "F1", true)};
249 PrivCheck[] noChecks = new PrivCheck[0];
250
251 try
252 {
253 runChecks( preExistingPrivChecks, false, "transaction test pre-existing table privileges");
254 owner.getConnection().setAutoCommit( false);
255 for( int i = 0; i < 2; i++)
256 {
257 testOneStatement( "Grant",
260 "grant select(c2), delete on s2.t3 to " + users[0].name,
261 tableChecks1, false,
262 "table privileges in transaction");
263 testOneStatement( "Grant",
265 "grant trigger on s2.t2 to public",
266 tableChecks2, false,
267 "table privileges in transaction");
268 testOneStatement( "Grant",
269 "grant execute on function s2.f1 to " + users[0].name,
270 routineChecks, false,
271 "routine privileges in transaction");
272 if( i == 0)
273 owner.getConnection().rollback();
274 else
275 owner.getConnection().commit();
276 runChecks( tableChecks1, i == 0, ((i == 0) ? "rolled back" : "committed") + " table privileges");
277 runChecks( tableChecks2, i == 0, ((i == 0) ? "rolled back" : "committed") + " table privileges");
278 runChecks( routineChecks, i == 0, ((i == 0) ? "rolled back" : "committed") + " routine privileges");
279 runChecks( preExistingPrivChecks, false, "transaction test pre-existing table privileges");
280 }
281 }
282 catch( SQLException sqle)
283 {
284 unexpectedException( "rollback and commit test", sqle);
285 }
286 }
288 private static final String [] grantTestSetupSQL =
289 {
290 "create schema s1",
291 "create schema s2",
292 "create table s1.t1(c1 int, c2 int, c3 int)",
293 "create table s2.t1(c1 int, c2 int, c3 int)",
294 "create table s2.t2(c1 int, c2 int, c3 int)",
295 "create table s2.t3(c1 int, c2 int, c3 int)",
296 "create table s2.noPerms(c1 int, c2 int, c3 int)",
297 "create function s1.f1() returns int" +
298 " language java parameter style java" +
299 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1F1'" +
300 " no sql called on null input",
301 "create function s2.f1() returns int" +
302 " language java parameter style java" +
305 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1a'" +
306 " no sql called on null input",
307
312
313 "create function s2.f2( p1 char(8), p2 integer) returns int" +
326 " language java parameter style java" +
327 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F2'" +
328 " no sql called on null input",
329
330
333 "create procedure s1.f1( )" +
334 " language java parameter style java" +
335 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1F1P'" +
336 " no sql called on null input",
337 "create procedure s1.p1( )" +
338 " language java parameter style java" +
339 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1P1'" +
340 " no sql called on null input"
341 };
342
343 public static int s1F1()
344 {
345 routineCalled = true;
346 return 1;
347 }
348 public static int s2F1a()
349 {
350 routineCalled = true;
351 return 1;
352 }
353 public static int s2F1b( String s)
354 {
355 routineCalled = true;
356 return 1;
357 }
358 public static int s2F1c( String s1, String s2)
359 {
360 routineCalled = true;
361 return 1;
362 }
363 public static int s2F1d( int i)
364 {
365 routineCalled = true;
366 return 1;
367 }
368 public static int s2F2()
369 {
370 routineCalled = true;
371 return 1;
372 }
373 public static void s1F1P( )
374 {
375 routineCalled = true;
376 }
377 public static void s1P1( )
378 {
379 routineCalled = true;
380 }
381
382 private void runRevokeTests( ) throws SQLException
383 {
384 setup( revokeTestSetupSQL);
385 owner.getConnection().setAutoCommit( true);
386
387 PrivCheck[] privCheck1 = { new SelectPrivCheck( false, false, users[0], "R1", "T1", null),
389 new SelectPrivCheck( false, false, users[0], "R1", "T1", new String [] {"C2"}),
390 new UpdatePrivCheck( false, false, users[1], "R1", "T1", new String [] {"C1", "C3"}),
391 new ExecutePrivCheck( false, false, users[0], "R1", "P1", false)};
392 owner.stmt.executeUpdate( "set schema r1");
393 runChecks( privCheck1, false, "Initial revoke test conditions");
394 testOneStatement( "Revoke",
395 "revoke all Privileges on t1 from " + users[0].name,
396 privCheck1,
397 "all with no permissions");
398 testOneStatement( "Revoke",
399 "revoke execute on procedure r1.p1 from " + users[0].name + " restrict",
400 privCheck1,
401 "execute with no permissions");
402 testOneStatement( "Revoke",
403 "revoke select(c2), update(c1,c3) on table t1 from " + users[1].name,
404 privCheck1,
405 "column with no permissions");
406
407 owner.stmt.executeUpdate( "grant all privileges on r2.t1 to " + users[0].name);
409 owner.stmt.executeUpdate( "grant update(c3) on r2.t1 to " + users[0].name);
410 testOneStatement( "Revoke",
411 "revoke update on r2.t1 from " + users[0].name,
412 new PrivCheck[] { new SelectPrivCheck( true, false, users[0], "R2", "T1", null),
413 new UpdatePrivCheck( false, false, users[0], "R2", "T1", null),
414 new UpdatePrivCheck( false, false, users[0], "R2", "T1",
415 new String [] {"C3"}),
416 new InsertPrivCheck( true, false, users[0], "R2", "T1"),
417 new DeletePrivCheck( true, false, users[0], "R2", "T1"),
418 new ReferencesPrivCheck( true, false, users[0], "R2", "T1", null),
419 new TriggerPrivCheck( true, false, users[0], "R2", "T1")
420 },
421 "single table privilege, one user");
422 testOneStatement( "Revoke",
423 "revoke all privileges on r2.t1 from " + users[0].name,
424 new PrivCheck[] { new SelectPrivCheck( false, false, users[0], "R2", "T1", null),
425 new UpdatePrivCheck( false, false, users[0], "R2", "T1", null),
426 new UpdatePrivCheck( false, false, users[0], "R2", "T1",
427 new String [] {"C3"}),
428 new InsertPrivCheck( false, false, users[0], "R2", "T1"),
429 new DeletePrivCheck( false, false, users[0], "R2", "T1"),
430 new ReferencesPrivCheck( false, false, users[0], "R2", "T1", null),
431 new TriggerPrivCheck( false, false, users[0], "R2", "T1")
432 },
433 "single table privilege, one user");
434
435 owner.stmt.executeUpdate( "grant select on t1 to " + users[0].name + "," + users[1].name + "," + users[2].name);
438 owner.stmt.executeUpdate( "grant update(c1,c2,c3) on t1 to " + users[0].name);
439 owner.stmt.executeUpdate( "grant update(c3) on t1 to " + users[1].name);
440 owner.stmt.executeUpdate( "grant trigger on t1 to " + users[0].name);
441 runChecks( new PrivCheck[] { new SelectPrivCheck( true, false, users[0], "R1", "T1", null),
442 new SelectPrivCheck( true, false, users[1], "R1", "T1", null),
443 new SelectPrivCheck( true, false, users[2], "R1", "T1", null),
444 new UpdatePrivCheck( true, false, users[0], "R1", "T1",
445 new String [] {"C1", "C2", "C3"}),
446 new UpdatePrivCheck( true, false, users[1], "R1", "T1",
447 new String [] {"C3"}),
448 new TriggerPrivCheck( true, false, users[0], "R1", "T1"),
449 new TriggerPrivCheck( false, false, users[1], "R1", "T1")
450 },
451 false,
452 "setup (1)");
453 testOneStatement( "Revoke",
454 "revoke select, update(c2,c3) on t1 from " + users[0].name + ","
455 + users[1].name + "," + users[2].name,
456 new PrivCheck[] { new SelectPrivCheck( false, false, users[0], "R1", "T1", null),
457 new SelectPrivCheck( false, false, users[1], "R1", "T1", null),
458 new SelectPrivCheck( false, false, users[2], "R1", "T1", null),
459 new UpdatePrivCheck( true, false, users[0], "R1", "T1",
460 new String [] {"C1"}),
461 new UpdatePrivCheck( false, false, users[0], "R1", "T1",
462 new String [] {"C2", "C3"}),
463 new UpdatePrivCheck( false, false, users[1], "R1", "T1",
464 new String [] {"C1", "C2", "C3"}),
465 new TriggerPrivCheck( true, false, users[0], "R1", "T1"),
466 new TriggerPrivCheck( false, false, users[1], "R1", "T1")
467 },
468 "multiple table permissions, multiple users");
469 testOneStatement( "Revoke",
470 "revoke update on r1.t1 from " + users[0].name,
471 new PrivCheck[] { new UpdatePrivCheck( false, false, users[0], "R1", "T1",
472 new String [] {"C1"}),
473 new UpdatePrivCheck( false, false, users[0], "R1", "T1", null)
474 },
475 "table privilege implies column privileges");
476 testOneStatement( "Revoke",
478 "revoke all privileges on r1.t1 from " + users[0].name,
479 new PrivCheck[] { new UpdatePrivCheck( false, false, users[0], "R1", "T1",
480 new String [] {"C1", "C2", "C3"}),
481 new TriggerPrivCheck( false, false, users[0], "R1", "T1")
482 },
483 "all privileges");
484
485 owner.stmt.executeUpdate( "grant execute on function f1 to " + users[0].name + "," + users[1].name);
487 owner.stmt.executeUpdate( "grant execute on procedure f1 to " + users[0].name);
488 runChecks( new PrivCheck[] { new ExecutePrivCheck( true, false, users[0], "R1", "F1", true),
489 new ExecutePrivCheck( true, false, users[1], "R1", "F1", true),
490 new ExecutePrivCheck( true, false, users[0], "R1", "F1", false)},
491 false,
492 "setup for revoke execute");
493 testOneStatement( "Revoke",
494 "revoke execute on function f1 from " + users[0].name + " restrict",
495 new PrivCheck[] { new ExecutePrivCheck( false, false, users[0], "R1", "F1", true),
496 new ExecutePrivCheck( true, false, users[1], "R1", "F1", true),
497 new ExecutePrivCheck( true, false, users[0], "R1", "F1", false)},
498 "function execute permission");
499
500 testOneStatement( "Revoke",
502 "revoke execute on procedure f1 from " + users[0].name + " restrict",
503 new PrivCheck[] { new ExecutePrivCheck( false, false, users[0], "R1", "F1", true),
504 new ExecutePrivCheck( true, false, users[1], "R1", "F1", true),
505 new ExecutePrivCheck( false, false, users[0], "R1", "F1", false)},
506 "function execute permission");
507
508 owner.stmt.executeUpdate( "grant select, delete on r2.t1 to public");
510 owner.stmt.executeUpdate( "grant select, delete on r2.t1 to " + users[1].name + "," + users[2].name);
511 owner.stmt.executeUpdate( "grant update(c1,c3) on r2.t1 to public");
512 owner.stmt.executeUpdate( "grant update(c1,c3) on r2.t1 to " + users[1].name + "," + users[2].name);
513 runChecks( new PrivCheck[] { new SelectPrivCheck( true, true, users[1], "R2", "T1", null),
514 new SelectPrivCheck( true, true, users[2], "R2", "T1", null),
515 new SelectPrivCheck( true, true, publicUser, "R2", "T1", null),
516 new DeletePrivCheck( true, true, users[1], "R2", "T1"),
517 new DeletePrivCheck( true, true, users[2], "R2", "T1"),
518 new DeletePrivCheck( true, true, publicUser, "R2", "T1"),
519 new UpdatePrivCheck( true, true, users[1], "R2", "T1",
520 new String [] {"C1", "C3"}),
521 new UpdatePrivCheck( true, true, users[2], "R2", "T1",
522 new String [] {"C1", "C3"}),
523 new UpdatePrivCheck( true, true, publicUser, "R2", "T1",
524 new String [] {"C1", "C3"})},
525 false,
526 "setup for revoke individual permissions leaving public permissions");
527 testOneStatement( "Revoke",
528 "revoke select, update(c1,c3), delete on table r2.t1 from " + users[1].name,
529 new PrivCheck[] { new SelectPrivCheck( false, true, users[1], "R2", "T1", null),
530 new SelectPrivCheck( true, true, users[2], "R2", "T1", null),
531 new SelectPrivCheck( true, true, publicUser, "R2", "T1", null),
532 new DeletePrivCheck( false, true, users[1], "R2", "T1"),
533 new DeletePrivCheck( true, true, users[2], "R2", "T1"),
534 new DeletePrivCheck( true, true, publicUser, "R2", "T1"),
535 new UpdatePrivCheck( false, true, users[1], "R2", "T1",
536 new String [] {"C1", "C2", "C3"}),
537 new UpdatePrivCheck( true, true, users[2], "R2", "T1",
538 new String [] {"C1", "C3"}),
539 new UpdatePrivCheck( true, true, publicUser, "R2", "T1",
540 new String [] {"C1", "C3"})},
541 "individual permissions leaving public permissions");
542 testOneStatement( "Revoke",
543 "revoke select, update(c1,c3), delete on table r2.t1 from public",
544 new PrivCheck[] { new SelectPrivCheck( false, false, users[1], "R2", "T1", null),
545 new SelectPrivCheck( true, false, users[2], "R2", "T1", null),
546 new SelectPrivCheck( false, false, publicUser, "R2", "T1", null),
547 new DeletePrivCheck( false, true, users[1], "R2", "T1"),
548 new DeletePrivCheck( true, true, users[2], "R2", "T1"),
549 new DeletePrivCheck( false, true, publicUser, "R2", "T1"),
550 new UpdatePrivCheck( false, false, users[1], "R2", "T1",
551 new String [] {"C1", "C2", "C3"}),
552 new UpdatePrivCheck( true, false, users[2], "R2", "T1",
553 new String [] {"C1", "C3"}),
554 new UpdatePrivCheck( false, false, publicUser, "R2", "T1",
555 new String [] {"C1", "C3"})},
556 "public permissions");
557
558 owner.stmt.executeUpdate( "grant execute on function r2.f1 to public");
559 owner.stmt.executeUpdate( "grant execute on function r2.f1 to " + users[2].name + "," + users[0].name);
560 runChecks( new PrivCheck[] { new ExecutePrivCheck( true, true, users[0], "R2", "F1", true),
561 new ExecutePrivCheck( true, true, users[2], "R2", "F1", true),
562 new ExecutePrivCheck( true, true, publicUser, "R2", "F1", true)},
563 false,
564 "setup for revoke execute leaving public permission");
565 testOneStatement( "Revoke",
566 "revoke execute on function r2.f1 from " + users[0].name + " restrict",
567 new PrivCheck[] { new ExecutePrivCheck( false, true, users[0], "R2", "F1", true),
568 new ExecutePrivCheck( true, true, users[2], "R2", "F1", true),
569 new ExecutePrivCheck( true, true, publicUser, "R2", "F1", true)},
570 "execute leaving public permission");
571 testOneStatement( "Revoke",
572 "revoke execute on function r2.f1 from Public restrict",
573 new PrivCheck[] { new ExecutePrivCheck( false, false, users[0], "R2", "F1", true),
574 new ExecutePrivCheck( true, false, users[2], "R2", "F1", true),
575 new ExecutePrivCheck( false, false, publicUser, "R2", "F1", true)},
576 "execute leaving public permission");
577
578 testRevokeRollback( );
579
580 testAbandonedView( );
581 testAbandonedTrigger( );
582 testAbandonedConstraint( );
583 }
585 private void testErrors( String [][] errorCases) throws SQLException
586 {
587 System.out.println("Testing error cases ...");
588 for( int i = 0; i < errorCases.length; i++)
589 {
590 try
591 {
592 System.out.println("testErrors: " + errorCases[i][0]);
593 owner.stmt.executeUpdate( errorCases[i][0]);
594 reportFailure( "No error generated by \"" + errorCases[i][0] + "\"");
595 }
596 catch( SQLException sqle)
597 {
598 if( ! errorCases[i][1].equals( sqle.getSQLState()))
599 reportFailure( "Incorrect SQLState for error case " + i
600 + ". Expected " + errorCases[i][1] + ", got " + sqle.getSQLState()
601 + ": " + sqle.getMessage());
602 else if( ! errorCases[i][2].equals( sqle.getMessage()))
603 reportFailure( new String [] {"Incorrect message for error case " + i + ".",
604 " Expected " + errorCases[i][2],
605 " Got " + sqle.getMessage()});
606 }
607 }
608 }
610 private static final String [][] stdErrorCases =
611 {
612 {"grant xx on s1.t1 to " + users[0].name, "42X01",
613 "Syntax error: Encountered \"xx\" at line 1, column 7."}, {"grant between on s1.t1 to " + users[0].name, "42X01",
615 "Syntax error: Encountered \"between\" at line 1, column 7."}, {"grant select on schema t1 to " + users[0].name,
617 "42X01", "Syntax error: Encountered \"schema\" at line 1, column 17."},
618 {"grant select on decimal t1 to " + users[0].name, "42X01",
619 "Syntax error: Encountered \"decimal\" at line 1, column 17."},
620 {"grant select(nosuchCol) on s1.t1 to " + users[0].name, "42X14",
621 "'NOSUCHCOL' is not a column in table or VTI 'S1.T1'."},
622
623 {"grant select on nosuch.t1 to " + users[0].name, "42Y07", "Schema 'NOSUCH' does not exist"},
624 {"grant select on s1.nosuch to " + users[0].name, "42X05", "Table/View 'S1.NOSUCH' does not exist."},
625 {"grant execute on function nosuch.f0 to " + users[0].name, "42Y07", "Schema 'NOSUCH' does not exist"},
626 {"grant execute on function s1.nosuch to " + users[0].name, "42Y03",
627 "'S1.NOSUCH' is not recognized as a function or procedure."},
628 {"grant execute on function s1.p1 to " + users[0].name, "42Y03",
629 "'S1.P1' is not recognized as a function or procedure."},
630 {"grant execute on procedure nosuch.f0 to " + users[0].name, "42Y07", "Schema 'NOSUCH' does not exist"},
632 {"grant execute on procedure s1.nosuch to " + users[0].name, "42Y03",
633 "'S1.NOSUCH' is not recognized as a function or procedure."},
634 {"grant execute on procedure s1.f2 to " + users[0].name, "42Y03",
635 "'S1.F2' is not recognized as a function or procedure."},
636 {"grant execute on table s1.t1 to " + users[0].name, "42X01",
637 "Syntax error: Encountered \"table\" at line 1, column 18."},
638 {"grant select on function s1.f1 to " + users[0].name, "42X01",
639 "Syntax error: Encountered \"function\" at line 1, column 17."},
640
641 {"grant select on procedure s1.p1 to " + users[0].name, "42X01",
642 "Syntax error: Encountered \"procedure\" at line 1, column 17."},
643 {"grant execute on function s1.f1 to " + users[0].name + " restrict", "42X01",
644 "Syntax error: Encountered \"restrict\" at line 1, column 40."}, {"revoke execute on function s1.f1 from " + users[0].name, "42X01",
646 "Syntax error: Encountered \"<EOF>\" at line 1, column 41."}, {"revoke select on s1.t1 from " + users[0].name + " restrict", "42X01",
648 "Syntax error: Encountered \"restrict\" at line 1, column 33."}, {"grant delete(c1) on s1.t1 to " + users[0].name, "42X01",
650 "Syntax error: Encountered \"(\" at line 1, column 13."}, {"grant trigger(c1) on s1.t1 to " + users[0].name, "42X01",
653 "Syntax error: Encountered \"(\" at line 1, column 14."} };
656 private void testRevokeRollback( ) throws SQLException
657 {
658 owner.getConnection().setAutoCommit( false);
659 owner.stmt.executeUpdate( "grant select(c1,c2), update(c1), insert, delete on r2.t3 to " + users[0].name);
660 owner.stmt.executeUpdate( "grant select, references on r2.t3 to " + users[1].name);
661 owner.stmt.executeUpdate( "grant select on r2.t3 to " + users[2].name);
662 owner.stmt.executeUpdate( "grant execute on procedure r1.p1 to " + users[0].name);
663 owner.getConnection().commit();
664 runChecks( new PrivCheck[] { new SelectPrivCheck( true, false, users[0], "R2", "T3",
665 new String [] { "C1", "C2"}),
666 new UpdatePrivCheck( true, false, users[0], "R2", "T3",
667 new String [] { "C1"}),
668 new InsertPrivCheck( true, false, users[0], "R2", "T3"),
669 new DeletePrivCheck( true, false, users[0], "R2", "T3"),
670 new SelectPrivCheck( true, false, users[1], "R2", "T3", null),
671 new ReferencesPrivCheck( true, false, users[1], "R2", "T3", null),
672 new SelectPrivCheck( true, false, users[2], "R2", "T3", null),
673 new ExecutePrivCheck( true, false, users[0], "R1", "P1", false)
674 },
675 false,
676 "setup for rollback test");
677 for( int i = 0; i < 2; i++)
678 {
679 boolean doRollback = (i == 0);
680 testOneStatement( "Revoke",
681 "revoke select(c2), update(c1), delete on r2.t3 from " + users[0].name,
682 new PrivCheck[] { new SelectPrivCheck( true, false, users[0], "R2", "T3",
683 new String [] { "C1"}),
684 new SelectPrivCheck( false, false, users[0], "R2", "T3",
685 new String [] { "C2", "C3"}),
686 new UpdatePrivCheck( false, false, users[0], "R2", "T3",
687 new String [] { "C1", "C2", "C3"}),
688 new InsertPrivCheck( true, false, users[0], "R2", "T3"),
689 new DeletePrivCheck( false, false, users[0], "R2", "T3")
690 },
691 false,
692 "table privileges (uncommitted)");
693 testOneStatement( "Revoke",
694 "revoke references on r2.t3 from " + users[1].name,
695 new PrivCheck[] { new SelectPrivCheck( true, false, users[1], "R2", "T3", null),
696 new ReferencesPrivCheck( false, false, users[1], "R2", "T3", null)},
697 false,
698 "table privileges (uncommitted)");
699 testOneStatement( "Revoke",
700 "revoke select on r2.t3 from " + users[2].name,
701 new PrivCheck[] { new SelectPrivCheck( false, false, users[2], "R2", "T3", null)},
702 false,
703 "table privileges (uncommitted)");
704 testOneStatement( "Revoke",
705 "revoke execute on procedure r1.p1 from " + users[0].name + " restrict",
706 new PrivCheck[] { new ExecutePrivCheck( false, false, users[0], "R1", "P1", false)},
707 false,
708 "execute privilege (uncommitted)");
709 if( doRollback)
710 owner.getConnection().rollback();
711 else
712 owner.getConnection().commit();
713 runChecks( new PrivCheck[] { new SelectPrivCheck( doRollback, false, users[0], "R2", "T3",
714 new String [] { "C2"}),
715 new UpdatePrivCheck( doRollback, false, users[0], "R2", "T3",
716 new String [] { "C1"}),
717 new DeletePrivCheck( doRollback, false, users[0], "R2", "T3"),
718 new ReferencesPrivCheck( doRollback, false, users[1], "R2", "T3", null),
719 new SelectPrivCheck( doRollback, false, users[2], "R2", "T3", null),
720 new ExecutePrivCheck( doRollback, false, users[0], "R1", "P1", false)
721 },
722 false,
723 doRollback ? "rollback of revokes" : "commit of revokes");
724 }
725 owner.getConnection().setAutoCommit( true);
726 }
728 private void testAbandonedView( ) throws SQLException
729 {
730 }
732
733 private void testAbandonedTrigger( ) throws SQLException
734 {
735 }
737
738 private void testAbandonedConstraint( ) throws SQLException
739 {
740 }
742
743 private static final String [] revokeTestSetupSQL =
744 {
745 "create schema r1",
746 "create schema r2",
747 "create table r1.t1(c1 int, c2 int, c3 int)",
748 "create table r2.t1(c1 int, c2 int, c3 int)",
749 "create table r2.t2(c1 int, c2 int, c3 int)",
750 "create table r2.t3(c1 int, c2 int, c3 int)",
751 "create function r1.f1() returns int" +
752 " language java parameter style java" +
753 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1F1'" +
754 " no sql called on null input",
755
756
759 "create procedure r1.f1()" +
760 " language java parameter style java" +
761 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1P1'" +
762 " no sql called on null input",
763 "create function r2.f1() returns int" +
764 " language java parameter style java" +
765 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1a'" +
766 " no sql called on null input",
767 "create function r2.f2( p1 char(8), p2 integer) returns int" +
768 " language java parameter style java" +
769 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F2'" +
770 " no sql called on null input",
771 "create procedure r1.p1( )" +
772 " language java parameter style java" +
773 " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1P1'" +
774 " no sql called on null input"
775 };
776
777 private void runChecks( PrivCheck[] checks,
778 boolean invertExpecation,
779 String testLabel)
780 {
781 runChecks( checks, true, invertExpecation, testLabel);
782 }
783
784 private void runChecks( PrivCheck[] checks,
785 boolean runStatements,
786 boolean invertExpecation,
787 String testLabel)
788 {
789 try
790 {
791 for( int i = 0; i < checks.length; i++)
792 {
793 if( invertExpecation)
794 checks[i].invertExpectation();
795 checks[i].checkPriv( runStatements, testLabel);
796 if( invertExpecation)
797 checks[i].invertExpectation();
798 }
799 }
800 catch( SQLException sqle)
801 {
802 unexpectedException( testLabel, sqle);
803 }
804 }
806 private void reportFailure( String msg)
807 {
808 errorCount++;
809 System.out.println( msg);
810 }
811
812 private void reportFailure( String [] msg)
813 {
814 errorCount++;
815 for( int i = 0; i < msg.length; i++)
816 System.out.println( msg[i]);
817 }
818
819 private void unexpectedException( String testLabel, SQLException sqle)
820 {
821 reportFailure( (testLabel == null) ? "Unexpected exception"
822 : ( "Unexpected exception in " + testLabel + " test"));
823 while( sqle != null)
824 {
825 System.out.println( sqle.getSQLState() + ": " + sqle.getMessage());
826 SQLException next = sqle.getNextException();
827 if( next == null)
828 {
829 sqle.printStackTrace(System.out);
830 break;
831 }
832 sqle = next;
833 }
834 }
835
836 private abstract class PrivCheck
837 {
838 boolean expectPriv;
839 boolean privIsPublic;
840 User user;
841 String schema;
842
843 PrivCheck( boolean expectPriv, boolean privIsPublic, User user, String schema)
844 {
845 this.expectPriv = expectPriv;
846 this.privIsPublic = privIsPublic;
847 this.user = user;
848 this.schema = schema;
849 }
850
851 void invertExpectation()
852 {
853 expectPriv = ! expectPriv;
854 }
855
856 void checkPriv( boolean runStatements, String testLabel) throws SQLException
857 {
858 checkSQL( testLabel);
859 checkMetaData( testLabel);
860 if( runStatements && ! user.isPublic())
861 {
862 checkUser( user, testLabel);
863 }
864 }
865
866
873 abstract void checkUser( User user, String testLabel) throws SQLException;
874
875
882 abstract void checkMetaData( String testLabel) throws SQLException;
883
884
891 abstract void checkSQL( String testLabel) throws SQLException;
892
893 protected void checkSQLException( SQLException sqle,
894 boolean expected,
895 String expectedSQLState,
896 String testLabel,
897 String [] fixedSegs,
898 String [][] variables,
899 boolean[] ignoreCase)
900 {
901 if( ! expected)
902 unexpectedException( testLabel, sqle);
903 else if( ! sqle.getSQLState().startsWith( expectedSQLState))
904 unexpectedException( testLabel, sqle);
905 else
906 {
907 if( msgTxtOK( sqle.getMessage(), 0, 0, fixedSegs, variables, ignoreCase))
908 return;
909 StringBuffer expectedMsg = new StringBuffer ();
910 for( int segIdx = 0; segIdx < fixedSegs.length; segIdx++)
911 {
912 expectedMsg.append( fixedSegs[segIdx]);
913 if( segIdx < variables.length)
914 {
915 if( variables[ segIdx].length == 1)
916 expectedMsg.append( variables[ segIdx][0]);
917 else
918 expectedMsg.append( "{?}");
919 }
920 }
921 reportFailure( "Incorrect error message. Expected \"" + expectedMsg.toString() +
922 "\" got \"" + sqle.getMessage() + "\"");
923 }
924 }
926
929 private boolean msgTxtOK( String actualMsg,
930 int offset,
931 int segIdx,
932 String [] fixedSegs,
933 String [][] variables,
934 boolean[] ignoreCase)
935 {
936 for( ; segIdx < fixedSegs.length; segIdx++)
937 {
938 if( ! actualMsg.startsWith( fixedSegs[ segIdx], offset))
939 return false;
940 offset += fixedSegs[ segIdx].length();
941 if( segIdx < variables.length)
942 {
943 if( variables[ segIdx].length == 1)
944 {
945 if( ! actualMsg.regionMatches( ignoreCase[ segIdx],
946 offset,
947 variables[ segIdx][0],
948 0,
949 variables[ segIdx][0].length()))
950 return false;
951 offset += variables[ segIdx][0].length();
952 }
953 else
954 {
955 int i;
957 for( i = 0; i < variables[ segIdx].length; i++)
958 {
959 if( actualMsg.regionMatches( ignoreCase[ segIdx],
960 offset,
961 variables[ segIdx][i],
962 0,
963 variables[ segIdx][i].length())
964 && msgTxtOK( actualMsg,
965 offset + variables[ segIdx][i].length(),
966 segIdx + 1,
967 fixedSegs,
968 variables,
969 ignoreCase))
970 {
971 offset += variables[ segIdx][i].length();
972 break;
973 }
974 }
975 if( i >= variables[ segIdx].length)
976 return false;
977 }
978 }
979 }
980 return true;
981 }
983 }
985 private static final String [] columnPrivErrMsgFixedSegs
986 = { "User '", "' does not have ", " permission on column '", "' of table '", "'.'", "'."};
987
988 private static final String [] tablePrivErrMsgFixedSegs
989 = { "User '", "' does not have ", " permission on table '", "'.'", "'."};
990
991 private static final String [] executePrivErrMsgFixedSegs
992 = { "User '", "' does not have execute permission on ", " '", "'.'", "'."};
993
994 private abstract class TablePrivCheck extends PrivCheck
995 {
996 String table;
997 String [] columns;
998 private String [] allColumns;
999
1000 TablePrivCheck( boolean expectPriv,
1001 boolean privIsPublic,
1002 User user,
1003 String schema,
1004 String table,
1005 String [] columns)
1006 {
1007 super( expectPriv, privIsPublic, user, schema);
1008 this.table = table;
1009 this.columns = columns;
1010 }
1011
1012
1021 void checkSQL( String testLabel,
1022 String tablePermsColName,
1023 String colPermsType)
1024 throws SQLException
1025 {
1026 if (TestUtil.isNetFramework())
1028 return;
1029
1030 if( columns == null)
1031 {
1032 ResultSet rs = owner.stmt.executeQuery(
1033 "select p." + tablePermsColName + " from SYS.SYSTABLEPERMS p, SYS.SYSTABLES t, SYS.SYSSCHEMAS s"
1034 + " where p.GRANTEE = '" + user.name + "' and p.TABLEID = t.TABLEID and "
1035 + " t.TABLENAME = '" + table + "' and t.SCHEMAID = s.SCHEMAID and "
1036 + " s.SCHEMANAME = '" + schema + "'");
1037 if( rs.next())
1038 {
1039 String hasPerm = rs.getString(1);
1040 if( "N".equals( hasPerm))
1041 {
1042 if( expectPriv)
1043 reportFailure( getPrivName() + " permission not in SYSTABLEPERMS for " + user
1044 + " on table " + schema + "." + table);
1045 }
1046 else if( "y".equals( hasPerm))
1047 {
1048 if( ! expectPriv)
1049 reportFailure( getPrivName() + " permission was in SYSTABLEPERMS for " + user
1050 + " on table " + schema + "." + table);
1051 }
1052 else if( "Y".equals( hasPerm))
1053 {
1054 reportFailure( getPrivName() + " WITH GRANT OPTION in SYSTABLEPERMS for " + user
1055 + " on table " + schema + "." + table);
1056 }
1057 if( rs.next())
1058 reportFailure( "Multiple SYS.SYSTABLEPERMS rows for user " + user
1059 + " on table " + schema + "." + table);
1060 }
1061 else
1062 {
1063 if( expectPriv)
1064 reportFailure( "No SYSTABLEPERMS rows for " + user + " on table " + schema + "." + table);
1065 }
1066 rs.close();
1067 }
1068 else
1069 {
1070 ResultSet rs = owner.stmt.executeQuery(
1072 "select p.type,p.columns from SYS.SYSCOLPERMS p, SYS.SYSTABLES t, SYS.SYSSCHEMAS s"
1073 + " where p.GRANTEE = '" + user.name + "' and (p.type = '" + colPermsType.toLowerCase()
1074 + "' or p.type = '" + colPermsType.toUpperCase() + "') and p.TABLEID = t.TABLEID and "
1075 + " t.TABLENAME = '" + table + "' and t.SCHEMAID = s.SCHEMAID and "
1076 + " s.SCHEMANAME = '" + schema + "'");
1077 if( rs.next())
1078 {
1079 String type = rs.getString(1);
1080 FormatableBitSet colBitSet = (FormatableBitSet) rs.getObject(2);
1081 if( type == null || colBitSet == null)
1082 reportFailure( "Null type or columns value in SYSCOLPERMS row for "
1083 + user + " on table " + schema + "." + table);
1084 else
1085 {
1086 FormatableBitSet expectedColBitSet = getColBitSet( );
1087 colBitSet.and( expectedColBitSet);
1088 if( expectPriv)
1089 {
1090 if( ! colBitSet.equals( expectedColBitSet))
1091 reportFailure( "Expected " + getPrivName() + " permissions not all in SYSCOLPERMS for "
1092 + user + " on table " + schema + "." + table);
1093 }
1094 else
1095 {
1096 if( colBitSet.anySetBit() >= 0)
1097 reportFailure( "Unexpected " + getPrivName() + " permissions in SYSCOLPERMS for "
1098 + user + " on table " + schema + "." + table);
1099 }
1100 }
1101 if( rs.next())
1102 reportFailure( "Multiple " + getPrivName() + " rows in SYSCOLPERMS for "
1103 + user + " on table " + schema + "." + table);
1104 }
1105 else
1106 {
1107 if( expectPriv)
1108 reportFailure( "No " + getPrivName() + " permissions in SYSCOLPERMS for "
1109 + user + " on table " + schema + "." + table);
1110 }
1111 rs.close();
1112 }
1113 }
1115 String getUserCurrentSchema(User user) throws SQLException
1116 {
1117 String schemaString = null;
1118
1119 Statement s = user.getConnection().createStatement();
1120 ResultSet rs = s.executeQuery("values current schema");
1121 while (rs.next())
1122 schemaString = rs.getString(1);
1123 return schemaString;
1124 }
1125
1126 void setUserCurrentSchema(User user, String schema) throws SQLException
1127 {
1128 Statement s = user.getConnection().createStatement();
1129 try {
1130 s.executeUpdate("set schema "+schema);
1131 } catch (SQLException sqle) {
1132 if (sqle.getSQLState() == "42Y07") {
1134 s.executeUpdate("create schema "+schema);
1135 s.executeUpdate("set schema "+schema);
1136 }
1137 }
1138 }
1139
1140 private HashMap columnHash;
1141
1142 FormatableBitSet getColBitSet( ) throws SQLException
1143 {
1144 if( columns == null)
1145 return null;
1146
1147 if( columnHash == null)
1148 {
1149 columnHash = new HashMap ();
1150 ResultSet rs = dbmd.getColumns( (String ) null, schema, table, (String ) null);
1151 while( rs.next())
1152 {
1153 columnHash.put( rs.getString( "COLUMN_NAME"),
1154 new Integer ( rs.getInt( "ORDINAL_POSITION") - 1));
1155 }
1156 rs.close();
1157 }
1158 FormatableBitSet colBitSet = new FormatableBitSet( columnHash.size());
1159 for( int i = 0; i < columns.length; i++)
1160 {
1161 Integer colIdx = (Integer ) columnHash.get( columns[i].toUpperCase());
1162 if( colIdx == null)
1163 throw new SQLException("Internal test error: table " + schema + "." + table
1164 + " does not have a " + columns[i].toUpperCase() + " column.");
1165 colBitSet.set( colIdx.intValue());
1166 }
1167 return colBitSet;
1168 }
1170
1177 void checkMetaData( String testLabel) throws SQLException
1178 {
1179 if (TestUtil.isNetFramework())
1181 return;
1182
1183 if( columns == null)
1184 {
1185 ResultSet rs = dbmd.getTablePrivileges( (String ) null, schema, table);
1186 boolean found = false;
1187 while( rs.next())
1188 {
1189 String go = rs.getString( 4); String ge = rs.getString( 5); String p = rs.getString( 6); String ig = rs.getString( 7); if( ! dbmd.getUserName().equals( go))
1194 reportFailure( "DatabaseMetaData.getTablePrivileges returned incorrect grantor");
1195 if( ge == null)
1196 reportFailure( "DatabaseMetaData.getTablePrivileges returned null user");
1197 if( p == null)
1198 reportFailure( "DatabaseMetaData.getTablePrivileges returned null privilege");
1199 if( ig == null)
1200 reportFailure( "DatabaseMetaData.getTablePrivileges returned null is_grantable");
1201 if( ig.equals("YES"))
1202 reportFailure( "grantable " + p + " privilege reported by DatabaseMetaData.getTablePrivileges");
1203 else if( ! ig.equals("NO"))
1204 reportFailure( "DatabaseMetaData.getTablePrivileges returned invalid is_grantable");
1205 if( user.name.equals( ge) && getPrivName().equals( p))
1206 found = true;
1207 }
1208 rs.close();
1209 if( expectPriv && !found)
1210 reportFailure( "DatabaseMetaData.getTablePrivileges did not return expected " + getPrivName()
1211 + " permision");
1212 else if( found && !expectPriv)
1213 reportFailure( "DatabaseMetaData.getTablePrivileges returned an unexpected " + getPrivName()
1214 + " permision");
1215 }
1216 else
1217 {
1218 FormatableBitSet expectedColBitSet = getColBitSet( );
1219 FormatableBitSet found = new FormatableBitSet( expectedColBitSet.getLength());
1220 ResultSet rs = dbmd.getColumnPrivileges( (String ) null, schema, table, "%");
1221 while( rs.next())
1222 {
1223 String colName = rs.getString( "COLUMN_NAME");
1224 String go = rs.getString( "GRANTOR");
1225 String ge = rs.getString( "GRANTEE");
1226 String p = rs.getString( "PRIVILEGE");
1227 String ig = rs.getString( "IS_GRANTABLE");
1228 if( ! dbmd.getUserName().equals( go))
1229 reportFailure( "DatabaseMetaData.getColumnPrivileges returned incorrect grantor");
1230 if( ge == null)
1231 reportFailure( "DatabaseMetaData.getColumnPrivileges returned null user");
1232 if( p == null)
1233 reportFailure( "DatabaseMetaData.getColumnPrivileges returned null privilege");
1234 if( ig == null)
1235 reportFailure( "DatabaseMetaData.getColumnPrivileges returned null is_grantable");
1236 if( ig.equals("YES"))
1237 reportFailure( "grantable " + p + " privilege reported by DatabaseMetaData.getColumnPrivileges");
1238 else if( ! ig.equals("NO"))
1239 reportFailure( "DatabaseMetaData.getColumnPrivileges returned invalid is_grantable");
1240 Integer cI = (Integer ) columnHash.get( colName);
1241 if( cI == null)
1242 reportFailure( "DatabaseMetaData.getColumnPrivileges returned invalid column name: "
1243 + colName);
1244 else if( user.name.equals( ge) && getPrivName().equals( p))
1245 {
1246 int cIdx = cI.intValue();
1247 if( found.isSet( cIdx) )
1248 reportFailure( "DatabaseMetaData.getColumnPrivileges returned duplicate rows");
1249 else
1250 found.set( cIdx);
1251 }
1252 }
1253 rs.close();
1254 if( expectPriv)
1255 {
1256 for( int i = expectedColBitSet.anySetBit(); i >= 0; i = expectedColBitSet.anySetBit(i))
1257 {
1258 if( !found.isSet(i))
1259 {
1260 reportFailure( "DatabaseMetaData.getColumnPrivileges missed " + getPrivName()
1261 + " permission on column " + (i+1));
1262 break;
1263 }
1264 }
1265 }
1266 else
1267 {
1268 for( int i = expectedColBitSet.anySetBit(); i >= 0; i = expectedColBitSet.anySetBit(i))
1269 {
1270 if( found.isSet(i))
1271 {
1272 reportFailure( "DatabaseMetaData.getColumnPrivileges returned unexpected " + getPrivName()
1273 + " permission on column " + (i+1));
1274 break;
1275 }
1276 }
1277 }
1278 }
1279 }
1281 abstract String getPrivName();
1282
1283 protected String [] getAllColumns( ) throws SQLException
1284 {
1285 if( allColumns == null)
1286 {
1287 ArrayList columnList = new ArrayList ();
1288 ResultSet rs = dbmd.getColumns( (String ) null, schema, table, (String ) null);
1289 String separator = "";
1290 while( rs.next())
1291 {
1292 columnList.add( rs.getString( 4));
1293 }
1294 allColumns = (String []) columnList.toArray( new String [0]);
1295 }
1296 return allColumns;
1297 }
1299 protected void appendWhereClause( StringBuffer sb, String [] columns)
1300 throws SQLException
1301 {
1302 if( columns == null)
1303 columns = getAllColumns( );
1304 sb.append( " where (");
1305 for( int i = 0; i < columns.length; i++)
1306 {
1307 if( i > 0)
1308 sb.append( " or (");
1309 sb.append( columns[i]);
1310 sb.append( " is null)");
1311 }
1312 }
1314
1317 protected void checkTablePermissionMsg( SQLException sqle,
1318 User user,
1319 String action,
1320 String testLabel)
1321 {
1322 checkSQLException( sqle, ! expectPriv, "28506", testLabel,
1323 tablePrivErrMsgFixedSegs,
1324 new String [][]{ new String [] { user.name},
1325 new String [] { action},
1326 new String [] { schema},
1327 new String [] { table}},
1328 new boolean[]{true, true, false, false});
1329 }
1331 protected void checkColumnPermissionMsg( SQLException sqle,
1332 User user,
1333 String action,
1334 String testLabel)
1335 throws SQLException
1336 {
1337 checkSQLException( sqle, ! expectPriv, "28508", testLabel,
1338 columnPrivErrMsgFixedSegs,
1339 new String [][]{ new String [] { user.name},
1340 new String [] { action},
1341 (columns == null) ? getAllColumns() : columns,
1342 new String [] { schema},
1343 new String [] { table}},
1344 new boolean[]{true, true, false, false, false});
1345 } }
1348 static void appendAColumnValue( StringBuffer sb, int type)
1349 {
1350 switch( type)
1351 {
1352 case Types.BIGINT:
1353 case Types.DECIMAL:
1354 case Types.DOUBLE:
1355 case Types.FLOAT:
1356 case Types.INTEGER:
1357 case Types.NUMERIC:
1358 case Types.REAL:
1359 case Types.SMALLINT:
1360 case Types.TINYINT:
1361 sb.append( "0");
1362 break;
1363
1364 case Types.CHAR:
1365 case Types.VARCHAR:
1366 sb.append( "' '");
1367 break;
1368
1369 case Types.DATE:
1370 sb.append( "CURRENT_DATE");
1371 break;
1372
1373 case Types.TIME:
1374 sb.append( "CURRENT_TIME");
1375 break;
1376
1377 case Types.TIMESTAMP:
1378 sb.append( "CURRENT_TIMESTAMP");
1379 break;
1380
1381 default:
1382 sb.append( "null");
1383 break;
1384 }
1385 }
1387 private class SelectPrivCheck extends TablePrivCheck
1388 {
1389 SelectPrivCheck( boolean expectPriv,
1390 boolean privIsPublic,
1391 User user,
1392 String schema,
1393 String table,
1394 String [] columns)
1395 {
1396 super( expectPriv, privIsPublic, user, schema, table, columns);
1397 }
1398
1399 String getPrivName() { return "SELECT";}
1400
1401
1408 void checkSQL( String testLabel) throws SQLException
1409 {
1410 checkSQL( testLabel, "SELECTPRIV", "s");
1411 }
1412
1413
1420 void checkUser( User user, String testLabel) throws SQLException
1421 {
1422 StringBuffer sb = new StringBuffer ();
1423 sb.append( "select ");
1424 if( columns == null)
1425 sb.append( "*");
1426 else
1427 {
1428 for( int i = 0; i < columns.length; i++)
1429 {
1430 if( i != 0)
1431 sb.append( ",");
1432 sb.append( columns[i]);
1433 }
1434 }
1435 sb.append( " from ");
1436 if( schema != null)
1437 {
1438 sb.append( schema);
1439 sb.append( ".");
1440 }
1441 sb.append( table);
1442
1443 checkUser( user, sb, testLabel);
1444
1445 sb.setLength( 0);
1447 sb.append( "select count(*) from \"");
1448 sb.append( schema);
1449 sb.append( "\".\"");
1450 sb.append( table);
1451 sb.append( "\"");
1452 appendWhereClause( sb, columns);
1453 checkUser( user, sb, testLabel);
1454 }
1456 private void checkUser( User user, StringBuffer sb, String testLabel) throws SQLException
1457 {
1458 System.out.println("SelectPrivCheck: " + sb.toString());
1459 PreparedStatement ps = user.getConnection().prepareStatement( sb.toString());
1460 try
1461 {
1462 ResultSet rs = ps.executeQuery();
1463 rs.next();
1464 rs.close();
1465 if( ! (privIsPublic || expectPriv))
1466 reportFailure( "A select was performed without permission. (" + testLabel + ")");
1467 }
1468 catch( SQLException sqle)
1469 {
1470 checkColumnPermissionMsg( sqle, user, "select", testLabel);
1471 }
1472 ps.close();
1473 }
1474 }
1476 private class DeletePrivCheck extends TablePrivCheck
1477 {
1478 DeletePrivCheck( boolean expectPriv,
1479 boolean privIsPublic,
1480 User user,
1481 String schema,
1482 String table)
1483 {
1484 super( expectPriv, privIsPublic, user, schema, table, (String []) null);
1485 }
1486
1487 String getPrivName() { return "DELETE";}
1488
1489
1496 void checkSQL( String testLabel) throws SQLException
1497 {
1498 checkSQL( testLabel, "DELETEPRIV", "d");
1499 }
1500
1501
1508 void checkUser( User user, String testLabel) throws SQLException
1509 {
1510 StringBuffer sb = new StringBuffer ();
1511 sb.append( "delete from \"");
1512 sb.append( schema);
1513 sb.append( "\".\"");
1514 sb.append( table);
1515 sb.append( "\"");
1516 boolean savedAutoCommit = user.getConnection().getAutoCommit();
1517 user.getConnection().setAutoCommit( false);
1518 System.out.println("DeletePrivCheck: " + sb.toString());
1519 PreparedStatement ps = user.getConnection().prepareStatement( sb.toString());
1520 try
1521 {
1522 ps.executeUpdate();
1523 if( ! (privIsPublic || expectPriv))
1524 reportFailure( "A delete was performed without permission. (" + testLabel + ")");
1525 }
1526 catch( SQLException sqle)
1527 {
1528 checkTablePermissionMsg( sqle, user, "delete", testLabel);
1529 }
1530 finally
1531 {
1532 try
1533 {
1534 user.getConnection().rollback();
1535 }
1536 finally
1537 {
1538 user.getConnection().setAutoCommit( savedAutoCommit);
1539 }
1540 }
1541 }
1543 }
1545 private class InsertPrivCheck extends TablePrivCheck
1546 {
1547 InsertPrivCheck( boolean expectPriv,
1548 boolean privIsPublic,
1549 User user,
1550 String schema,
1551 String table)
1552 {
1553 super( expectPriv, privIsPublic, user, schema, table, (String []) null);
1554 }
1555
1556 String getPrivName() { return "INSERT";}
1557
1558
1565 void checkSQL( String testLabel) throws SQLException
1566 {
1567 checkSQL( testLabel, "INSERTPRIV", "i");
1568 }
1569
1570
1577 void checkUser( User user, String testLabel) throws SQLException
1578 {
1579 StringBuffer sb = new StringBuffer ();
1580 sb.append( "insert into \"");
1581 sb.append( schema);
1582 sb.append( "\".\"");
1583 sb.append( table);
1584 sb.append( "\" values(");
1585 ResultSet rs = dbmd.getColumns( (String ) null, schema, table, (String ) null);
1586 boolean first = true;
1587 while( rs.next())
1588 {
1589 if( first)
1590 first = false;
1591 else
1592 sb.append( ",");
1593 appendAColumnValue( sb, rs.getInt( 5));
1594 }
1595 sb.append(")");
1596 boolean savedAutoCommit = user.getConnection().getAutoCommit();
1597 user.getConnection().setAutoCommit( false);
1598 System.out.println("InsertPrivCheck: " + sb.toString());
1599 PreparedStatement ps = user.getConnection().prepareStatement( sb.toString());
1600 try
1601 {
1602 ps.executeUpdate();
1603 if( ! (privIsPublic || expectPriv))
1604 reportFailure( "An insert was performed without permission. (" + testLabel + ")");
1605 }
1606 catch( SQLException sqle)
1607 {
1608 checkTablePermissionMsg( sqle, user, "insert", testLabel);
1609 }
1610 finally
1611 {
1612 try
1613 {
1614 user.getConnection().rollback();
1615 }
1616 finally
1617 {
1618 user.getConnection().setAutoCommit( savedAutoCommit);
1619 }
1620 }
1621 }
1623 }
1625 private class UpdatePrivCheck extends TablePrivCheck
1626 {
1627 UpdatePrivCheck( boolean expectPriv,
1628 boolean privIsPublic,
1629 User user,
1630 String schema,
1631 String table,
1632 String [] columns)
1633 {
1634 super( expectPriv, privIsPublic, user, schema, table, columns);
1635 }
1636
1637 String getPrivName() { return "UPDATE";}
1638
1639
1646 void checkSQL( String testLabel) throws SQLException
1647 {
1648 checkSQL( testLabel, "UPDATEPRIV", "u");
1649 }
1650
1651
1658 void checkUser( User user, String testLabel) throws SQLException
1659 {
1660 String [] checkColumns = (columns == null) ? getAllColumns() : columns;
1661 StringBuffer sb = new StringBuffer ();
1662 boolean savedAutoCommit = user.getConnection().getAutoCommit();
1663 user.getConnection().setAutoCommit( false);
1664 try
1665 {
1666 for( int colIdx = 0; colIdx < checkColumns.length; colIdx++)
1667 {
1668 sb.setLength( 0);
1669 sb.append( "update ");
1670 sb.append( schema);
1671 sb.append( ".");
1672 sb.append( table);
1673 sb.append( " set ");
1674 sb.append( checkColumns[ colIdx]);
1675 sb.append( "=");
1676 ResultSet rs = dbmd.getColumns( null, schema, table, checkColumns[ colIdx]);
1677 if( ! rs.next())
1678 {
1679 rs.close();
1680 reportFailure( "Could not get column metadata for " + schema + "." + table +
1681 "." + checkColumns[ colIdx]);
1682 continue;
1683 }
1684 appendAColumnValue( sb, rs.getInt(5));
1685 rs.close();
1686 System.out.println("UpdatePrivCheck: " + sb.toString());
1687 PreparedStatement ps = user.getConnection().prepareStatement( sb.toString());
1688 try
1689 {
1690 ps.executeUpdate();
1691 if( ! (privIsPublic || expectPriv))
1692 reportFailure( "An update of " + schema + "." + table + "." +
1693 checkColumns[ colIdx] + " was performed without permission. ("
1694 + testLabel + ")");
1695 }
1696 catch( SQLException sqle)
1697 {
1698 checkColumnPermissionMsg( sqle, user, "update", testLabel);
1699 }
1700 }
1701 }
1702 finally
1703 {
1704 try
1705 {
1706 user.getConnection().rollback();
1707 }
1708 finally
1709 {
1710 user.getConnection().setAutoCommit( savedAutoCommit);
1711 }
1712 }
1713 }
1715 }
1717 private class ReferencesPrivCheck extends TablePrivCheck
1718 {
1719 HashMap colNameHash;
1720
1721 ReferencesPrivCheck( boolean expectPriv,
1722 boolean privIsPublic,
1723 User user,
1724 String schema,
1725 String table,
1726 String [] columns)
1727 {
1728 super( expectPriv, privIsPublic, user, schema, table, columns);
1729 if( columns != null)
1730 {
1731 colNameHash = new HashMap ( (5*columns.length)/4);
1732 for( int i = 0; i < columns.length; i++)
1733 colNameHash.put( columns[i], columns[i]);
1734 }
1735 }
1736
1737 String getPrivName() { return "REFERENCES";}
1738
1739
1746 void checkSQL( String testLabel) throws SQLException
1747 {
1748 checkSQL( testLabel, "REFERENCESPRIV", "r");
1749 }
1750
1751
1758 void checkUser( User user, String testLabel) throws SQLException
1759 {
1760 } }
1764 private class TriggerPrivCheck extends TablePrivCheck
1765 {
1766 TriggerPrivCheck( boolean expectPriv,
1767 boolean privIsPublic,
1768 User user,
1769 String schema,
1770 String table)
1771 {
1772 super( expectPriv, privIsPublic, user, schema, table, (String []) null);
1773 }
1774
1775 String getPrivName() { return "TRIGGER";}
1776
1777
1784 void checkSQL( String testLabel) throws SQLException
1785 {
1786 checkSQL( testLabel, "TRIGGERPRIV", "t");
1787 }
1788
1789
1796 void checkUser(User user, String testLabel) throws SQLException
1797 {
1798 StringBuffer sb = new StringBuffer ();
1799 sb.append("create trigger ");
1800 sb.append("\"");
1801 sb.append(table+"Trig");
1802 sb.append("\"");
1803 sb.append(" after insert on ");
1804
1805 sb.append("\"");
1806 sb.append(schema);
1807 sb.append("\".\"");
1808 sb.append(table);
1809 sb.append("\"");
1810 sb.append(" for each row mode db2sql values 1");
1811
1812 boolean savedAutoCommit = user.getConnection().getAutoCommit();
1813 String currentSchema = getUserCurrentSchema(user);
1814 setUserCurrentSchema(user, user.toString());
1816 user.getConnection().setAutoCommit(false);
1817 System.out.println("TriggerPrivCheck: " + sb.toString());
1818 PreparedStatement ps = user.getConnection().prepareStatement(sb.toString());
1819 try
1820 {
1821 ps.executeUpdate();
1822 if( ! (privIsPublic || expectPriv))
1823 reportFailure( "An execute was performed without permission. (" + testLabel + ")");
1824 }
1825 catch( SQLException sqle)
1826 {
1827 checkTablePermissionMsg( sqle, user, "trigger", testLabel);
1828 }
1829 finally
1830 {
1831 try
1832 {
1833 user.getConnection().rollback();
1834 }
1835 finally
1836 {
1837 user.getConnection().setAutoCommit( savedAutoCommit);
1838 setUserCurrentSchema(user, currentSchema);
1839 }
1840 }
1841 } }
1844 private class ExecutePrivCheck extends PrivCheck
1845 {
1846 String routine;
1847 boolean isFunction;
1848
1849 ExecutePrivCheck( boolean expectPriv,
1850 boolean privIsPublic,
1851 User user,
1852 String schema,
1853 String routine,
1854 boolean isFunction)
1855 {
1856 super( expectPriv, privIsPublic, user, schema);
1857 this.routine = routine;
1858 this.isFunction = isFunction;
1859 }
1860
1861
1868 void checkSQL( String testLabel) throws SQLException
1869 {
1870 ResultSet rs = owner.stmt.executeQuery(
1871 "select p.GRANTOPTION from SYS.SYSROUTINEPERMS p, SYS.SYSALIASES a, SYS.SYSSCHEMAS s"
1872 + " where p.GRANTEE = '" + user.name + "' and p.ALIASID = a.ALIASID and"
1873 + " a.ALIAS = '" + routine + "' and a.ALIASTYPE = '"
1874 + (isFunction ? "F" : "P") + "' and a.SCHEMAID = s.SCHEMAID and"
1875 + " s.SCHEMANAME = '" + schema + "'");
1876 if( rs.next())
1877 {
1878 if( ! expectPriv)
1879 reportFailure( "Execute permission in SYSROUTINEPERMS for " + user +
1880 " on " + (isFunction ? "function" : "procedure") + " "
1881 + schema + "." + routine);
1882 else
1883 {
1884 if( ! "N".equals( rs.getString(1)))
1885 reportFailure( "WITH GRANT OPTION specified in SYSROUTINEPERMS for " + user +
1886 " on " + (isFunction ? "function" : "procedure") + " "
1887 + schema + "." + routine);
1888 }
1889 if( rs.next())
1890 reportFailure( "Multiple rows in SYSROUTINEPERMS for " + user +
1891 " on " + (isFunction ? "function" : "procedure") + " "
1892 + schema + "." + routine);
1893 }
1894 else
1895 {
1896 if( expectPriv)
1897 reportFailure( "No execute permission in SYSROUTINEPERMS for " + user +
1898 " on " + (isFunction ? "function" : "procedure") + " "
1899 + schema + "." + routine);
1900 }
1901 rs.close();
1902 }
1904
1911 void checkMetaData( String testLabel) throws SQLException
1912 {
1913 ; }
1916
1923 void checkUser( User user, String testLabel) throws SQLException
1924 {
1925 StringBuffer sb = new StringBuffer ();
1926 if (isFunction)
1927 sb.append( "values \"");
1928 else
1929 sb.append( "call \"");
1930 sb.append(schema);
1931 sb.append("\".\"");
1932 sb.append(routine);
1933 sb.append("\"");
1934 sb.append("()");
1935
1936 boolean savedAutoCommit = user.getConnection().getAutoCommit();
1937 user.getConnection().setAutoCommit(false);
1938 System.out.println("ExecutePrivCheck: " + sb.toString());
1939 PreparedStatement ps = user.getConnection().prepareStatement(sb.toString());
1940 try
1941 {
1942 if (isFunction)
1943 {
1944 ResultSet rs = ps.executeQuery();
1945 rs.close();
1946 }
1947 else
1948 ps.executeUpdate();
1949 if( ! (privIsPublic || expectPriv))
1950 reportFailure( "An execute was performed without permission. (" + testLabel + ")");
1951 }
1952 catch( SQLException sqle)
1953 {
1954 checkExecutePermissionMsg( sqle, user, testLabel);
1955 }
1956 finally
1957 {
1958 try
1959 {
1960 user.getConnection().rollback();
1961 }
1962 finally
1963 {
1964 user.getConnection().setAutoCommit( savedAutoCommit);
1965 }
1966 }
1967 }
1969
1972 protected void checkExecutePermissionMsg( SQLException sqle,
1973 User user,
1974 String testLabel)
1975 {
1976 checkSQLException( sqle, ! expectPriv, "2850A", testLabel,
1977 executePrivErrMsgFixedSegs,
1978 new String [][]{ new String [] { user.name},
1979 new String [] { (isFunction)?"FUNCTION":"PROCEDURE"},
1980 new String [] { schema},
1981 new String [] { routine}},
1982 new boolean[]{true, true, false, false});
1983 } } }
1986
1987class User
1988{
1989 public final String name;
1990 public final String password;
1991 private final boolean isPublic;
1992 private Connection conn;
1993 public Statement stmt;
1994
1995 User( String name, String password)
1996 {
1997 this.name = name;
1998 this.password = password;
1999 isPublic = "public".equalsIgnoreCase( name);
2000 }
2001
2002 boolean isPublic()
2003 {
2004 return isPublic;
2005 }
2006
2007 void setConnection( Connection conn) throws SQLException
2008 {
2009 this.conn = conn;
2010 stmt = conn.createStatement();
2011 }
2012
2013 Connection getConnection() throws SQLException
2014 {
2015 if( conn == null)
2016 {
2017 if( ! isPublic)
2018 {
2019 String connAttrs = "user=" + name + ";password=" + password;
2020 conn = TestUtil.getConnection("wombat", connAttrs);
2021 stmt = conn.createStatement();
2022 }
2023 }
2024 return conn;
2025 }
2026
2027 public String toString()
2028 {
2029 return name;
2030 }
2031} |
Popular Tags |
Java API By Example, From Geeks To Geeks.