Monthly Archives: November 2017

How to Implement Transparent Data Encryption (TDE) in an Oracle Database

November 15, 2017

Author: Cameron Ross, Performance Architects

The reason for using Oracle Transparent Data Encryption Oracle (TDE) column encryption is to protect confidential data, such as credit card and social security numbers, stored in table columns. TDE column encryption encrypts and decrypts data transparently when data passes through the SQL layer with no modification required to any existing application.

The first step to implementing Oracle TDE is to create a wallet file location that will store all of the encryption key information. Below is a screenshot after manually creating the “orcl_wallet” folder in the file directory on the machine where the Oracle database is installed:

The next step is to specify the location of the wallet in the “sqlnet.ora” file, located in the “NETWORK/admin” folder.

  • ENCRYPTION_WALLET_LOCATION =(SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = ’file location’)))

Then, open up a command window and connect to “sqlplus” using the “sys” as an “sysdba” account.   As an alternative, a user assigned the “SYSKM” privilege also login.

After logging into “sqlplus,” execute the following commands to create, open, and set the master key:

  • ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘file location’ IDENTIFIED BY ‘password’;
    • This command creates the keystore file in the location that was inserted into the sqlnet.ora file
  • ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY ‘password’;
    • This command opens the keystore to enable encrypting columns or tablespaces
  • ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY ‘password’ WITH BACKUP;
    • This command sets up the master key that controls all of the keys that are used to encrypt specific columns

Query “V_$ENCRYPTION_WALLET” to ensure that the “STATUS” column is set to “OPEN” and that the “WALLET_TYPE” column is set to “PASSWORD.” This ensures that the wallet has been set up correctly and is open.

One advanced feature of TDE is being able to set the wallet for auto-login. This opens the wallet and key store automatically whenever the database is started.   If auto-login is not enabled, commands to open the key store must be executed each time the database is started.

To enable auto-login, enter the following statements:

  • ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘file location’ IDENTIFIED BY ‘password’;
  • After entering the “Create Auto Login” command, use the “Startup Force” command to restart the database instance. The reason to restart the database instance is so that it can switch the wallet type from “password” to “auto-login.”

To ensure that the auto-login is set to “Wallet,” log in to the database via any SQL querying tool and query the “V_$ENCRYPTION_WALLET” table. Ensure that your “WALLET_TYPE” column is set to “AUTOLOGIN” and that the “STATUS” column is set to “OPEN.”

 


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Renaming Presentation Tables in OBIEE 12c

November 8, 2017

Author: Linda Stewart, Performance Architects

Today I was working in an Oracle Business Intelligence Enterprise Edition (OBIEE) 12c RPD (Version 12.2.1.2, the on-premise Oracle BI solution) and I dragged my table from the logical level to the presentation level.  I wanted to rename the table, but the name in the property sheet was locked.

The solution is to set a property in the “Tools > Option > General” tab.

Let’s walk through the solution:

My logical table object is: “Fact_PC_Reconciliation_Extension” and I want the presentation table object name to be: “Fact – PC Reconciliation Report.”

If I go to the presentation layer and right-click my table, I then can select “Properties” from the menu:

The property sheet has the “Name” value greyed out and it cannot be edited:

To fix this, click “Tools > Options” and then in the “General” tab, check “Edit presentation names” and click “OK:”

Reopen the menu for the table by right clicking on “Presentation Table name”:

Notice that we now have two new menu options: “Rename Wizard” and “Rename.”  Open “Properties:”

The value for “Name” is now unlocked and we can rename the “Presentation Table.”

After renaming:

I hope this blog might save some others some time when working in the RPD.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

How to Modify the OBIEE Error Message

November 1, 2017

Author: Cameron Ross, Performance Architects

The Oracle Business Intelligence Enterprise Edition (OBIEE) error message can be a very daunting alert for an end user to receive. This message can be edited in order to change the display.

The standard Oracle error message contains three content objects: 1. The title; 2. The subtitle; and 3. Error details.  The steps below outline how to change this message:

  1. Create a new folder called “Custom Messages” inside the Oracle BI Presentation Service (OBIPS) folder in the following path of your OBIEE install. The reason for doing this is because when upgrades or patches are done to the install, the xml files will be reset in the initial file path:
  2. A user has the ability to change the title of the error message. In order to change the title of the error, find the “viewmessages.xml” file in your OBIEE install directory:

/OBIEE_Home/Middleware/bi/bifoundation/web/msgdb/l_en/messages/

The first part of the error message can be customized by editing the “viewmessages.xml” file. Search for “kmsgEVCViewDisplayErrorTitle” in the xml file. This is the location of the error message that will display. Change the “View Display Error” to the text of your choice.

Save the “viewmessages.xml” file in the custom messages folder that was created earlier.

  1. Just like with the title, a user can alter the subtitle. The one downfall of editing subtitles is that it will make it harder for the administrator to fix the issue since this is diagnosing the error source. To edit the “ODBC Subtitle,” go to the same folder that the “viewmessages.xml” file was located in above. This time find the “odbcaccessmessages.xml” file. Edit this file and search for “kmsgOdbcAccessOdbcException”. Replace the text “ODBC driver returned an error” message and replace it with the text of your choice.

Save the “odbcaccessmessages.xml” file in the custom messages folder that was created earlier.

  1. An OBIEE administrator can alter the privileges that other end users have. One thing that the administrator can change is the ability to see the SQL in the error details of an OBIEE message. Login to OBIEE as an administrator. Go to the top right corner of the OBIEE home screen and click the “Administration” tab:

On the “Administration” page, select “Manage Privileges.” In “Manage Privileges,” scroll down to the “Admin: General” section and find the “See SQL issued in errors” and alter this message so that only administrators have the rights to see the SQL for the error.

By setting this to “Administrators Only”, this cleans up the appearance of the error details when end users run into an error message.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.