Home

The Encryption Wizard for Oracle - Relational Database Consultants

1. Without a default mask a query by an unauthorized user results in the Encryption Wizard returning an Oracle error and auditing the event With a default mask an end user simply is returned a meaningless string To set a default mask call this Encryption Wizard API procedure from SQL Plus SQL gt exec RDC_Encrypt_Object SetDefaultMask SCOTT EMP ENAME This will set a default mask for the encrypted column ENAME that will return to all unauthorized users attempting decryption You can also set a default mask to simply return a NULL value SQL gt exec RDC_Encrypt_Object SetDefaultMask Null SCOTT EMP ENAME Encrypted number columns can also have a numeric default mask SQL gt exec RDC_Encrypt_Object SetDefaultMask 0 SCOTT EMP SAL Dates can also have a default mask of the type date either a date constant or the current SYSDATE can be returned as the mask SQL gt exec RDC_Encrypt_Object SetDefaultMask SYSDATE SCOTT EMP HIREDATE If you are not using restricted user lists or runtime passwords you do not need default masks Users will be authorized to view decrypted data based on Oracle privileges only For more on setting changing and removing default masks see Section One of the Encryption Wizard API Reference Guide Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational
2. Recover If this value is set to Yes the table or column in question is only partially encrypted You should perform encryption decryption or re keying of this object again to back out of the partially encrypted state Usually this value is set to No Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Security Manager Overview From the Manage Encrypted Data Screen we can enter the Security Manager by clicking on the button labeled Manage Security To enter the security manager simply click on the button labeled Security Manager from the Manage Encrypted Data screen You may define security setting at the schema table or column scope Schema Level Scope When you define security at this level the Encryption Wizard will mark every current encrypted column for a given schema with the security specifications you choose All previous security specifications for tables or columns in the schema are overwritten To enter the Security Manager at this level simply choose a schema name from the pop up list at the top of screen leave table name blank then click on the button labeled Manage Security Table Level Scope Table level scope will define security for all encrypted columns within a given table All previous security parameters set at the column level for the chosen table will b
3. The Encryption Wizard now ships with a Java interface to Hardware Security Modules HSM thus providing compliance for remote key storage and management The Encryption Wizard stores one master key on the HSM and using SHA hashing techniques the HSM stored key is used to generate all of the Encryption Wizard s runtime keys and passwords Thus encrypted data will not be accessible unless the Encryption Wizard can access the HSM Valid HSM Types 1 Luna PCI 7000 Make sure you have decrypted all data before installing the Encryption Wizard HSM interface You will also need to re register with both your demo and or production license codes after the HSM installation since all keys are reset 1 Oracle JVM Jar amp Class File Installation Certain java classes must be loaded into the Oracle JVM from the command line using Oracle s loadjava utility Only SYS is able to load the HSM DLL s at runtime For Luna PCI 7000 users OS gt loadjava u sys v resolve f ibmpkcs jar The jar file ibmpkcs jar can be obtained at the following link IBM JCE OS gt loadjava u sys v resolve f LunaJCASP jar LunaJCESP jar These Jar files are provided by Safenet Inc with the Luna installation OS gt loadjava u sys v resolve f RDC JavaHSM class This file is included with your Encryption Wizard installation 2 Encryption Wizard HSM Installation The Encryption Wizard HSM installation is always performed as SYS since SYS is the only Oracle user that c
4. After table encryption all columns that appear on the right of the screen will be encrypted Column Level Encryption Column Level Encryption is the fine grain level of encryption for the Encryption Wizard With column encryption you specify and encrypt distinct column s only To define encryption at this level first chose the table owner and table name from the two uppermost pick lists that appear at the top of the Encryption Decryption Screen Once you have chosen the appropriate table to encrypt click on those column s you wish to encrypt that appear in the column list on the right of screen To choose multiple columns press down on the CRTL key as you click with your mouse We recommend that the Encryption Wizard Administrator first employ Column Encryption before attempting table or schema encryption Tip Hold down the CTRL key on your keyboard as you right click on the mouse to select multiple columns or to unselect a column Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Step Two Choosing an Encryption Type After we have defined our level of encryption we must now instruct the Encryption Wizard as to what type of encryption to perform on our given data set For a discussion of the various encryption types please refer to Chapter 2 Encryption Wizard Overview To choose an encryption type sim
5. SUE JOE BOB SUE JOE BOB SUE BOB SUE JOE BOB SUE JOE www relationalwizards com Copyright 2002 2013 From Page Program sql sql sql sql sql sql sql sql oo sqlpl sqlpl sqlpl sqlpl sqlpl sqlpl sqlpl sqlpl sqlpl 00000 LUsw LUSW LUSW LUSW LUSW LUsw LUsw LUusw usw usw usw usw usw usw usw usw usw Writ Wri Writ Wri Wri Wri Wri Writ Wri Wri Writ Wri Writ Wri Wri Wri Wri te te te The Encryption Wizard For Oracle User Manual Time 07 30 03 13 25 15 Table Owner Table Name Relational Database Consultants Inc Audit Log Details TEST Column Name Page The Encryption Wizard for Oracle Management Reports CUSTOMER DEPARTMENT EMPLOYEE ITEM JOB LOCATION PRODUCT SALES ORDER ADDRESS AREA CODE CITY CREDIT LIMIT NAME PHONE NUMBER NAME COMMISSION FIRST NAME HIRE DATE LAST NAME MIDDLE INITIAL SALARY ACTUAL PRICE QUANTITY TOTAL FUNCTION REGIONAL GROUP DESCRIPTION RDER DATE HIP DATE no Audit User Timestamp Name 07 30 03 13 31 07 BOB 07 30 03 13 31 07 SUE 07 30 03 13 31 07 JOE 07 30 03 13 31 07 BOB 07 30 03 13 31 07 SUE 07 30 03 13 31 07 JOE 07 30 03 13 31 06 JOE 07 30 03 13 31 07 JOE 07 30 03 13 31 07 BOB 07 30 03 13 31 07 SUE 07 30 03 13 31 07 JOE 07 30 03 13 31 07 BOB 07 30 03 13 31 07 SUE 07
6. ada Hac 237 7373 52 52 752 44 44 45 45 45 45 45 45 46 46 46 42 42 42 42 42 42 42 42 42 43 43 SUE JO BO JOE BO SUE JOE BO SU JO BO SU JO BO SU JO BO SUE JO SU BO JOE BO SUE JO vao E a Ba WE Ww pay 00 ca ca w a ca pass ES W T www relationalwizards com Copyright 2002 2013 sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlpl sqlpl sqlpl sqlplusw LusSw LusSw usw LusSw lusw salp salp sqlpl salp sqlp sqlplusw usw usw sqlplusw sqlplusw sqlplusw sqlpl sqlpl sql sql sql sql 0 000 sql sql OO sqlplusw sqlplusw usw usw usw LUSW LUSW LUSW LUsw iusw iusw Read Read Read Read Read Read Read Read Read Read Read Read Read Read Write Write Write Write Write Write Write Write Write Write Write Fail Fail Fail Fail Fail Fail Fail Fail Fail Fail Fail Fail The Encryption Wizard For Oracle User Manual Time 07 30 03 13 25 15 The Encryption Wizard for Oracle Management Reports Relational Database Consultants Inc Audit Log Details Table Owner SUPPS Table Name Column Name Audit Timestamp Page From Program SOURCE FILES SOURCE FILES MA ASSIGNED TO DESCRIPTION MODULE ID PRODUCT ID SOURCE FILE M
7. 03 13 37 51 BOB sqlplusw Read Fail PRODUCT ID 07 30 03 13 37 51 SUE sgqlplusw Read Fail Relational Database Consultants Inc www telationalwizards com Copyright 2002 2013 Time Table Owner Table Name The Encryption Wizard For Oracle User Manual 07 30 03 13 25 15 Relational Database Consultants Inc Audit Log Details SUPPS Column Name Audit Timestamp Page The Encryption Wizard for Oracle Management Reports From Program MODULES MAIL PRIORITY CODES PRODUCTS PRODUCT RELEASE RELEASES MAIL REPORTS ROLE CODES SEVERITY CODES MAIL STOP MODULE ID PRODUCT ID USER ID USER NAME PRIORITY CODE RIORITY DESC lav ASSIGNED TO PRODUCT DESC RODUCT ID Hm SSIGNED TO ESCRIPTION RODUCT ID LEASE DATE LEASE ID RSION ID SOOU ES Sm ON I RINT COMMAND EPORT TYPE Attu mto ROLE CODE ROLE DESC SEVERITY CODE SEVERITY DESC 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 Relational Database Consultants Inc WWW WW WwW WWWW WW WWW WW 237 7373 377 72 3 3a 1374 fada 3T Heit Ss 137 2373 372 2373 2373 137 2373 3 1 7373 374 237
8. 2013 Restrict Time Table Owner 07 30 03 13 25 15 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Page The Encryption Wizard for Oracle Management Reports Listing of Current Encrypted Columns Table Name CUSTOMER DEPARTMENT EMPLOYEE ITEM JOB LOCATION PRODUCT SALES ORDER Data Column Name Type ADDRESS VARCHAR2 AREA CODE NUMBER CITY VARCHAR2 CREDIT LIMIT NUMBER NAME VARCHAR2 PHONE NUMBER NUMBER NAME VARCHAR2 COMMISSION NUMBER FIRST NAME VARCHAR2 HIRE DATE DATE LAST NAME VARCHAR2 MIDDLE INITIAL VARCHAR2 SALARY NUMBER ACTUAL PRICE NUMBER QUANTITY NUMBER TOTAL NUMBER FUNCTION VARCHAR2 REGIONAL GROUP VARCHAR2 DESCRIPTION VARCHAR2 ORDER_DATE DATE SHIP DATE DATE Relational Database Consultants www relationalwizards com Copyright 2002 2013 Encryption Type uscation uscation Mh Wh WM Mh Ww uscation uscation f S fuscation S Obfuscation Obfuscation Obfuscation Obfuscation DES DES DES Obfuscation Obfuscation Inc None None None None None None None None None None None None None None None Restrict Time Data Table Owner Table Name Column Name Type SUPPS BUGS ASSIGNED DATE DATE ASSIGNED TO VARC BUG SEQ NUMBE ENTERED BY VARC ENTERED DATE DATE MODULE ID VARC PRIORITY CODE VARC PRODUCT ID VARC PRODUCT RELEASE VARC PRODUCT VERS
9. Database Consultants Inc Decrypted Views for Application Transparency Decrypted views are objects that appear as regular tables with unencrypted data to applications or users with the appropriate privileges We can create decrypted views in any schema with the appropriate privileges to view the underlying encrypted base table This will allow applications with the appropriate privileges to view or change encrypted data From the Manage Encrypted Data Screen we can create modify or drop a Decrypted View by first choosing a base table of your decrypted view and then clicking on the button labeled Manage View At this point you will enter the Decrypted View Management Window Ebecrypted view Management As View for Encrypted Table SCOTT EMP View Owner IRDC ENCRYPT USER View Name EMP Decrypted View Type Read Only Updateable None Current columns with Function Index Choose Column for Function Index COMM COMM v Create Index Drop Index Function Index successfully created Exit Reset View From the above window you may create a decrypted view against the chosen base table by simply entering a schema owner and view name for the decrypted view Once you have defined the view simply click on a Decrypted View Type of either Read Only or Updateable and then click on the button at the bottom of the screen labeled Reset View From this screen we can change the owner view_name
10. FILE DIR to a valid directory or a wildcard this allows the PL SQL engine to write data to the file system on your server For example if you want to only allow backups to occur to a certain directory on your file system you might set the INIT ORA parameter UTL FILE DIR as such utl file dir u01 encrypt backups If you don t want a restriction on the location of backup key files you may enter the following wildcard as your parameter utl file dir Backup and Recovery Levels The Encryption Wizard for Oracle generates small backup file because no data is actually stored in this backup simply the encryption keys that will transform the encrypted data into useful data Therefore it is equally important to have a standard data backup procedure also in place to backup your encrypted data The Encryption Wizard for Oracle can perform the key backup process at four levels or backup domains 1 Complete Database Backup To choose this level un select any chosen schema within the Manage Encrypted Data Screen and then click on the button labeled Manage Backups This backup domain will backup all encryption keys within the database 2 Schema Backup To choose this level select a given schema with the Manage Encrypted Data Screen and then click on the button labeled Manage Backups This backup domain will backup all encryption keys within the chosen schema 3 Table Backup To choose this level select a given table with
11. correct port 1D x E Encryption Wizard for Oracle Login Screen The Encryption Wizard for Oracie Database Login Screen Password frac Database SID prod817 Hast Name frdcs Oracle Port 1521 Exit Login Java Build 2 001 Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc If your connection succeeds then you should see the Encryption Wizard Main Menu It is from this window that you will navigate through the various functions of the Encryption Wizard fe Encryption Wizard for Oracle Main Menu When you first enter this menu you will be instructed to enter a demo license code which can be obtained from e After you receive your code you can register the Encryption Wizard by clicking c on Admini 1 Option 4 of the main menu Relational Danna Consultants Inc w relationa S Copyright 2002 201 3 om The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Chapter Two Encryption Wizard Overview The Encryption Wizard for Oracle allows you to physically encrypt the data that resides within your Oracle RDBMS You can specify this physical encryption at the schema table or column level 1 Encryption Types Ciphers The Encryption Wizard gives you a number of encryption ciphers to choose from to protect your
12. data l Obfuscation Obfuscation is not technically encryption Obfuscation simply obscures and makes your data apparently useless Advanced decryption techniques can break obfuscation yet obfuscation makes casual data theft unlikely among threats inside or outside your organization unless sophisticated and time consuming techniques are employed to break the obfuscation keys Il DES Encryption DES Encryption is the certified encryption standard provided by the Oracle Corporation through their package DBMS Obfuscation Toolkit The Encryption Wizard utilizes a 64 bit key to protect your data Ill Triple DES Encryption Triple DES Encryption DES3 is a response to advanced techniques used to break standard DES encrypted data With Triple DES a data value is encrypted recursively using three 64 bit keys to insure an almost infinite number of key combinations Currently the Encryption Wizard uses the Triple DES scheme C Ek3 Dk2 Ek1 P IV AES 128 bit Encryption AES encryption is available to Oracle 10g users through the DBMS Crypto package or optionally with GNU Crypto AES encryption is recognized as more secure than DES Encryption and we have tested it as 20 faster AES encryption is not available as of release 5 0 0 1 for CLOB or BLOB datatypes V AES 256 bit Encryption 256 bit AES encryption uses large 32 byte encryption keys This encryption type also is called through Oracle s certified DBMS_Crypto package and or GNU Cr
13. in the C Program Files Encryption Wizard for Oracle directory or within the EncryptionWizardJava zip download 1 Step One Login as RDC Encrypt User using SQL Plus 2 Step Two Start the report from SQL Plus as such SQL gt audit_log_summary rep Or SQL gt start encrypted_columns rep 3 Step Three You will be prompted for four parameters that define the scope of your report 1 Schema Owner Enter the Schema Owner or hit return for all schema data 2 Table Name Enter the Table Name or hit return for all tables 3 Column Name Enter the Column Name or hit return for all columns 4 Output File Enter a valid file name for your output 4 Step Four View your report in the Output File you specified above Exporting these reports to MS Word or a PDF tool allows for quick formatting Sample Output of Management Reports Following we have provided three examples of the management report s output In each case we hit lt return gt for the schema table and column specifications so these reports cover all encrypted columns within our database Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 Time 07 30 03 13 Table Owner The Table The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Example One Current Encrypted Columns 25715 Page Encryption Wizard for Oracle Management Reports Listing of Current E
14. must be added the sqlnet ora file on the database server sglnet allowed login version 8 The sglnet ora file is usually in the directory SORACLE HOME network admin To test that you have the proper java version in your command path issue this command from either your Windows command prompt or Unix Linux shell gt java version Make sure the version of the java runtime is version 1 4 or higher Ifjava cannot be recognized by the operating system you will need to include the correct JRE in your OS path Oracle already has the Java JRE installed by default on its databases yet this JRE is usually not in the client computer path Setting the proper Java path for Windows Clients 1 Navigate to Control Panel Administrative Tools Computer management 2 Choose Actions properties Advanced Environment Variables 3 Click on the system variable in the list called path and click on EDIT to modify the path to include the proper java path Example mypath is already defined Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Path c mypath C Program Files s1studio_jdk j2sdk1 4 1_02 bin For Oracle users who don t want to install the SUN jdk this example uses the Oracle JRE 1 4 In this example ORACLE_HOME is defined as c oracle ora10g Path c mypath c oracle ora10g jdk jre bin Once yo
15. our encrypted table To highlight a column simply click on Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc the column name with your mouse Use the CTRL key with the mouse to un select a given column From the Encrypted Data Management Screen we can view the following information for each encrypted column Each database column has six parameters listed with the column name these values give us an overview of our encryption efforts for any particular column 1 Data Type This displays the data type of our database column Algorithm This field records the type of encryption used to protect a given column Password If this value is set to Yes a Default Runtime Password has been assigned to a particular database column Users viewing this data will need to authenticate the password before performing encryption or decryption operations We will learn how to set Runtime Passwords in the Security Manager Restricted If this value is set to Yes encryption and decryption of this column is restricted to a specific set of users as defined in the Security Manager These lists of users are called Restricted User Lists Audit If this value is set to Yes users performing encryption and or decryption activities will be audited Auditing is also defined in the Security Manager
16. recommend that the output of these reports be exported into MS Word or a similar tool for advanced formatting If you are looking for reports that output PDF or HTML files you will need to use Oracle Reports with the Oracle Forms version of the Encryption Wizard which is available at ewdemo relationalwizards com Report Descriptions All of the Encryption Wizard management reports are files within the installation directory with the extension rep At the writing of this manual the current provided managements reports are 1 Current Encrypted Columns Description This report displays basic information on all of your encryptions File Name encrypted columns rep 2 Audit Log Summary Description This report displays summary statistics for any audited columns that you have set for either read or write auditing File Name audit log summary rep 3 Audit Log Details Description This report displays detailed row information for any audited columns that you have set for either read or write auditing including failed transactions due to restricted user lists set up in the Security Manager of the Encryption Wizard File Name audit log details rep Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Running the Management Reports All reports are run directly from SQL Plus These reports can be found
17. simply click on the button labeled Decrypt Object to decrypt the data All other information on the screen will be ignored by the Encryption Wizard since it is already stored along with the key for any given encrypted column Note If you need to decrypt data quickly then use Schema Level Decryption Decrypting a schema will decrypt all tables and columns that you previously encrypted within that schema If there is a problem during decryption such as a database shutdown or lock contention error simply click on the Decrypt Object button again to complete the decryption or click on the Encrypt Object button to back out and re encrypt the complete table If you are decrypting a table in partial recovery you may have to decrypt twice if some of the columns are not in recovery The reason for this is that recovery only repairs inconsistent columns that have been partially encrypted or decrypted To view the recovery status of any encrypted database object simply choose option 2 from the Main Menu Decryption of a complete table will also drop any decrypted view created for that table Warning If you are a demo version user of the Encryption Wizard decryption will not operate after your trial license expiration date this is because by the terms of the Encryption Wizard License Agreement demo users can only encrypt and decrypt worthless data of no value Relational Database Consultants Inc www relationalwizards com Copyright 2
18. 002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Chapter Five Managing Encrypted Data The real value of the Encryption Wizard for Oracle is its easy to use and well organized management features To enter the Manage Encrypted Data Screen simply click on the button on the main menu labeled Manage Encrypted Data amp Encryption Wizard for Oracle Manage Encrypted Data iol x The Encryption Wizard for Oracle Copyright 2003 Relational Database Consultants Inc Manage Encrypted Data Schema Owner SCOTT v Table Name EMP Algorithm NUMBER Obfuscation Yes NUMBER Obfuscation Yes i NUMBER Obfuscation Ives VARCHARZ Triple DES3 Yes DATE Obfuscation Yes WARCHAR2 Triple DES3 Yes NUMBER Obfuscation Yes NUMBER Obfuscation Yes Choose an encrypted column to manage Optional There are currently 19 encrypted columns Exitto Main Manage View Manage Security gt Manage Backups Once we enter this screen we notice that the Manage Encrypted Data Screen has a similar format to the Encryption Decryption Screen You may manage encrypted data at the schema table or column level To manage a given encrypted table all we need to do is optionally select the schema owner and table name of the given object After choosing a table name from the two pick list the Encryption Wizard will show us the encryption details for each column of
19. 30 03 13 31 08 BOB 07 30 03 13 31 08 SUE 07 30 03 13 31 08 JOE 07 30 03 13 31 09 BOB 07 30 03 13 31 09 SUE 07 30 03 13 31 10 JOE 07 30 03 13 31 10 BOB 07 30 03 13 31 10 SUE Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 From Program JDBC Thin JDBC Thin JDBC Thin JDBC Thin JDBC Thin JDBC Thin JDBC BC BC BC BC BC BC DODODO E iw BC BC ey iw JDBC JDBC JDBC JDBC JDBC hin hin hin hin hin hin hin hin hin hin hin hin hin hin hin Writ Writ Wri Wri Wri Wri Writ Wri Wri Wri Writ Writ Writ Wri Wri Fail Fail Fail Fail Fail Fail Fail Fail Fail Fail Fail Fail Fail Fail
20. AIL STOP STATUS CODES STATUS SECURITY STATUS TRANSITI TEST TEST PHASE TEST SCRIPT TEST SCRIPT TEX MODULE ID PRODUCT ID SOURCE FILE USER ID INTERNAL CODE STATUS CODE STATUS DESC STATUS CODE USER ID STATUS CODE 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 VALID TRANSITIO 07 30 03 PHASE DESC CREATE DATE MODULE ID PRODUCT ID TEST SCRIPT ID LINE NO TEST SCRIPT ID TEXT 07 30 03 07 30 03 07 30 03 TEST PHASE CODE 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 Relational Database Consultants Inc WWW WW WWW WW WWW WW w w w 1373 2374 ES Heche Hes 2373 373 373 737 ror 37 aT 237 37s 237 2373 37 237 1373 Hach 37 4 2373 2374 Hecht ie P37 ou 3 37 43 43 43 43 43 44 44 44 739 739 239 739 40 40 40 40 BP SP 737 37 Tegi to oO AO m JOE BO SU JO BO Of BE w m SUE JO BO o A rm SUE JOE BOB SUE JOE L BOB L SUE L JOE L BOB L SUE L JOE L BOB BOB SUE JOE www relationalwizards com Copyright 2002 2013 sql sql sql sql sqlplusw sqlplusw plusw sqlplusw plusw sql sql sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw
21. GNU Crypto Once your installation is complete a list of valid encryption types will be displayed as part of the output When the installation script finishes you should receive this message Installation successful Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc The Encryption Wizard user rdc encrypt user is granted these following privileges by SYSTEM Connect Resource Execute Catalog Role Select Catalog Role Select Any Table Insert Any Table Analyze Any Global Query Rewrite Update Any Table Delete Any Table Alter Any Table Lock Any Table Create Any Trigger Alter Any Trigger Drop Any Trigger Create Any View Drop Any View Create Any Index Drop Any Index Create Public Synonym Drop Public Synonym Grant Any Object Privilege Select Any Dictionary 3 Java User Interface Configuration and Client Verification The main menu of the Encryption Wizard front end is a Java class called EncryptionWizard class and exists in the EncryptionWizard user interface file named EncryptionWizard jar To successfully run the Encryption Wizard s Java front end a Java JRE 1 4 or higher must be installed on the client you wish to run the Encryption Wizard front end Oracle 12c required modification for sqlnet ora To run the Encryption Wizard user interface for Oracle version 12c databases this line
22. ION VARC SEVERITY CODE VARC STATUS CODE VARC SUMMARY VARC TEST PHASE ID VARC TEST SCRIPT ID VARC TIMESTAMP DATE USER ID VARC VERIFIED BY VARC VERIFIED DATE DATE BUG ENVIRONMENT BUG SEQ NUMB ENVIRONMENT VAL VARC ENVIRONMENT VAR VARC BUG SOURCE FILE BUG SEQ NUMBE COMMENTS VARC MODIFIED BY VARC MODIFIED DATE DATE MODULE ID VARC PRODUCT ID VARC SOURCE FILE VARC BUG TEXT BUG SEQ NUMB LINE NO NUMB TEXT VARC TEXT TYPE VARC CONFIGURE COMPANY NAME VARC LICENSE DATE DATE LICENSE NO VARC OPERATING SYSTE VARC PRINT COMMAND VARC The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Example Two Audit Log Summary 07 30 03 13 25 15 Page The Encryption Wizard for Oracle Management Reports Audit Log Summary Relational Database Consultants Inc www relationalwizards com Total Writes i oo pD ER ER ER R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 Copyright 2002 2013 san NNN NNN NYA NY NI NYINYI NI NINN s BPP PE HP HSH BP A PPP PPP SP HS p Om Oe Oo Oo oo 000 Om oo amp NONNNNNNNNNNNNNNNN DN NY Time 07 30 03 13 25 15 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Page The Encryption Wizard for Oracle Management Reports Table Owner Tab
23. MS and accesses all information directly within the SGA Therefore no external libraries or OS dependent files are there to hamper your installation efforts The Encryption Wizard requires the following to be implemented successfully on your Oracle platform s 1 An Oracle database of version 10g to 11g with the package DBMS Obfuscation Toolkit currently installed Optionally AES Encryption requires Oracle 10 1 or higher with DBMS_ Crypto installed Contact us for backward compatible Oracle 9i and 8i versions of the Encryption Wizard 2 To run the user interface of the Encryption Wizard you will need the Java JRE 1 4 or higher installed on your client s If you plan on using the Encryption Wizard API Library exclusively you do not need Java installed on your client You may download the Java JRE from Oracle at this link http java com en download index jsp You can encrypt Unix Linux and Windows servers from any clientthat runs java 3 Technical ability to perform Oracle DBA tasks 4 A working backup strategy 5 A valid Oracle support license Once you have determined that these above conditions are met Installation of the Encryption Wizard starts by downloading the product from http www relationalwizards com html oracle_encryption htm 1 Client Installation The Encryption Wizard for Oracle download is offered as either a Windows EXE installation file or as a ZIP file which can be installed on any OS file syste
24. Read Fai MODIFIED DATE 07 30 03 13 37 46 JOE sqlplusw Read Fai MODULE_ID 07 30 03 13 37 46 BOB sqlplusw Read Fail PRODUCT_ID 07 30 03 13 37 46 SUE sqlplusw Read Fai SOURCE_FILE 07 30 03 13 37 46 JOE sqlplusw Read Fail BUG_TEXT BUG SEQ 07 30 03 13 37 46 BOB sqlplusw Read Fail LINE_NO 07 30 03 13 37 46 SUE sqlplusw Read Fail TEXT 07 30 03 13 37 46 JOE sqlplusw Read Fail TEXT TYPE 07 30 03 13 37 46 BOB sqlplusw Read Fai CONFIGURE COMPANY NAME 07 30 03 13 37 47 SUE sqlplusw Read Fail LICENSE DATE 07 30 03 13 37 47 JOE sqlplusw Read Fail LICENSE NO 07 30 03 13 37 47 BOB sqlplusw Read Fail OPERATING SYSTE 07 30 03 13 37 47 SUE sqlplusw Read Fail PRINT COMMAND 07 30 03 13 37 47 JOE sqlplusw Read Fail ENVIRONMENT DESCRIPTION 07 30 03 13 37 47 BOB sqlplusw Read Fail ENVIRONMENT VAR 07 30 03 13 37 47 SUE sqlplusw Read Fail MANDATORY 07 30 03 13 37 47 JOE sqlplusw Read Fail FIELD RULES DEFAULT VALUE 07 30 03 13 37 48 SUE sqlplusw Read Fail FIELD NAME 07 30 03 13 37 48 BOB sqlplusw Read Fail FORM NAME 07 30 03 13 37 50 SUE sqlplusw Read Fail FUNC_KEYS BUG_USAGE 07 30 03 13 37 50 JOE sqlplusw Read Fail CUSTOMER USAGE 07 30 03 13 37 50 BOB sqlplusw Read Fail FORM NAME 07 30 03 13 37 50 SUE sqlplusw Read Fail KEY ID 07 30 03 13 37 50 JOE sqlplusw Read Fai ORACLE FUNCTION 07 30 03 13 37 50 BOB sqlplusw Read Fail MODULES ASSIGNED TO 07 30 03 13 37 51 SUE sqlplusw Read Fail MODULE DESC 07 30 03 13 37 51 JOE sqlplusw Read Fail MODULE ID 07 30
25. Reports Audit Log Details Table Owner SUPPS Table Name Column Name Audit Timestamp From Program Audit Type BUG ENVIRONMENT ASSIGNED DATE ASSIGNED DATE ASSIGNED TO ASSIGNED TO BUG SEQ BUG SEQ ENTERED BY ENTERED BY ENTERED DATE ENTERED DATE MODULE ID MODULE ID RIORITY CODE RIORITY CODE RODUCT ID RODUCT ID EVERITY CODE EVERITY CODE TATUS CODE TATUS CODE UMMARY UMMARY EST PHASE ID EST PHASE ID EST SCRIPT ID TEST SCRIPT ID TIMESTAMP TIMESTAMP USER ID USER ID VERIFIED BY VERIFIED BY VERIFIED DATE VERIFIED DATE HHHNNNNNN DV VDD BUG SEQ 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 RODUCT RELEASE 07 30 03 RODUCT RELEASE 07 30 03 RODUCT VERSION 07 30 03 RODUCT VERSION 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 ENVIRONMENT VAL 07 30 03 ENVIRONMENT VAR 07 30 03 Relational Database Consultants Inc H PEt et PERLI Pee Pe H H H H H H H H H H H H H H H fl Cl ll www ll ll ll ll www Ww Www pti PE 2373 1373 237 ANANANANANANBA O H O H CO H o H o H o H o H 49 49 49 wuu ng ng ww q ng SU JOE ea ca w Rc ca o a O a o PPO Peo Pee ee
26. SCOTT as the owner of your decrypted view s Simply change the current schema owner name in the field under the label Schema to Own Decrypted Views to the owner of the table s you are about to encrypt By default decrypted view will have the same name as their base table unless they are created in the schema of the base table In this case the base table can be renamed prior to allow for the decrypted view to have the base table name Strategies for decrypted view creation are discussed in length in Chapter 6 Migrating Applications to Encrypted Data If we choose to create our decrypted view in the same schema as our encrypted table we must first assign a prefix to it so the view name will not conflict with the base table name Later we can rename our decrypted view to any valid object name using the View Management screen To enter a decrypted view prefix enter a short prefix in the field under the label decrypted view prefix Example If the table we are encrypting is scott emp we might enter a decrypted view prefix like V_ In this case our decrypted view would have the name v_emp and would exist in whatever schema we choose for it The decrypted view prefix is optional if you are creating the view in a separate schema from the base table Caution If you are creating a decrypted view in another schema make sure grant select update delete insert privileges on your table s to that schema Note When encrypting an
27. SQL statements to your new encrypted data sets using Decrypted Views is the easiest option Refer to chapter 6 Migrating Applications to Encrypted Data Tables for more discussion of this topic Tip If you haven t thought about a decrypted view strategy yet simply skip this step and create your decrypted views later in the Manage Encrypted Data screen option 2 from the main menu If you specify read only Decrypted Views the Encryption Wizard will create a decrypted view that gives authorized users a transparent view of your encrypted data but will not allow them to update delete or insert rows using that decrypted view If instead you select updateable decrypted views the Encryption Wizard will create decrypted views through which authorized users may insert update or delete encrypted records via instead of triggers automatically generated by the Encryption Wizard supporting the decrypted view After specifying which type of decrypted view s you would like the Encryption Wizard to create we need to choose a schema for that view and optionally a prefix for the decrypted view By default all decrypted views are created in the Encryption Wizard schema rdc_encrypt_user You Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc may instead assign the schema owner of your data or another schema in this case
28. The Encryption Wizard for Oracle User Manual For Oracle 10g 11g and 12c Databases Version 8 Relational Database Consultants Inc Copyright 2002 2013 Relational Database Consultants Inc The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc The Encryption Wizard for Oracle User Manual Relational Database Consultants Inc RDC 12021 Wilshire Blvd Suite 108 Los Angeles CA 90025 310 281 1915 www relationalwizards com Restricted Rights Notice Copyright 2002 All Rights Reserved No portion of this document may be reproduced recorded transmitted or copied without permission from the copyright holders Information in this document is subject to change without notice Trademark Notice All trademarks in this document belong to their respective holders Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc The Encryption Wizard for Oracle User Manual Table of Contents Introduction Chapter One Installation of the Encryption Wizard Chapter Two Encryption Wizard Overview Chapter Three Encrypting Table Data Chapter Four Decrypting Table Data Chapter Five Managing Encrypted Data Restricted User Lists for Secure Access Runtime Passwords for User Authentication Session Auditing for Security History Re Keying of Data for Secure Keys Defaul
29. al form Encryption will complete the encryption of the partially encrypted table Whenever you choose to encrypt or decrypt a schema table or column that needs recovery the Encryption Wizard will perform the recovery task by notifying you with the following message fez Recovery Operation Necessary i x Operation will encrypt those columns in recovery for 1 encrypted table s You can always view which data objects need recovery by using the Encrypted Object Management screen discussed in Chapter 5 Managing amp Auditing Encrypted Data Note If only some of your columns need recovery for a given table you may need to encrypt or decrypt the remaining columns after recover since recovery operations only modify columns that need recovery You can check which columns require recovery from the Encrypted Data Management Screen Option 2 from the main menu Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Chapter Four Decrypting Table Data Decrypting table data using the Encryption Wizard is a simple process When we decrypt data we return it to its original readable form To perform this task simply enter the Encryption Decryption Screen of the Encryption Wizard from the Main Menu and choose the schema table or column object you wish to decrypt After choosing your decryption domain
30. an load the HSM API s LunaAPI dil To run the installation script you will need the following information 1 The HSM Key Name you would like to assign the master key of the Encryption Wizard 2 The Password that you used on your HSM to create a key store partition 3 The Slot Number your HSM is installed on Optional login can search 4 The case sensitive valid Card Type For example Luna PCI 7000 5 The file LunaAPI dll You will prompted by this script to copy it into one of a choice of directories that are in the java library path of your Oracle instance 6 You will also need the Encryption Wizard Oracle user password and optionally the demo license code you installed the product with Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc 3 Running the installation script Once the above prerequisites are met HSM installation is completed by running the script rdc hsm install sgl directly from the Encryption Wizard installation directory C Program Files Encryption Wizard for Oracle or your Zip file extraction SQL gt rdc_hsm_install sql Output of the script will go to its Ist extension After you have copied the HSM API file s to Oracle s java library path and successfully entered the parameters of your HSM card you should see output something like this HSM Parameters Successfully In
31. and view_type at any time Function Indexes for Performance If you have encrypted a very large table decrypted views will be very slow against SQL that refers to an encrypted column To avoid this you may create function indexes that will allow your Decrypted view to use function index searches when encrypted data is queried The drawback of function indexes is that column values will be stored in reverse compressed and not encrypted format within the index tablespace To create a function index simply choose a column name using the drop down list and then click on the button labeled Create Index Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc To drop a function index click on the column name from the list on the left and then click on the button labeled Drop Index To use function indexes the following parameters must be set in your init ora file query rewrite integrity trusted query rewrite enabled TRUE You will then need to run statistics against the table you have indexed to insure that the Oracle optimizer will choose to use the new function index Tip We also recommend setting the init ora parameter optimizer index cost adjto a value between 1 and 30 for optimal use of function indexes Key Backup and Recovery for Continuity Planning The Encryption Wizard for Oracle offers you th
32. ating Applications to Encrypted Data Tables The Encryption Wizard makes it easy for the user to encrypt and decrypt data yet migrating applications to encrypted data always takes some planning The easiest way to migrate applications to the Encryption Wizard s encrypted tables is through the use of decrypted views Decrypted views are views on our encrypted tables that appear to the user as our encrypted data in its original form 1 Decrypted View Strategy Decrypted Views always appear like the original encrypted table aside from the addition of a rowid column if there is no primary key on your table Users can read and if specified write to these views just as if they were base tables When using decrypted views there are 3 basic strategies you can employ to migrate your applications to encrypted data tables Rename Application Base Tables Renaming the application base tables in most cases is the best way to migrate your applications to the Encryption Wizard This method entails renaming your base tables before encryption and then instructing the Encryption Wizard to create views with the names of your original table names To name a decrypted view to an exact table name simply create the decrypted view in your application schema with a temporary prefix as you are encrypting your data After verifying the integrity of your encryption rename the view in the Encrypted Data Management Screen discussed in Chapter 5 Managing amp A
33. auditing can be set or unset for any encrypted object and below auditing you can set up restricted user lists and or runtime passwords required to authenticate access for the chosen schema table or column 1 Restricted User Lists for Secure Access The Encryption Wizard allows you to define restricted user lists for any given schema table or column A restricted user list instructs the Encryption Wizard to only grant those database accounts listed the ability to encrypt and or decrypt data You may also specify a Runtime Password that these users must supply before they can view encrypted data This feature used in conjunction with the Administrative Password discussed in Chapter Seven allows you to protect encrypted data even from a user with full DBA privileges In the above example we have restricted access to our chosen data object Scott Emp Only the users System and Scott are able to read decrypt the data of this column Not even the user SYS will be able to decrypt this data or read the decrypted view generated by the Encryption Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Wizard instead SYS will receive a fatal exception when attempting data decryption or encryption furthermore an auditing record will record the failed transaction To add users to a Restricted User List simply type in a valid O
34. chose for a schema table or column Audit Encryption The Encryption of new data occurs when a user inserts or updates an encrypted column using either a Decrypted View or the Encryption Wizard API Library When auditing is set to this type updates and inserts against encrypted columns are tracked for all sessions Audit Decryption Decryption occurs whenever a user selects your decrypted data either through issuing Select statements against a decrypted view or directly decrypting data through the Encryption Wizard API Library Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Audit Both This will employ both of the above auditing types for the columns effected Use the Session Auditing radio buttons to set the auditing type of the object you wish to track to Audit Encryption Audit Decryption or Audit Both After you have changed the radio button settings click on the button labeled Set Auditing The new settings will go into affect for all new sessions that access the encrypted data specified in your auditing scope 4 Re Keying of Data for Secure Keys Many encryption standards such as PCI compliance now require the definition of a key lifecycle The concept being that using the same encryption key on your database column for years increases the possibility of long term attempts to find patterns in the encr
35. crypted view called emp Insert Into Backup Emp Select From Emp Or Insert Into Emp Values a b c Or Select From Emp Fetch Into A B C If these three SQL Language statements refer to the decrypted view Emp then they will fail unless the base table for EMP has a primary key The reason is that the additional rowid column created by the Encryption Wizard will not exist in the above example table Old Emp and will exist in the decrypted view Emp If you do have potential SQL that uses wildcards in DDL it is recommended a primary key be created for a base table being encrypted if one is not already present The syntax to add a primary key to a table is Alter Table Table Name Add Constraint Constraint Name Primary Key Column Name Constraint Name can be any string such as MyConstraint1 23 Multiple column names can also be used to define primary keys Alter Table Table Name Add Constraint Constraint Name Primary Key Co 1 Col2 Col2 Warning The column s used in a primary key cannot be encrypted columns If you do not use primary keys SQL such as the above statements will need to be rewritten to explicitly name every column they reference For example the second statement should read Insert into Emp a b c d Values a b c d Generally speaking wildcard DDL is rare in SQLand is considered bad practice Tip If you would like the Encryption Wizard to add a rowid to all decrypted views and not use a pri
36. d in user memory This allows for a hidden mutating key strategy for the Encryption Wizard s eventual sets of 256 bit keys Thus the Encryption Wizard uses an algorithm to choose mutating 256 bit subsets of a 2048 bit key per encryption round which is AES protected at runtime using Oracle s certified DBMS Crypto package ll User Defined Keys All user defined keys or pass phrases are expanded to the 256 byte key value and obfuscated using AES256 and then again obfuscated at runtime Yet with a user defined key recovery is possible if the key is lost This recovery can only be performed using the Encryption Wizard API lll Key Backup The Encryption Wizard for Oracle now offers complete or partial key backups to a flat file This will allow users to backup database encryption keys to removable disks and or use removable disks as a requirement to read encrypted data The encryption wizard also allows you to password protect your backups and insures that backups from one Oracle database cannot be loaded into another database Because the Encryption Wizard performs another encryption round on these stored keys at runtime it is not necessary to encrypt the backup file unless you want another level of protection The Encryption Wizard always generates one unique key per database column This makes unauthorized decryption much more difficult for large data sets containing many columns Relational Database Consultants Inc www relatio
37. e ability to backup the vital encryption keys audit rules runtime passwords decrypted view definitions and restricted user lists of your encrypted data The backup of the Encryption Wizard always originates from an Oracle RDBMS to a flat file on your operating system For security reasons the following rules apply to backups 1 Backups may never be transferred between machines or databases or versions of the Oracle RDBMS 2 Users may protect backups with a user defined password 3 Backup and Recovery operations will protect your restricted user lists and Runtime Passwords 4 Recovery operations will re create decrypted views and their triggers 5 Function Index definitions are not backed up 6 Key backup files are not encrypted again to insure integrity of data and to encourage users to use another encryption tool on the server i e PGP or WinZip with AES encryption to protect your key backup files with multiple points of failure 7 Key backup files are restricted by the Oracle parameter UTL FILE DIR this parameter allows you to control if and where on the file system backup and recovery operations will be allowed Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Preparing the Backup Manager for Use To use the backup and recovery features of the Encryption Wizard you will need to set the parameter UTL
38. e administrative password will stop unauthorized privileged users from using the Encryption Wizard Relational Database Consultants Inc www telationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc To enter an administrative password simply follow these steps Step One Click on the radio button labeled yes for the question Require Administrative Password Step Two Enter an administrative password of up to 30 characters Step Three Click on the button labeled Save Password You will be asked to verify your entry After you have completed these steps users will be required to enter this password before using the Encryption Wizard user interface or the Encryption Wizard API To remove the use of the administrative password simply click on the radio button labeled no next to the question Require Administrative Password After this simply click on the button labeled Save Password to remove the administrative password requirement 2 Setting a Default Rollback Segment for Transactions Even though the Encryption Wizard performs a commit every 4 096 rows if you are encrypting a table with many large columns and not using undo tablespaces you may need to assign a larger rollback segment for the Encryption Wizard s use than supplied by your default Oracle installation To accomplish this task simply type in a valid currently online ro
39. e eo Peo a w d eo eee ca LR www relationalwizards com Copyright 2002 2013 BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw BC Thin lplusw C Thin sw BC Thin lplusw BC Thin ilusw NUNAGQCAGHAAQGCHGE Er od o RR oc Rc Rc Rc RR RR QUU QUU JIa Sao SA RP ero a O RS a gel ca a RAS AROS as O RR E W Ko j fer u q v Q o sqlplusw sqlplusw sqlplusw Write Read Write Read Write Read Write Read Write Read Write Read Write Read Write Read Write Read Write Read Write Read Write Read Write Read Write Read Write Write Read Write Read Write Read Write Read F ail Fail ail Fail Read Fail Fail Fail Read Fail Read Fail Read Fail The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Time 07 30 03 13 25 15 Page The Encryption Wizard for Oracle Management Reports Audit Log Details Table Owner SUPPS Audit User From Audit Table Name Column Name Timestamp Name Program Type BUG SOURCE FILE BUG SEQ 07 30 03 13 37 49 JOE sqlplusw Read Fai COMMENTS 07 30 03 13 37 49 BOB sqlplusw Read Fail MODIFIED BY 07 30 03 13 37 46 SUE sqlplusw
40. e most powerful of the three levels of Encryption When using Schema Level Encryption all one must do is specify a schema by using the uppermost pick list that appears on the Encryption Decryption screen Simply leave the Table Name list blank When we choose Schema Level Encryption we are instructing the Encryption Wizard to encrypt all valid columns for that schema Encryption of this type should not be performed until you are comfortable using the Encryption Wizard Note When you choose Schema or Table Encryption all valid date and number fields are obfuscated regardless of the encryption type you request Schema encryption does not mean that your complete schema will always be encrypted If new tables are added to your schema the Encryption Wizard will not be aware of them unless you choose schema encryption again to protect the remaining tables Table Level Encryption When you choose to encrypt a single table without selecting any columns to your right the Encryption Wizard will of course attempt to encrypt every valid column within that table As discussed in the previous chapter columns that are primary keys unique keys foreign keys default values and conditional constraints are skipped When employing Table Encryption specify a schema and table name by using the two uppermost pick list that appears on the Encryption Decryption screen Choose both the schema owner and table name of the object that you want encrypted
41. e overwritten Make sure you have chosen a table name from the pick list before you choose this option To enter the Security Manager at this level simply choose a schema name and a table name from the pop up list at the top of screen then click on the button labeled Manage Security Column Level Scope Column level scope will define security for a single encrypted column Be sure to chose a schema owner and table name and then click on the given column s row before clicking onthe label Manage Security Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Security Manger Screen Once you have chosen your Security Scope click on the Manage Security button from the management screen to bring up the Security Manager Security Manager ba ba e Security Manager Security Settings for Table Re Key Data SCOTT EMP Audit the following operations O Reads O Writes Both O None Set Audit Type Restricted User List Enter New Restricted User Read SYSTEM Password Write SCOTT Add User Remove User Runtime Password for the table SCOTT EMP now set for the user SYSTEM Exit Set Password Remove Password The Encryption Wizard s security manager allows you to perform three basic security tasks At the top of the screen you can re key data Below re keying
42. entire schema the decrypted view prefix will be applied to all tables If you are not sure of a decrypted view naming strategy don t be concerned The Encryption Wizard allows you to drop rename or recreate any decrypted view dynamically using the Encrypted Data Management Screen discussed in Chapter Five Managing amp Auditing Encrypted Data Step Six Encrypting Data After we have specified the above options we are ready to encrypt our data Encrypting data is a very disk and CPU intensive task When you encrypt data all rows of the specified data set are encrypted Encryption also involves key generation activities that are very CPU intensive Thus it is ideal to start encryption during off hours Before encrypting data make sure no users are modifying the data tables being encrypted Caution Always back up your data before encryption Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Note If for some reason you need access to your encrypted data table while the Encryption Wizard is encrypting the data you may delete records and or insert records into your partially encrypted tables without any data loss If you encounter a lock contention error these errors do not affect the integrity of the encrypted data set and can be corrected by restarting the encryption process again Ideally one
43. ers Other table triggers such as after triggering event triggers might still corrupt other tables or make incorrect business decisions if they reference any decrypted column These triggers still fire after the decrypted view issues an encrypted write and therefore might read obfuscated data that will make no logical sense unless of course the Encryption Wizard API is used to decrypt these trigger variables RDC will gladly modify sample triggers of your choosing with the Encryption Wizard API as examples for use You can insert your trigger logic into the Instead Of Trigger generated by the Encryption Wizard Use your favorite database trigger editing tool and insert your trigger logic before the Encryption Wizard s routines encrypt the table data These triggers are always owned by the Encryption Wizard under the schema owner rdc_encrypt_user Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc 3 SQL Migration The only SQL migration that is necessary when using the Encryption Wizard is the conversion of wildcard and or implicit references to all columns of any given encrypted table This task is only required if you are encrypting a table with no primary key In this case the Encryption Wizard adds a rowid column to the decrypted view necessary for DDL operations For instance consider the SQL statement on a de
44. ign Keys nor does it encrypt columns with default values or condition constraints aside from the popular Not Null constraint The Encryption Wizard default settings also prohibit encryption of Oracle compressed tables Write us at ewdemo relationalwizards com for instructions on how to enable this feature 4 Transparent Decrypted Views To allow for applications to access physically encrypted data the Encryption Wizard Administrator can optionally create decrypted views against any table with encrypted data Decrypted Views display data in unencrypted format and thus allow applications to seamlessly read and or write to the decrypted data objects through the use of an automatically generated instead of database trigger created for each view Decrypted views can be dynamically created and dropped at any time through the Encryption Wizard User Interface or the Encryption Wizard API Typically the application base table is renamed and then encrypted After this a decrypted view is created with the original table name This allows for transparent access to the same database object by applications 5 Function Indexes To allow for fast access of decrypted views the Encryption Wizard now offers point and click function index creation to speed up access to your encrypted data Function Index data is stored in Reverse Compress format to minimize data search time in the index tablespace Relational Database Consultants Inc www relationalw
45. izards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc 6 Session Auditing The Encryption Wizard also allows the user to specify session auditing Session auditing will record all distinct Encryption and Decryption read write requests for all sessions Session Auditing allows you to see who has had access to your encrypted data down to the Terminal ID and database column level The Encryption Wizard also supplies you with management reports to trace user activity against your encrypted data 7 Restricted User Lists and Runtime Passwords A Restricted User List allows you to specify which users you wish to grant the ability to read and or write encrypted data You can specify user lists for a given schema table or column If there is no user list specified then a user s ability to access encrypted data is based on traditional Oracle grants Runtime passwords protect your data by requiring all users to login and supply a password before viewing encrypted data objects The actual login method is a PL SQL call to the Encryption Wizard API and can be embedded in any SQL script or application A runtime password can be assigned for each column table or schema depending on your preference You may also add password protection for any user defined in a restricted user list This requires the user to login with the password before they can view or change the encrypted data do
46. l perform these tasks Load encryption keys of the current backup domain into the Encryption Wizard schema Load any restricted user lists audit rules and runtime passwords into the database Re create decrypted views of the affected tables Once you have entered the flat file full path name you wish to perform recovery on and any password assigned for that file simply click on Start Operation to begin recovery After each recovery operation the count of how many columns tables and user authorizations that were restored are displayed Using Restore Column to Retrieve Old Keys When a column key is deleted from the Encryption Wizard key table the key is sent to a history table encrypted_column_history To retrieve a column key from the history table use the API procedure call RestoreColumn This method will restore the key to the Encryption Wizard key table but will not encrypt decrypt any data For Instance if we wish to use an encrypted backup of a table no longer encrypted we can restore the previous key in the Encryption Wizard schema using RestroeColumn to give us access to the encrypted data For more on RestoreColumn please reference the Encryption Wizard API Reference Guide Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc System Log for Debugging To debug any errors in the Enc
47. le Name SUPPS ENVIRONMENT FIELD RULES FUNC KEYS MODULES MODULES MAIL PRIORITY CODES PRODUCTS PRODUCT RELEASE Audit Log Summary Column Name DESCRIPTION ENVIRONMENT VAR MANDATORY DEFAULT VALUE FIELD NAME FORM NAME BUG USAGE CUSTOMER USAGE FORM NAME KEY ID ORACLE FUNCTION ASSIGNED TO MODULE DESC MODULE ID PRODUCT ID MAIL STOP MODULE ID PRODUCT ID USER ID USER NAME as RIORITY CODE RIORITY DESC as ASSIGNED_TO PRODUCT DESC RODUCT ID las ASSIGNED TO DESCRIPTION PRODUCT ID R R ELEASE DATE ELEASE ID Relational Database Consultants Inc www relationalwizards com Data Type VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA VA RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC RC DATE VA RC D Dp p p D DDP pp DDD pp R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 R2 Copyright 2002 2013 PR A PP HH HS PP PP HB HS PP PP HP HP HL Total Writes Ooo So 20000 20000 o 20000 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Example Three Audit Log Details Time 07 30 03 13 25 15 Page The Encryption Wizard for Oracle Management
48. llback segment and then click on the button labeled Set Rollback Segment Now Encryption and Decryption operations will be set to use the rollback segment you specified 3 Registering the Encryption Wizard After Purchase After your purchase of the Encryption Wizard simply provide your sales representative with the current database ID of your database This value is displayed when you enter the Administration Screen of the Encryption Wizard Your sales representative with give you a new production license number and registration To purchase the Encryption Wizard for Oracle contact us at ewdemo relationalwizards com 4 Setting Encryption for Null Data To encrypt null character data make a call to the Encryption Wizard API from SQL Plus or your favorite SQL tool that connects to Oracle before encryption For specifications of the API call refer to the description of EncryptNullData in the API Library Reference To set null encryption as default for the Encryption Wizard issue this package call before encrypting your data SQL gt Exec RDC Encrypt Util EncryptNullData Yes To turn off this feature which will only affect future data to be encrypted issue this command SQL gt Exec RDC Encrpyt Util EncryptNullData No Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Chapter Eight HSM Interface
49. m running Java Installing the Encryption Wizard on a Windows client does not preclude its use on non Windows Oracle servers such as Unix or Linux platforms The Client Installation is performed when you download the Encryption Wizard for Oracle and extract the exe file or the zip file If you choose to run the EncryptionWizardJava exe Windows installation file the Encryption Wizard will create a Windows program group and install the appropriate files and documentation needed for the Oracle Server Installation Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Note Windows 7 and 8 Clients The Encryption Wizard client installation may be placed in the directory c program files x86 Encryption Wizard For Oracle during the client install Windows should modify the links but in some cases it fails to so In this case you will need to manually modify your links in the Encryption Wizard for Oracle Program Group or create new ones 2 Database Installation Once you have completed the client installation for the Encryption Wizard you are ready to begin the Oracle database installation You can start the Encryption Wizard database installation from SQL Plus directly by running the installation script rdc_encrypt_install sql SQL gt rdc_encrypt_install sql Or if you are running a Windows client with SQL Plus yo
50. main Restricted User Lists and Runtime Passwords allow you to block out any Oracle user from viewing your encrypted data even a DBA user such as SYS To block DBA access to data use Restricted User Lists and or Runtime Passwords in conjunction with the Administrative Password which is discussed below 8 Administrative Password To prevent Database Administrators unauthorized access to the Encryption Wizard the Encryption Wizard Administrator can set an optional password required to Encrypt and Decrypt data and use the Encryption Wizard interface Likewise the Encryption Wizard administrator does not require DBA privileges to use the Encryption Wizard 9 Data Consistency The Encryption Wizard also employs intelligent recovery operations if any Encryption or Decryption attempt fails The Encryption Wizard will self diagnose any incomplete Encryption or Decryption operation and allow the administrator to simply continue the process or back out This helps guard against serious data inconsistency due to partially completed operations that may occur due to an unexpected database event like a shutdown 10 Key Backup amp Recovery for Continuity Planning The Encryption Wizard allows users to backup encryption keys to a flat file These files may be password protected and cannot be used on any other database than from where they were created This enhances the security of your key backup and recovery operations Relational Database Consul
51. mary key even if one is present issue this SQL after installation Update rdc_encrypt_license set use rowid for views X commit Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Chapter Seven Encryption Wizard Administration By using the Encryption Wizard Administration Screen we can set an administration password for the Encryption Wizard and define a default rollback segment for large encryption efforts To enter the Administration screen simply click on the main menu item labeled Administration ES Encryption Wizard for Oracle Administration and Registeration l Ioj x The Encryption Wizard for Oracle Copyright 2003 Relational Database Consultants Inc Encryption Wizard Administration License Type License Number e Demo Production suejones mycompany com 108 Administrative Password Password Yes No DO r Default Rollback Segment Segment Name Yes e No Exitto Main Register Wizard Save Password Set Rollback Register your demo or production license 1 Creating an Administrative Password Because any DBA can access all objects in your database the Encryption Wizard allows you to choose an administrative password that will be required to enter before a user can use the Encryption Wizard or its underlying API Th
52. n Wizard of a new HSM slot number 2 Informing the Encryption Wizard of a new HSM password Both of these functions are accomplished using the Encryption Wizard API SQL gt exec RDC Encrypt HSM ChangeParameter Slot Num 2 HSMPasswora SQL gt exec RDC Encrypt HSM ChangeParameter Password NewHSMPassword OldHSMPassword Refer to section 3 of the Encryption Wizard API Reference Guide for specifications on the HSM API calls All runtime errors regarding the HSM card are stored in the Encryption Wizard System Log in the table internal_audit 5 Un installing the HSM Interface To un install the Encryption Wizard HSM interface simply decrypt your data and run the original installation script rdc encrypt install sgl You may also want to drop these objects from SYS drop function SYS RDC ObfuscateSeed drop function SYS RDC lnitMasterKey drop function SYS RDC GetJavaParameter drop java class RDC JavaHSM Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Chapter Nine Management Reports The Encryption Wizard for Oracle provides you with three useful management reports The first of these reports allow you to keep track of your current encrypted columns The two auditing reports are designed to track auditing and usage of encrypted columns that we learned to set in the Security Manager We
53. nalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc 3 Data Types to be Encrypted The Encryption Wizard for Oracle allows you to encrypt these basic types of data Character Data Varchar2 and Character data types can be encrypted using any of the three above methods If DES AES 128 bit or AES 256 bit encryption is utilized the Encryption Wizard will modify the column lengths of your character data upwards to the nearest multiple of 8 16 and 32 characters respectively Number and Date Data Date and Number data can only be obfuscated Large floating point numbers over 38 places are rounded in accordance with Oracle s to_number function default settings Large Object Binaries The Encryption Wizard is able to encrypt both BLOB and CLOB Oracle data types There are no size restrictions yet large objects take considerable time to encrypt and decrypt Be sure to test the time of encryption before attempting to encrypt a large table of BLOB or CLOB data CLOB data is trimmed of trailing blanks Null Data The Encryption Wizard can be instructed to encrypt null data With a call to the API Library you can set the Encryption Wizard to encrypt null values for varchar2 char and nchar datatypes For instruction on enabling or disabling this feature please refer to chapter seven section 4 of this document The Encryption Wizard will not encrypt Primary Unique or Fore
54. ncrypted Columns Data Encryption Audit Name Column Name Type Type Type ASSIGNED_DATE DATE Obfuscation None ASSIGNED TO VARCHAR2 Triple DES3 None BUG_SEQ NUMBE Obfuscation None CLOSED BY VARCHAR2 Triple DES3 None CLOSED_DATE DATE Obfuscation None CLOSED_TIME_SPE NUMBER Obfuscation None CLOSED_TIME_SPE NUMBER Obfuscation None CTN_SEQ NUMBER Obfuscation None DUE_DATE DATE Obfuscation None ENTERED BY VARCHAR2 Triple DES3 None ENTERED DATE DATE Obfuscation None FIXED BY VARCHAR2 Triple DES3 None FIXED DATE DATE Obfuscation None FIX TIME SPENT NUMBER Obfuscation None FIX TIME SPENT_ NUMBER Obfuscation None MODULE ID VARCHAR2 Triple DES3 None PRIORITY CODE VARCHAR2 Triple DES3 None PRIORITY DATE DATE Obfuscation None PRODUCT ID VARCHAR2 Triple DES3 None PRODUCT RELEASE VARCHAR2 Triple DES3 None PRODUCT VERSION VARCHAR2 Triple DES3 None RELATED BUG SEQ NUMBER Obfuscation None SEVERITY CODE VARCHAR2 Triple DES3 None SEVERITY DATE DATE Obfuscation None STATUS CODE VARCHAR2 Triple DES3 None STATUS DATE DATE Obfuscation None SUMMARY VARCHAR2 Triple DES3 None TEST PHASE ID VARCHAR2 Triple DES3 None TEST SCRIPT ID VARCHAR2 Triple DES3 None TIMESTAMP DATE Obfuscation None USER ID VARCHAR2 Triple DES3 None VERIFIED BY VARCHAR2 Triple DES3 None VERIFIED DATE DATE Obfuscation None VERI TIME SPENT NUMBER Obfuscation None VERI TIME SPENT NUMBER Obfuscation None Relational Database Consultants Inc www relationalwizards com Copyright 2002
55. nd create a table containing some hypothetical valuable information Now read the small tablespace in a text editor You will see how easy it is to obtain your corporate data Imagine if someone had used a tool such as FTP to transfer your tablespace to their computer would you be comfortable with the information they now had The Encryption Wizard will eliminate these real fears Simply encrypt your valuable corporate data so anyone downloading our hypothetical tablespace will not possess your corporate value but will instead own useless information Not employing Encryption techniques is akin to leaving money and valuable documents out in the open Thus the lack of encryption methodology for relational corporate data is a primary cause of the billions of dollars lost in Information Systems each year By leaving money credit and valuable information unobfuscated hacking and corporate theft is encouraged Use the encryption techniques of the Encryption Wizard and protect the value of your corporate data at a fraction of the cost of other less secure methods Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Chapter One Installation There is no database encryption tool on the market that is easier to install than the Encryption Wizard for Oracle This is because the Encryption Wizard runs exclusively on the Oracle RDB
56. ng re keyed simply enter the re keying screen later and re key the object in question again The Encryption Wizard will do a partial re keying in this case updating only those rows that were not re keyed before the event Table encryption and decryption will not be allowed for partially re keyed tables and other users will not be able to encrypt decrypt data Partially re keyed tables will show up as needing recovery in the main listing of the Manage Encrypted Data screen as shown above Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc 5 Default Masks for Error Handling In many cases applications need to query and modify table data with encrypted columns For instance a utility company may employ data entry clerks to update customer data yet may not want them to view private information Using the Encryption Wizard we accomplish this division of tasks by using a default mask With a default mask an unauthorized user querying a decrypted view of an encrypted base table will not receive an Oracle error instead a constant string will be returned masking the actual data For instance we could return this default mask for a customer bank account number XXX XXX XXX Using a default mask allows end users and applications to work around encrypted data and work with other columns within sensitive encrypted tables
57. of your encryption keys simply enter the complete path of a file you wish the Encryption Wizard to create or overwrite This file will contain your encryption key seeds in un encrypted format This is the same format the data appears in the database these keys are further encrypted at runtime You may also optionally enter a password below the file name to further protect users from restoring these keys In all cases these files cannot be restored by any other database than the database for which the backup was created For further protection we recommend using a third party encryption tool like PGP or WinZip with AES to protect your backup files Once you are ready to backup your encryption keys simply click on the button labeled Start Operation To overwrite an existing flat file simply you may click on the Yes radio button labeled Overwrite previous data Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Recovering Encryption Keys from a Backup To recover or restore lost keys simply click on the radio button labeled recovery After this enter the filename and any password that may have been assigned to that file If you wish to overwrite existing keys in the Encryption Wizard schema you may click on the Yes radio button labeled Overwrite previous data The recovery operation wil
58. ply use the pick list labeled Encryption Type Warning If you chose AES encryption for a Blob or Clob data type the data will be encrypted using DES3 technology AES encryption is not yet available Optionally you may check the flag Add Salt Salt makes detection of identical data more difficult by adding random bytes to data Leaving this box unchecked will save the data storage space used to add salt Step Three Key Generation After we have chosen our encryption type we may optionally enter a key for encryption A key is like a password and can be used in recovery of data if your rdc_encrypt_user schema becomes corrupt You can also choose to have your key automatically generated inn this case simply skip this step Step Four Specify a Temporary Index for Speed Optional If you are encrypting a very large table over 10 million rows you may choose to have the Encryption Wizard build a Temporary Binary Index for performance during encryption Only use this option if encryption proves to be too slow for a given database object Step Five Transparent Decrypted View Creation Optional A Decrypted View is a duplicate projection of any table that the Encryption Wizard partially or completely encrypts Decrypted Views allow users to transparently perform SQL operations to read and or write data to the encrypted table Data appears in its original form when using a Decrypted View If you plan on migrating applications or
59. protection against the potential theft or destruction of online marketplaces where goods and services are exchanged and the actual money result of those transactions are stored The reason being is that these forms of value and capital accumulation must be stored as exact information If this information is obfuscated it loses its value until such a time that it is unobfuscated where it again becomes a value To encrypt data is a magical process It would be like a banker of old being able to transform currency into obfuscated pieces of worthless paper Thus any thief would only be stealing the product of our forests and not much value at that Today we possess this magical art of obfuscation and encryption thus it is inevitable that capital will employ this practice to protect data since data content not only determines the money result in the circuit of production and consumption but the actual marketplace velocity itself The Encryption Wizard for Oracle offers users the ability to encrypt relational data an emerging necessity in the networked global economy and among the governments that comprise the superstructure of this epoch in history With the Encryption Wizard data administrators can easily transform valuable corporate data into useless bytes of information until such a time as they choose to re establish the value within their data Try an experiment if you are not convinced Create a small tablespace in your Oracle database a
60. pt class was loaded properly issue this query from rdc_encrypt_user Select From User Objects Where Object Type JAVA CLASS And Object Name JavaEncrypt And Status VALID To verify the new GPL installed encryption ciphers issue this query from rdc encrypt user select from valid encryption types The Free GNU Public License GPL software notice www gnu org copyleft gpl html Source code for GNU Crypto is available at www gnu org software gnu crypto downloading Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc 5 Starting the Encryption Wizard User Interface To envoke the Encryption Wizard Main Menu from windows simply click on the icon labeled Encryption Wizard User Interface If you are running Unix or Linux simple run the provided shell script that you modified as such in the directory containing the Encryption Wizard install EncryptionWizard sh After you start the User Interface you will be prompted for your database login to the Encryption Wizard account rdc encrypt user Make sure you remember the password that you assigned during installation step 2 and simply supply the login information as such In most Oracle shops the Oracle port is either 1521 or 1522 if this is not the case check your tnsnames ora file and the listener ora file on the server to find the
61. racle account in the field labeled Enter new restricted user After this click on the button labeled Add User At this point you will be prompted as to what type of restricted access you would like to grant Define Restricted User Level of Access Esm e i Ly Please choose the level of access for this user Read write Both Cancel Read Select Read operations on object are allowed Update Insert denied Write Update Insert Write operations are allowed Select denied This option cannot be used in conjunction with decrypted views because instead of triggers must populate with decrypted data Both Select Read and update insert Write operations on object are allowed To remove a user from a Restricted User List simply choose a user name from the Restricted User List and click on the button labeled Remove User 2 Runtime Passwords for User Authentication Runtime Passwords add a further layer of protection for your encrypted data Runtime Passwords require users to validate a password before they can gain encryption and or decryption privileges for a given schema table or column There are two types of Runtime Passwords 1 Default Runtime Passwords Default Runtime Passwords are assigned to a given database object such as a schema table or column After the Default Runtime Password is defined a users must authenticate the password before they can have access to
62. ryption Wizard User Interface the API or the Runtime Library first check the System Log To do this simply click on the button labeled System Log from the main menu From here we will enter the System Log window E The Encryption Wizard for Oracle Internal Audit Log The Encryption Wizard for Oracle Copyright 2003 Relational Database Consultants Inc Internal Message Log Oracle User RDC_ENCRYPT_USER Timestamp 12 05 2003 20 24 10 Message ExecuteDDL ORA 00955 name is already used by an existing object Terminal ID unknown Machine frdc4 OS User garuda Logon Time 2003 1 2 05 20 24 09 0 Program oBc Thin Client Module Additional Information RDC_ENCRYPT_USER RDC_Encrypt_Runtime DecryptData 558 SQL BUFFER SQL BUFFER RowlD RDC Encrypt Rowid From RDC STANDBY USER INTERNAL AUDIT With Check Option 4 lt Return Previous Record Next Record gt Clear Log It is from this screen that we can scroll through any errors or messages and view the details of the event These records are all stored in the table Internal Audit owned by the user RDC ENCRYPT USER Feel free to periodically clear the System Log by clicking on the button labeled Clear Log Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Chapter Six Migr
63. s com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc The drawback of this method is that your applications will now have to point to the new table names in this case the names assigned to your decrypted views Create Decrypted Views and Use Synonyms By default the Encryption Wizard will create all decrypted views in its own schema rdc encrypt user Using this migration method a developer would encrypt a table let us say table EMP in the SCOTT schema After completion the developer would point his or her applications to the table rdc encrypt user emp You may also use a third schema for this method This method works well with the use public synonyms In this case after the decrypted view EMP is created you can issue this command from a DBA account Create public synonym emp for rdc encyrpt user emp Now users can seamlessly use this new synonym as if they were referring to the original table EMP 2 Database Trigger Migration There are two concerns regarding database triggers that are defined on tables that are encrypted Tip Data Corruption Before Update or Insert Row Level Triggers These triggers fire after the Encryption Wizard decrypted view issues an encrypted write to the encrypted table Therefore these triggers cannot modify any column that is encrypted unless they are modified with the Encryption Wizard API Invalid Selects All Other Table Trigg
64. should not attempt this on a regular basis When you are ready to encrypt your data simply click on the button at the lower part of the Encryption Decryption Screen labeled Encrypt Object At this point the Java interface will lose control of the screen and the Encryption Wizard will encrypt table by table the data set you specified At the end of the encryption round a message will appear giving a total number of rows that were successfully encrypted Once the Encryption Wizard notifies you that your tables and or columns have been encrypted it is vital that you now point all users and applications to the decrypted views and not to the original encrypted tables Encryption Recovery If something happens during the encryption process such as your database shuts down or another user exclusively locks the table being encrypted the Encryption Wizard provides simple recovery from these potential disasters In these above cases if the Encryption Wizard is interrupted your data will be in an intermediate state Some of the rows will be encrypted and some will not be Thus it is important to perform encryption recovery if your encryption process is interrupted To perform encryption recovery simply pick again the schema table and or column which you originally encrypted that is in need of recovery To recover this object you can either choose decryption or encryption Decryption will back out your partially encrypted table to its origin
65. sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw sqlplusw plusw plusw sqlplusw plusw plusw Write Fail Write Fail Read Fail Read Fail Read Fail Read Fail Read Fail Read Fail Write Fai Write Fai Write Fai Write Fai Write Fai Write Fail Write Fail Write Fai Write Fai Write Fai Write Fai Write Fai Write Fai Write Fai Write Fail Write Fai Write Fai Write Fail Write Fai Write Fai The Encryption Wizard For Oracle User Manual Time 07 30 03 13 25 15 The Encryption Wizard for Oracle Management Reports Relational Database Consultants Inc Audit Log Details Table Owner SUPPS Table Name USERS VALID ENVIRONME VERSION VERSIONS MAIL Column Name ADMIN ALLOWED ENTRY ALLOWED MAIL STOP QUERY ALLOWED USER ID USER NAME Audit Timestamp 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 ENVIRONMENT VAL 07 30 03 ENVIRONMENT VAR 07 30 03 ASSIGNED TO DESCRIPTION PRODUCT ID RELEASE DATE VERSION ID MAIL STOP PRODUCT ID USER ID VERSION ID 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 07 30 03 Relational Database Consultants Inc WWW WW WwW WWW WW WWW WwW 237 Hecht ie ES ror Hes ie 137 237 237 ou 3 37 37 237 ow 37 37 3 17 38 36 239 739 239 36 36 2 37 37 237 BOB SUE JOE BOB
66. stalled NAME VALUE Card Type Luna PCI 7000 Cipher Algorithm HmacSHA1 Cipher Provider SunJCE version 1 4 Input Key Algorithm GenericSecret Input Key Length 256 Inserted Key Algorithm HmacSHA256 Inserted Key Length 256 Java Library Path c oracle prod10g bin C WIN Java Version 1 4 2 04 If your installation fails you can start this script over as many times as you would like keeping in mind the Encryption Wizard does not delete old key names from the HSM Installation errors are stored in the Encryption Wizard System Log as discussed in Chapter Five Make sure to manually drop the table hsm parameters if you are not running this script for the first time and the table was created This is a safeguard against unintentionally overwriting a master key and or its parameters At the end of the installation you will be prompted for your demo license code If you wish to register later you may hit return Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc 4 HSM Maintenance Tasks Once the Encryption Wizard HSM interface is successfully installed all passwords and data will be dependent on the successful functioning of the HSM card Make sure to use your HSM vendor s backup utilities The two maintenance tasks regarding your HSM card and the Encryption Wizard are the following 1 Informing the Encryptio
67. sword To remove a password for a given user simply choose the user from the restricted user list and then click on the button labeled Remove Password Password protection insures that a malicious individual with DBA privileges cannot access any Oracle account and view encrypted data Use this in conjunction with the Administrative Password to protect your encrypted data from malicious hacks into your Oracle RDBMS In the previous example we have set a runtime password for the user SYSTEM This requires the SYSTEM user to login to Encryption Wizard runtime library for authentication before the user can view or change encrypted data In this case SYSTEM would need to login to the Encryption Wizard as such before they could view the table SCOTT EMP SQL gt exec rdc_encrypt_runtime login TIGER SCOTT EMP See the Encryption Wizard API Reference Manual for further details on the runtime login method that is used to authenticate restricted users 3 Session Auditing for Security History Session Auditing records all attempts at encryption or decryption of your protected data This information is valuable for reporting and or tracing any unwanted access to encrypted data If a user selects 1000 decrypted rows of sensitive data from an encrypted table the Encryption Wizard will simply add one auditing record per column to report that a given user has decrypted that given column There are the three types of auditing you can
68. t Masks for Error Handling Decrypted Views for Application Transparency Function Indexes for Performance Key Backup and Recovery for Continuity Planning System Log for Debugging Chapter Six Migrating Applications to Encrypted Data Tables Chapter Seven Encryption Wizard Administration Chapter Eight Hardware Security Module HSM Interfaces Chapter Nine Management Reports Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Introduction Database Encryption In this age value is stored within the world s information systems Before the advent and proliferation of computers corporate value took the form of money and physical documents Yet just as capital seeks to automate labor capital has automated exchange value itself thus replacing physical money with credit and other forms of virtual money Today not only is money value stored within the worlds information systems but the actual marketplace where the velocity of capital turnover is determined has been abstracted to a virtual space within the computer and network infrastructure of the world s information systems thus today it becomes paramount to protect this real value just as a bank in the past would protect its physical deposit of money and its ability to conduct business transactions Data Encryption and Obfuscation is the ultimate
69. tants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Chapter Three Encrypting Table Data To Encrypt Data within your Oracle RDBMS click on the top most option of the Encryption Wizard Main Menu labeled Encrypt Decrypt Data At this point you will enter the Encrypt Decrypt Data Screen F The Enayption Wizard for Oracle Encrypt Decrypt Data The Encryption Wizard for Oracle Copyright 2003 Relational Database Consultants Inc Encrypt Decrypt Schema Name Choose columns to encrypt sco Table Name Encryption Type AES 256 Bit vi Key Value Optional Add Salt Create temporary index for speed Tablespace for temporary index OYes No Create Decrypted Views None ReadOnly Updateable Schema to own Decrypted View s Decrypted View prefix fRDC ENCRYPT USER Choose a database objectto encrypt or decrypt lt Exit to Main Manage Data gt Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Step One Choosing a Data Set for Encryption From the Encryption Decryption Screen we can encrypt database data at the schema table or column level These three levels define our data domain for encryption Schema Level Encryption Schema Level Encryption is th
70. the Manage Encrypted Data Screen and then click on the button labeled Manage Backups This backup domain will backup all encryption keys for a given table 4 Column Backup To choose this level click on a given database column within the Manage Encrypted Data Screen s detail listing and then click on the button labeled Manage Backups This backup domain will backup one encryption key for the chosen schema Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc Once you have clicked on the button labeled Manage Backups you will enter the key backup and recovery screen of the Encryption Wizard Ekey Backup and Recovery A iol x Key Backup amp Recovery Current Backup Domain Complete Database Which operation would you like to perform Overwrite previous data e Key Backup O Key Recovery Yes e No Please enter the complete path and name of the backup file for this operation ju01 encryptionbackupiny_backup bt File Password You are now managing key backup for the complete database lt Exit Window Start Operation System Log gt From the Key Backup and Recovery window we can backup all or a subset of our keys based on the Current Backup Domain which is the topmost field in the window Creating a Backup of Your Encryption Keys To create a backup
71. the given database object protected To assign a Default Runtime Password enter the Security Manager for a given schema table or column scope Once you have done this simply click on the button labeled Set Password at the bottom of the Security Manager screen At this point you may enter a default password of up to 30 characters To check if a given column or set of columns for a table have a Default Runtime Password query the Manage Encrypted Data Screen for the table in question and look under the heading labeled Password If there is a Default Runtime Password that field will be labeled Yes Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc 2 User Specific Runtime Passwords User specific runtime passwords are assigned on databases objects to users on a restricted user list User Specific Runtime Passwords will take precedence over any Default Runtime Password assigned for a given database object Once you have a Restricted User List created from the Security Manager Screen simply click on the user you wish to define a user specif password for and then click on the button below labeled Set Password Passwords are always case sensitive If you wish to periodically change the Runtime Password for a selected object or user simply click on Set Password again and supply a new pas
72. u have edited the path information and clicked on OK open a new command prompt and run the test java version again Setting the Proper Path for Unix Linux Clients After you have extracted the Encryption Wizard for Oracle ZIP file move all of the files up to a single Unix Linux directory where you wish to start the Encryption Wizard To change the Java path for your session simply edit your profile or bash_profile file in the home directory of the Unix account that will run the Encryption Wizard to include the proper CLASS PATH value You do not need to run the Encryption Wizard from the Oracle Unix account Depending on your FTP or file transfer commands you may need to set the EncryptionWizard sh as an executable script as such from your shell chmod 744 EncryptionWizard sh Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc 4 Installing AES Encryption With GPL GNU Crypto Optional For Oracle licenses that do not have access to Oracle s AES Encryption the Encryption Wizard now integrates with the free GPL GNU Crypto java library to offer AES256 and AES128 ciphers for all customers This feature is not available for Oracle 9i users Contact us if you need a customization After Encryption Wizard Database Installation follow these steps to install GPL GNU AES To verify the GPL JavaEncry
73. u may simply click on the Icon labeled Database Installation in the new Encryption Wizard program group created by the Windows client installation During the server installation the Encryption Wizard will prompt you for these following five items 1 The System Password This value is not stored and is used to create the Encryption Wizard user account 2 The SQL Net connection string used by SQL Plus to connect to the database where you are installing the Encryption Wizard 3 The new password you would like for the Encryption Wizard account rdc_encrypt_user 4 The default tablespace you would like to assign to the Encryption Wizard Optional 5 The Temporary tablespace you would like to assign the Encryption Wizard Optional Use may manually remove this parameter from the installation script if you do not use SQL Net Once you have supplied the Encryption Wizard installation script with these values the script will create the Encryption Wizard Oracle account rdc_encyrpt_user and compile the intelligent PL SQL packages that make up the Encryption Wizard for Oracle Using Oracle s DBMS Crypto AES Encryption Optional During installation you will be prompted to execute the following SQL connected as SYS SQL gt grant execute on DBMS Crypto to RDC_Encrypt_User This step requires Oracle10g and above If you need AES encryption yet cannot implement DBMS Crypto see section four of this chapter Installing AES Encryption With
74. uditing Encrypted Data to the name of the original base table or another name of your choice The only catch to this methodology is that when you rename a table you will invalidate all triggers procedures functions and views to that table Make sure you evaluate these objects to see if they are still needed for your encrypted data and more importantly make sure these objects will not corrupt your encrypted data Any insert or update of encrypted data should be done through the use of decrypted views or the Encryption Wizard API Create Decrypted Views in Application Schema with Prefix This second method is easy to implement with the Encryption Wizard Simply create your decrypted views in the same schema as your encrypted application In this case you will need to use a decrypted view prefix so your decrypted view name will not conflict with the original base table You can rename any view after your initial encryption of table data using the Decrypted View Manager discussed in Chapter 5 therefore this prefix is not binding Using this method we might assign a decrypted view prefix of VE for the encrypted table EMP In this case our decrypted view will have the name VE_EMP and will be owned by the schema owner SCOTT When accessing data we now refer to the new decrypted view in our SQL as in these examples Select from VE_Emp Update VE_Emp Set Salary Salary 100 Relational Database Consultants Inc www relationalwizard
75. ypted data for the purpose of data theft Although the Encryption Wizard for Oracle further obfuscates keys at runtime re keying is the best practice for a highly secure environment For instance if re keying is done once a week a data thief working to crack a key will have only a week to accomplish the reverse cryptography involved When data is re keyed all the encrypted rows for a given database object schema table or column are updated with a new encryption key thus this process will take approximately a little longer than the initial encryption of the same object To re key data enter the Security Manager Screen by choosing a schema and then optionally a table and column for re keying This is the scope of re keying you are choosing as discussed in the overview for this chapter above Once you have chosen a schema table or column to re key and have navigated to the security manager simply click on the re key data button at the top right of the window Screen control will be lost until the object s you have specified are re keyed During a re keying event the Encryption Wizard will store the new and old key value seeds in the system log Internal_Audit Please make sure to verify the re keyed data with a Decrypted View or an API call before clearing the System Log after a re keying event Re key Recovery If for some reason during a large re keying operation the database crashes or a user obtains a lock on a table bei
76. ypto AES encryption is not available as of release 5 0 0 1 for CLOB or BLOB datatypes VI Triple AES 256 bit Encryption Triple AES 256 consists of three rounds of AES 256 encryption Each encryption round is performed by Oracle s DBMS Crypto package and or GNU Crypto VII Blowfish 448 bit Encryption This older cipher is available when you have loaded GNU Crypto Blowfish uses a large 448 bit key but is slower than the other ciphers available Relational Database Consultants Inc www relationalwizards com Copyright 2002 2013 The Encryption Wizard For Oracle User Manual Relational Database Consultants Inc VIII None Choosing None as your encryption type allows auditing restricted user lists and password protection for non encrypted columns The Encryption Wizard stores your encryption keys in the Oracle database as a hash value and then generates a second hash at runtime in memory using a large 2048 bit key Encryption Keys can either be specified by the user or automatically generated by the Encryption Wizard For character data salt can be specified which obfuscates identical data with a random string concatenation before encryption 2 Key Management I Key Values are Stored in the Oracle RDBMS and Protected All keys values are stored as 2048 bit raw variables within the Oracle RDBMS the potential mathematical seed of the eventual key to be utilized At runtime this key matrix is again encrypted and cache

The Encryption Wizard for Oracle - Relational Database Consultants

Contents

Download Pdf Manuals

Related Search

Related Contents