Oracle 12c Launch Event (2)

Continuing from the previous post, here are some more of the new features of Oracle 12c that I am excited about. The first is transparent redaction. Like the privilege analysis feature, this is something that you could implement using 11g features, such as views with inline functions or virtual columns and very careful allocation of grants, or even VPD. All of these methods carry a significant manageability overhead. Another alternative would be to do it in the application – e.g. in the middleware, performing a regexp-based substitution before sending the result to the GUI. That approach is fraught with problems – what if there is a bug and the redaction is not applied to a particular column, as it has been added recently or the app developer was unaware that it needed redacting, and of course, the app’s credentials will give it unrestricted access. The redaction facility allows us to apply a policy at the database without changing any existing applications as the datatypes will all match – a credit card number will still be a 16-digit number, stored in one, normal column in the database, and one app, say the one that talks to the payment gateway will see it in its true form, whereas another app, say the one that prints invoices, will see the redacted version. This should be of great interest to developers too according to the DRY principle.

A related feature is data masking. This is known by many names, such as anonymizing, or at one company I worked for, it was called “snipsnip”. There are many reasons that you don’t want real customer data in your dev and test environments, including that it violates the Data Protection Act. Another is the risk of using real email addresses and accidentally spamming customers or even worse, real credit card details and double-billing. And yet, there is a dilemma, since to be useful, tests must be done with representative data. Like several other 12c features, masking is a built-in mechanism that for something everyone had had to implement with a one-off solution for every database they managed. At the snipsnip company this was a major undertaking, many hours to run the script over a copy of production restored from backup, during which time it had to be securely fenced off from the rest of the development environment – any by their very nature, dev enviroments are not designed to be a securable as production, so there was a risk there. Data Masking however contains enhancement to make this process as fast as possible, for example rather than updating scattered rows in-place, it creates new contiguous blocks to store the masked rows, and to reduce maintenance overhead on hand-written scripts, it resolves foreign-key dependencies automatically. It is also possible to apply the masking policies on-the-fly during DataPump exports, so the sensitive data never even touches the development environment. However I personally prefer to refresh development environments via a production backup (which can then be masked, then cloned for rapid provisioning of development DBs). The reason is simple: it validates the backup and restore process, daily, for free. Ideally you would actually read back from the tape before it goes offsite rather than just copying the RMAN files from a disk staging area.

Continuing the security theme, the next feature to look at is the Audit Vault/Database Firewall, which are now integrated into a single product. This makes sense as really they are two sides of the same coin, validating access and detecting and logging unauthorized access. Previously I have used Imperva devices to fulfill this role – it was a while ago but I think the reason could have been the licensing cost of the Oracle solution. It will be worth revisiting in 12c to see if the value proposition stacks up now – as I often say there is no cheap or expensive in business, there in only worth the money, or not. The DB Firewall operates on a similar principle to conventional protocol aware deep packing inspecting firewalls, rather that just permitting or blocking based simply on IP address, it parses and analyzes SQL statements and accepts or rejects them accordingly. This is on top of privileges granted on tables to users inside the DB, and on top of redaction, providing defence in depth (this is what I was referring to in part 1 when I said there are better methods for securing data). Oracle does not log “insufficient privilege” errors normally, and its internal audit functions write to a table inside the database, SYS.AUD$, the firewall/vault solution neatly addresses both of these by providing an external repository that can log anything a firewall rule detects. At one site I worked at, a combination of over-zealous auditing and sloppy coding brought the datawarehouse to its knees – rather than a single select to bring back all the rows, the developer had somehow coded one select per row, and on a large table, had swamped the SYSAUX tablespace with millions of audit entries every time the ran their code! The firewall/vault solution allows us to separate these duties from the DB and where necessary for regulatory compliance, even from the DBAs.

Another kind of separation that has historically been missing from Oracle is that of the roles and responsibilities of DBAs. This is the same problem as in most (but not all) Unixes – an all-powerful root user. There was the SYSOPER role which despite the official word, I have never actually seen used anywhere. In fact connecting / as sysdba (or rman target /) seems to be the norm among working DBAs, despite the obvious potential for “fat fingers” causing catastrophe. The concept of “least privileges” in my mind as far as a trusted user like a DBA is concerned is less about security and more about protecting an administrator from himself! Anyway there are some new roles now, SYSBACKUP, SYSDG (for administering DataGuard) and SYSKM (for key management). I hope to make use of these when I have a 12c in Production 🙂

In part 3: New features in RAC, RMAN and DataGuard, and in part 4, some performance/monitoring enhancements.

About Gaius

Jus' a good ol' boy, never meanin' no harm
This entry was posted in Oracle and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s