Creating users for Oracle databases

For all databases except the Content database, use one of the following methods to create each database user that you require. Then see the third bullet to create the Content database.

• Create and run the createUser.sql file as described in the following substeps:
  1. Save the following SQL statements into a file named createUser.sql:

     -- create a new user
     CREATE USER @DB_USER@ IDENTIFIED BY @DB_PASSWD@;
     
     -- allow the user to connect to the database
     grant connect to @DB_USER@;
     
     -- provide quota on all table spaces with BPM tables
     grant unlimited tablespace to @DB_USER@;
     
     -- grant privileges to create database objects:
     grant resource to @DB_USER@;
     grant create view to @DB_USER@;
     
     -- grant access rights to resolve lock issues
     grant execute on dbms_lock to @DB_USER@;
     
     -- grant access rights to resolve XA related issues:
     grant select on pending_trans$ to @DB_USER@;
     grant select on dba_2pc_pending to @DB_USER@;
     grant select on dba_pending_transactions to @DB_USER@;
     -- If using Oracle 10.2.0.3 or lower JDBC driver, un-comment the following statement:
     -- grant execute on dbms_system to @DB_USER@;
     -- If not using Oracle 10.2.0.4 or higher JDBC driver, comment the following statement:
     grant execute on dbms_xa to @DB_USER@;

     For information about recovering Oracle database transactions, see the topic Configuring XA transactions for Oracle in a network environment on Windows.
  2. In the SQL statements, replace @DB_USER@ with the user name that you want to use for the database and replace @DB_PASSWD@ with the password for that user.
  3. Run the following command to create the database user:

     sqlplus oracle_user_ID/oracle_password@db_name @createUser.sql

• Run SQL statements in a command editor as described in the following substeps:
  1. Copy the following SQL statements into a command editor:

     -- create a new user
     CREATE USER @DB_USER@ IDENTIFIED BY @DB_PASSWD@;
     
     -- allow the user to connect to the database
     grant connect to @DB_USER@;
     
     -- provide quota on all table spaces with BPM tables
     grant unlimited tablespace to @DB_USER@;
     
     -- grant privileges to create database objects:
     grant resource to @DB_USER@;
     grant create view to @DB_USER@;
     
     -- grant access rights to resolve lock issues
     grant execute on dbms_lock to @DB_USER@;
     
     -- grant access rights to resolve XA related issues:
     grant select on pending_trans$ to @DB_USER@;
     grant select on dba_2pc_pending to @DB_USER@;
     grant select on dba_pending_transactions to @DB_USER@;
     -- If using Oracle 10.2.0.3 or lower JDBC driver, un-comment the following statement:
     -- grant execute on dbms_system to @DB_USER@;
     -- If not using Oracle 10.2.0.4 or higher JDBC driver, comment the following statement:
     grant execute on dbms_xa to @DB_USER@;

  2. In the SQL statements, replace @DB_USER@ with the user name that you want to use for the database and replace @DB_PASSWD@ with the password for that user.
  3. Run the SQL statements in the command editor.
• Create the content database (CPEDB) and the users for the design object store (DOS), target object store (TOS), and IBM Content Navigator (ICN):
  1. Save the following SQL statements into a file named createDatabase_ECM.sql:

     
     CREATE SMALLFILE TABLESPACE @ECM_DATA_TS@ DATAFILE '@DB_DIR@/@DB_NAME@/@ECM_DATA_TS@.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
     
     CREATE USER @DB_USER@ PROFILE DEFAULT IDENTIFIED BY @DB_PASSWD@ DEFAULT TABLESPACE @ECM_DATA_TS@ TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
     GRANT CREATE TABLESPACE TO @DB_USER@;
     GRANT UNLIMITED TABLESPACE TO @DB_USER@;
     GRANT CONNECT TO @DB_USER@;
     GRANT RESOURCE TO @DB_USER@;
     grant create view to @DB_USER@;
     grant execute on dbms_lock to @DB_USER@;
     grant select on pending_trans$ to @DB_USER@;
     grant select on dba_2pc_pending to @DB_USER@;
     grant select on dba_pending_transactions to @DB_USER@;
     grant execute on dbms_xa to @DB_USER@;
     

  2. Update and run the file to create the target object schema (TOS) user. Replace @DB_DIR@ with the data directory of your database, which you must create before running the SQL files. Replace @DB_NAME@ with the Oracle instance name, for example, orcl, @DB_USER@ with the schema name, and @DB_PASSWD@ with the password for the user. Replace @ECM_DATA_TS@ with TOSSA_DATA_TS.
     Remember the names you choose for users. You will enter them in the launchpad.
  3. Update and run the file again to create the design object schema (DOS) user. Replace @ECM_DATA_TS@ with DOSSA_DATA_TS.
     Remember the names you choose for users. You will enter them in the launchpad.
  4. Update and run the createUser.sql file you created in an earlier step to create one user for the Content database.
  5. Save the following SQL statements into a file named createTablespaceICN.sql:

     
     -- Create table spaces
     
     CREATE TABLESPACE @ECMClient_TBLSPACE@
         DATAFILE '@DB_DIR@/@DB_NAME@/@ECMClient_TBLSPACE@.dbf' SIZE 200M REUSE
         AUTOEXTEND ON NEXT 20M
         EXTENT MANAGEMENT LOCAL
         SEGMENT SPACE MANAGEMENT AUTO
         ONLINE
         PERMANENT
     ;
     
     CREATE TEMPORARY TABLESPACE @ECMClient_TBLSPACE@TEMP
         TEMPFILE '@DB_DIR@/@DB_NAME@/@ECMClient_TBLSPACE@TEMP.dbf' SIZE 200M REUSE
         AUTOEXTEND ON NEXT 20M
         EXTENT MANAGEMENT LOCAL
     ;
     
     
     -- Alter existing schema
     
     ALTER USER @ECMClient_SCHEMA@
         DEFAULT TABLESPACE @ECMClient_TBLSPACE@ 
         TEMPORARY TABLESPACE @ECMClient_TBLSPACE@TEMP;
     
     GRANT CONNECT, RESOURCE to @ECMClient_SCHEMA@;
     GRANT UNLIMITED TABLESPACE TO @ECMClient_SCHEMA@;
     

  6. Update and run the file to create the IBM Content Navigator (ICN) user. Replace @ECMClient_SCHEMA@ with the user you created in createUser.sql. Replace @DB_USER@ with the user name that you want to use and replace @DB_PASSWD@ with the password for that user. Replace the table space name @ECMClient_TBLSPACE@ with WFICNTS.