Month: March 2014

Certification Alternative For Retired Oracle Exadata 11g Certified Implementation Specialist Certification Path

Oracle retired Oracle Exadata 11g Essentials (1Z0-536) on March 31, 2014. Existing certification holders should note that this retirement only suspends the delivery of further exams, but does not affect your certification. If you currently hold the “Oracle Exadata 11g Certified Implementation Specialist” certification, your credential remains fully valid. 

Oracle PartnerNetwork members should note that this certification may no longer count toward their company’s specialization level. Check with your program administrator for more information.

There is a newer version of this certification available:

Retired Exam Newer Version of Retired Exam
Oracle Exadata 11g Essentials (1Z0-536) Oracle Exadata Database Machine 2014 Implementation Essentials (1Z0-485)
Retired Certification Path Newer Version of Retired Certification Path
Oracle Exadata 11g Certified Implementation Specialist Oracle Exadata Database Machine 2014 Certified Implementation Specialist

Oracle Midlands : Event #3 – Registration Open

Registration is now open for Oracle Midlands Event #3 on Tuesday 20th May.. As I mentioned in a previous post, Christian Antognini will be the speaker for both the sessions this time. He’ll be covering “12c Adaptive Query Optimization” and “Row Chaining and Row Migration Internals”. Red Gate Software have kindly offered to sponsor the event again, so registration […]


Oracle Midlands : Event #3 – Registration Open was first posted on March 31, 2014 at 6:04 pm.
©2012 “The ORACLE-BASE Blog“. Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

APEX World 2014

The fifth edition of OGh APEX World took place last Tuesday at Hotel Figi, Zeist in the Netherlands. Again it was a beautiful day full of great APEX sessions. Every year I think we’ve reached the maximum number of people interested in APEX and we’ll never attract more participants. But, after welcoming 300 attendees last year, 347 people showed up this year. Michel van Zoest and Denes Kubicek

SQL Server: transparent data encryption, key management, backup strategies

Transparent Data Encryption requires the creation of a database key encryption. The database key is part of the hierarchy of the SQL Server encryption tree with the DPAPI at the top of the tree. Then, if we go through the tree from top to bottom, we can find the service master key, the database master key, the server certificate or the asymmetric key, and finally the database encryption key (AKA the DEK). In this hierarchy each encryption key is protected by its parent. Encryption key management is one of the toughest tasks in cryptography. Improperly managing the encryption keys can compromises the entire security strategy.

Here is some basic advice on encryption keys:

  • Limit encryption key access to those who really only need it
  • Backup encryption keys and secure them. This is important to be able to restore them in case of corruption or disaster recovery scenarios
  • Rotate the encryption keys on a regular basis. Key rotation based on a regular schedule should be part of the IT policy. Leaving the same encryption key in place for lengthy periods of time gives hackers and other malicious persons the time to attack it. By rotating your keys regularly, your keys become a moving target, much harder to hit.

SQL Server uses the ANSI X.917 hierarchical model for key management which has certain advantages over a flat single-model for encryption keys, particularly in terms of key rotation. With SQL Server, rotate the encryption key that protects the database encryption key requires decrypting and reencrypting an insignificantly small amount of symmetric key data and not the entire database.

However, managing the rotation of the encryption key is very important. Imagine a scenario with a schedule rotation of every day (yes, we are paranoid!!!) and you have a strategy backup with a full back up every Sunday and a transaction log backup every night between Monday and Sunday.

 

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

FULL

LOG

LOG

LOG

LOG

LOG

LOG

TDE_Cert1

TDE_Cert2

TDE_Cert3

 

Here is an interesting question I had to answer: If I have a database page corruption on Tuesday morning that requires a restore of the concerned page from the full backup and the couple of backup logs from Monday to Tuesday, does it work with only the third encryption key? In short: do I need all the certificates TDE_Cert1, TDE_Cert2 and TDE_Cert3 in this case?

To answer this, let’s try with the AdventureWorks2012 database and the table Person.Person.

First, we can see the current server certificate used to protect the DEK of the AdventureWorks2012 database (we can correlate this with the certificate thumbprint):

SELECT
       name AS certificate_name,
       pvt_key_encryption_type_desc AS pvt_key_encryption,
       thumbprint
FROM master.sys.certificates
WHERE name LIKE ‘TDE_Cert%’;
GO

 

billet5_tde_certificate_1

 

SELECT
       DB_NAME(database_id) AS database_name,
       key_algorithm,
       key_length,
       encryptor_type,
       encryptor_thumbprint
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID(‘AdventureWorks2012’)

 

billet5_tde_dek_1

 

Now, we perform a full backup of the AdventureWorks2012 database followed by the database log backup:

BACKUP DATABASE [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.BAK’
WITH INIT, STATS = 10;
 
BACKUP LOG [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.TRN’
WITH INIT, STATS = 10;

 

billet5_tde_bckp_1

 

Then, according to our rotation strategy, we change the old server certificate TDE_Cert by the new one TDE_Cert_2 to protect the DEK

— Create a new server certificate
USE [master];
GO
 
CREATE CERTIFICATE TDE_Cert2
WITH SUBJECT = ‘TDE Certificat 2’;
 
— Encrypt the DEK by the new server certificate TDE_Cert_2
USE AdventureWorks2012;
GO
 
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_2;
GO
— Drop the old server certificate TDE_Cert
USE [master];
GO
 
DROP CERTIFICATE TDE_Cert;
GO
 
SELECT
       name AS certificate_name,
       pvt_key_encryption_type_desc AS pvt_key_encryption,
       thumbprint
FROM master.sys.certificates
WHERE name LIKE ‘TDE_Cert%’;
GO

 

billet5_tde_dek_2

 

SELECT
       DB_NAME(database_id) AS database_name,
       key_algorithm,
       key_length,
       encryptor_type,
       encryptor_thumbprint
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID(‘AdventureWorks2012’)

 

billet5_tde_certificate_2

 

We perform again a new backup log:

BACKUP LOG [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_2.TRN’
WITH INIT, STATS = 10;

 

billet5_tde_bckp_2

 

Finally, we repeat the same steps as above a last time (rotate the server certificate and perform a new log backup):

— Create a new server certificate
USE [master];
GO
 
CREATE CERTIFICATE TDE_Cert3
WITH SUBJECT = ‘TDE Certificat 3’;
 
— Encrypt the DEK by the new server certificate TDE_Cert_3
USE AdventureWorks2012;
GO
 
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_3;
GO
 
— Drop the old server certificate TDE_Cert
USE [master];
GO
 
DROP CERTIFICATE TDE_Cert_2;
GO
 
SELECT
       name AS certificate_name,
       pvt_key_encryption_type_desc AS pvt_key_encryption,
       thumbprint
FROM master.sys.certificates
WHERE name LIKE ‘TDE_Cert%’;
GO

 

billet5_tde_certificate_3

 

SELECT
       DB_NAME(database_id) AS database_name,
       key_algorithm,
       key_length,
       encryptor_type,
       encryptor_thumbprint
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID(‘AdventureWorks2012’)

 

billet5_tde_dek_3

 

BACKUP LOG [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_3.TRN’
WITH INIT, STATS = 10;

 

billet5_tde_bckp_3

 

So we have achieved our backup strategy with a full backup and a sequence of 3 transaction logs backups before initiating a database corruption next. In the same time, we have performed a rotation of 3 server certificates as encryption keys. Now it’s time to corrupt a data page that belongs to the table Person.Person in the AdventureWorks2012 database:

— First we check IAM page to get a page ID that belongs to the Person.Person table
DBCC IND(AdventureWorks2012, ‘Person.Person’, 1);
GO

 

billet5_tde_dbcc_ind_person_person

 

Then we randomly take a page from the result with the ID 2840. Then, to quickly corrupt the page, we use the undocumented DBCC WRITEPAGE as follows (/! Don’t use DBCC WRITEPAGE in a production environment /!)

 

ALTER DATABASE AdventureWorks2012 SET SINGLE_USER;
GO
 
DBCC WRITEPAGE(AdventureWorks2012, 1, 2840, 0, 2, 0x1111, 1);
GO
 
ALTER DATABASE AdventureWorks2012 SET MULTI_USER;
GO


We corrupt the page with the ID 2840 by introducing at the offset 0 two bytes with a global value of 0x1111. The last directORBufferpool option allows the page checksum failures to be simulated by bypassing the bufferpool and flushing the concerned page directly to the disk. We have to switch the AdventureWorks2012 database in the single user mode in order to use this option.

No let’s try to get data from the Person.Person table:

USE AdventureWorks2012;
GO
 
SELECT * FROM Person.Person;
GO

 

As expected a logical consistency I/O error with an incorrect checksum occurs during the reading of the Person.Person table with the following message:

 

billet5_tde_error_consistency

 

At this point, we have two options:

  • Try to run DBCC CHECKDB and the REPAIR option but we will likely lose data in this case
  • Restore the page ID 2840 from a consistent full back up and the necessary sequence of transaction log backups after taking a tail log backup

We are reasonable and decide to restore the page 2840 from the necessary backups, but first, we have to perform a tail log backup:

 

USE [master];
GO
 
— tail log backup
BACKUP LOG [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_TAILLOG.TRN’
WITH NORECOVERY, INIT, STATS = 10;

Now we begin our restore process by trying to restore the concerned page from the full backup, but we encounter the first problem:

 

— Restore the page ID 2840 from the full backup
RESTORE DATABAE AdventureWorks2012
PAGE = ‘1:2840’
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.BAK’
WITH NORECOVERY, STATS = 10;
GO


billet5_tde_restore_page_full_backup_error


According to the above error message, we can’t restore the page from this full backup media because it is protected by a server certificate. The displayed thumbprint corresponds to the TDE_Cert certificate which has been deleted during the rotation operation. At this point, we can understand why it is important to have a backup of the server certificate stored somewhere. This is where we remember the basis of encryption and key management.

Of course, we were on the safe side and performed a backup of each server certificate after their creation. Thus, we can restore the server certificate TDE_Cert:

 

USE [master];
GO

CREATE CERTIFICATE TDE_Cert

FROM FILE = ‘E:SQLSERVERENCRYPTEDBACKUPTDE_Cert.cer’
WITH PRIVATE KEY
(
       FILE = ‘E:SQLSERVERENCRYPTEDBACKUPTDE_Cert.pvk’,
       DECRYPTION BY PASSWORD = ‘P@$$w0rd’
);
GO


Then, if we try to restore the page from the full database backup, it now works:

billet5_tde_restore_page_full_backup_success

 

To continue with the restore process we now have to restore the transaction log backup sequence with beginning with the ADVENTUREWORKS2012_DB.TRN media:

 

RESTORE LOG [AdventureWorks2012]
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.TRN’
WITH NORECOVERY, STATS = 10;
GO


billet5_tde_restore_page_full_backup_success


Then we try to restore the second transaction log backup ADVENTUREWORKS2012_DB_2.TRN and we face to the same problem as during the earlier full backup. To open the backup media, we first have to restore the certificate with the thumbprint displayed below:

 

RESTORE LOG [AdventureWorks2012]
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_2.TRN’
WITH NORECOVERY, STATS = 10;
GO


billet5_tde_restore_page_tran_log_backup_1_success


Ok, we have to restore the TDE_Cert_2 certificate …

 

CREATE CERTIFICATE TDE_Cert_2
FROM FILE = ‘E:SQLSERVERENCRYPTEDBACKUPTDE_Cert_2.cer’
WITH PRIVATE KEY
(
       FILE = ‘E:SQLSERVERENCRYPTEDBACKUPTDE_Cert_2.pvk’,
       DECRYPTION BY PASSWORD = ‘P@$$w0rd’
);
GO


… and we retry to restore the second transaction log. As expected, it works:

 

billet5_tde_restore_page_tran_log_backup_2_success

At this point, we have only two transaction log backups to restore: ADVENTUREWORKS2012_DB_3.TRN and the tail log backup ADVENTUREWORKS2012_DB_TAILLO.TRN. Fortunately, these last two backup medias are encrypted by the TDE_Cert_3 which is the current server certificate that protects the DEK.

 

RESTORE LOG [AdventureWorks2012]
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_3.TRN’
WITH NORECOVERY, STATS = 10;
GO


billet5_tde_restore_page_tran_log_backup_3_success

 

RESTORE LOG [AdventureWorks2012]
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_TAILLOG.TRN’
WITH RECOVERY, STATS = 10;
GO


billet5_tde_restore_page_tran_log_backup_4_success

 

The restore process is now finished and we can read the data from the Person.Person table without any problem:

 

USE AdventureWorks2012;
GO
 
SELECT * FROM Person.Person


billet5_tde_select_person_person_table

 

To summarize, we have seen the importance of a good key management with a backup / restore strategy in this post. Of course, we chose a paranoid scenario to quickly highlight the problem, but you can easily transpose the same in a normal context with a normal rotation schedule of the encryptions keys – either it is a server certificate, an asymmetric key, or a third party tool.

So what about you, how do you manage your backup strategy with the rotation of the encryption keys?

Visualising OBIEE DMS metrics with Graphite

Assuming you have set up obi-metrics-agent and collectl as described in my previous post, you have a wealth of data at your disposal for graphing and exploring in Graphite, including: OS (CPU, disk, network, memory) OBIEE’s metrics Metrics about DMS itself Carbon (Graphite’s data collector agent) metrics In this post I’ll show you some of […]

300 : Rise of an Empire

I went to see 300 : Rise of an Empire yesterday. My feelings on this were a bit of a mixed bag. I was not the biggest fan of the original 300 movie at the cinema, but I have subsequently warmed to it. This film adds a bit more story about the lead up to […]


300 : Rise of an Empire was first posted on March 30, 2014 at 5:32 pm.
©2012 “The ORACLE-BASE Blog“. Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Running an Airport

If Manston Airport is up for sale (and the current owner’s motavation to make money may mean that she has other plans) I’d buy the place. The price has to be right though as a lot of extra money will need to be invested to give a chance of success. Sadly, I can’t stretch to […]

ASM spfile in a disk group

Starting with ASM version 11.2, the ASM spfile can be stored in an ASM disk group. Indeed, during a new ASM installation, the Oracle Universal Installer (OUI) will place the ASM spfile in the disk group that gets created during the installation. This i…

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569