Hi Lincoln,
The sql schema file are shipped from policy server binary, but loaded onto Oracle database.
The ODBC connection account should have full ownership of the specific database being created, so that it can read, execute, modify, write to it after setup.
Installation and Upgrade Guides › Policy Server Installation Guide › Configuring CA SiteMinder® Data Stores in a Relational Database › How to Store Key Information in Oracle
The documentation says "Note: We recommend that you do not create CA SiteMinder® schema with the SYS or SYSTEM users. If necessary, create an Oracle user, such as SMOWNER, and create the schema with that user.
"
Oracle Schema Files
The following Oracle schema files are in the siteminder_home\db\SQL directory.
- siteminder_home
Specifies the Policy Server installation path.
- sm_oracle_ps.sql
Creates the schema for a policy store and key store.
Note: If you are storing keys in a different database, this schema file creates the schema for the key store data.
- sm_oracle_logs.sql
Creates the schema for the audit logs.
- sm_oracle_ss.sql
Creates the schema for a session store.
- smsampleusers_oracle.sql
Creates the schema for a sample users database and populates the database with sample users.
The following Oracle schema file is provided in the policy_server_home\xps\db directory.
- Oracle.sql
Creates the XPS schema for a policy store.
Hope this helps.
Hongxu