|There are 66 visitors online|
I will be co-chairing/hosting a twitter chat on Thursday 6th March at 7pm UK time with Confio. The details are here. The chat is done over twitter so it is a little like the Oracle security round table sessions I do at the UKOUG Oracle conference here in the UK live each year. This chat is done via twitter and sounds like fun. You can ask questions or join in the chat. Simply use the tag #DataChat in your responses or posts from 7pm (UK time) on Thursday. You can also use a tool like tweetdeck and set up a new column for the tag #DataChat and you can see all of the threads.
If you fancy joining in, please do so. If you have a subject or question to raise before hand then please email me (pete at petefinnigan dot com) or send me a tweet to @petefinnigan
We are going to start a reseller program for PFCLScan and we have started the plannng and recruitment process for this program. I have just posted a short blog on the PFCLScan website titled "PFCLScan Reseller Program". If you are running a consultancy specialising in Oracle or security and you feel you would like to be involved in reselling PFCLScan then please speak to us.
We released version 1.3 of PFCLScan our enterprise database security scanner for Oracle a week ago. I have just posted a blog entry on the PFCLScan product site blog that describes some of the highlights of the over 220 new changes/features/fixes/content added in 1.3. Please have a look and if you would like any details please contact me.
We have just updated PFCLScan our companies database security scanner for Oracle databases to version 1.2 and added some new features and some new contents and more. We are working to release another service update also in the next couple of weeks that will add some updates to the reporting tool and language and also a new feature to allow the customer through point and click and change data values that are not known until they come to use them BUT we have already tests and checks in our standard audit policies that use these values. This will allow customers to enable additonal checks based on their own values and data. An example is that a customer may have a custom DBA role that they have designed for their own use. We don't know what that is in advance so cannot add a check to test its exists and is used only by the DBA team. The new PFCL.config screens and functionallity allows a user to add the values in a configuration screen and have custom checks work for them and so allows us to answer questions that we could not answer before.
I have also just written a blog post titled "Powerful features of PFCLScan" and published it on our PFCLScan product website. Please have a look.
It has been a few weeks since my last blog post but don't worry I am still interested to blog about Oracle 12c database security and indeed have nearly 700 pages of notes in MS Word related to 12c security research I have gathered already.
I have not blogged because I have been very busy and also been on vacation for two weeks in Calabria Italy which was very nice and hot and relaxing on the beach. I have quite a lot of news items to cover here, some have been mentioned on Twitter previously but its worth re-iterating them here:
1) I am teaching my class "How to perform a security audit of an Oracle database" in Rome, Italy with Oracle University on November 19th and 20th. If you would like to attend a public class in Rome then please have a look at Oracles registration http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=609&p_org_id=32&lang=I&get_params=dc:D80323_1025738,p_preview:N - Sorry its not a live link, there is something about Oracles links that make them break IE so please cut and paste it into your browser.
2) I am teaching my class "How to perform a security audit of an Oracle database" in Kiev, Ukraine wth ISSA on October the 16th and 17th with ISSP, based in Kiev. If you would like to attend this class then please vist their registration link.
3) I am teaching my class "How to perform a security audit of an Oracle database" online on October the 7th to 10th inclusive. The class is two days normally but for on-line we are going to do it over 4 days. The timing each day will be 2pm to 6pm UK time to allow students to join from the USA and also CET. We advertised this class a few weeks ago once on Twitter and had a lot of interest and a few registrations but there are still places available. The class costs £750 + VAT (if applicable) per student and if you book and pay for more than one place we will give you a discount on all places you book. Each student gets a free download of pdfs from the course (approx 570 pages) and also over 70 free tools/scripts.
If you are interested and want to book a place then please email me at pete at petefinnigan dot com
4) We have just updated our Oracle database security scanner PFCLScan to version 1.2 and released to clients. This is a reasonable size update with Oracle security specific changes and additions and also some core product changes to make it better for customers to customise and automate. We have made changes to add the first tranche of changes to support Oracle 12c and we have updated the password cracker to make its logic faster, we have re-implemented the encryption used to protect credentials and we have added the ability to create "plug-ins" for PFCLScan using PFCLScan projects and policies themselves. There are around 40 individual changes, new features, bug fixes and changes and additions to the standard policies. Please talk to us if you are interested in licensing PFCLScan
5) The other major change we have made with PFCLScan is to introduce a new license. We have a great license model for PFCLScan where the license is an annual model and based on the number of installations of the software and not the number of databases or targets that you scan. We believe that this is a fairer model and our customers seem happy with this. We had two PFCLScan licenses "Pro" and "Enterprise" that allows a single installation or as many installations as you wish on your site for the "Enterprise" license. We also offer great renewal deals, if you renew your license after one year then the renewal is 54% of the original fee.
We have a very simple license model but we have had a number of discussions with people who say there is a licence missing or a gap in our model. We have decided to rectify this. We have specifically added an engagement license so that customer can licesen PFCLScan for a 30 day period to enable a single audit engagement either internally or for a consultants customers to take place. This is again competitively priced at £85 + VAT for a single installation with a renewal for 30 more days at £70 + VAT is renewed concurrently.
6) Our second product PFCLObfuscate is also selling licenses well and is aimed at protecting Intellectual Property in PL/SQL or SQL. We have been promising to release version 2.0 for some time now. We created an alpha test version earlier this year and have tested and experimented with this. Finally we have started work to implement the production version of version 2.0. Version 2.0 is a major release and will add some great new functionallity. The core of the product is a built ibn scripting language and "hook" points that allows each piece of PL/SQL code that is parsed to be interpreted at various points (Start, Begin, End and also when any defined package call is found or when a string is found). PFCLObfuscate still obfuscates code but when a hook point is found it can decleratively execute a script to generate PL/SQL code to insert which is added and recursively obfuscated. What does all this mean?
Well it means that you can obfuscate standard package calls within your PL/SQL where you add dynamically covering APIs and also obfuscate them without adding external procedures to do this; it means that you can use any dynamic method to obfuscate strings; it means that you can insert code to execute when a string is found or a package found or when a block starts or ends. This all allows us to much more strongly protect and add license type features to PL/SQL. When combined with Wrap and WrapProtect it means that your PL/SQL code is much safer.
If anyone wants more details or would like to purchase a license then please email me.
7) I have also had two slots accepted for the UKOUG conference coming up at the start of December and this year for the first time held in Manchester. I will be chairing an open session on the 3rd of December about Oracle security so please come along and join in. My second session also on the 3rd December is going to be an hour long demo session with no slides!!, that should be really fun and exciting (spelt terror!). This talk is going to revolve around Oracle database audit trails and protections. I am going to focus on the core database and not the new 12c Unified audit and look at what works and what doesn't in terms of designing simple audit trails that are practical and can be taken away and used in your own databases. This means I am not going to focus on data auditing but on privilege audit and escalation of privilege. I am going to discuss adaptive controls, breakgalss and the aftermath of issuing a privileged password, I am going to also talk about allowing third parties into your database and capturing what they do whilst in there, I will talk about security of audit, audit of security and audit of audit. I am also going to demonstrate some simple packages that you can deploy to detect issues in the database.
I aim to cover quite a bit of ground but the goal is really to show how you can create a comprehensive audit of privilege, security and audit and cover breakglass and adaptive controls, IDS and IPS but show how its all acheivable as a good start point in your own database. Clearly there are two issues with audit, performance and storage of audit data so again I am focusing on privilege as that should not cause performance as anyone abusing privilege should not be using privilege hence if there is a performance issue the action should be stopped not the performance sorted to allow the abuse to continue. The second issue is around storage of audit trails; my solution (which can be combined with stored audit) is to act upon the trail and report on summaries and issues rather than store enourmous amounts of data.
The demo is made up of a package in PL/SQL I am calling PFCLFirewall that will implement the audit and the protections and also reports and dashboard. This is at a simple level to start with. I am going to walk through the code and explain it and then install it and abuse my database and demonstrate what it all does and how the whole thing sits together. If you come to the talk I will let you have a copy of the PL/SQL PFCLFirewall or if you ask me for it i will give it to you but after the talk of course in December. The PL/SQL package PFCLFirewall was started and the core written around a year ago but its been worked on again in between times.
8) I have also been asked to speak about Oracle security and PFCLObfuscate on Paul Dot Com on September 13th this year, so if you are interested to see this then please head over to their site and watch and listen to me speak.
9) I have also just written a new 1500 word article on Oracle 12 New Security features for the Oracle Scene magazine which will be published in time for the UKOUG conference in December in Manchester. So watch out for that.
10) I have just agreed to write some reviews of some Oracle Security products for some famous companies and I will write a nice review based on flow and practical use of a product. There will be a paper and a webcast for that later in the year. As I get more details I will let you know, so watch out for that to get a free paper and also hear me speak on a webcast.
11) Finally.... as I have now quite a lot of research on Oracle Security around Audit trails, database IDS (built in PL/SQL) discussed above, identity in the database, adaptive controls, protection of audit, security of security, breakglass and of course on 12c security i have been thinking to write to all into a nice e-book and publish on Kindle. This would be a nice book, around 150 - 190 pages and not expensive so that people can afford to buy it. It is an idea now but if i get some encouragement I will write it and publish it. A book like this may also be a good basis for a single day training class. Lets see.
OK, thats some news on Oracle security, more on 12c security next time.
There has been some big new security items added to 12cR1 such as SHA2 in DBMS_CRYPTO, code based security in PL/SQL, Data Redaction, unified audit or even privilege analysis but also as I hinted in some previous blogs there are lots of little things that have changed related to security or big things that have changed that affect security such as the multitenant architecture.
I am going to discuss a couple here this time in this short post. The first came from a post on Kerry Osborne's blog that I found via the oaktable list, titled "SQL Translation Framework" that discusses that the SQL translation framework from SQl Developer is now in the database as a feature. This was intended to translate non Oracle SQL to Oracle SQL but the key thing is that it can translate Oracle SQL to Oracle SQL. The first comment in Kerry's blog by Stew Ashton summed it up: " Wow, generalised bottom-up SQL injection!". It is not really SQL injection but SQL replacement but the concerns and the sentiment are justified.
The feature is great for resolving application issues with bad code for performance or workability issues but quite obviously it could be abused for attacking the database as well. It could also be use for security "good" as well as security "bad". It could be used for instance to aid an application to use a shared "view/logon" account instead of having users use the schema to connect to, it could be used to enable security within an existing application, it could maybe be used to enable encryption BUT the bigger issue is to make sure it is not used to bypass security. Imagine an application that logs in and then says "select role from app_roles where username=lowest of the low" it could be changed to say "select role from app_roles where username=god" . To enable the translation the package DBMS_SQL_TRANSLATOR must be available:
And the user must be able to do:
So this needs access to ALTER SESSION to enable the event:
So we can test who has this:
By default ALTER SESSION is only granted to some default accounts, my 12cR1 system shows this:
So; if you are worried that the SQL Translator can be used for bad then block access to the API DBMS_SQL_TRANSLATOR package and also make sure no one has ALTER SESSION. Also ensure that you review whats set up already.
Finally if you read Kerrys blog it would seem that it uses exact match so if you used this package to enable some security be aware as Kerry exampled that comments in the SQL can make it do something else, i.e. not translate.
The second simple new feature added to 12cR1 is the last logon. I have mentioned this many times in the past that it would be nice to see users last logon. I even wrote a script in my Oracle security tools page that used audit to test for last logon because up until 12cR1 you needed audit to be able to do this.
It states in the documentation that last logon is recorded in the SYS.USER$ table, in fact its recorded in SYS.USER$.SPARE6 or you can get it from the DBA_USERS.LAST_LOGIN column or the SYS.CDB_USERS.LAST_LOGIN column. In fact this information is only available in these two views and SYS.USER$:
That is a simplistic search of course!.
It also states in the documentation that security administrators can view when accounts were last used with this column and also that individuals can see their own last logon via the SQL*Plus banner. That of course assumes that you are using SQL*Plus!
For my system I can see:
This is interesting as I have logged in as SYSTEM and also C##TST today but I didnt login as CTXSYS on my Oracle 12cR1 PE installation. That means Oracle itself logged in as CTXSYS during the installation. Also if i do this:
It does not record last login as SYSDBA. The mandatory audit trail records the logon as SYSDBA already but it would be nice to distinguish an interactive logon as sysdba and record it.
Also the banner does not show up if you use an older SQL Client or older SQL*Plus. I am using 11.2 SQL client and SQL*Plus and no banner for 12cR1, this is to be expected of course as the banner must be built into SQL*Plus. If i instead login with a 12c client and I have logged in previously as the same user then I get the banner:
The interesting thought would be how does it read the spare6 column or the last_login column of cdb_users or dba_users as these are not available to all users normally, it would imply that access to the user$ table is exposed to everyone for this column at least. USER$ is removed from SELECT ANY DICTIONARY but its data is available still of course. Either SQL*Plus runs code as SYS or its exposed via a public view.
The main new feature of Oracle 12cR1 has to be the multitennant architecture that allows tennant databases to be added or plugged into a container database. I am interested in the security of this of course and one element that permeates the whole container database architecture is the use of local and common user accounts and local and common privileges. We can create common accounts and these are identified by columns on various views including DBA_USERS:
As you can see my container database has a common user called C##ORASCAN; the C## part of the name is demanded by Oracle in its documentation and is enforced in the database. All common users that you create must start C## and also any local user or indeed any user (as you cannot create common users in a plug database from the plug database) cannot start C##. But, you may ask then how come Oracle can create its users with names it chooses such as SYS, SYSTEM, DBSNMP etc. Are Oracles created users special, is there a maintained list somewhere that says its an Oracle user and not one we created? There is a column on DBA_USERS and other views that is called ORACLE_MAINTAINED that shows Oracle installed or created accounts. But what if we want to control the names of accounts.
OK, so the rules say we cannot create a common user without C##, lets try:
So it fails, the rules are met. So lets try again:
So it seems to have worked; I was able to create a common user with a name i defined; i.e. not defined by Oracle. Check the user exists and its settings:
It is there and it is a COMMON account and also it states it is ORACLE_MAINTAINED because we tricked the database to think its an Oracle account
Is it visible in the plug database (first show its the plug by the connect string):
The user is not there in the plug. Hmmmm, it says its a common user in the root container, see the output above but it isn't as it does not exist in the plug. Lets create a second account MYSECOND without the container clause:
So the user is created and its again a COMMON user and also ORACLE_MAINTAINED. Has this created the COMMON user in the plug:
Nope, it still has not worked, it is not a real common account. So we have a common user in the root container and its name doesnt start with C##. What about if we do it properly. We can create a common user starting c## and it should appear in the plug database also.
So we have a COMMON user, not ORACLE_MAINTAINED and does it exist in the plug?
Yes, so it works correctly when we use the correct name for a common user. What about if we change the common users password for the account that didn't have C## in its name:
Hmmm, this does not look good but is obvious really as the common user does not exist in the pluggable database therefore we cannot change its password. The error also gives a clue to how this all works; the ORA-01918 is from the plug and is a normal error, the DDL executed in the root is obviously grabbed by a trigger/DV/OLS/VPD policy and run against the plug from the container.
Interesting; what if we now create a local user in the plug database with the same name as the common user in the root container:
So it works; we now have a user MYCOMMON in the root, marked as COMMON but it doesnt exist in the plug (as a common user, it exists as a local user) and we obviously have the same user in the plug but its local not common. What happens when we now change the password for MYCOMMON in the root container.
I am in the root container and have changed the common user in root:
In the plug container:
It has the same 10g password in both the root and the plug container. The MYCOMMON user is still obviously not a common user in the plug but i changed its password from the root container; so I was able to change a local users password in a pluggable container from the root container when i should not be able to do that:
If we are logged into the plug and try and change a common users password we get this:
But what happens with MYCOMMON:
If we compare the hashes:
And in the root container:
Obviously the passwords are different. If we have a common account the passwords should be sync'd across all databases but the issue is that for the plug the account is local so we can change it, for the root its common so we can also change it in the plug. What happens if we use the same ideas to create an account in the plug database that starts with c## which according to the documentation we are not supposed to be able to do:
This fails as expected as thats the rules that are documented, we are not supposed to create accounts called C## or c## in the plug databases, only accounts called C## or c## are allowed in the root container. OK, lets use the same hack as see what happens:
OK, this is good as we cannot bypass the name prefix in this way so there is an unbalanced issue.
There is another hidden parameter related to this area that we can play with "_common_user_prefix" - this is what enforces the 'C##' name for a common user. I have changed this in my database to a NULL string:
Now create a common user with a name i decide and check it:
This works. Has it been created in the plug database?
So, yes this is much cleaner if you need to name common accounts without C##. The first method with the _oracle_script hidden parameter creates a complex scenario where we are able to create an account with the same name in the root and also the pluggable database but they can have different passwords and one is a common account and the other a local account. Finally if we need to drop an account created under the regime of the _oracle_script variable then this happens:
The solution is to drop under the _oracle_script parameter:
And of course it works. If you need to create accounts with your own name convention then use the hidden parameter _common_user_prefix but its hidden so you would need to check with Oracle whether you are still supported when doing this, my gut feeling would be yes because this parameter must exist for a reason and its most likely to ensure legacy applications (including Oracle default schemas) where the name cannot be changed will still work.
We should not rely on _oracle_script as its clearly coded to work under certain circumstances when installing Oracle defined accounts and appears to be inconsistant.
What else does all of this tell us? well we cannot rely on the COMMON column in views such as DBA_USERS as i created a COMMON user that is not COMMON and did not exist in each pluggable database until i created manually. Then I could sync passwords BUT i could still create seperate passwords if I needed. We cannot assume a COMMON account in the root container has the same password in all pluggable databases. Also the account in the plug cannot be considered common as i had to create it manually and it was listed as not common. We also cannot rely on a COMMON user being called C## something. We also cannot rely on the ORACLE_MAINTAINED column as I created a user that was not ORACLE_MAINTAINED.
Obviously do not use these parameters in production without discussing it with support; the first parameter _oracle_script seems buggy and unreliable but the second _common_user_prefix seems sensible.
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.
Home and Archives
Other useful blogs
Syndication - Feeds