Configuring Oracle TDE Tablespace Encryption on an Existing Windchill Database
The following procedures provide instructions for configuring Oracle TDE tablespace encryption on an existing Windchill database. Instructions are included for completing the data conversion (import/export) and unencrypted data clean-up.
Configuring the Database Server for TDE Tablespace
1. Using datadump, export the existing database as a full export by running the following command. Replace the values for DMPDIR, WC10.dmp, and exp_WC10.log with values appropriate for your environment.
expdp system@wind directory=DMPDIR dumpfile=WC10.dmp logfile=exp_WC10.log Full=y
Copy the generated export dump file and the log file (WC10.dmp and exp_WC10.log in the previous command) from the DMPDIR location to another safe location before proceeding.
2. Drop and re-create the current database.
3. Prepare the database for encryption.
Before attempting to create an encrypted tablespace, create a wallet on the database server to hold the encryption key.
A wallet is an encrypted container that is used to store authentication and signing credentials, including passwords, the TDE master key, PKI private keys, certificates, and trusted certificates needed by SSL. With TDE, wallets are used on the database server to protect the TDE master key. Because data is encrypted in REDO logs, UNDO tablespaces, and TEMP tablespaces, the TDE master encryption key needs to be available to the database before it is opened.
 |
Although encrypted tablespaces can share the default database wallet, Oracle recommends using a separate wallet for transparent data encryption functionality by specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file. Using the default location for the wallet, by not specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file, is a known security risk. For more information, see http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf.
|
a. Set the ENCRYPTION_WALLET_LOCATION parameter in the $ORACLE_HOME/network/admin/sqlnet.ora file to the wallet directory as follows:
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/wallets/wind/)))
Replace the DIRECTORY value with a value appropriate for your environment.
b. Using SQL*Plus, log in to the target database as a database administrative user, and execute the following commands to initialize the wallet and add the master encryption key. Execute each command individually, and replace <password> with the appropriate password.
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “<password>”;
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “<password>”;
Configure Wallet auto open after database restart:
orapki wallet create -wallet <wallet_home> -auto_login
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “<password>”;
Configure Wallet auto open after database restart:
orapki wallet create -wallet <wallet_home> -auto_login
4. Using SQL*Plus, log in to the target database as a database administrative user, and execute the following statement to change the default tablespace to SYSAUX:
ALTER DATABASE DEFAULT TABLESPACE "SYSAUX";
5. Using SQL*Plus, drop the original tablespaces (USERS, BLOBS, INDX, WCAUDIT):
DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE BLOBS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE INDX INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE WCAUDIT INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE BLOBS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE INDX INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE WCAUDIT INCLUDING CONTENTS AND DATAFILES;
6. Using SQL*Plus, create new encrypted tablespaces with the same names as the original tablespaces. In the example statements below, replace the DATAFILE values with values appropriate for your environment.
CREATE TABLESPACE "USERS"
DATAFILE '/u01/app/oracle/oradata/wind/users01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE TABLESPACE "BLOBS"
DATAFILE '/u01/app/oracle/oradata/wind/blobs01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE TABLESPACE "INDX"
DATAFILE '/u01/app/oracle/oradata/wind/indx01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE TABLESPACE "WCAUDIT"
DATAFILE '/u01/app/oracle/oradata/wind/wcaudit01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
DATAFILE '/u01/app/oracle/oradata/wind/users01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE TABLESPACE "BLOBS"
DATAFILE '/u01/app/oracle/oradata/wind/blobs01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE TABLESPACE "INDX"
DATAFILE '/u01/app/oracle/oradata/wind/indx01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE TABLESPACE "WCAUDIT"
DATAFILE '/u01/app/oracle/oradata/wind/wcaudit01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
7. Reset the default tablespace to USERS:
ALTER DATABASE DEFAULT TABLESPACE "USERS";
8. Using SQL*Plus, execute the following query to verify that the tablespaces are created as encrypted tablespaces. The ENCRYPTED column must display a value of YES.
SELECT TABLESPACE_NAME , ENCRYPTED FROM DBA_TABLESPACES;
9. From the Windchill server, use SQL*Plus to log in to the database server as an administrative user and create a Windchill database user using the <Windchill>/db/sql/create_user.sql script, where <Windchill> is the Windchill installation directory.
10. From the Windchill server, use SQL*Plus to log in to the database server as the Windchill database user (created in the previous step), and execute the following scripts:
◦ <Windchill>/db/sql/wnc/Foundation/nonmodeled/procedures/WTReversePK.sql
◦ <Windchill>/db/sql/wnc/Foundation/nonmodeled/procedures/WTPrefixPK.sql
◦ <Windchill>/db/sql/wnc/Foundation/nonmodeled/procedures/WipPk.sql
11. Using datadump, import the Windchill schema exported in step 1, using a command similar to the following:
12. impdp system@wind directory=DMPDIR dumpfile= WC10.dmp logfile=imp_WC10.log schemas=WC10
Replace the parameter values in the example command (for example, DMPDIR, WC10.dmp, imp_WC10.log, and WC10) with values appropriate for your environment.
13. After a successful import, use SQL*Plus to compile all invalid objects in the Windchill schema, using a command similar to the following.
EXEC DBMS_UTILITY.compile_schema(schema => 'WC10');
Replace the parameter value WC10 in the example command with a value appropriate for your environment.
Configuring Windchill for the Database Configured for TDE Tablespaces
1. Open a Windchill shell.
2. From the Windchill shell, run the following commands to set and propagate the necessary configuration properties:
xconfmanager -s wt.pom.dbConnectionPropertiesNameList="oracle.net.
encryption_client,oracle.net.encryption_types_client,oracle.net.
crypto_checksum_client,oracle.net.crypto_checksum_types_client"
-t "db/db.properties"
xconfmanager -s wt.pom.dbConnectionPropertiesValueList="ACCEPTED,
( AES256 ), ACCEPTED,( MD5 )" -t "db/db.properties"
xconfmanager -p
encryption_client,oracle.net.encryption_types_client,oracle.net.
crypto_checksum_client,oracle.net.crypto_checksum_types_client"
-t "db/db.properties"
xconfmanager -s wt.pom.dbConnectionPropertiesValueList="ACCEPTED,
( AES256 ), ACCEPTED,( MD5 )" -t "db/db.properties"
xconfmanager -p
|
|
For configuring oracle.net.encryption_client and oracle.net.crypto_checksum_client, you can use any of the following values:
• REJECTED
• ACCEPTED
• REQUESTED
• REQUIRED
The default value is ACCEPTED. For more information, see Oracle Database Advanced Security Administrator’s Guide.
|
3. Start Windchill.