Preparing and executing the grantusr.sql file

For an initial Siebel CRM installation, it is mandatory to prepare and execute the SQL script provided in the grantusr.sql file. We can find this file in the database-specific subdirectory of the Database Server Utilities installation folder. In our example, the file is situated in the ORACLE subdirectory of the dbsrvr folder.

The following amendments need to be made to the file before executing it against the Oracle database:

• Enter the correct tablespace names
• Modify default passwords if needed
• Add additional user accounts

We will discuss these steps in the following sections.

Enter the correct tablespace names

The file is prepared for use through Oracle SQL*Plus, which means that tablespace names are represented by placeholders such as &&siebel_tablespace. SQL*Plus will stop at these placeholders and prompt the user to enter the names. In order to create a script that is usable with any SQL tool, we will write the tablespace names directly to the file.

We can open the file in any plain text processor such as Microsoft Notepad and replace the text as follows:

1. Replace all the occurrences of &&siebel_tablespace with the name of the tablespace for data tables as identified in the planning document (in our example SIEBELDB_DATA).
2. Replace all the occurrences of &&temp_tablespace with TEMP (we use the default TEMP tablespace provided during installation of the Oracle database).
3. Find the line alter user SIEBEL quota unlimited on SIEBELDB_DATA;. Below this line, create a new line (for example by copying and pasting the line) and change it to "alter user SIEBEL quota unlimited on SIEBELDB_IDX;". This ensures that the user SIEBEL (the table owner) will have unlimited quota on the index tablespace.

Modify default passwords if needed

The grantusr.sql file contains commands to create three user accounts in the database:

• SIEBEL (table owner)
• SADMIN (administrative user)
• LDAPUSER (proxy user for LDAP authentication)

All three are created in the Oracle database by commands similar to the following:

create user SIEBEL identified by SIEBEL;

The first occurrence of SIEBEL will be the user account name whereas the second occurrence (after the words identified by) will be the password. If required to, we must replace the default passwords—which are the same as the account name—with the correct passwords identified in the planning document.

Add additional user accounts

If we inspect our planning documentation carefully, we will find that we specified other user accounts besides SIEBEL and SADMIN. Namely, GUESTCST will be used for anonymous access to customer and partner-facing applications.

If any additional accounts have to be established before the database installation, it might be a good idea to add the respective commands to the grantusr.sql file.

To accomplish this, we can simply copy and paste the lines that cater for the LDAPUSER account and modify the copied lines accordingly to represent the creation of the GUESTCST account.

create user GUESTCST identified by 8icJIPZH;
grant sse_role to GUESTCST;
alter user GUESTCST default tablespace SIEBELDB_DATA;
alter user GUESTCST temporary tablespace TEMP;

The code above shows the lines that were added to the grantusr.sql file in order to create the GUESTCST account.

In summary, the grantusr.sql file now contains commands to direct the Oracle database to do the following:

• Create the sse_role role for normal accounts and grant the create session privilege to it:

  create role sse_role;
  grant create session to sse_role;

• Create the tblo_role role for the table owner account and grant various privileges to it:

  create role tblo_role;
  grant ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, CREATE DIMENSION, CREATE MATERIALIZED VIEW, QUERY REWRITE, ON COMMIT REFRESH
  to tblo_role;

• Create the table owner account SIEBEL, associate it to the sse_role and tblo_role roles and define quotas on the tablespaces:

  rem Create SIEBEL user
  create user SIEBEL identified by dQ7JXufi;
  grant tblo_role to SIEBEL;
  grant sse_role to SIEBEL;
  alter user SIEBEL quota 0 on SYSTEM quota 0 on SYSAUX;
  alter user SIEBEL default tablespace SIEBELDB_DATA;
  alter user SIEBEL temporary tablespace TEMP;
  alter user SIEBEL quota unlimited on SIEBELDB_DATA;
  alter user SIEBEL quota unlimited on SIEBELDB_IDX;

• Create three additional user accounts with the sse_role role assigned:

  create user SADMIN identified by TJay357D;
  grant sse_role to SADMIN;
  alter user SADMIN default tablespace SIEBELDB_DATA;
  alter user SADMIN temporary tablespace TEMP;
  
  create user LDAPUSER identified by BFxR87DT;
  grant sse_role to LDAPUSER;
  alter user LDAPUSER default tablespace SIEBELDB_DATA;
  alter user LDAPUSER temporary tablespace TEMP;
  
  create user GUESTCST identified by 8icJIPZH;
  grant sse_role to GUESTCST;
  alter user GUESTCST default tablespace SIEBELDB_DATA;
  alter user GUESTCST temporary tablespace TEMP;
  

Executing the grantusr.sql Script

After saving the changes we made to the grantusr.sql file, we can open the SQL tool of our choice such as Oracle's SQL*Plus, connect to the Oracle database with sufficient privileges, and execute the contents of the grantusr.sql file.

In Oracle's SQL*Plus, we use a command similar to the example below to execute the file's content:

@C:\SIA8\dbsrvr\Oracle\grantusr.sql

The @ sign is used to open and execute the SQL script in the given path. Messages such as "user created" and "user altered" indicate successful execution of the script.