Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 77 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog


New Conference Speaking Dates Added

July 6th, 2015 by Pete

In the last few years I have not done as many conference speaking dates as I used to. This is simply because when offered they usually clashed with pre-booked work. I spoke for the UKOUG in Dublin last year and I also spoke at the main UKOUG connference last year. I also spoke at DOAG for the first time last year. I did not submit a paper for DOAG but was there for the whole event on my German reseller for PFCLScan stand (LoopBack.org) where we were reselling PFCLScan or database security scanner for Oracle. Loopback.org did a great job looking after me in Nuremberg and we also did a lot of demonstrations of PFCLScan to potential customers. If you want more details of PFCLScan in Germany and Austria or indeed to speak about it in German then please contact the guys at Lookback.org directly.

Whilst at the DOAG conference I decided to drop an email to the organisers to see if they had any free speaking slots where someone had dropped out and great news; they had. I got a slot on the last day at the first slot of the day (the night after the conference party!) and was really encouraged to see that the room was full and some standing. It was a good talk and some good feedback and questions and i enjoyed it greatly.

More recently I have submitted some papers for this years UKOUG conference so lets see if anything is accepted. As usual these are new papers from me. I also was asked to submit a paper for the ISACA conference in Dublin later this year so again lets see if i get accepted.

I also have been asked to speak at the UKOUG Database Server SIG on September 15th in London and i will present about "Designing Practical Audit Trails in Oracle". Next week on July 15th I have been asked to speak at the YoDB#2 meet up in Leeds and I will speak about "Locking Down Oracle". This should be fun as it is a local event for me and also the second meet of this group. Also I am planning to do what I did in Dublin last year and speak with no slides and just hands on demos. I have done this a few times over the last couple of years and its fun but harder to do with no slides to prompt you. I am going to do some hacking and then some demos of locking down my database and show that the hacking is blocked. There will of course be lots of talking about what I am doing, why the hacks are possible, what the problems are and what solutions may work - should be fun. If you are in Leeds then come along!!

I have updated my sites home page with links and dates to speaking events.

If I get a slot at the UKOUG (Birmingham in December) and ISACA (Dublin in October) I will of course update you here.

[No Comments]


Happy 10th Belated Birthday to My Oracle Security Blog

July 3rd, 2015 by Pete

Make a Sad Face..:-( I seemed to have missed my blogs tenth which happened on the 20th September 2014. My last post last year and until very recently was on July 23rd 2014; so actually its been a big gap in blog posts until recently - I have not checked but I suspect it was the biggest gap I have had so far.

My first ever blog post was posted on the 20th September 2004 and was titled - A new Oracle Security based weblog, which introduced the blog. Actually I had been blogging of sorts for about 6 months before that with the individual single articles on their own pages in the Ramblings section of my site but the official first blog was the one quoted above.

So I missed the tenth aniversary by about nine months, so the blog is now about 10 years and 9 months old, so if we factor in the first Ramblings posts mentioned above then I have missed the 11th aniversary as well..:-(

So how well have I posted over the years?, lets have a look in two ways; First number of posts per year and also number of posts per blog year. So how many posts in 2004, 2005, etc and the how many posts from start to first anniversary, to second aniversary.... I will put them together in a table

Post per calendar year:

Year Number Posts
----- ---------------
2004 189
2005 495
2006 296
2007 161
2008 87
2009 78
2010 39
2011 13
2012 6
2013 16
2014 4
2015 3

And posts per blog year:

Blog Year Number Posts
---------- --------------
To Sep 2005 558
To Sep 2006 354
To Sep 2007 184
To Sep 2008 107
To Sep 2009 59
To Sep 2010 72
To Sep 2011 17
To Sep 2012 8
To Sep 2013 14
To Sep 2014 6
To Sep 2015 3

Obviously 2015 is work in progress in both tables. There has been a total of 1383 posts (actually 1386 as three posts have been removed for various reasons). The curves in both tables are different as it peaks in 2005 on pure year and tails off slowley after that. In blog years it peaks in the first year and tails off after that, again slowley. In the first year of blogging I was managing on average almost 1.5 posts per day and in the second year still almost one post per day. As the years roll by the number of blog posts has dropped to almost nothing and in fact the biggest gap in actual posts BUT the number of visits to my companies site continues to grow - more on site stats in a minute.

I recorded the birthdays of my blog from its first birthday until the 5th and then never again until now!. These are here as links:

The First blog birthday
The second blog birthday
The third blog birthday
The fourth blog birthday in
2008

The fifth blog birthday in 2009

And then its this blog post. I do not have a proper historic record of the sites visits since the start but have some snippits in the birthday blogs so here is a summary.

In the first year the number of visits (not hits) went from around 10,000 a month to around 64,000 a month and approximately 0.5 million visits a year. In the second year that grew to around 1.2 million visits a year, in the third year about 1.5 Million visits a year and in the fourth 1.6 Million visits a year and around 4.2 million page views per year. We are now running at around 2.2 million visitors a year, 6.1 million page views a year and an average of around 6,200 visitors per day sometimes peaking around 10,000 visitors in one day.

So it is indeed interesting to see site visits rise while blog posting drops. This is probably due to collateral on the site and partly traction in google although a lot of visits do not eminate from Google so we do have some immunity from Googles fickle rankings.

The stats also show me the most popular pages now and these are in order:

1) How to Grant all Privileges in Oracle
2) Hacking Oracle 12c Common Users
3) which special characters can be used in Oracle database passwords
4) Allowing a user read-only access to stored procedure source code

This is interesting as we have a mix of modern - 12c and older core issues and even a post from the original blog (pre-blog!).

Whats happened in the years between birthdays, 5th and now? well we now have 4 training classes, ranging from auditing Oracle databases, secure coding in PL/SQL, designing audit trails for Oracle and locking down Oracle. All of these classes will be taught from September 21st to 25th here in York at the Holiday Inn, Tadcaster road, York; being taught back to back.

Also in these intervening years we have been busy creating two software products; PFCLScan a useful tool to help you perform a security audit of an Oracle database and PFCLObfuscate, a tool that can be used to protect and lock PL/SQL that you deploy to a database.

Contact us if you would like a demo of either product or to buy a license!

[2 Comments]


Oracle Database Vault 12c Paper by Pete Finnigan

June 30th, 2015 by Pete

I wrote a paper about Oracle Database Vault in 12c for SANS last year and this was published in January 2015 by SANS on their website. I also prepared and did a webinar about this paper with SANS. The Paper on Database Vault in 12c was sponsored by Oracle.

The paper was an expert walkthrough of implementing and using Database Vault in 12c. It also covered the main components of Database Vault and showed how easy it is to enabled in 12c. The paper covered the new features added in 12c; the fact it is installed by default and just needs enabling, my favourite feature which is mandatory realms and also the new privilege analysis feature.

I installed an application locally on some VMs, added Database Vault and also built an Oracle Cloud control server and OEM repository. The application is meant to look realistic with its design and implementation issues and I showed how to enable Database Vault in this database and also showed some examples of how it can be used to protect the database out of the box and also how it can be configured to add additional protections.

I also added a walk through of using privilege analysis. The paper is over 24 pages long and is a nice practical walk through of this technology.

If you would like to read the paper it is here - Protecting Access to Data and Privilege With Oracle Database Vault

If you would like to learn much more about securing Oracle databases and protecting them then consider booking a place and attending PeteFinnigan.com Limiteds Oracle Security training Event in York in September 2015.

[No Comments]


Unique Oracle Security Trainings In York, England, September 2015

June 25th, 2015 by Pete

I have just updated all of our Oracle Security training offerings on our company website. I have revamped all class pages and added two page pdf flyers for each of our four training classes. In have also updated the list of public up-coming classes.

Most importantly I have added a new unique event to be held in York, England from September 21st to 25th 2015. I am going to teach all four of our current Oracle security classes back to back during that week as a public training. Our classes are regularly updated and some are new so this is a great oppertunity. The classes are:

o - How to perform a security audit of an Oracle database - 2 Days
o - Secure coding in PL/SQL - 1 Day
o - How to design practical audit trails for an Oracle database - 1 Day
o - Harden and secure Oracle - 1 Day.

Each class is described in detail on the website; This is a unique event as I have never offered all four classes ( 5 days ) in one event before and also I do not teach at public events in the UK very often. This is a good oppertunity to get the best Oracle security training in the UK and at a good price per student.

You can book all five days or just some of them, it is entirely up to you!

If you are interested to join this training and book your place then please have a look at the web page I have created - Oracle Security Training in York 2015. I look forward to meeting you there.

[No Comments]


Coding in PL/SQL in C style, UKOUG, OUG Ireland and more

July 23rd, 2014 by Pete

My favourite language is hard to pin point; is it C or is it PL/SQL? My first language was C and I love the elegance and expression of C. Our product PFCLScan has its main functionallity written in C. The core engines are C and the GUI is .NET but I love C for the fact its fast and you can control every aspect of it.

Our other main product PFCLObfuscate is also written in C with Lex and Yacc for the parser. The utilities for PFCLObfuscate are also written in C. But PFCLObfuscate is a program to process (parse) PL/SQL programs and protect them by converting them into a different form and also in version 2.0 by adding license type features automatically.

I also have a great interest in PL/SQL for many years; indeed my first PL/SQL program was actually written in C. I wrote a Pro*C program that generated PL/SQL code for testing an application. That PL/SQL code was driven by lookup tables that configured obfuscation and also numbers of records annd sources to sample from. Its purpose was to generate large amounts of data for the main applications testing and as the PL/SQL was working on applications tables it could be generated. Therefore I generated PL/SQL from Pro*C and C and that PL/SQL generated SQL insert statements based on config tables for all of the tables of the database that needed to be populated. This of course included referential connections. So it was fun to write my first PL/SQL program in C all those years ago and now we use C again to write PL/SQL (obfuscated of course) in PFCLObfuscate; so nothing has changed!

This interest in PL/SQL is why i was inspired to create PFCLObfuscate to protect IPR in PL/SQL for people. I have also written many many utilities in PL/SQL over the years . The biggest was oscan.sql which is a huge SQL*Plus script that was used to audit databases until we created PFCLScan. Oscan is around 45,000 lines of mostly PL/SQL. I have also written tools to analyse privileges such as find_all_privs.sql and scripts to assess who has particular roles, system privileges or access to objects on the database. I also wrote a password cracker completely in PL/SQL and even a PL/SQL unwrapper completely in PL/SQL; so I also love to write PL/SQL code and indeed I will still often prototype things in PL/SQL before adding them to products such as PFCLScan.

Indeed, we have a new training course about designing practical audit trails in Oracle databases to focus on the possible core audit facilities in the database including core audit, unified audit, system triggers, DML triggers and more. The focus is to concentrate on the database layer, actions, privilege actoons and possible attacks and as part of this class we have created a simple audit framework that includes a simple firewall, privilege monitoring and more. This is completely SQL*Plus based and mostly PL/SQL. When i was writing that some time ago I made a note to discuss a couple of things on PL/SQL. One thing I miss when writing PL/SQL is the simpleness of C. In C its great to write idioms such as:

i++;
i+=2;

or other elegant constructs; so i wondered if instead of:

i:=i+1;
i:=i+2;

in PL/SQL can we do the same as C. Oracle in stdspec.sql create procedures and functions such as "=" and ">=" which resolve to a special syntax called BUILTIN that we cannot use; most liklely because the C thats called is hard coded to work as SYS only and be linked to the right PL/SQL in the ICD vector table. But we can create a procedure called:

create or replace procedure "++"(i in out number) is
begin
i:=i+1;
end;
/

And call it like this:

declare
i number:=0;
begin
"++"(i);
dbms_output.put_line(i);
"++"(i);
dbms_output.put_line(i);

end;
/

Which results in the same desired affect:

SQL> @z
Connected.

Procedure dropped.


Function dropped.


Procedure created.

1
2

PL/SQL procedure successfully completed.

It is not as elegant as the C syntax but it is quite close and for a C programmer its intuitive to write "++"(i), actually its probably more intuitive to write i++ not ++1 but we won't split hairs now. It would be nice of course to write ++i in PL/SQL but we cannot so easily. In PL/SQL I also miss printf() in PL/SQL. The C function has the great syntax

int printf(condt char *fmt, ...);

The elipse (the "...") is the syntax in C that means there is a variable number of arguments to the function that are read and resolved at run time based on the format string. There is a limited printf in PL/SQL - well in the database, the UTL_LMS.FORMAT_MESSAGE() which allows %s and %d from printf and not more and not with the width and precision formats. In the C printf() there are widths and precisions and also lots more options for different data types such as CHAR, LONG and hexidecimals and much more. The UTL_LMS.GET_MESSAGE is interesting as it uses "..." in its specification so Oracle have added "..." for its own use but not for us. The prototype is:

FUNCTION format_message(format IN VARCHAR2 CHARACTER SET ANY_CS, args ...)
RETURN VARCHAR2 CHARACTER SET format%CHARSET;

If we try and use this syntax:

SQL> create or replace procedure printf(fmt in varchar2, args ...) is
2 begin
3 null;
4 end;
5 /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE PRINTF:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/35 PLS-00999: implementation restriction (may be temporary) ellipsis
not allowed in this context

SQL>

We cannot, pity, maybe in the future?. Its possible to work around this with a VARRAY but the call to the procedure in PL/SQL is not quite as elegant as we need to create the VARRAY as we pass it in. If we dig deeper we can see that Oracle passes the variable args list to C and the C handles this variable args list. As the C that handles it using the "AS LANGUAGE C" syntax via a trusted library, is a specific C function to do the language format and simple printf its not going to be possible to use this syntax ourselves until Oracle make the syntax generic. A few people have created printf functions for PL/SQL by now. Scott Stevens has a great PL/SQL printf that shows quite a lot of printf functionalliy in PL/SQL. If you see books like "The Standard C Library" then you can appreciate to write printf() in C is a huge task so to replicate it fully in PL/SQL is equally as challenging.

If you dig also into stdspec.sql then another hint of possible language extras that we cannot use and that may be added:

-- The following data types are generics, used specially within package
-- STANDARD and some other Oracle packages. They are protected against
-- other use; sorry. True generic types are not yet part of the language.

type "" as object (dummy char(1));

Generics are a great feature that we use a lot in .NET in PFCLScan (think templates in C++) so it would also be great to add generics to PL/SQL. I would also like to see the precompiler add true #defines to PL/SQL so that they are the same as C. You could possibly hack the current pre-compiler to do this but my reason would be to do a Bjarne on PL/SQL and add syntax or constructs not possible such as

i++

where i is substituted with "++"(i) at compile time. This would look horrible with the $IF etc from the PL/SQL precompiler and would spoil the look and not acheive the intent of using elegant syntax as the $IF $var is NULL $THEN etc would look much worse.

We could also maybe use defines to achieve calls such as "++"(i) but this is a SQL*Plus construct not PL/SQL so not much use in pure PL/SQL called from the database when compiled.

We could also use PFCLObfuscate to achieve this. We could write PL/SQL code with "..." and also i++; or i+=2; or even (some code)?"TRUE":"FALSE" or similar and then use PFCLObfuscate as a pre-compiler to convert it into compilable PL/SQL in a similar way to CFront converting (literal description!) C++ into C to compile it with CFront. The trick with CFront was that it was also compiled with CFront so bootstrap isses ensued whena new machine had to support it. What is the point of writing PL/SQL with unsupported syntax? not totally sure at this stage. The thought process.

We could also simulate the C syntax for i++ or i+=2 with a procedure/function pair. First we could run this code:

declare
i number;
begin
i:=0;
dbms_output.put_line('i='||i);
i:=i+1;
dbms_output.put_line('i='||i);
end;
/

This gives (in SQL*Plus):


i=0
i=1

PL/SQL procedure successfully completed.

When run through SQL*Plus. If we instead made "i" a procedure and function, so the variable "i" is no longer a variable so that we can get the same effect as i++ or i+=2 then we can do that also as an alternative to a function such as "++"(i). Here is the code:

-- instead make i a function
declare
i_self number:=0;
procedure i (lv in varchar2) is
begin
if(lv='++') then
i_self:=i_self+1;
elsif(lv='+=2') then
i_self:=i_self+2;
end if;
end;
function i return number is
begin
return(i_self);
end;
begin
--i:=0;
dbms_output.put_line('i='||i);
i('+=2');
dbms_output.put_line('i='||i);
end;
/

And we can now run this in SQL*Plus and the results are here:

i=0
i=2

PL/SQL procedure successfully completed.

SQL>

So obviously it works. There is a lot more code than simply writing i:=i+1; or i:=1+2; though and its not the same syntax as C as we need to cover the ++ or +=2 with a string passed to the procedure BUT its neat. We could extend this to pass the value "2" so that we could easily do +=3 or +=7 or whatever or we could parse it from the string.

OK, whats the point of all of this? apart from that it's interesting; well for me just to take the language and push it a little to see what it does is fun; this also helps us in research for PFCLObfuscate and also particularly in the license protections and tamper proofing we are doing at the moment so that customers can sell licenses to their PL/SQL products and enforce that in the database when customers code is deployed by using PFCLObfuscate.

Finally I got an email a week or so ago to tell me that my two slots at the UKOUG tech 2014 conference in Liverpool, UK in December have been accepted. I am doing an Oracle security round table and also a one hour live demo around securing annd locking down Oracle. There will be no slides and just live demos!, should be fun. I am going to do a high level audit of my database to show the issues; i am going to do some simple hacks of my database and application and then I am going to apply some lock down around hardening, users, defaults, passwords, profiles and also some least privilege issues and finish with the same hacks and show if its improved my database.

I also have agreed to speak at the OUG Ireland in Dublin on 24th September and I will do the same one hour live lock down demo and also my talk on secure coding in PL/SQL so that will be nice to be over in Dublin again.

[No Comments]


Integrating PFCLScan and Creating SQL Reports

June 25th, 2014 by Pete

We were asked by a customer whether PFCLScan can generate SQL reports instead of the normal HTML, PDF, MS Word reports so that they could potentially scan all of the databases in their estate and then insert either high level results of the scans (pass / fail, number of issues) into a database and also potentially insert all of the actual detailed results of each policy/test failure.

We can do this and the ability was designed into the product from day 1. This is because we can create reports that are any text based template file. The reporting language of PFCLScan is simple and template based. So you can create a text file that is a template of how you want the report to look - so a nice HTML report, an XML report or even an MS Word document or a SQL*Plus script and then you use the PFCLScan reporting language template variables to insert report data where you need it. That "data" can be from the product, policies, project or scan results of course. So its easy instead to create an SQL file to run instead of a fancy report.

I have written a new blog post on the product website running through an example of how to create SQL reports from PFCLScan.

This makes PFCLScan powerful as its easy to use the output and also to use automation. PFCLScan uses projects to manage each peice of work (a scan of all your databases, or a scan of a single system, or a scan of prod or of dev....) and in each project you manage targets, policy sets and of course the checks defined in the policy sets and also report templates. All of the policies are easily added to a new project so defining a project with what targets you need and what checks you want is quick and simple.

The really cool thing though is that you can also run PFCLScan itself as a check in a policy. You can also run the reporting tool as a check in a policy. This is how we make automation very easy in PFCLScan to achieve powerful results and also to simplify the tasks that you need to do. So, for example one project can be created that reads an Excel sheet with a list of databases that need to be scanned. It tests if each can be reached first and for those that can be it generates a PFCLScan project for each to run. Then it runs each project to perform a detailed scan of each database and then runs a report for each. This means that once set up (and each part is just projects and policies and checks configured in the normal way) you can run one project and supply one Excel sheet and scan any number of databases on demand each day from the GUI or from the command line and also bring in inserting data to a vulnerability database if needed.

We will post a new blog next week on the PFCLScan Website showing a simple example of this automation in PFCLScan to complement the SQL report demo in this new blog post.

Remember also that our pricing model is simple and very competitive; we charge per installation of our software not the number of databases that you scan so its very cost effective to use PFCLScan.

[No Comments]


Automatically Add License Protection and Obfuscation to PL/SQL

April 17th, 2014 by Pete

Yesterday we released the new version 2.0 of our product PFCLObfuscate. This is a tool that allows you to automatically protect the intellectual property in your PL/SQL code (your design secrets) using obfuscation and now in version 2.0 we have added "dynamic obfuscation". This is a new engine added to our parser that allows dynamic obfuscation to be run at various "hook" points in your PL/SQL source code files. These hooks are reached at the start of a declaration block, end of a declaration block, start of a PL/SQL block or end of a PL/SQL block. Also hooks occur when a defined PL/SQL package or procedure is located. The dynamic obfuscation is completely configurable by the customer and adds very powerful features to PFCLObfuscate.

Dynamic obfuscation allows us to add deeper obfuscations and also to automatically add license protections to your PL/SQL code or even tamperproof protections. This means that you can deploy your PL/SQL code with license type features controlling how and when your customers can use your code.

Please contact me for more details if you are interested in this new version of our product. We have kept the license fees at version 1.0 levels for the initial release. I have just written a detailed blog post on our product website and included an example of automatically adding license features to a set of PL/SQL code. The blog is PFCLObfuscate - Protect your PL/SQL

[2 Comments]


July 2015
SMTWTFS
   1234
567891011
12131415161718
19202122232425
262728293031 

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.

Weblog Home
Weblog Archives


Home
Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

RSS 1.0 FEED
RSS 2.0 FEED
Atom 0.3 FEED
Powered by gm-rss 2.0.0


Valid XHTML 1.0!