RoundhousE Structure Tables Cannot Be Created By User With All Privileges

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Do one of a or b.

a. Connect to the database as SYSTEM or a user with similar privileges. Execute 
the attached CreateAllPrivsRoundhouseUser.sql script. This creates a user with 
ALL PRIVILEGES and UNLIMITED quota on the USERS tablespace. If desired, there 
is no reason you cannot change the password assigned to the user; if you do, I 
recommend sticking with upper case alphanumeric characters. Oracle does some 
funny stuff with passwords.

b. If you do not want to create a new user with ALL PRIVILEGES, then you should 
be able to use SYSTEM or a similar account. However, you should ensure that it 
has a sufficient quota on its default tablespace. If you want a specific 
number, 50 M should be more than enough. This is because no quota will also 
cause the error to occur. (See Issue 65 for my thoughts on that.) You must also 
ensure that the account does not own any previously created RoundhousE 
structure tables.

I would like to note that I think creating a new user does more to ensure that 
there isn't something strange about the user since it wasn't a built in account.


2. Execute RoundhousE as the user decided on in step 1.
3. Observer error below.
4. Connect to the database as the user chosen in step 1 and execute the 
following queries:

Checks for tables on current schema:
SELECT DISTINCT *
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME LIKE '%ROUNDHOUSE%';

Observe that the query returns no results, indicating that no tables with 
"ROUNDHOUSE" in the name exist on the schema.

Checks for tables on all schemas:
SELECT OWNER, TABLE_NAME FROM dba_tables WHERE table_name LIKE '%ROUNDHOUSE%';

This may find tables on other schemas, but take note of the owner. There 
shouldn't be any structure tables in the chosen schema or in schemas that 
didn't have structure tables from previous, explicable runs.

6. If you created the new user (chose a) in step 1, connect as SYSTEM or 
similar and execute the DropAllPrivsRoundhouseUser.sql script to drop the user.

What is the expected output? What do you see instead?

Running RoundhousE v0.8.5.0 against roundhouse_bugs - .
Looking in . for scripts to run.
==================================================
Setup, Backup, Create/Restore/Drop
==================================================
==================================================
RoundhousE Structure
==================================================
 Running database type specific tasks.
Creating a sequence for the 'Version' table.
Creating a sequence for the 'ScriptsRun' table.
Creating a sequence for the 'ScriptsRunErrors' table.
 Creating [Version] table if it doesn't exist.
 Creating [ScriptsRun] table if it doesn't exist.
 Creating [ScriptsRunErrors] table if it doesn't exist.
==================================================
Versioning
==================================================
roundhouse.databases.oracle.OracleDatabase with provider 
System.Data.OracleClient does not provide a facility for retrieving versions at 
this time.
 Attempting to resolve version from C:\Projects\CSharp\experiments\roundhousebugs\_BuildInfo.xml using //buildInfo/version.
 Found version 1.0.0.0 from C:\Projects\CSharp\experiments\roundhousebugs\_BuildInfo.xml.
 Migrating  from version 0 to 1.0.0.0.
 Versioning  database with version 1.0.0.0 based on blah.
RoundhousE encountered an error.
System.ArgumentException: The OracleParameter is already contained by another 
OracleParameterCollection.
   at System.Data.OracleClient.OracleParameterCollection.Validate(Int32 index, Object value)
   at System.Data.OracleClient.OracleParameterCollection.Add(Object value)
   at roundhouse.databases.AdoNetDatabase.setup_database_command(String sql_to_run, ConnectionType connection_type, IEnumerable`1 parameters)
   at roundhouse.databases.AdoNetDatabase.run_command_with(String sql_to_run, ConnectionType connection_type, IList`1 parameters)
   at roundhouse.databases.AdoNetDatabase.run_sql(String sql_to_run, ConnectionType connection_type, IList`1 parameters)
   at roundhouse.databases.oracle.OracleDatabase.insert_version_and_get_version_id(String repository_path, String repository_version)
   at roundhouse.migrators.DefaultDatabaseMigrator.version_the_database(String repository_path, String repository_version)
   at roundhouse.runners.RoundhouseMigrationRunner.run()
The OracleParameter is already contained by another OracleParameterCollection.


What version of the product are you using? On what operating system?

RoundhousE v0.8.5.0
Windows 7, 64-bit
Oracle XE 11g 32-bit running locally

Please provide any additional information below.

See Issue 65 for information about how I know this problem is caused by the 
structure tables not being created.

I have no idea why the tables aren't created. I'm guessing that it's probably 
related to a specific privilege or combination of them and that revoking them 
would make the structure create correctly. But there are far too many 
privileges to test that by brute force.

This issue isn't very high priority since it a has simple work around that best 
practices dictate ought be done anyway: don't use a schema with all privileges.

Original issue reported on code.google.com by [email protected] on 3 Dec 2011 at 11:13

Attachments:

• CreateAllPrivsRoundhouseUser.sql
• DropAllPrivsRoundhouseUser.sql