Configuring a Database Application User
A database installation user is used to create the database schema and load required data. A database application user executes transactions from Windchill.
If you did not create a database application user during installation and do not intend to use one, you can skip the steps in this section. However, certain modules, such as Windchill Business Reporting require a database application user, even if one was not created during the installation process. In those scenarios you must complete these procedures.
If you created a database application user when you installed your Windchill solution with the PTC Solution Installer, perform the following steps to complete the configuration:
1. From a Windchill shell, execute the following commands to modify Windchilldb.properties file with appropriate values:
◦ xconfmanager -s wt.pom.dbUser=<WINDCHILL_APP_USER_NAME> -t "db/db.properties" -p
◦ xconfmanager -s wt.pom.dbPassword=<WINDCHILL_APP_USER_PASSWORD> -t "db/db.properties" -p
◦ xconfmanager -s wt.pom.dbSchemaUser=<WINDCHILL_INSTALL_USERNAME> -t "db/db.properties" -p
2. Start Windchill and its related services. From the method server output, verify that the database application user name <WINDCHILL_APP_USER_NAME> has been used for the database connection.
If you did not create a database application user when you installed your Windchill solution, you can create one after the PTC Solution Installer (PSI) finishes installing your solution by using database-specific scripts and the database client. The database client must either be installed on the Windchill server or the script should be copied and executed from the database server. Use the following steps to manually create the database application user:
Oracle
1. Open a Windchill shell.
2. Change the directory to <Windchill>\db\sql (or sql3). The script is located in both folders, and they are not dependent on single or multi-byte Windchill configuration.
3. Using the Sqlplus utility, log in to the Windchill database as a database administrative user.
|
|
Users executing the following script must have read/write privileges on the <Windchill>\db\sql (or sql3) folder.
|
4. Execute the create_wc_app_user.sql file to create an application user, a database role, and an after login trigger.
|
|
PTC recommends creating the application user and role by appending the Windchill maintenance release version to the name; for example, WindchillAppUser_release_version, WindchillAppRole_release_version. This will help uniquely identify the names and correlate them with the target Windchill version.
|
The following is an example of the script execution output:
Windchill Install database User Name: <WINDCHILL_INSTALL_USERNAME>
Windchill Application database Role Name: <WINDCHILL_APP_DATABASE_ROLE>
Windchill Application database User Name: <WINDCHILL_APP_USERNAME>
Windchill Application database User Password: <WINDCHILL_APP_USER_PASSWORD>
Default Tablespace Name: USERS
Temporary Tablespace Name: TEMP
5. Verify that the application user and role was created correctly by logging on to the database as that user.
6. From a Windchill shell, execute the following commands to modify Windchilldb.properties file with appropriate values:
◦ xconfmanager -s wt.pom.dbUser=<WINDCHILL_APP_USER_NAME> -t "db/db.properties" -p
◦ xconfmanager -s wt.pom.dbPassword=<WINDCHILL_APP_USER_PASSWORD> -t "db/db.properties" -p
◦ xconfmanager -s wt.pom.dbSchemaUser=<WINDCHILL_INSTALL_USERNAME> -t "db/db.properties" -p
7. Start Windchill and its related services. From the method server output, verify that the database application user name <WINDCHILL_APP_USER_NAME> has been used for the database connection.
SQL Server and Azure SQL Database
|
|
Ensure that you have installed SQL command-line tool before you install the SQL and Azure SQL Database
|
1. Open a Windchill shell and change the directory to $WT_HOME\db\sqlServer.
2. To create an application user and a database role, execute the following batch file:
For Windows: <WT_HOME>\Windchill\db\sqlServer>create_wc_app_user.bat
For Linux : <WT_HOME>/Windchill/db/sqlServer>create_wc_app_user.sh
D:\ptc\Windchill\db\sqlServer> create_wc_app_user.bat
Users executing this script must have read/write privileges on $WT_HOME\db\sqlServer folder.
|
|
PTC recommends creating an app user and role by appending the Windchill MOR version to the name, such as WindchillAppUser_release_version and WindchillAppRole_release_version. This practice helps to identify and correlate unique names with target Windchill version
|
Example of the script execution output:
SQL Server Host Name: <DB HOST Name>
SQL Server Instance Name (for Named Instance only):
SQL Server Admin User name (default is sa):
Password for user sa:
Windchill Install Database User Name: <WINDCHILL_INSTALL_USERNAME>
Default database name for user s:
Windchill Application Database Role Name: <WINDCHILL_APP_DATABASE_ROLE>
Windchill Application Database User Name: <WINDCHILL_APP_USER_NAME>
Windchill Application Database User Password: <WINDCHILL_APP_USER_PASSWORD>
Verify that the application user and role was created accurately by logging on to the database as that user.
3. From the Windchill shell, execute the following commands to modify Windchill db.properties file with appropriate values.
xconfmanager -s wt.pom.dbUser=<WINDCHILL_APP_USER_NAME> -t "db/db.properties" -p
xconfmanager -s wt.pom.dbPassword=<WINDCHILL_APP_USER_PASSWORD> -t "db/db.properties" -p
xconfmanager -s wt.pom.dbSchemaUser=<WINDCHILL_INSTALL_USERNAME> -t "db/db.properties" –p
4. Start Windchill and related services.
From the method server output, verify the database application user name <WINDCHILL_APP_USER_NAME> has been used for database connection.
Grant View Definition for SQL Server Windchill Database Application User
For systems that use a SQL Server Windchill database application user, this database role must be granted the “VIEW DEFINITION” permission. This permission can be granted from the Properties dialog of the target database role in the SQL Server Management Studio application, or by using SQLCMD as follows:
sqlcmd -S <SQLSERVER_INSTANCE> -U sa -{ <SA_Password> -d <Windchill_DB> -Q
"GRANT VIEW DEFINITION ON SCHEMA::[WINDCHILL_SCHEMA_USER] TO [WINDCHILL_APP_DB_ROLE]"
for example:
D:\sqlcmd -S host01 -U user1 -P pass1 -d test1 -Q "GRANT VIEW DEFINITION ON
SCHEMA::[test1] TO [test1_role]"
Adding Windchill Components or Features When Configured With Database Application Users
If your Windchill installation is configured with a database application user, and you want to add new Windchill features or components that result in a schema change then the following steps must be followed:
1. Configure Windchill with database Install user.
◦ Stop the Windchill Server.
◦ From a Windchill Shell execute the following statements to update Windchill database properties to reflect the Windchill database installation user:
xconfmanager -s wt.pom.dbUser=< WINDCHILL_INSTALL_USERNAME > -t "db/db.properties" -p
xconfmanager -s wt.pom.dbPassword=<WINDCHILL_INSTALL_PASSWORD> -t "db/db.properties" -p
◦ Start the Windchill server.
2. Add the new features or components to your Windchill installation.
3. For Oracle databases only: Grant access to newly created database objects:
◦ Using Sqlplus, login to the database as database admin user, and execute the script <WT_HOME>/db/sql/update_wc_app_role_privs.sql to grant privileges to the new schema objects.
4. When prompted provide the following values:
◦ Windchill Install database User Name: <WINDCHILL_INSTALL_USERNAME>
◦ Windchill Application database Role Name: <WINDCHILL_APP_DATABASE_ROLE>
5. Configure Windchill with the database application user:
a. Stop the Windchill Server.
b. From Windchill Shell execute the following statements to update Windchill database properties to reflect Windchill database installation user:
xconfmanager -s wt.pom.dbUser=< WINDCHILL_APP_USER_NAME> -t "db/db.properties"
?pxconfmanager -s wt.pom.dbPassword=<WINDCHILL_APP_USER_PASSWORD> -t
"db/db.properties" -p
c. Start the Windchill Server.
