Closure Report


An “Encryption of Data at Rest” report was produced by PwC. Within the content, a number of recommendations were made.

The primary recommendation was that the University should continue to progress with the Oracle Advanced Security solution. It was agreed that this supported the intended development of the Enterprise Data Warehouse (EDW) and that a project should be initiated to progress.


The Project Manager would like to acknowledge the support and commitment of all project team members:

Iain Fiddes Development Services Head of Development Services
Gillian Henderson Development Services Senior Database and systems Administrator 
Heather Larnach             Production Management Technology Management Manager 
Mark Lang Development Services Technology Manager 
Maurice Franceschi IT Infrastructure IT Portfolio Manager 
Stefan Kaempf Production Management Project Sponsor 
Gordon McKenna Development Services Database and Systems Administrator
Chris Cord Production Management Database and Systems Administrator
Ewan Scott
Development Services             Database and Systems Administrator 


The initial objective was to understand what work has already been undertaken in terms of prior preparation, what tasks have yet to be fulfilled and who as resource are available and skilled to complete.

The second objective focused on execution and delivery of the security solution, including security key management.


Objectives and Deliverables Priority Achieved
O.1   Understanding of technical requirements    

D1.1 Work identified and to be carried forward from DTI021 project

Must         Y
D2.1 Trial environment has been set-up and is available for use Should         Y      
O.2   Progression of encryption at rest solution    
D2.1 The encryption algorithm to be used, the encryption key and where it is to be securely stored Must         Y
D2.2 A review of the implementation plan, risks, requirements and delivery benefits Must         Y

D3.3 Performance testing  (agreement would need to be reached that the results from DTI021 are satisfactory)  

Should         Y
D3.4 Implementation  Must         Y 
O.3   Project documentation will be maintained for future use    
D3.1 Essential go-live documentation has been written and maintenance administration proven Must         Y     
D3.2 Supplementary supporting documentation has been written and maintenance administration proven    Should         Y
D3.3 Instructions have been shared with Production Management Must          Y
O.4  Document all completed/outstanding work at project end    
D4.1 Produce a project closure report Must         Y



Description Project stayed within scope? 

Review the outcomes from project DTI021 investigation.

2 Make a decision on the encryption algorithm (this could affect performance and affect the complexity of table-space creation).                Y
3 Determine the encryption key to be used (password, auto-login, local auto-login).                Y

Key-store type, location and management to be reviewed (software/hardware, separate infrastructure, key-store VM, security).


Risks of TDE implementation to be reviewed alongside requirements and benefits (mainly around security of key-store, added complexity for admin of db, encryption cannot easily be removed once implemented, if the password is lost)

6 Undertake performance testing.                Y
7 Determine the procedures and test standard administration  tasks (backup/recovery, exports, key-store password change, creating new encrypted table-spaces).                 Y



No.  Description  Achieved? 

  The safe guard of both present and future data that is/will be contained in the Enterprise Data Warehouse.



Success Criteria

No.  Description  Achieved? 
1 A working advanced security solution in the Enterprise Data Warehouse.         Y


Analysis of Resource Usage:


Staff Usage Estimate: 60 days

Staff Usage Actual:       46.5 days

Staff Usage Variance:   77.5 %

Explanation for variance 

The project was delivered to plan in terms of timescale but some elements were perhaps not to a mature level had more time been available for more effort to be expended.


Key Learning Points

Ref Title Description Recommendations
1 Quality of 3rd Party Support Assistance from a third party was not a positive experience.  A reported problem had to be dealt with over numerous telephone calls and with different technical support teams in efforts to get a resolution. An awareness and further exploration perhaps with a Relationship Manager as to how future support might be improved.
2 Complexities of New Technology Adoption It is with the use of the new technology that additional steps of complexity have now been introduced. Specifically around recovery, exports, table-space creation, upgrades and migration. Incorporate more practical rehearsals with knowledge and experience being shared more widely on a regular/periodic basis.


Outstanding Issues

The  following issues are listed and will be taken forward with a separate ASTA code set-up and as Encryption at Rest Part 2 Project planned for next year.

Ref: Description Owner


There are a number of open risks (see below) that are to be reviewed by the Project Sponsor for mitigation/acceptance. A paper is to be written with follow-on actions.    Stefan Kaempf
02 Fire-Safe and Last Pass are to be introduced as mechanisms for storage of encryption keys. Heather Larnach                             
03 A bug remains in the development environment. However, the data is accessible with a workaround in place. A patch/alternative fix needs to be considered longer term.  Gillian Henderson
04 Complete the update to the TAD and systems documentation.  Gillian Henderson





Password for Keytore lost - Password is critical. If password lost the data can NEVER be accessed.

-Verify DR procedure for PMP. -Mitigate by having alternative location to store keystore password: lastpass, firesafe -if 2 or 3 locations - passwords could get out of sync – introduce procedure/policy for password change. -old passwords need to be retained in case need to restore beyond time password changed. The time passwords change also needs to be kept.

Performance - 1-10 time performance reduction by having Oracle encryption at rest in place

-Some basic performance testing has been carried out and no issues identified but not under heavy load. - carry out further performance testing? - can allocated more memory to database if required. - can limit data being encrypted if required at a later stage - Determine if risk that merits encrypting data is more important that performance or if there is some alternative solution.

Administration overhead of TDE - Additional steps/complexity added to several standard admin steps i.e recovery, exports, server restarts

-Tested out key procedures/admin and document and knowledge share (partially done. Some issues encountered which being investigated by Oracle support) -Minimal testing and deal with issues and changes to other procedures as arise.

-Hands on training and familiarisation for Dev Tech/Tech Man

Oracle Support- from initial responses support for TDE specialised and in silo. Problematic if calls spanning different components I,e Data Guard, Single Tenant, rman and TDE.


-contact oracle support to raise issue and identify how this can be improved. Call should be passed to relevant team rather than new spin off calls or multiple calls for same issue.


Keystore files lost

-Keystore exists on KB and AT so some resilience. -Keystore backup should not be written to same location as database backup (TSM).

-Database backup (TSM) and VM backup both carried out seperately but currently being backed up to same site. -Identify alternative location to backup keystore file or accept current arrangement.


Keystore on same server as Database - If disks stolen could include database files and keystore file.

-Test local Autologin (done and OK). This allows database to automatically open without supplying a password but as local autologin file is specific to that server, the database cannot be recovered onto another server without supplying a password. -Investigate installing keystore on separate server and making available to EDW server. -Investigate Hardware Keystore (once installed cannot revert back to software keystore).  -Investigate other options for Keystore (Keystore VM?)


It is later determined that Oracle TDE is not required.


-According to Oracle TDE cannot be removed once installed. Data can be unencrypted and TDE can be disabled but the keystore file and keystore password must be retained forever unless database recreated. (Oracle remembers the previous TDE information if ever want to encrypt in the future) -Would need to rebuild the 12 EDW databases if oracle TDE no longer required.

Is TDE introducing more risks than it is resolving? Does it offer protection for the scenarios where we think there could be a risk if we didn’t have TDE in place?

Ensure implementation of TDE meeting requirements.


Project Info

Protecting Data in the Enterprise Data Warehouse (EDW)
ISG - IS Applications Infrastructure (INF)
Management Office
Project Manager
Kevin Hone
Project Sponsor
Stefan Kaempf
Current Stage
Project Classification
Start Date
Planning Date
Delivery Date
Close Date
Overall Priority