Oracle roles can cause problems during ClearQuest user database creation
http://www-1.ibm.com/support/docview.wss?uid=swg21217027
Problem(Abstract)
This technote addresses a potential issue that can occur if creating a database in IBM® Rational® ClearQuest®, when the Oracle user accounts have been granted roles in addition to CONNECT and RESOURCE, which are the required roles.
Symptom
Sample error message if Oracle user accounts have incorrect roles:
Unable to initialize database "TEST" with schema "Enterprise" version 1. Reason: SQLExecDirect: RETCODE=-1, State=42502, Native Error=942
SQL statement="delete from userdb_packagerev_usage"
[OpenLink][ODBC][Oracle Server]ORA-00942: table or view does not exist
The log file C:\DOCUME~1\myuser\LOCALS~1\Temp\TEST_Enterprise_1_init.log may be useful in resolving this problem.
Cause
Oracle user accounts that have been granted roles in addition to CONNECT and RESOURCE can cause a failure during ClearQuest database creation.
Resolving the problem
To determine which roles have been granted to a specific Oracle user follow the steps as outlined below.
Using Oracle SQL Plus:
* Connect to the Oracle Database as a 'SYSDBA'
'SYSDBA' would be the system DBA account
* Execute the following SQL script, providing the Oracle username in question:
select grantee, granted_role
from dba_role_privs
where grantee = <username>
Example:
If the User created initially was called 'CLEARQUEST', the sql statement would be:
select grantee, granted_role
from dba_role_privs
where grantee = 'CLEARQUEST'
The output desired would be:
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
CLEARQUEST RESOURCE
CONNECT
To alter an existing user account's permissions, execute the following statements:
To grant CONNECT and RESOURCE roles:
grant CONNECT, RESOURCE to CLEARQUEST;
To revoke roles:
revoke <role> from CLEARQUEST;
Where <role> would be any role such as DBA
Note for ClearQuest 2002 and 2003 versions:
The only exception to the above would be the granted role of CTXAPP - this role is pertinent to Multi-line Text searching using the Oracle InterMedia Option and does not need to be revoked.
For more information regarding Multi-line Text searching see "Enhancing Oracle Search Capabilities" in the Rational 2003 Server Product Installation Guide.
This guide is located on the 'Rational Solutions for Windows Online Documentation CD. From the table of contents:
Topics -> Rational Server Products Installation Guide
From the Rational Server Product Installation Guide's table of contents:
> After Install: Configuring Databases and Administrative Tasks for ClearQuest
> Configuring the Oracle Server for ClearQuest
> Enhancing Oracle Search Capabilities
