View Javadoc
1   package org.dbunit.database;
2   
3   import static org.junit.Assert.assertEquals;
4   import static org.junit.Assert.assertNotNull;
5   import static org.junit.Assert.fail;
6   
7   import java.sql.Connection;
8   import java.util.Arrays;
9   
10  import org.dbunit.H2Environment;
11  import org.dbunit.dataset.DataSetException;
12  import org.dbunit.dataset.IDataSet;
13  import org.dbunit.dataset.ITable;
14  import org.dbunit.dataset.NoSuchTableException;
15  import org.dbunit.ext.h2.H2DataTypeFactory;
16  import org.dbunit.testutil.TestUtils;
17  import org.junit.After;
18  import org.junit.AfterClass;
19  import org.junit.BeforeClass;
20  import org.junit.Test;
21  
22  /**
23   * Test the multiple schema support of DatabaseDataSet.
24   * 
25   * <p>
26   * This test case uses the H2 database because it offers easy handling of
27   * schemas / users.
28   * </p>
29   */
30  public class DatabaseDataSet_MultiSchemaTest
31  {
32      private static final String DATABASE = "multischematest";
33      private static final String USERNAME_ADMIN = "sa";
34      private static final String USERNAME_DBUNIT = "DBUNITUSER";
35      private static final String USERNAME_DEFAULT = "DEFAULTUSER";
36      private static final String PASSWORD = "test";
37      private static final String PASSWORD_NONE = "";
38      private static final String SCHEMA_DEFAULT = USERNAME_DEFAULT;
39      private static final String SCHEMA_DBUNIT = USERNAME_DBUNIT;
40      private static final String SCHEMA_NONE = null;
41  
42      private static final String TABLE_BAR = "BAR";
43      private static final String TABLE_FOO = "FOO";
44  
45      private static final String TABLE_BAR_IN_SCHEMA_DBUNIT = SCHEMA_DBUNIT
46              + "." + TABLE_BAR;
47      private static final String TABLE_FOO_IN_SCHEMA_DEFAULT = SCHEMA_DEFAULT
48              + "." + TABLE_FOO;
49  
50      private static final Boolean IS_USING_QUALIFIED_TABLE_NAMES = Boolean.TRUE;
51      private static final Boolean IS_NOT_USING_QUALIFIED_TABLE_NAMES =
52              Boolean.FALSE;
53  
54      private static final String SETUP_DDL_FILE =
55              "sql/h2_multischema_permission_test.sql";
56  
57      private static Connection connectionDdl;
58  
59      private IDatabaseConnection connectionTest;
60  
61      @BeforeClass
62      public static void setUpClass() throws Exception
63      {
64          // create database and schemas for tests
65          connectionDdl = H2Environment.createJdbcConnection(DATABASE);
66          H2Environment.executeDdlFile(TestUtils.getFile(SETUP_DDL_FILE),
67                  connectionDdl);
68      }
69  
70      @AfterClass
71      public static void tearDownClass() throws Exception
72      {
73          // close connection after all tests so schemas stay around
74          if (connectionDdl != null && !connectionDdl.isClosed())
75          {
76              connectionDdl.close();
77          }
78      }
79  
80      @After
81      public void tearDown() throws Exception
82      {
83          if (connectionTest != null)
84          {
85              connectionTest.close();
86          }
87      }
88  
89      /**
90       * Admin user has full access to all tables in all schemas.
91       * 
92       * @throws Exception
93       */
94      @Test
95      public void testPermissions_AdminUser_QualifiedTableNames()
96              throws Exception
97      {
98          IDataSet dataSet =
99                  makeDataSet(DATABASE, USERNAME_ADMIN, PASSWORD_NONE,
100                         SCHEMA_NONE, IS_USING_QUALIFIED_TABLE_NAMES);
101 
102         String[] allTables = dataSet.getTableNames();
103         Arrays.sort(allTables);
104         assertEquals(2, allTables.length);
105         assertEquals(TABLE_BAR_IN_SCHEMA_DBUNIT, allTables[0]);
106         assertEquals(TABLE_FOO_IN_SCHEMA_DEFAULT, allTables[1]);
107     }
108 
109     /**
110      * As basic schema owner you will have access to your own tables, but not to
111      * other ones.
112      * 
113      * @throws Exception
114      */
115     @Test
116     public void testPermissions_OwningUser_QualifiedTableNames()
117             throws Exception
118     {
119         IDataSet dataSet =
120                 makeDataSet(DATABASE, USERNAME_DEFAULT, PASSWORD,
121                         SCHEMA_DEFAULT, IS_USING_QUALIFIED_TABLE_NAMES);
122 
123         // Own table
124         String[] allTables = dataSet.getTableNames();
125         Arrays.sort(allTables);
126         assertEquals(1, allTables.length);
127         assertEquals(TABLE_FOO_IN_SCHEMA_DEFAULT, allTables[0]);
128 
129         // Table of other user/schema
130         try
131         {
132             dataSet.getTable(TABLE_BAR_IN_SCHEMA_DBUNIT);
133             fail();
134         } catch (DataSetException e)
135         {
136             // Not enough permissions
137         }
138     }
139 
140     /**
141      * If we don't use qualified table names, then we still use only our own
142      * tables.
143      * 
144      * @throws Exception
145      */
146     @Test
147     public void testPermissions_OwningUser_UnqualifiedTableNames()
148             throws Exception
149     {
150         IDataSet dataSet =
151                 makeDataSet(DATABASE, USERNAME_DEFAULT, PASSWORD,
152                         SCHEMA_DEFAULT, IS_NOT_USING_QUALIFIED_TABLE_NAMES);
153 
154         String[] allTables = dataSet.getTableNames();
155         Arrays.sort(allTables);
156         assertEquals(1, allTables.length);
157         assertEquals(TABLE_FOO, allTables[0]);
158 
159         // Table of other user/schema
160         try
161         {
162             dataSet.getTable(TABLE_BAR);
163             fail();
164         } catch (NoSuchTableException e)
165         {
166             // expected
167         }
168     }
169 
170     /**
171      * A special dbunit user could be allowed to access tables from other users
172      * to prepare test data.
173      * 
174      * @throws Exception
175      */
176     @Test
177     // THIS ONE FAILS WITHOUT ISSUE 368 IN PLACE
178     public void testPermissions_DbunitUser_QualifiedTables() throws Exception
179     {
180         IDataSet dataSet =
181                 makeDataSet(DATABASE, USERNAME_DBUNIT, PASSWORD, SCHEMA_DBUNIT,
182                         IS_USING_QUALIFIED_TABLE_NAMES);
183 
184         String[] allTables = dataSet.getTableNames();
185         Arrays.sort(allTables);
186         assertEquals(1, allTables.length);
187         assertEquals(TABLE_BAR_IN_SCHEMA_DBUNIT, allTables[0]);
188 
189         // Access table of other owner - metadata will be lazy loaded
190         ITable table = dataSet.getTable(TABLE_FOO_IN_SCHEMA_DEFAULT);
191         assertNotNull(table);
192 
193         // Unqualified access to table isn't possible
194         try
195         {
196             table = dataSet.getTable(TABLE_FOO);
197             fail();
198         } catch (NoSuchTableException e)
199         {
200             // expected
201         }
202     }
203 
204     @Test
205     public void testPermissions_DbunitUser_UnqualifiedTables() throws Exception
206     {
207         IDataSet dataSet =
208                 makeDataSet(DATABASE, USERNAME_DBUNIT, PASSWORD, SCHEMA_DBUNIT,
209                         IS_NOT_USING_QUALIFIED_TABLE_NAMES);
210 
211         String[] allTables = dataSet.getTableNames();
212         Arrays.sort(allTables);
213         assertEquals(1, allTables.length);
214         assertEquals(TABLE_BAR, allTables[0]);
215 
216         // Access table of other owner
217         ITable table = dataSet.getTable(TABLE_BAR);
218         assertNotNull(table);
219 
220         try
221         {
222             dataSet.getTable(TABLE_FOO);
223             fail();
224         } catch (NoSuchTableException e)
225         {
226             // expected
227         }
228     }
229 
230     /**
231      * Without explicit schema selection, all available tables will be loaded...
232      * 
233      * @throws Exception
234      */
235     @Test
236     public void testPermissions_DbunitUser_QualifiedTableNames_NoSpecifiedSchema()
237             throws Exception
238     {
239         IDataSet dataSet =
240                 makeDataSet(DATABASE, USERNAME_DBUNIT, PASSWORD, SCHEMA_NONE,
241                         IS_USING_QUALIFIED_TABLE_NAMES);
242 
243         String[] allTables = dataSet.getTableNames();
244         Arrays.sort(allTables);
245         assertEquals(2, allTables.length);
246         assertEquals(TABLE_BAR_IN_SCHEMA_DBUNIT, allTables[0]);
247         assertEquals(TABLE_FOO_IN_SCHEMA_DEFAULT, allTables[1]);
248 
249         // Qualified access to own tables...
250         ITable table = dataSet.getTable(TABLE_BAR_IN_SCHEMA_DBUNIT);
251         assertNotNull(table);
252 
253         // Qualified access to other tables...
254         table = dataSet.getTable(TABLE_FOO_IN_SCHEMA_DEFAULT);
255         assertNotNull(table);
256 
257         // But unqualified access doesn't work...
258         try
259         {
260             dataSet.getTable(TABLE_FOO);
261             fail();
262         } catch (NoSuchTableException e)
263         {
264             // expected
265         }
266     }
267 
268     /**
269      * Without explizit schema selection, all available tables will be loaded -
270      * but without qualified table access, no metadata will be found.
271      * 
272      * @throws Exception
273      */
274     @Test
275     public void testPermissions_DbunitUser_UnqualifiedTableNames_NoSpecifiedSchema()
276             throws Exception
277     {
278         IDataSet dataSet =
279                 makeDataSet(DATABASE, USERNAME_DBUNIT, PASSWORD, SCHEMA_NONE,
280                         IS_NOT_USING_QUALIFIED_TABLE_NAMES);
281 
282         String[] allTables = dataSet.getTableNames();
283         Arrays.sort(allTables);
284         assertEquals(2, allTables.length);
285         assertEquals(TABLE_BAR, allTables[0]);
286         assertEquals(TABLE_FOO, allTables[1]);
287 
288         // Qualified access to own tables...
289         try
290         {
291             dataSet.getTable(TABLE_BAR);
292         } catch (DataSetException e1)
293         {
294             // No metadata could be loaded...
295         }
296 
297         // Qualified access to other tables...
298         try
299         {
300             dataSet.getTable(TABLE_FOO);
301         } catch (DataSetException e1)
302         {
303             // No metadata could be loaded...
304         }
305 
306         // But unqualified access doesn't work...
307         try
308         {
309             dataSet.getTable(TABLE_FOO_IN_SCHEMA_DEFAULT);
310             fail();
311         } catch (NoSuchTableException e)
312         {
313             // expected
314         }
315     }
316 
317     private IDataSet makeDataSet(String databaseName, String username,
318             String password, String schema, boolean useQualifiedTableNames)
319             throws Exception
320     {
321         makeDatabaseConnection(databaseName, username, password, schema,
322                 useQualifiedTableNames);
323 
324         return connectionTest.createDataSet();
325     }
326 
327     private void makeDatabaseConnection(String databaseName, String username,
328             String password, String schema, boolean useQualifiedTableNames)
329             throws Exception
330     {
331         Connection jdbcConnection =
332                 H2Environment.createJdbcConnection(databaseName, username,
333                         password);
334         connectionTest = new DatabaseConnection(jdbcConnection, schema);
335         final DatabaseConfig config = connectionTest.getConfig();
336         config.setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES,
337                 useQualifiedTableNames);
338         config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
339                 new H2DataTypeFactory());
340     }
341 }