Auditing an Oracle database for security issues is very important. provides all of the information and tools that you will need Click here for details of Limited's detailed Oracle database security audit service Click here for details of Limited's Oracle Security Training Courses
There are 64 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

Oracle Security And Delphix Paper and Video Available

April 1st, 2016 by Pete

I did a webinar with Delphix on 30th March 2016 on USA time. This was a very good session with some great questions at the end from the attendees. I did a talk on Oracle Security in general, securing non-production by virtualising from production with Delphix and also discussed data masking and why people do not mask and also the Delphix data masking solution. I will be also doing the talk again next week on UK/EU time zone at 10am UK tome on the 7th April 2016. If you would like to hear me speak live then details to register are in this blog post.

If you would like to watch the USA webinar then please go to this link and t download the new white paper I have written on Data Masking with Delphix then please download from this link.

I have updated my Oracle Security white papers page to reflect the new paper and I have also create a new Oracle Security Videos page - More on this soon.

We are currently having the whole of this website redesigned and as soon as that is complete we plan to release three new pages on this site; Oracle Security videos about Oracle security in general and also about using our database security scanner PFCLScan, a new Oracle security articles page with some in depth articles on Oracle security and a page with shorter notes/articles around areas to concentrate on to secure your Oracle databases.

[No Comments]

3 Days of Oracle Security Training In York, UK

March 31st, 2016 by Pete

I have just updated the public Oracle Security training dates on our Oracle Security training page to remove the public trainings that have already taken place this year and to add a new training in York for 2016. After the success of the Oracle security training event that I held last year in York we have decided to run a new public training for three days from May 17th to May 19th 2016. This will include our most popular 2 day class - How to perform a security audit of an Oracle database and our 1 day class - secure and lock down Oracle. These two classes are a great combination to see the whole process from cradle to grave of auditing, securing and locking down your valuable data in your Oracle databases.

There are more details on the Oracle security in York training page and if you would like to secure your place then please email with your details.

I look forward to meeting you on my class in York!

[No Comments]

Oracle Data Masking and Secure Test Databases

March 14th, 2016 by Pete

My daily work is helping my customers secure their Oracle databases. I do this in many ways from performing detailed security audits of key databases to helping in design of secure lock down policies to creating audit trails to teaching people how to secure their own databases through attendance on my Oracle security classes and much more. Talking to customers either generally or as part of an audit often reveals common Oracle database security issues over and over again.

My efforts in helping securing Oracle is not lead by the goal of securing Oracle itself. My focus is to help secure data. We must locate the data that is critical to the business and understand where that data is held across the organization. The goal is to ensure all copies of that data are secure. Obviously we must use the features of the Oracle database to secure the data in the database BUT the emphasis is not on securing Oracle but securing data.

My motivation is aimed generally on the production database and data. Therefore one major issue I come across often is the fact that I can help advise the customer to use the production database to lock down their data but they regularly advise me that data is also copied to test and development and UAT and sometime external suppliers as well. This is so that the applications and database can be developed and tested. Almost always companies are happy to lock and secure production but do not secure the data in these other databases. This is a problem as the potential risk or threat moves to test and development from production.

Customers are willing to spend money and assign budget to secure and lock down the production database with less efforts targeted at securing a test or development system. Similar is the issue of creating and building audit trail solutions; the client is happy to spend money on production for audit trails but usually not to test and development systems.

My aim is to secure data across all databases BUT often money is spent on production only. What if we could secure production and add audit trails to production and then copy all of that Oracle database security to all test and development systems? More importantly what if we could set up and mask the data once but copy just the masked data to all test and development systems without the clear data ever leaving production? Securing and setting audit trails once and having those configurations copied to every cloned database could be a great benefit to most companies but masking once and copying only masked data to all cloned databases is a marked improvement over the often lack of Oracle data masking seen in the real world in all databases. Data masking in Delphix is a very flexible tool and combined with the virtualized facilities it becomes very powerful.

I am going to be delivering a webinar with Delphix on the 30th March at 10am PST (USA time) and also the 7th April at 10am UK time where I am going to explore these Oracle security issues and more and also look at what Delphix can offer to help solve some of these issues with a detailed look at the problems often voiced as to why customers do not mask data in cloned databases. Please join me for the USA webinar by registering on the Delphix registration page or if you would prefer to join me for the UK/EU webinar then please register on the Delphix registration page for that event.

[No Comments]

BOF: A Sample Application For Testing Oracle Security

March 10th, 2016 by Pete

In my Oracle security training classes I use a couple of sample applications for various demonstrations. I teach people how to perform security audits of Oracle databases, secure coding in PL/SQL, designing audit trail solutions and locking down Oracle. We also have some combination classes - the two day how to perform an audit of an Oracle database combined with the secure lockdown of an Oracle database over three days is a popular choice. In fact the lockdown class taught in combination with the two day audit class is different to the standard single day secure and lock down Oracle class. For details of my classes see my Oracle security class pages or email me at info at petefinnigan dot com.

Actually I use three applications in my test systems used in my classes; the third is my PFCL ATK toolkit, my Audit ToolKit (ATK) which is developed and used in the one day class (and given away for free - actually a few thousand lines of PL/SQL and SQL code) - Designing practical audit trails in Oracle databases - But I am going to focus on one of the other two. I have two functional applications, one front of house - a public facing website driven out of an Oracle database and a second back office application that is used to manage customers, payments, products, orders and more. The front facing site is for the public and access is unauthenticated but back office customer processing is by employees and via http passworded access.

I want to focus on the back office application. This is called BOF, BackOFfice. This was written around 5 years ago and recently I am working on a test of some software and needed a sample application with data to test with. So I grabbed the scripts used to build my two (three) applications and installed them in the test database I am using to test this software.

The need to Install BOF by hand (via scripts) for one of my current pieces of work made me look at it again and understand how it works and was written. When I wrote it I wanted to not write the application at all but generate it completely from a schema in the database.

I love programs that write programs; I remember maybe 25 years ago reading about Ken Thompson and Dennis Ritchie doing just this; writing programs that write programs. I always thought this was cool. In fact the first ever PL/SQL program I wrote maybe 20 years ago was written in Pro*C. I needed to generate test data (wow, the same problem again!) and I decided rather than create a large bunch of SQL*loader files I would create a simple control schema that allowed PL/SQL to act upon it and read the rules and tables of data and generate test data in application schema tables that were specified. As the structure of the control programs (PL/SQL) was repeatable it made sense to generate the control programs with PL/SQL. Move forward some years to around 16 years ago and I was helping migrate an application from Dec Alpha, Rdb (owned by Oracle by then of course) and Cognos Powerhouse to Sun Solaris, Oracle and a later version of Cognos Powerhouse. One of the requirements that we needed to be able to do was revert the Oracle system back to the Dec/Rdb system if the migration after up to one was deemed to not be successful. So out came my code generating skills again. I wrote a SQL*Plus script that generated a trigger for update, insert and delete for every migrated table. In each trigger it called a PL/SQL package procedure. In each procedure I generated an update or insert or delete script for DEC Rdb so that all transactions could be re-applied to the original database if necessary. So in the case I generate a program that in turn generated a program. Fun!

Back to the plot. I needed to re-install my BOF sample application used in my training into a new Oracle database for use in a new piece of work. This lead me to understand how it was created and written again. As I said I wanted to not write the web application at all - the front end that is and I wanted to instead generate the web front end. The front end is php and runs in an Apache webserver and this webserver uses OCI8 to connect to the database and provide Oracle access.

The whole application is driven from a sample schema and sample data in a script; The data is not necessary and you can create the schema, generate the application and then fire up the application and add data that way and then extract it for subsequent reload later. In my case I also created some data and the schema. The schema needs to follow some simple rules for the application generator to work. These are:

o - The tables should have the same prefix - in my case they are all BOF_ but you can use any name you like
o - The tables must have a primary key column called ID and it must have sequence called {PREFIX}_your_table_name{_SEQ} again for the generator to find it.
o - If you need connected records then you must define foreign keys.
o - You must create an entity view - a summary view that dereferences the foreign keys and shows useful data from the child table. This view must be named {tabe_name}_ENTITY_V
o - You should create a foreign key view that supplies the meaningful data (not ID) from the foreign keyed table. These views can be used in the entity views and should be name {table_name}_F

I also created a drop script to be able to drop and re-create the sample application. Here is some examples next. The first is an example of one of my BOF application tables definitions:

Create the BOF_ORDERS table

And the custom rules tutorial is here (BTW: click on the images to get a bigger image). The next example is the creation of an entity view as listed above:

Creating an entity view

And finally for the application schema itself here is an example of creating a foreign key view:

Creating a foreign key view

The application schema for my sample is simple to create and of course I created my BOF schema for my training classes BUT I can now create any schema I need and generate a PHO application for it. The application includes some very basic styling with CSS to try and tidy it up but remember this is not production code and not intended for public consumption. The site does not include sessons, passwords or the like and limited error checking in the php. The site is styled in html5 and css and is structured using divs not tables. The toolkit generates separate header, footer and navigation sections. Each of these sections is generated by separate PL/SQL scripts that write the PHP code. The website is made up of grid based tables with a separate page for each table in the database schemae. From each row of data there is a link to edit that record and a global link at the top and bottom of the page to add a new record. This means that there are two main pages for each table in the schema; a view page in a grid and a form that allows a new record to be added and an existing record to be deleted. The menu navigation is also generated automatically from the database schema.

To create a website from a database schema as set up above I wrote a set PL/SQL scripts that generate the header, footer, nav bar and database config php files. Wrapped above this is a script that generates the view pages and also generates the edit/new pages from the database. The whole thing is controlled by a simple script called bof_conf.sql that you an edit. This is the only file that should be edited. Here it is:

Main PL/SQL Configuration page

This is simple to configure, you need to add the database schema user and password that will be used to access the database from PHP and also to access to generate the website. Also add the connection details, IP Address, port etc and details to appear in the site such as its name and copyright notice. Finally add a search string to be used to find the tables to have web pages generated from. Everything is generated from one script bof_gen_all.sql and part of this is shown here:

Main PL/SQL Configuration page

This shows the generation of the single files such as header, footer and nav and also shows the generated loop used to then generate the individual grid read pages for each table in the schema. The generate script bof_generate_edir_new.sql creates the pages for each table that include a form to edit a record or add new records. This also generates lookups for the foreign key records and check boxes for status records. A small section of this is shown here:

PL/SQL to generate a drop down in the update screen

The generated code forms a complete website running against an Oracle database. An example of the shipping page based on the table BOF_SHIPPING is shown below:

Screen to show all of the shipping records

And another example shows the update form for editing a shipping record:

Screen to allow updates to a shipping record

It was fun to have a good look at this sample application again as I needed to generate it in another test database. This generator can be used to create any sample application BUT the emphasis is on sample; this should not e used in production as it has no security (well actually the php uses binds so that's good), no sessions, no users etc BUT it could be extended I guess if I had the time and inclination.

I like these scripts because they follow the tradition of generating code with code. In fact this is 4 deep. I have a SQL*Plus script bof_gen_all.sql that creates and writes a SQL*Plus script bof_run_all.sql that in turn runs bof_generate_web.sql for each table that itself generates a PHP page for each table and that PHP page when run in the web server generates HTML to be served to the end user. So code that generates code that generates code that generates code..

The whole tool is about 2000 odd lines of PL/SQL and if anyone would like a copy of the tool then please let me know by email at pete at petefinnigan dot com and I will send you a copy. The license is the same as all my tools, its free, its not GPL and you can use it internally but not remove my copyright or license and you cannot sell or give it away yourself.

[No Comments]

Two New Oracle Security Presentations Available

December 14th, 2015 by Pete

I attended the UKOUG conference last week Monday to Wednesday in Birmingham. This is the first year for three years that it has been back at the ICC in the center of Birmingham. The last two years have seen the conference held in Manchester and last year in Liverpool. The journey from the railway station has changed slightly as New Street Station has been completely modernised from its dark and dingy past and the route through the galleria is blocked off.

I had three slots this year; on the Monday I spoke about application design in the database. I covered the ideas that we must create least privilege and ideally separate the data from the functionality (PL/SQL) and also ideally connected users from the schemas so creating a privilege model and least privilege. We also looked at invoker rights vs definer rights, With Admin and With Grant as well as INHERIT privileges in 12c. We also looked at context based security with some examples. We explored privilege analysis of existing users and also how to design users rights with least privilege in mind and also covered privileges in different modes such as build time, run time, maintenance time and more. This was a good talk as I had some good discussion during and after the talk.

On the Tuesday I chaired an Oracle security round table and Piet De Visser made a valiant contribution as the session proctor. We had some great discussion and questions particularly around least privilege and breakglass for the Oracle database.

On the Wednesday I made my last talk which was also well attended and was about Oracle Database Password Design. We looked at the core issues of weak passwords, what makes them weak and also the cor4e password algorithms available in the database. We also looked at password cracking and the different types of cracking that are possible (default, dictionary, brute force and password=username). We also looked at the types of Cracker (PL/SQL based, C based, GPU and FPGA crackers). We also looked at password design, profiles to enforce password strength and security of password hashes. We looked at password choice and also password safes.

Links to the pdfs of my new papers are on our Oracle Security White Papers Page.

[No Comments]

Oracle Security Training In York

October 22nd, 2015 by Pete

We ran a five day Oracle Security training event in York, England from September 21st to September 25th at the Holiday Inn hotel. This proved to be very successful and good fun. The event included back to back teaching by me of all of my Oracle Security classes:

1 - two day - How to perform a security audit of an Oracle database
2 - Secure Coding in PL/SQL
3 - Designing practical audit trails for the Oracle database
4 - secure and lock down Oracle

A number of people have already asked me if I will re-run this same training event again. I have decided to do just that in late January 2016 or early February 2016. This will be held in York, England at the Holiday Inn, Tadcaster Road, York. The date is not finalised yet as I have not agreed dates for the hotel meeting room but the exact dates will be released soon. This time it will be a three day event and not five days. The format will be:

Training Day 1 - First day of "how to perform a security audit of an Oracle database", 2 day class- including some elements of "secure coding in PL/SQL" focusing on the issues
Training Day 2 - Most of the Second day of "How to perform a security audit of an Oracle database" and including some elements of "secure coding in PL/SQL" - focusing on the auditing elements
Training Day 3 - Core of the second half of the "Locking down and securing Oracle" one day class and including some of the solutions elements from secure coding and also an overview and use of ATK our Audit Trail toolKit for Oracle. The day finishes with the round up from the "Locking down and securing Oracle" class and also from the "How to perform a security audit of an Oracle database"

This will provide a shorter event but will include all of the core classes of why data is insecure, why you as designers and users of Oracle make data insecure and also a complete walk through of an audit of an Oracle database and lots of structured lessons on how to secure and lock down a database, data and application. I use a web based application that uses an Oracle database as a demonstration system. I show at the start how it can be hacked and abused, how audit does not work and how code is vulnerable. We then use this system as the stooge system to audit and we also do our best to lock down and secure all of the core data breach problems in it. We also implement a comprehensive audit trail solution and even including a simple and robust home made application firewall. We show then by attacking this database again how it is locked down and also how audit is generated and how our firewall can kill some attacks.

This is a great opportunity to get this new combined 3 day class that shows comprehensively how to focus on locking down and understanding your data security.

As an attendee you will also get a great set of free tools and scripts to use in your own audits. This is approx 200 free tools and scripts written in SQL and PL/SQL. You will also receive pdfs of all of the slides and also printed books of the course materials to take away. The class also includes tea/coffee at breaks and hot buffet lunch every day. We can also arrange a discounted rate at the same hotel venue for attendees.

If you are interested then please email me at pete at petefinnigan dot com for more details and to register a place. The cost will be £1125GBP + VAT per person for the 3 days and discounts are available for multiple person bookings.

[No Comments]

New Presentation - Building Practical Oracle Audit Trails

October 1st, 2015 by Pete

I wrote a presentation on designing and building practical audit trails back in 2012 and presented it once and then never again. By chance I did not post the pdf's of these slides at that time. I did though some time after create a full one day training course that I have taught many times now on the same subject. I recently was asked by the UKOUG to present the same talk again at the UKOUG Database SIG in London at Oracles office on September 15th 2015. So I went down to London and did just that. I updated the slides and made many changes to the whole paper and some major and some minor changes to most slides.

This paper went down reasonably well and we had some good questions and good chats there. The slides have been posted to my Oracle Security White Papers page so please head over there and download the pdf's if you are interested.

[No Comments]

April 2016

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

Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

Atom 0.3 FEED
Powered by gm-rss 2.0.0

Valid XHTML 1.0!