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 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
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_10M0XX, WindchillAppRole_10M0XX. 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 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
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
• Create Database for Cognos:
1. Right Click on the Databases folder and select New Database
a. In General tab of New Databases dialog, type in a meaningful name for the target database
b. Click on the Options tab and click on the drop down list for Collation and select appropriate collation
a. Cognos requires its database to be configured with SQL_Collation:
▪ SQL Server 2008 R2: SQL_Latin1_General_CP1_CI_AS
▪ SQL Server 2012: Latin1_General_100_CI_AS
c. Click OK to create the database
d. Right Click on Databases folder and select refresh to view the newly created database
2. From SSMS (SQL Server Management Studio) open a new query window and execute the following statements as user ‘sa’, replace <DBNAME> variable with newly created database name (case-sensitive).
use [master]goALTER DATABASE [<DBNAME>] SET READ_COMMITTED_SNAPSHOT ONONgo
3. From SSMS open another New Query window as admin user ‘sa’, and execute the following code segment after replacing the variable <DBNAME> with newly created database to create a login, user and schema in this new database.
USE [master]
GOCREATE LOGIN [<DBNAME>] WITH PASSWORD=N’<DBNAME>’, DEFAULT_DATABASE=[<DBNAME>],CHECK_EXPIRATION=OFF,CHECK_POLICY=OFFGOUSE [<DBNAME>]GOCREATE USER [<DBNAME>] FOR LOGIN [<DBNAME>]GOEXEC sp_addrolemember N’db_owner’, N’<DBNAME>’GOCREATE SCHEMA [<DBNAME>] AUTHORIZATION [<DBNAME>]GOALTER USER [<DBNAME>] WITH DEFAULT_SCHEMA=[<DBNAME>]GO
|
|
In this case for simplicity the database user name and password is set the same as database name.
|
4. From a Windchill shell, execute the following commands to modify Windchill db.properties file with appropriate values:
|
|
The created Cognos database user name should be used while executing the following commands in place of <DBNAME>.
|
◦ xconfmanager -s wt.pom.dbUser=<DBNAME> -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
5. Start Windchill and its related services. From the method server output, verify that the Windchill Business Reporting database user name <DBNAME> has been used for the 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" -pxconfmanager -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:
◦ Stop the Windchill Server.
◦ 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
◦ Start the Windchill Server.
