Store Session Information in Oracle

Contents
sm1252sp1
Contents
2
Gather Database Information
Configuring a single Oracle database to function as a policy store or any other type of 
CA Single Sign-On
 data store requires specific database information.
Information prefixed with (U) indicates that the information is only required if the Policy Server is installed on a UNIX system. This information is required when configuring Oracle data source for UNIX.
Required Information
Gather the following required information before configuring a supported Oracle or Oracle RAC database as a policy store or any other type of 
CA Single Sign-On
 data store:
  • (U)
     Policy Server installation path
    —Identify the explicit path to where the Policy Server is installed.
  • Data source
    —Determine the name you will use to identify the Oracle data source.
    Example:
     SM Oracle Server Wire DS.
  • Database administrative account
    —Determine the user name of an account with privileges to create, read, modify, and delete objects in the database.
    Ensure the administrative account does not have the DB role. Audit-based reports will not return correct results if the administrative account has the DB role.
  • Database administrative Password
    —Determine the password for the Administrative account.
Oracle Database Information
Gather the following information only if you are configuring a supported Oracle database as a policy store or any other type of 
CA Single Sign-On
 data store:
  • Oracle machine name
    —Determine the name of the machine on which the Oracle database is installed.
  • Oracle instance service name
    —Determine the service name of the database instance to which you will connect. The tnsnames.ora file specifies service names.
  • Oracle port number
    —Determine the port number on which the Oracle database is listening.
Oracle RAC Database (without SCAN) Information
Gather the following information if you are configuring a supported Oracle RAC database (without SCAN functionality configured) as a policy store or any other 
CA Single Sign-On
 data store:
  • Oracle RAC system service name—
    Determine the service name for the entire system.
    Example:
     In the following tnsnames.ora file, SMDB is the service name for the entire system:
    SMDB=
    (Description =
    (ADDRESS = PROTOCOL = TCP)(HOST = nete_servername1)(PORT=1521
    (ADDRESS = PROTOCOL = TCP)(HOST = nete_servername2)(PORT=1521)
    (ADDRESS = PROTOCOL = TCP)(HOST = nete_servername3)(PORT=1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA=
    (SERVER = DEDICATED)
    (SERVER_NAME = SMDB))
    )
  • Oracle RAC node service names
    —Determine the service names for each node in the system.
  • Oracle RAC node IP addresses
    —Determine the IP Address of each node in the Oracle RAC system.
    If you are using Oracle RAC 10g, determine the virtual IP address of each node in the system.
  • Oracle RAC node port numbers
    —Determine the port number for each node in the Oracle RAC system.
Oracle RAC Database (Using SCAN) Information
The Oracle RAC Single Client Access Name (SCAN) feature provides a single name for clients to access any Oracle Database running in a cluster.
Gather the following information if you are configuring an Oracle RAC database with SCAN functionality as a policy store or any other 
CA Single Sign-On
 data store:
  • Oracle RAC system service name
    —Determine the service name for the entire system.
    Example:
     In the following tnsnames.ora file, SMDB is the service name for the entire system:
    SMDB=
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = clus-scan.example.com)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = SMDB)
        )
      )
  • Oracle RAC SCAN Address
    —Determine the FQDN of the Oracle RAC system SCAN.
  • Oracle RAC SCAN port number
    —Determine the port number for the Oracle RAC system SCAN.
Create the Session Store Schema
Create the session store schema so the Oracle database can store the session information.
Follow these steps:
  1. Log in to Oracle as the user who administers the database information. Log in with an Oracle utility, such as sqlplus.
    We recommend that you do not create the schema with the SYS or SYSTEM users. If necessary, create an Oracle user, such as SMOWNER, and create the schema with that user.
  2. To store Unicode characters, confirm that the character set for the Oracle database is set correctly. If you plan to use only English characters, skip this step.
    1. To find the character set, use the following query:
      SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
    2. Verify that the character set is AL32UTF8 or UTF8 before importing the schema.
  3. Import the following script:
    $NETE_PS_ROOT/db/sql/sm_oracle_ss.sql
    Note:
    Some Oracle SQL utilities have problems with environment variables. If you experience problems importing the script using the utility, specify an explicit path.
  4. Create a table space for the session store.
  5. Create a user with the following privileges to manage the table space in the database:
    • Connect
    • Resource
    • Unlimited Tablespace
Configure an Oracle Data Source for
CA Single Sign-On
If you are using ODBC, you need to configure a data source for the Oracle wire protocol driver.
Create an Oracle Data Source on Windows
Create an ODBC data source for an Oracle database.
Follow these steps:
  1. Do one of the following:
    • If you are using a supported 32–bit Windows operating system, click Start and select Programs, Administrative Tools, ODBC Data Sources.
    • If you are using a supported 64–bit Windows operating system:
      1. Navigate to the 
        install_home
        \Windows\SysWOW64.
      2. Double–click odbcad32.exe
    The ODBC Data Source Administrator appears.
  2. Click the System DSN tab, and then click Add.
    The Create New Data Source dialog appears
  3. Select 
    CA Single Sign-On
     Oracle Wire Protocol, and click Finish.
    The ODBC Oracle Wire Protocol Driver Setup dialog appears. The General tab is pulled to the front.
  4. Enter a name that identifies the data source in the Data Source Name field.
    Record this name. You will need the data source name when pointing the Policy Server to the database.
  5. Enter the machine name where the Oracle database is installed in the Host Name field.
  6. Enter the port number where the Oracle database is listening on the machine in the Port Number field.
  7. Enter the name of the Oracle instance to which you want to connect in the SID field.
    The service name is specified in the tnsnames.ora file. The SID is the system identifier for the database instance. The tnsnames.ora file contains service names and details that Oracle uses to identify and connect to Oracle instances.
    Example:
     if the tnsnames.ora file contains the following entry for an Oracle instance, you enter instance1 in the SID field:
    instance1 =
        (Description=
        (Address = (PROTOCOL = TCP)(Host = myhost)(Port=1521))
        (Connect_DATA_ = (SID = SIDofinstance1))
        )
  8. Click Test Connection.
    The connection settings are tested and a prompt appears specifying that the connection is successful.
  9. Click OK.
    The Oracle data source is configured for the wire protocol driver.
Create an Oracle RAC (no SCAN) Data Source on Windows
Create an ODBC data source for an Oracle RAC database that does not use the SCAN feature.
Follow these steps:
  1. Do one of the following:
    • If you are using a supported 32–bit Windows operating system, click Start and select Programs, Administrative Tools, ODBC Data Sources.
    • If you are using a supported 64–bit Windows operating system:
      1. Navigate to the C:\Windows\SysWOW64.
      2. Double–click odbcad32.exe
    The ODBC Data Source Administrator appears.
  2. Click the System DSN tab, and then click Add.
    The Create New Data Source dialog appears.
  3. Select 
    CA Single Sign-On
     Oracle Wire Protocol, and click Finish.
    The ODBC Oracle Wire Protocol Driver Setup dialog appears. The General tab is pulled to the front.
  4. Enter a name that identifies the data source in the Data Source Name field.
    Record this name. You will need the data source name when pointing the Policy Server to the database.
  5. Enter the IP Address of the first node in the Oracle RAC system in the Host field.
    Oracle RAC 10g: Enter the virtual IP Address.
  6. Enter the service name for the entire Oracle RAC system in the Service Name field.
    Example:
     In the following tnsnames.ora file, the SMDB value is the service name for the entire Oracle RAC system, which contains 3 nodes:
    SMDB=
        (Description =
    (ADDRESS = (Protocol = TCP)(HOST = nete_servername1)(PORT = 1521))
    (ADDRESS = (Protocol = TCP)(HOST = nete_servername2)(PORT = 1521))
    (ADDRESS = (Protocol = TCP)(HOST = nete_servername3)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = SMDB)
    )
  7. Click the Failover tab.
    Failover settings appear.
  8. Specify the host name or virtual IP Address, port number, and service name for the remaining Oracle RAC nodes in the environment in the Alternate Servers field.
    The ServiceName is the service name for the entire Oracle RAC system.
  9. Specify the AlternateServers to provide connection failover to the other Oracle nodes if the primary server is not accepting connections. The entry should have the following format:
    (HostName=nete_servername2:PortNumber=1521:ServiceName=nete_servicename[,...])
  10. Select LoadBalancing.
  11. Click OK
    The Oracle RAC data source is configured for the wire protocol driver.
Create an Oracle RAC SCAN Data Source on Windows
Create an ODBC data source for an Oracle RAC database that uses the SCAN feature.
Follow these steps:
  1. Do one of the following:
    • If you are using a supported 32–bit Windows operating system, click Start and select Programs, Administrative Tools, ODBC Data Sources.
    • If you are using a supported 64–bit Windows operating system:
      1. Navigate to the C:\Windows\SysWOW64.
      2. Double–click odbcad32.exe
    The ODBC Data Source Administrator appears.
  2. Click the System DSN tab, and then click Add.
    The Create New Data Source dialog appears.
  3. Select CA 
    CA Single Sign-On
     Oracle Wire Protocol, and click Finish.
    The ODBC Oracle Wire Protocol Driver Setup dialog appears. The General tab is pulled to the front.
  4. Enter a name that identifies the data source in the Data Source Name field.
    Record this name. You will need the data source name when pointing the Policy Server to the database.
  5. Enter the FQDN or IP Address of the SCAN in the Host field.
  6. Enter the port number of the SCAN in the Port Number field.
  7. Enter the service name for the entire Oracle RAC system in the Service Name field.
    Example:
     In the following tnsnames.ora file, the SMDB value is the service name for the entire Oracle RAC system, which contains the SCAN:
    SMDB =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = clus-scan.rac.com)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ORCL)
        )
      )
  8. Click OK
    The Oracle RAC data source is configured for the wire protocol driver.
Create an Oracle Data Source on UNIX Systems
You configure the names of available ODBC data sources and the attributes that are associated with these data sources in the system_odbc.ini file.
To create the system_odbc.ini file:
  1. Navigate to 
    policy_server_installation
    /db
  2. Rename oraclewire.ini to "system_odbc.ini".
Customize the system_odbc.ini file for each site. You can also add more data sources to this file, such as defining extra ODBC user directories for 
CA Single Sign-On
.
The first section of the system_odbc.ini file, [ODBC Data Sources], contains a list of all of the currently available data sources. The name before the “=” refers to a subsequent section of the file describing each individual data source. After the “=” is a comment field.
If you modify of the first line of the data source entry ([
CA Single Sign-On
Data Source]), take note of the change. This value is required to configure your ODBC database as a policy store.
Each data source has a section in the system_odbc.ini file describing its attributes. The first attribute is the ODBC driver to be loaded when 
CA Single Sign-On
 uses this data source. The remaining attributes are specific to the driver.
To add an Oracle Data source
:
  1. Define a new data source name in the [ODBC Data Sources] section of the file.
  2. Add a section that describes the data source using the same name as the data source.
To create a service name or use a different driver, edit the system_odbc.ini file. Entries for the SQL Server or Oracle drivers belong under [
CA Single Sign-On
 Data Source].
Configure the Oracle Wire Protocol Driver
You configure the wire protocol driver to specify the settings the Policy Server uses to connect to the database.
This procedure only applies if the Policy Server is installed on a UNIX system. If you have not already done so, copy one of the following files and rename it 
system_odbc.ini
:
  • sqlserverwire.ini
  • oraclewire.ini
  • mysqlwire.ini
  • postgresqlwire.ini
These files are located in 
siteminder_home
/db.
The system_odbc.ini file contains the following sections. The data source that you are configuring determine the section or sections that you edit:
  • [SiteMinder Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to use to connect to the database functioning as the policy store.
  • [SiteMinder Logs Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to use to connect to the database functioning as the audit log database.
  • [SiteMinder Keys Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to connect to the database functioning as the key store.
  • [SiteMinder Session Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to connect to the database functioning as the session store.
  • [SmSampleUsers Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to connect to the database functioning as the sample user data store.
Follow these steps:
  1. Open the system_odbc.ini file.
  2. Depending on the data source you are configuring, edit the applicable data source sections with the following information. When editing data source information, do not use the pound sign (#). Entering a pound sign comments the information, which truncates the value. The truncated value may cause ODBC connections to fail.
    Driver=
    nete_ps_root
    /odbc/lib/NSora27.so
    Description=DataDirect 7.1 Oracle Wire Protocol
    LoginID=
    uid
    Password=
    pwd
    HostName=
    host_name
    PortNumber=1521
    SID=
    server_id
    CatalogOptions=0
    ProcedureResults=0
    EnableDisableParam=0
    EnableStaticCursorsForLongData=0
    ApplicationUsingThreads=1
    • nete_ps_root
      Specifies the explicit path of the Policy Server installation.
    • uid
      Specifies the user name of the database account that has full access rights to the database.
    • pwd
      Specifies the password for the database account that has full access rights to the database.
    • host_name
      Specifies the name of the Oracle database host system.
      server_id
      Specifies the Oracle instance service name (SID). The SID is the system identifier for the database instance.
    Example:
     In the following sample tnsnames.ora file, the value instance1 is the SID
    instance1 =
    (Description =
    (ADDRESS = (Protocol = TCP)(Host = myhost)(Port = 1521)
    (CONNECT_DATA = (SID = instance1))
    )
  3. Save the file.
The Oracle wire protocol driver is configured.
Configure the Oracle Wire Protocol Driver for Oracle RAC without SCAN
You configure the wire protocol driver to specify the settings the Policy Server uses to connect to the database.
This procedure only applies if the Policy Server is installed on a UNIX system. If you have not already done so, copy one of the following files and rename it 
system_odbc.ini
:
  • sqlserverwire.ini
  • oraclewire.ini
  • mysqlwire.ini
  • postgresqlwire.ini
These files are located in 
siteminder_home
/db.
The system_odbc.ini file contains the following sections. The data source that you are configuring determine the section or sections that you edit:
  • [SiteMinder Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to use to connect to the database functioning as the policy store.
  • [SiteMinder Logs Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to use to connect to the database functioning as the audit log database.
  • [SiteMinder Keys Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to connect to the database functioning as the key store.
  • [SiteMinder Session Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to connect to the database functioning as the session store.
  • [SmSampleUsers Data Source]
    Specifies the settings 
    CA Single Sign-On
     is to connect to the database functioning as the sample user data store.
Follow these steps:
  1. Open the system_odbc.ini file.
  2. Depending on the data source you are configuring, edit the applicable data source sections with the following information. When editing data source information, do not use the pound sign (#). Entering a pound sign comments the information, which truncates the value. The truncated value may cause ODBC connections to fail.
    • Add ServiceName=
      nete_servicename
    • Add AlternateServers=
    • Add Loadbalancing=1
    • Remove or comment SID=nete_serverid
    The modified text for the data source should appear as follows:
    Driver=
    nete_ps_root
    /odbc/lib/NSora27.so
    Description=DataDirect 7.1 Oracle Wire Protocol
    Logon=
    uid
    Password=
    pwd
    HostName=
    server_name1
    PortNumber=1521
    ServiceName=
    service_name
    CatalogOptions=0
    ProcedureRetResults=0
    EnableDescribeParam=0
    EnableStaticCursorsForLongData=0
    ApplicationUsingThreads=1
    AlternateServers=
    LoadBalancing=1
    • nete_ps_root
      Specifies an explicit path to the directory where Policy Server is installed.
    • uid
      Specifies the user name of the database account that has full access rights to the database.
    • pwd
      Specifies the password for the database account that has full access rights to the database.
    • server_name1
      Specifies the IP Address of the first Oracle RAC node.
      (Oracle 10g) Specifies the virtual IP Address of the first Oracle RAC node.
    • service_name
      Specifies the Oracle RAC system service name for the entire RAC system.
    • AlternateServers=
      If the primary server is not accepting connections, specifies the connection failover to the other Oracle nodes.
      Example:
       (HostName=nete_servername2:PortNumber=1521:ServiceName=nete_servicename[,...])
    • LoadBalancing=1
      Turns on client load balancing, which helps to distribute new connections to keep RAC nodes from being overwhelmed with connection requests. When enabled, the order in which primary and alternate database servers are accessed is random.
  3. Save the file.
    The Oracle wire protocol driver is configured.
Point the Policy Server to the Database
You point the Policy Server to the database so the Policy Server can read and store session information.
To point the Policy Server to the data store
  1. Open the Policy Server Management Console, and click the Data tab.
    Database settings appear.
  2. Select Session Server from the Database list.
    Data source settings become active.
  3. Enter the name of the data source in the Data Source Information field.
    • (Windows) this entry must match the name you entered in the Data Source Name field when you created the data source.
    • (UNIX) this entry must match the first line of the data source entry in the system_odbc.ini file. By default, the first line in the file is [
      CA Single Sign-On
       Data Sources]. If you modified the first entry, be sure that you enter the correct value.
  4. Enter and confirm the user name and password of the database account that has full access rights to the database instance in the respective fields.
  5. Specify the maximum number of database connections allocated to 
    CA Single Sign-On
    .
    We recommend retaining the default for best performance.
  6. Click Apply.
    The settings are saved.
  7. Click Test Connection.
    CA Single Sign-On
     returns a confirmation that the Policy Server can access the data store.
  8. Click OK.
    The Policy Server is configured to use the database as a session store.
Restart the Policy Server
You restart the Policy Server for certain settings to take effect.
Follow these steps:
  1. Open the Policy Server Management Console.
  2. Click the Status tab, and click Stop in the Policy Server group box.
    The Policy Server stops as indicated by the red stoplight.
  3. Click Start.
    The Policy Server starts as indicated by the green stoplight.
    Note
    : On UNIX, execute the stop-ps and start-ps commands to restart Policy Server. To restart Policy Server and CA Risk Authentication, execute the stop-all and start-all commands.