Connecting ALM to an Oracle Database
Verify the following:
Database server name
Verify the name of the database server.
Database user permissions
Verify that you have the database permissions required to install ALM on the Oracle database server.
Database tablespace name and size
➤ Verify the name of the database server, and check the connection to the database server. Ping the database server machine name to test DNS resolution.
➤ Verify you have the tablespace names (default and temporary) and the minimum tablespace sizes for
storing the Site Administration database schema.
➤ Verify that the tablespace is not locked.
Database Column Length Semantics
Ensure that column length is defined according to characters, and not according to bytes.
User Permissions for Connecting ALM to an Oracle
To connect ALM to an Oracle database server, the installing database user must have sufficient permissions to perform certain administrative tasks in Oracle. These tasks include creating the ALM project user schema, copying data between projects, and checking that there is sufficient storage in a specific tablespace.
If you are unable to use the Oracle system user due to security reasons, it is recommended that your database administrator create an ALM database administrative user, for example qc_admin_db, with the specific privileges required to install ALM.
Your database administrator can create an ALM database administrative user using the example script, qc_admin_db___oracle.sql, which is located in the \Utilities\Databases_scripts directory on the installation DVD. This script creates the ALM database administrative user with the recommended grants required on the database. Your database administrator should run the script and create this user on the staging database server.
This section includes:
➤ "Database Administrative User Privileges" on page 63
➤ "Project User Schema Privileges" on page 65
Database Administrative User Privileges
Following are the privileges required by the ALM database administrative user. Additional explanations about these privileges can be found in the notes at the end of the table.
➤ (1) An ALM database administrative user must have privileges with Admin Option.
➤ (2) The SELECT ON SYS privileges can be given directly by the table owner, or through a database application role. To avoid giving these privileges each time, you can grant this role to the ALM database administrative user. The recommended name for this role is QC_SELECT_ON_SYS_OBJECTS.
You can create this role using the qc_sys_db___oracle.sql example script, which is located in the \Utilities\Databases_scripts directory on the installation DVD.
You should run this script before you run the qc_admin_db___oracle.sql script.
Privilege and Description:
CREATE SESSION WITH ADMIN OPTION (1)
ALM uses this privilege to connect to the database as the ALM database administrative user.
Required to create a new project user schema when creating a new ALM project.
When deleting an ALM project, ALM attempts to remove the Site Administration database schema from the database server. If there is an insufficient privileges error, ALM ignores the error and requests that the user notify the database administrator to delete (drop) the database user schema.
CREATE TABLE WITH ADMIN OPTION (1)
Required for granting this permission to a newly created ALM project user schema.
CREATE VIEW WITH ADMIN OPTION (1)
Required to create views for ALM projects.
CREATE TRIGGER WITH ADMIN OPTION (1)
Required to create triggers for ALM projects. ALM uses database triggers to collect change history for specific tables.
CREATE SEQUENCE WITH ADMIN OPTION (1)
Required to create sequences for ALM projects.
CREATE PROCEDURE WITH ADMIN OPTION (1)
Required to create stored packages for ALM projects. ALM uses packages to collect change history for specific tables.
CTXAPP ROLE WITH ADMIN OPTION (1)
Enables ALM to use the Oracle text searching feature. This role exists only if the Oracle text search component was installed and enabled on the database server.
SELECT ON DBA_FREE_SPACE (2)
Required to check free space on the database server prior to creating a new Site Administration database
schema or a new project.
SELECT ON SYS.DBA_TABLESPACES (2)
Required to collect a list of tablespaces that exist on the database server prior to creating a new Site
Administration database schema or a new project.
SELECT ON SYS.DBA_USERS (2)
Required to verify the existence of specific database project users. For example, you might want to verify
the existence of an Oracle CTXSYS user before creating a new ALM project.
SELECT ON SYS.DBA_REGISTRY (2)
Required to verify that the text search component is installed on the database server.
SELECT ON SYS.DBA_ROLES (2)
Required to verify that the text search role (CTXAPP) is installed on the database server.
SELECT ANY TABLE WITH ADMIN OPTION (1)
INSERT ANY TABLE
Required for various administrative operations when upgrading the Site Administration database schema during installation using the copy and upgrade method, and for enhancing performance when copying a project that has the same source and target database server.
Project User Schema Privileges
When creating a new project or restoring an existing project, ALM creates a project user schema. This user schema hosts all the tables that are used by the project for storing and retrieving data. Following are the required privileges for an ALM project user schema:
QUOTA UNLIMITED ON <default tablespace>
Required for creating database objects that are owned by the ALM project user schema. This privilege allows users to create tables in the default tablespace. It replaces the UNLIMITED TABLESPACE system privilege that gave users system privileges to create tables in any tablespace, including the SYSTEM tablespace.
ALM uses this privilege to connect to the database user schema in order to perform required operations. For example creating database objects such as tables, and using them to insert, retrieve, and delete data.
➤ CREATE TABLE
➤ CREATE VIEW
➤ CREATE TRIGGER
➤ CREATE SEQUENCE
➤ CREATE PROCEDURE
➤ CTXAPP Role