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 info@petefinnigan.com with your details.
I look forward to meeting you on my class in York!
Blog
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.
Oracle Data Masking and Secure Test Databases
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.
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.
BOF: A Sample Application For Testing Oracle Security
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.
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:
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:
And finally for the application schema itself here is an example of 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:
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:
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:
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:
And another example shows the update form for editing 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.
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:
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:
And finally for the application schema itself here is an example of 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:
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:
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:
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:
And another example shows the update form for editing 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.
