When Auditing Attacks

I had a particularly annoying problem with Oracle auditing a few days ago. I thought I would write about it here in case anybody knows a solution, or if anyone at Oracle cares to add the functionality I need. 🙂

The problem

I was asked to audit selects against tables in a particular schema when issued by several users. For the sake of this post let’s assume the following.

  • SCHEMAOWNER : The owner of the tables that are to be audited.
  • USER1, USER2, USER3 : The three users whose select statements are to be audited.

So I decided the write an audit policy.

Option 1 : Audit all selects, regardless of schema

My first thought was to do this.

create audit policy my_select_policy
actions select
when q'~ sys_context('userenv', 'session_user') in ('USER1','USER2','USER3') ~'
evaluate per session

The problem is it produced masses of audit records, most of which were referencing selects against objects owned by other schemas. I have so many records I can’t actually purge the audit trail. I’m having to wait until the next partition is created in a month, so I can drop the current partition and shrink the data files. 🙁

Option 2 : Explicitly list all objects to be audited

So I need to make the policy more granular, which I can do by explicitly referencing all objects I want to audit, like this.

create audit policy my_select_policy
actions select on schemaowner.tab1,
select on schemaowner.tab2,
select on schemaowner.tab3
when q'~ sys_context('userenv', 'session_user') in ('USER1','USER2','USER3') ~'
evaluate per session

The problem is there are loads of tables in the schema, so doing this is a pain. I could generate the statement using a script, but even then if someone adds a new table the audit policy wouldn’t pick it up.

What I really want

What I really want is to be able to limit the action to a specific schema. The specific syntax is not important. The result is what matters. Maybe something like this.

-- Use and explicit schema reference,
create audit policy my_select_policy
actions select on schema schemaowner
when q'~ sys_context('userenv', 'session_user') in ('USER1','USER2','USER3') ~'
evaluate per session


I don’t believe the current syntax for audit policies allows them to be limited by schema, so I’m faced with generating masses of unnecessary audit records, or having to explicitly name every table. 🙁

This all sounded kind-of familiar, and when I did a bit of Googling I found this note by Pete Finnigan. So I’m not alone in finding this frustrating.



What is the end goal for automation and AI?

I’ve been a big proponent of automation over the years. I recognise that automation will cause the loss of some jobs, but I’ve always assumed people would move on to other things. A consistent theme of mine was, why waste humans on mundane work when they could do something of more value?

I recently read The Naked Sun by Isaac Asimov and now I feel rather uneasy about automation and AI…

I don’t want to give too much of the story away, but we are told fairly early that the planet Solaria only has 20,000 humans, and millions of robots. The robots do everything as far as the economy is concerned, and the humans are essentially idle custodians. Rather than having millions of people doing a variety of occupations, there are only the elite sitting at the top of the pile…

As I was reading this I was thinking about the economic elite on our planet and I started to feel some concern. My dream of a post-scarcity society is one where we are all equal, but I can’t imagine there are many people at the top that want to be equal. They would always want to be more than equal. Call me a pessimist, but when I think of Elon Musk calling himself the emperor of Mars, I wonder if a future post-scarcity society will be less like Star Trek, and more like Solaria…

Of course, the AI could just turn into Skynet and kill us all, but I have this feeling that greedy humans using AI and automation against the majority of the population poses the greater threat. Why have “them and us”, if there could just be “us”? 🙁

All of a sudden automation has lost some of its lustre…



HP-UX and Oracle 11g : Don’t let the door hit you on ass on the way out…

We had a pretty momentous occasion recently. We finally got rid of the last of our HP-UX servers, and with it the last of our Oracle 11g databases and Oracle Forms apps.

We use VMware virtual machines running Oracle Linux for nearly all our databases. We pushed really hard over the last couple of years to get everything migrated to 19c. Despite this, there were two projects that were left behind on HP-UX and Oracle 11g. They were effectively dead projects, from the time before we moved to Oracle Cloud Apps, about 5 years ago. We couldn’t get rid of them because people wanted to look at the historical data, but there was very little appetite to do anything with them…

Finally, over the last year we’ve had an archive project running, which involved moving the databases on to Oracle Linux and upgrading to 19c. That bit was easy. The other bit of the project involved building two new APEX apps to replace a 3rd party application and an old Oracle Forms 11g app. Those went live a few months ago, but there was still a lot of reluctance to actually decommission the old systems.

Recently we finally got the approval to turn off the old systems, and decommission the old HP-UX kit. When the change went through the Change Advisory Board (CAB) it was such a relief…

The kit is now decommissioned, and I’ve just been clearing the agents out of Cloud Control, so it’s finally over. No more HP-UX. No more Oracle 11g. No more Oracle Forms…

Now all we have to do is replace a whole load of Oracle Linux 7 servers, and get ready to upgrade to the next long term release of the database… 🙂



Search Trends and Oracle

Please read the update before jumping to any conclusions. 🙂

I was chatting with some folks the other day and the question was raised about if we had seen a decline in our website activity. Since the vast majority of my website traffic comes from Google searches, I figured the obvious thing to do was to look at what is happening on Google Trends in terms of search terms. That way we were not focusing on the popularity of a specific site, but on searches in general. That resulted in this rather alarming graph showing a trend over the last 20 years.

I guess we all thought this decline was at the expense of another engine, so our obvious next thought was to compare against the other relational database engines. One of the guys suggested we also compare the word “database” as well, just to see. This is the resulting graph.

It should be noted we tried variations on “sql server” and “postgresql”, but they didn’t affect the searches. Feel free to try for yourself. 🙂

So the decline in searches was not restricted to Oracle. You may notice this is related to “All Categories” of search, and the word “oracle” is not specific to the database, so I tried in “Computers & Electronics”, and we got a similar result.

For subsequent searches I switched back to “All Categories”.

If we switch to the last 5 years, things seem to have levelled off somewhat.

So the next question was if NoSQL databases were eating into the relational database market. A straight search looked interesting.

But what happens when we add Oracle back into the mix. Does the rise in popularity of some NoSQL databases account for the drop in searches for relational databases?

It doesn’t look like it. The gains in some NoSQL databases are insignificant compared to the reductions in searches for relational databases.

So we are left with a question. Why is there a drop off in searches for relational databases, when there doesn’t seem to be a corresponding uptick in alternatives?

Update : So what is really going on?

The graphs are not absolute search numbers, but normalised compared to the total Google searches that happened. Back in the day only geeks were Googling, so tech searches were a comparatively high percentage. Now everyone is Googling, the proportion of tech searches is much lower compared to the random stuff. Check out the FAQ about Google Trends data. So in our case, the trend over time of an individual term is not so important as the comparison between two search terms over time, as both should be affected in a similar way by the normalisation…

What we can see is over time the searches for different relational engines have got closer. If we are using the number of searches as a proxy for popularity, then it seems it’s a much closer game now than before.



PS. Thanks to the folks that pointed me in the right direction about Google Trends.

PPS. I noticed the switch from 12c to 19c searches over recent years.

The Oracle ACE Program : My 18 Year Anniversary

It’s April 1st, which means it’s my 18th year anniversary of being an Oracle ACE.

As usual I’ll mention some of the other anniversaries that will happen throughout this year.

  • 29 years working with Oracle technology in August. (August 1995)
  • 24 years doing my website in July. (Original name: 03 July 2000 or current name: 31 August 2001)
  • 19 years blogging in June. (15 June 2005)
  • 18 years on the Oracle ACE Program. (01 April 2006)
  • 9 years doing videos on my YouTube channel, with some breaks of course.

Fingers crossed for next year…