Creating databases and users for Oracle

This section includes information on setting up Oracle databases to work with WebSphere Portal.

Database considerations

When creating Oracle databases for use with WebSphere Portal, you should consider the following information:

Set up databases

You must create the databases that you are planning to use, create users, and grant privileges before you can configure WebSphere Portal to use the database. After you finish setting up the databases, continue to the Configuring WebSphere Portal for Oracle topic.

Creating databases

For additional information on the databases that you are creating, refer to the Oracle product documentation. For more information on the recommended database architecture, see the Planning for Oracle topic for information on the databases that you need to create.

Be sure that all databases to be used with WebSphere Portal are created as UTF-8 character set databases.

Note: If you are using remote Oracle databases, you must also copy the classes12.zip file from the remote Oracle server to the WebSphere Portal machine. The typical location is the <oracle_home>/jdbc/lib directory. Record the copy location on your local machine for future reference.

Creating Users

Ensure that you create users and grant the appropriate privileges in Oracle before configuring WebSphere Portal to work with Oracle.

Follow these steps to create the users in the WebSphere Portal databases:

  1. Log in to the database in which you want to create the new users.
  2. Using the architecture information in Planning for Oracle, create the users that you need in the appropriate databases by enter the following command in the SQL*Plus tool: 
    SQL> create user <username> identified by <password>
default tablespace <user_tablespace>
temporary tablespace <temp_tablespace>;

    where <user_tablespace> is the default tablespace that is identified by the database administrator to store user objects and <temp_tablespace> is identified to store temporary objects.

    For example, to create the wpsdbusr in the wps50 database, log in to the wps50 database and type: 

    SQL> create user WPSDBUSR identified by ABC123
default tablespace USERS
temporary tablespace TEMP;

To implement the architecture based on the recommendations in the planning section, follow these steps:

  1. Start SQL*Plus (the Oracle SQL command line tool), and type the following commands to log in: 
    $ sqlplus

    Press Enter

    Enter user-name:
    username/password@<dbname>

    where username is an existing administrative user in the database. For example:

    system/manager@wps50 will log the administrative user system with a password of manager into the wps50 database.

  2. Create the WebSphere Portal users 
    SQL> create user WPSDBUSR identified by PASSWORD default tablespace USERS temporary tablespace TEMP;
SQL> create user WMMDBUSR identified by PASSWORD default tablespace USERS temporary tablespace TEMP;
  3. Connect to the content publishing database 
    SQL> connect

    Press Enter

    Enter user-name:
    username/password@<dbname>

    where username is an existing administrative user in the database. For example:

    system/manager@wpcp50 will log the administrative user system with a password of manager into the wpcp50 database.

  4. Create the content publishing users 
    SQL> create user PZNADMIN identified by PASSWORD default tablespace USERS temporary tablespace TEMP;
SQL> create user EJB identified by PASSWORD default tablespace USERS temporary tablespace TEMP;
SQL> create user WCMDBADM identified by PASSWORD default tablespace USERS temporary tablespace TEMP;

  5. Connect to the Feedback database

    SQL> connect

    Press Enter

    Enter user-name:
    username/password@<dbname>

    where username is an existing administrative user in the database. For example:

    system/manager@fdbk50 will log the administrative user system with a password of manager into the fdbk50 database.

  6. Create the content publishing feedback user 
    SQL> create user FEEDBACK identified by PASSWORD default tablespace USERS temporary tablespace TEMP;
  7. To exit, type 
    SQL> exit

Granting privileges

Grant these two default roles and their corresponding default privileges to each of the users that you created:

To grant WebSphere Portal users the appropriate privileges, perform the following steps:

  1. Log in to the database in which you want to grant privileges to users.
  2. Log in to SQLPlus as system/manager or another administrative user. Use the connect command in the Creating users section to connect to the appropriate database.
  3. Type the following command from the prompt to grant privileges: 
    SQL> grant connect, resource to <username>;
    where <username> represents the database user.

    For example, to grant privileges for users in the wps50 database:

    SQL> grant connect, resource to wpsdbusr;

To implement the architecture based on the recommendations in the planning section, follow these steps:

  1. Connect to the WebSphere Portal database

    SQL> connect

    Press Enter

    Enter user-name:
    username/password@<dbname>

    where username is an existing administrative user in the database. For example:

    system/manager@wps50 will log you in to the wps50 database.

  2. Grant WebSphere Portal users the required privileges by typing

    SQL> grant connect, resource to WPSDBUSR;
SQL> grant connect, resource to WMMDBUSR;

  3. Connect to the content publishing database

    SQL> connect

    Press Enter

    Enter user-name:
    username/password@<dbname>

    where username is an existing administrative user in the database. For example:

    system/manager@wpcp50 will log you into the wpcp50 database.

  4. Grant content publishing users the required privileges by typing

    SQL> grant connect, resource to PZNADMIN;
SQL> grant connect, resource to EJB;
SQL> grant connect, resource to WCMDBADM;
SQL> grant insert any table to WCMDBADM;

    Note: The insert privilege is required only to import the database during WebSphere Portal configuration. After configuring WebSphere Portal, this privilege can be revoked, if desired.

  5. Connect to the content publishing Feedback database

    SQL> connect

    Press Enter

    Enter user-name:
    username/password@<dbname>

    where username is an existing administrative user in the database. For example:

    system/manager@fdbk50 will log you in to the fdbk50 database.

  6. Grant the content publishing feedback user the required privileges by typing

    SQL> grant connect, resource to FEEDBACK;
  7. To exit, type 
    SQL> exit

Next steps

You have completed this step. Continue to the next step by choosing one of the following topics:

Related information