Call: +44 (0)1904 557620 Call

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

New GDPR Book and the Oracle Database

I received a copy of Jamal Ahmeds book The Easy Peasy Guide to the GDPR last night. Of course I have not had chance to read it fully yet BUT I did have a read of the introduction and recommendations and I also browsed many of the Articles. This is a good book for getting up to speed with GDPR as it states the actual text from GDPR for each Article and the re-writes that in much simpler English. I will post a review when I have read it all.

I have done work on GDPR in terms of helping customers to do what is needed in the Oracle database to help their companies comply with GDPR. Of course complying with GDPR cannot be done solely by doing data security things in just the database itself. GDPR requires you to protect PII data throughout the business. This is interesting as this is what I have been saying for over 20 years. Oracle security is not security of the Oracle software itself; it is security of the data that is held in the database; we need to use Oracle security features to achieve this BUT we must start with data.

GDPR has been around quite a while now and during talks with people at conferences and at customers I am still surprised that so few (At least from who I have spoken to) have done a serious amount of work for GDPR in the database. I see companies that employ lawyers and legal means to comply with GDPR BUT GDPR is not just a law there are practical consequences and data must be protected and audited as well. If data is stolen a legal agreement doesn't stop it being stolen.

The GDPR speaks to us through the Articles as to what can affect the security of data in an Oracle database. Here are some of the key examples that must be considered in Oracle:

  • Article 4: Defines data, identifiable persons, pseudo anonymisation and more

  • Article 35: perform a data impact assessment and include current security measures

  • Article 25: Data protection by design and by default

  • Article 32: Security of processing including pseudo anonymisation and encryption

  • Article 30: maintain records of processing activities

  • Articles 33 and 34: Data breach notification

  • Articles 16 - 21: Data subjects rights - the right to rectification, the right to be forgotten...

  • Articles 5,6,15: Retention, training, policies

This is a summary; the key things for me that should be done in any database never mind if the data is PII or not is data security by design and by default. This Speaks to me; this is something I would do to protect data irrespective of GDPR or not. Another clear message that should play into any data protection work is audit trails for the database itself. We must audit what goes on otherwise we don't know we are breached and without an audit trail its difficult to know how and why it happened.

GDPR is good; it make sense for securing any data not just PII

#oracleace #gdpr #23c #dbsec #oracle #security #databreach

Oracle 23c And Removing Traditional Audit - Part 3

This has become a multi-part post about Traditional Audit in the 23c database. The first part - Oracle 23c Traditional Audit De-supported - discussed the fact that traditional audit is de-supported in 23c BUT if you migrate and the older database that you migrate from has traditional audit settings enabled then these will ne honoured in 23c and will fire BUT you cannot add new traditional audit settings in 23c.

I was asked a question in LinkedIn about whether we can enable traditional audit again in 23c with any parameter. I added a new blog - Re-Enable Traditional Audit in 23c - that showed how to do this. When I posted that I was sent a DM question / point on Twitter about the fact that Oracle do not support new audit settings anymore and it could be the case that you rely on traditional audit for a non-updated application that uses it and you use the parameter to enable it BUT maybe it doesn't fire in the database because Oracle are not maintaining traditional audit anymore. How would you know? in simple terms you wouldn't BUT if we have a migrated application that retains its traditional audit settings after the upgrade to 23c which Oracle state in the documentation that these settings will fire and will be honoured BUT update to Unified Audit as soon as you can. How can we rely on these settings firing and how does Oracle know what settings were enabled before the upgrade and honour them and how would it not honour ones you added by using the parameter to set?

I don't think there is a way to tell either case that Oracle would be interested in checking before firing. I think we have to assume that because traditional audit is de-supported Oracle will not fix issues; the traditional audit worked before 23c so it will probably work afterwards BUT you cannot rely on it. You must think about moving your traditional audit / standard audit to unified audit as soon as possible to be supported.

OK, so we have an old database that is migrated to 23c that had standard audit enabled and its firing now in 23c. We have decided that we must migrate to Unified Audit as soon as possible. This means at some point soon we should disable the traditional audit settings in the database. This brings up two points:

  • We need to disable the standard audit from firing

  • We need to migrate / or control the two audit trails at change over and remove the audit trail in SYS.AUD$ in 23c and then rely on Unified Audit

Let's have a look at 23c Free and see what we were given by Oracle by default. First check if we are in "pure" mode or mixed mode:

SQL> select * from v$option where parameter='Unified Auditing';

---------------------------------------------------------------- ----------
Unified Auditing


So, the database is in PURE mode and Unified audit is enabled. From 12c until 23c you could run in MIXED MODE where standard audit would fire but also Unified Audit would fire or PURE mode where Unified Audit would work BUT the traditional audit settings would be ignored. So, in 23c if we have migrated Standard Audit settings the documentation states that they will fire BUT you cannot change them. This means 23c is different to earlier databases as PURE mode is now a sort of MIXED mode in 23c.

What are the standard audit settings:

SQL> show parameter audit

------------------------------------ ----------- ------------------------------
_allow_traditional_audit_configurati boolean FALSE
audit_file_dest string /opt/oracle/admin/FREE/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_systemlog string

So standard audit is enabled to write to the DB, no SYSDBA audit to the file system. Are there any standard audit settings enabled in 23c?

SQL> select count(*) from dba_priv_audit_opts;


SQL> select count(*) from dba_stmt_audit_opts;


SQL> select count(*) from dba_obj_audit_opts;



Yes, there are existing traditional audit settings in the PDB of 23c Free. What are they?

SQL> select *
2 from dba_priv_audit_opts
3 order by user_name;

-------------------- -------------------- ------------------------------ --------- ---------
SQL> select *
2 from dba_stmt_audit_opts
3 order by user_name;

-------------------- -------------------- ------------------------------ --------- ---------
SQL> select *
2 from dba_obj_audit_opts
3 order by owner;

-------------------- ------------------------------ -------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
DVSYS MAC_POLICY$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS AUDIT_TRAIL$ TABLE A/A A/A A/A A/A A/A A/A -/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS CODE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS MAC_POLICY_FACTOR$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS FACTOR$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS FACTOR_TYPE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS COMMAND_RULE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS FACTOR_LINK$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS ROLE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS IDENTITY$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS IDENTITY_MAP$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS RULE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS POLICY_LABEL$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS RULE_SET_RULE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS RULE_SET$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS REALM_OBJECT$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS REALM_AUTH$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS REALM$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
LBACSYS OLS$PROPS TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-

So we can play with CREATE SESSION. Also you will see above that the hidden parameter _allow_traditional_audit_configuration is set to FALSE so we cannot enable anymore standard audit without changing it.

Why would Oracle leave these settings in the PDB of 23c Free? - whats the significance; if this were 23c production should we be concerned? as these would be Oracles old settings not ours or part of an old application that was migrated into 23c. In production we would need to understand these and remove them or replace them with Unified Audit equivelent policies.

An interesting aside not directly related to this discussion is that the online docs for error messages shows error ORA-46502 which states:
ORA-46402: _ALLOW_TRADITIONAL_AUDIT_CONFIGURATION initialization parameter cannot be set for this database.
Cause: An attempt is made to set _ALLOW_TRADITIONAL_AUDIT_CONFIGURATION initialization parameter.

Action: Traditional Audit has been desupported. Traditional Audit configuration cannot be enabled for this database.

Params: n/a

Clearly in Part 2 we set this underscore parameter to allow traditional audit settings (rules) to be enabled again. This message reads like this parameter will be removed or disabled soon.

So, we have CREATE SESSION audit in standard audit enabled. Lets see how many records there are in AUD$ and truncate it:

SQL> select count(*) from sys.aud$;


SQL> truncate table sys.aud$;

Table truncated.


Now, let's connect to the database and see if this traditional audit works still:

SQL> connect orablog/orablog@//
SQL> connect sys/oracle@// as sysdba
SQL> select count(*) from sys.aud$;



Get some details of these:

SQL> col timestamp for a30
SQL> col username for a10
SQL> col action_name for a10
SQL> select timestamp,username,action_name from dba_audit_trail;

------------------------------ ---------- ----------


So, this traditional audit settings that we inherited in 23c does work. We logged in as ORABLOG and this is captured in the AUD$ traditional audit trail. So assuming we are simulating the case where standard audit is migrated into 23c then at some point soon we design and implement Unified Audit policies and we need to remove the standard audit. So we can try:

SQL> sho user
SQL> noaudit create session;

Noaudit succeeded.

SQL> @hidden
_allow_traditional_audit_configuration FALSE FALSE


The parameter to add new settings to Traditional Audit is not set and we seem to be able to disable the standard audit still in 23c. Lets check and make sure:

SQL> select * from dba_priv_audit_opts;

no rows selected

SQL> select * from dba_stmt_audit_opts;

no rows selected


So, yes it has been removed.

This is good as it means we can migrate to 23c, keep traditional audit firing until we are ready and we are not forced to just disable standard audit by setting the audit_trail parameter and bouncing the database and turning off everything in one go; we can remove old audit step by step without resorting to hidden parameters

Well done Oracle on the noaudit working still

#oracleace #23c #oracle #database #security #traditional #standard #audit #audittrail

Coding, Languages and Oracle

Borland C and C++ Application Framework

I run a company that specialises in securing data for customers in their Oracle databases but I still love to code in many languages. This can either be for customers projects or to create tools to use myself in helping customers secure their databases and data or to give away free tools on this site or in assignments or trainings. I still find myself coding almost every day and love to code still.

All of my coding in different languages seems to get more varied over the years but the core focus now is always Oracle and interfacing with Oracle. I program in the following languages now or have done recently or I the distant past:

  • Basic: I started with Basic in the 1980s on various small computers; I didn't particularly like BASIC but it was simple and easy to get on with

  • C: C was my first proper language and I wrote PC based software and also on Unix boxes I used Pro*C and also wrote User Exits in C for Forms 2 or 3. I also wrote code to connect to Oracle using OCI. I have written an enormous amount of C over the years. I have written parsers, data processing, compilers and many many things

  • ASM: I have written in a few assembler languages over the years, starting with some simple things on 6502 and of course Intel x86. I did quite a bit in the early 90s with Intel assembler linking to C where I needed to do things like switch tasks, interrupts, direct graphics access...

  • PL/SQL: My favourite language after C. I have written huge amounts of PL/SQL and indeed normally write PL/SQL everyday. Its a great language to play with in Oracle

  • SQL: As with PL/SQL I tend to use SQL on a daily basis to interact with Oracle and do useful things. Of course I also use SQL*Plus formatting a lot

  • Lua: I use Lua quite a lot as a data processing language and as an integrated script language. This is a fantastic language to use to play with data from Oracle; in may case Security data

  • VB.NET: I write in regularly and its a fantastic language to create GUI apps and also console programs. Its fast to learn and really easy to get sophisticated programs written. We use third party libraries to access Oracle and also I have used Oracles VB/Oracle interface in the past

  • JavaScript / HTML / CSS: I write a lot of web content and use JavaScript, HTML of course and CSS for styling. I also use these in an Oracle context to create great HTML based reports and of course in 23c Oracle has integrated JavaScript into the database via MLE

  • Others: I have also used Perl, java, shell, MFC, Emacs Lisp, DOS/Win batch scripts and other languages over the years

In this post I wanted to briefly highlight some of the languages I use now and have used in the past and also highlight how many can be used with Oracle databases either directly or indirectly to process data. We are lucky with Oracle as we can use lots of different tools, toolkits, languages to connect to and process data in Oracle.

What has all of this got to do with Oracle security? well for me its all valid and relevant as I am working in the field of securing data in Oracle databases and I use these languages in my day job all of the time.

I am also interested in the security of programs that are used to connect to and process data in an Oracle databases such as PL/SQL programs. We need to secure these in the database to prevent loss of IPR or someone running them when they are not supposed to or to make sure that deployed PL/SQL does not become the security problem that would allow someone to exploit the database. We do these things by obfuscating and protecting the PL/SQL and also by code review and secure coding techniques to help prevent the PL/SQL from being a vulnerability.

Of course I have also been interested in parsing PL/SQL and have written PL/SQL lexers and parsers in C and also in PL/SQL. I have also written unwrappers in C and PL/SQL for unwrapping PL/SQL

There is more to code than just solving a business problem; we must protect our IPR , secure our code against theft and also secure our database and code so that it cannot be exploited so that the code doesn't become the attack vector.

The picture at the top of this post is the first proper C compiler I bought for Dos and Windows back in around 1992 just after it came out. It included a TUI framework for DOS and OWL for Windows but I had already written my own at that point

#oracleace #23c #oracle #security #securecode #securecoding #plsql #lua #c #databreach #obfuscate

Re-Enable Traditional Audit in 23c

I wrote a post at the end of last week that told that Oracle deprecated traditional audit in 21c and de-supported it in 23c. Oracle honour the traditional audit settings in 23c if you migrated from an older database BUT they don't allow you to add new traditional audit rules in 23c. Also Oracle have deprecated the parameters such as audit_trail in 23c and they will be removed at some point in the future.

If we try and enable a traditional audit rule in 23c Free then we get:

SQL> audit create session;
audit create session
ERROR at line 1:
ORA-46401: No new traditional AUDIT configuration is allowed. Traditional auditing is de-supported, and you should use unified auditing in its place.


I was aware of new hidden parameters in 23c as I looked at them as soon as 23c came out months ago. I was aware of a parameter that allows traditional audit to be still used in 23c but refrained from mentioning it in my last blog - Oracle 23c Traditional Audit De-supported - because we cannot recommend to use de-supported features or use backdoors to allow access. BUT, someone asked a question on Linkedin in relation to that blog post as to whether it is possible to reenable traditional audit in 23c. They made a good point, that someone may need to do it. I thought a little more and it could be that you have an application that has traditional audit and the vendor doesn't yet support Unified audit BUT as part of an upgrade it sets, re-sets traditional audit. If the 23c is in its current state then that would fail and you would lose your audit. So, yes I can see a need to do this BUT you should not use underscore parameters as the norm and if its production as Oracle support first. Remember, you could enable traditional audit to add missing settings and then disable again.

So, yes it is possible to re-enable traditional audit BUT probably not supported unless you ask first. So here is the parameter for completeness:

SQL> l
1 SELECT ppi.ksppinm "Parameter",
2 ppcv.ksppstvl "Session Value",
3 psv.ksppstvl "Instance Value"
4 FROM x$ksppi ppi,
5 x$ksppcv ppcv,
6 x$ksppsv psv
7 WHERE ppi.indx = ppcv.indx
8 AND ppi.indx = psv.indx
9 AND ppi.ksppinm = '_allow_traditional_audit_configuration'
10* order by ppi.ksppinm
SQL> /
_allow_traditional_audit_configuration FALSE FALSE


If we set this parameter:

SQL> alter system set "_allow_traditional_audit_configuration"=true scope=both;

System altered.


Check it:

SQL> @hidden
_allow_traditional_audit_configuration TRUE TRUE


Now test adding a new traditional audit rule:

SQL> audit create session;

Audit succeeded.

SQL> select banner from v$version;
Oracle Database 23c Free, Release - Developer-Release


So, lets put it back to its correct setting:

SQL> alter system set "_allow_traditional_audit_configuration"=false scope=both;

System altered.

SQL> @hidden
_allow_traditional_audit_configuration FALSE FALSE


So, it is possible to still fully use traditional auditing in 23c

#oracleace #23c #oracle #database #security #audit #audittrail #databreach

Oracle 23c Traditional Audit De-supported

It has been a long time coming. Traditional audit has been around since Oracle 6 and the new unified audit was added 10 years ago in Oracle 12c; at first unified audit was in a secure file and was slow. Lots of conversations with customers and at conferenced lead to the conclusion at least from what I heard that most people were sticking with traditional audit for now. Since 19c the unified audit is back in a normal table and is fast and more reliable. I am starting to hear more people using Unified audit in their databases. Bear in mind that a show of hands at conferences for years as to "who has audit enabled in the database to capture abuse of the database - i.e. not just because the application vendor enabled it" and then a second question, keep your hand up "if you do have audit enabled then do you actually do anything with the audit trail or just keep it in case?"

The first question usually gets a response of between 10% and 30% of the audience and the second (from those who raised a hand to the first question) is again around 10%. This is bad but consistent at least when I ask these questions. This means that between 1% and 3% of people create an audit trail of the database engine (not the applications - usually that is a much bigger percentage) to detect if anyone is hacking it in real time or semi-real time; and 10% to 30% who just collect and hope that they have enough audit to assist an investigation.

So, back to the story; Oracle added unified audit back in 2013 to 12c and they deprecated standard audit in 21c and announced that its de-supported in 23c. Its not gone completely. The parameters AUDIT_TRAIL, AUDIT_SYS_OPERATIONS, AUDIT_FILE_DEST and AUDIT_SYSLOG_LEVEL are deprecated in 23c and Oracle recommends that you move to Unified Audit as soon as possible.

If you used traditional audit in an earlier release and migrated to 23c then the traditional audit settings are honoured and the settings will write audit to their audit trails BUT you cannot add new traditional audit settings.

There is a need to start to think about Unified audit and migrating traditional audit to Unified Audit and if you are not using either then start to think about using Unified Audit to capture abuse of the database by anyone. You will find it useful to have a well designed audit trail to do two things; one; react to the audit and take action immediately if an attack looks like it could be happening and two; have enough evidence should an attack get through

If you try and set new traditional audit in 23c you get this:

SQL> audit create session;
audit create session
ERROR at line 1:
ORA-46401: No new traditional AUDIT configuration is allowed. Traditional
auditing is de-supported, and you should use unified auditing in its place.


#oracleace #23c #audit #oracle #unified #database #databreach