Store Audit Logs in MySQL

Contents
casso126
Contents
2
Before You Begin
Be sure that MySQL is installed using the default character set (Latin1). If MySQL was not installed using the default character set, reinstall MySQL before configuring the data store.
Gather Database Information
Gather the following information before configuring the policy store or any other type of 
CA Single Sign-On
 data store:
  • Database host
    —Identify the name of the database host system.
  • Database name
    —Identify the name of the database instance that is to function as the policy store or data store.
  • Database port
    —Identify the port on which the database is listening.
  • Administrator account
    —Identify the login ID of an administrator account with permission to manage objects in the database.
  • Administrator password
     —Identify the password for the administrator account.
Create the Audit Log Schema
You create the audit log schema so the MySQL database can store audit logs.
Follow these steps:
  1. Log in to the Policy Server host system.
  2. Navigate to the following location:
    siteminder_home\db\tier2\MySQL.
    • siteminder_home
      Specifies the Policy Server installation path.
  3. Open the following file in a text editor:
    sm_mysql_logs.sql
  4. Locate the following lines:
    DROP FUNCTION IF EXISTS `databaseName`.`getdate` $$
    CREATE FUNCTION `databaseName`.`getdate` () RETURNS DATE
  5. Replace each instance of 'databaseName' with the name of the database functioning as the audit store.
  6. Copy the contents of the entire file.
  7. Paste the file contents into a query and execute the query.
    The audit store schema is created.
Configure a MySQL Data Source for
CA Single Sign-On
You configure a data source to let the Policy Server communicate with the 
CA Single Sign-On
 data store.
If you are using MySQL 5.1.x, ensure that you assign the TRIGGER permission to the user name that is used to create the DSN.
Create a MySQL Data Source on Windows
You create a MySQL data source for the MySQL wire protocol driver.
Follow these steps:
  1. Log in to the Policy Server host system.
  2. Do one of the following steps:
    • 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.
  3. Click System DSN.
  4. Click Add.
  5. Scroll down and select 
    CA Single Sign-On
     MySQL Wire Protocol and click Finish.
  6. Complete the following steps in the General tab:
    1. Enter a data source name in the Data Source Name field.
      Example:
      CA SiteMinder® MySQL Wire Data Source
    2. Enter the name of the MySQL database host system in the Host Name field.
    3. Enter the port on which the MySQL database is listening in the Port Number field.
    4. Enter the name of the MySQL database in the Database Name field.
  7. Click Test Connect.
  8. Click OK.
    The data source is created and appears in the System Data Sources list.
You can now point the Policy Server to the
CA Single Sign-On
data store.
Create a MySQL Data Source on UNIX Systems
The 
CA Single Sign-On
 ODBC data sources are configured using a system_odbc.ini file, which you create by renaming mysqlwire.ini to system_odbc.ini. The mysqlwire.ini file is located in 
siteminder_home
/db.
  • siteminder_home
    Specifies the Policy Server installation path.
This system_odbc.ini file contains all of the names of the available ODBC data sources and the attributes that are associated with these data sources. This file must be customized to work for each site. Also, you can add other data sources to this file, such as defining other 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.
The value of the first line of data source entry is required when you configure the 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 that is loaded when 
CA Single Sign-On
 uses this data source. The remaining attributes are specific to the driver.
Adding a MySQL Server Data source includes:
  • A new data source name in the [ODBC Data Sources] section of the file.
  • A section that describes the data source using the same name as the data source.
Update the system_odbc.ini file when creating a new service name. You have entries for the MySQL driver under [
CA Single Sign-On
 Data Source].
Again, to configure a MySQL Server data source, you create the system_odbc.ini file by renaming mysqlwire.ini to system_odbc.ini.
Create the MySQL 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
  • db2wire.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. Enter the following line under [ODBC Data Sources]:
    SiteMinder Data Source=DataDirect 8.0 MySQL Wire Protocol
  3. Depending on the data source you are configuring, edit one or more of the 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 can cause ODBC connections to fail.
    Driver=
    nete_ps_root
    /odbc/lib/NSmysql28.so
    Description=DataDirect 8.0 MySQL Wire Protocol
    AlternateServers=
    ApplicationUsingThreads=1
    ConnectionReset=0
    ConnectionRetryCount=0
    ConnectionRetryDelay=3
    Database=
    database_name
    DefaultLongDataBuffLen=1024
    EnableDescribeParam=0
    EncryptionMethod=0
    FailoverGranularity=0
    FailoverMode=0
    FailoverPreconnect=0
    HostName=
    host_name
    HostNameInCertificate=
    InteractiveClient=0
    KeyPassword=
    KeyStore=
    KeyStorePassword=
    LicenseNotice=You must purchase commercially licensed MySQL database software or a MySQL Enterprise subscription in order to 
    use the DataDirect Connect for ODBC for MySQL Enterprise driver with MySQL software.
    LoadBalanceTimeout=0
    LoadBalancing=0
    LoginTimeout=15
    LogonID=
    root_user
    Password=
    root_user_password
    MaxPoolSize=100
    MinPoolSize=0
    Pooling=0
    PortNumber=
    mysql_port
    QueryTimeout=0
    ReportCodepageConversionErrors=0
    TreatBinaryAsChar=0
    TrustStore=
    TrustStorePassword=
    ValidateServerCertificate=1
    • nete_ps_root
      Specifies the Policy Server installation path. Enter this value as an explicit path, rather than one with an environment variable.
      Example:
       /export/smuser/siteminder
    • database_name
      Specifies the name of the MySQL database that is to function as the data store.
    • host_name
      Specifies the name of the MySQL database host system.
    • root_user
      Specifies the login ID of the MySQL root user.
    • root_user_password
      Specifies the password for the MySQL root user.
    • mysql_port
      Specifies the port on which the MySQL database is listening.
  4. Save the file.
    The 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 audit logs.
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 ODBC from the Storage list.
    ODBC settings appear.
  3. Select Audit Logs from the Database list.
  4. Select ODBC from the Storage list.
    Data source settings become active.
  5. 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.
  6. 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.
  7. Specify the maximum number of database connections allocated to 
    CA Single Sign-On
    .
    We recommend retaining the default for best performance.
  8. Click Apply.
    The settings are saved.
  9. Click Test Connection.
    CA Single Sign-On
     returns a confirmation that the Policy Server can access the data store.
  10. Click OK.
    The Policy Server is configured to use the database as an audit logging database.
Restart the Policy Server
casso126
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
    : To restart the Policy Server on UNIX, execute either the 
    stop-ps
     and 
    start-ps
     commands or
     stop-all
     and 
    start-all
     commands.