Call: +44 (0)7759 277220 Call
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.

Super Lock an Oracle Database

I started this blog post a few weeks ago and kept adding to it from time to time but I have been incredibly busy helping people secure data in their Oracle databases that it has taken a long time to get it complete and ready to publish. The looming brexit here in the UK does not seem to have reduced the need to protect data held and used in Oracle databases and indeed the introduction of GDPR law in May has added extra teeth to the laws to protect peoples personal data. There seems to be an upsurge in the need for people to protect data held in Oracle databases.

I have been working on securing Oracle for almost 20 years and I was instrumental in some of the first lists on securing Oracle and also was the author of one of the first big papers ever written on securing Oracle. I have been teaching people to secure and protect data in Oracle databases and of course performing security audits of Oracle databases, locking down Oracle, helping people design audit trail solutions and of course all aspects of Oracle security design around things like audit, Database Vault, Label Security, VPD, TSDP, masking and obfuscation and all aspects of securing data in Oracle. I also have been selling our database security scanner for Oracle; PFCLScan as well as helping people protect PL/SQL with PFCLObfuscate and much more.

It is fair to say I live and breath Oracle security. So, after all these years of doing everything i can in the Oracle security world what is a "Super Locked Oracle Database"?.

I have seen many levels of security in Oracle databases over the many years that I have been helping secure them; some i have come to see as part of my work that are already secured to some level and some i have of course helped to become secure. I have never seen a database though that is truly truly locked down. I have to say that my definition of this statement may be (actually almost certainly it is) different to others views of the same statement. So for some people a well locked down and secured Oracle database may not be the same for me. Thats just my opinion and i can justify why in each case. One example, a simple example could be that someone thinks that their mediocre security is fine because in their view they have good network security and desktop security but my view is that you should not assume that the attacker does not get into your network or into your building and indeed the attacker may not be an attacker in the traditional sense of the word and it maybe that they work for you and are doing things outside of their realm. Then there is the case where the production Oracle database is pretty well secured BUT they copy the production data to a test or development system and because they reset the passwords that is fine... hmmm... not really; the data is still the data and now its easy to steal from the test system that no one is monitoring. I have also seen cases particularly on the west side of the Atlantic where the company has applied most of the CIS benchmark for Oracle or the Stig or the ISACA list; the problem here is that hardening does not protect your data generally.

This is a problem with Oracle and the traditional idea of hardening does not work. Imagine you apply all clauses of the CIS 12c document to a production Oracle database and then step back and pat yourself on the back that you are compliant with the CIS benchmark 12c. Well done; its a lot of work BUT is the data in the database any more secure than it was before you started from attack by a logged in user? well that depends of course on the design of your data model and the data security you have added. Oracle is a generic engine that allows you to specify the rules for the design of your data model (tables, views, synonyms.....) and also your code (PL/SQL, Java....) and you are supposed to also design security but my experience of in-house Oracle database applications and also third party pre-packaged applications is that security is usually an after thought and some are pretty bad; they put all of the data and code into one schema and then you connect the web server to the database as the same schema; why? well because its easy and the code works and you didn't need to think about security at all; it just functions; the most important job often is to get it functionally working and make sure its performant. I have hear the same stories many times; the new system goes live in one week and oh, by the way can we do security now? madness.

So, yes you may have applied all of the CIS benchmark and feel you will pass an external audit but how well secured is your actual data? Even if the whole of the CIS is applied it does nothing for the users, the grants on tables and views and procedures of the actual application. This is the difference between hardening and data security design. I have been teaching people and helping people in consulting work for many years to focus more of their effort and budget on the security of data. We are trying to secure data not trying to secure Oracle with no thought about the data thats processed in the database.

Back to the question; "What is a super locked Oracle database"?. For me this would be the ultimate secured database; focused on the task of ensuring that only the exact number of people can see the explicit data that they are supposed to see and only when they are supposed to see it. i.e. absolute least privilege and absolute data access controls.

In real life we never get to build truly secure Oracle databases like this because budgets, man power and timescales get in the way. Mostly because security of the data in the Oracle database is not considered until later in the project or maybe because you bought an application and it doesn't really do security of data very well.

A search on line for a very secure database or definition of one doesn't throw up such at all so i would have to conclude that i am not the only one who has never seen a truly truly locked down Oracle database out their in the wild. I am sure some people are now going to email me and tell me of their absolutely secured Oracle database and thats fantastic; i would love to hear but I am sure its not super locked.

This idea first intrigued me about 2 years ago when a client approached me and asked me to help them create what he described as the most secure database on the planet. This was a small island government almost half way around the world from me in the UK. We discussed at a high level his requirements and some of his ideas and also my proposals on what we could do. As a teaser he wanted to obfuscate everything; tables, views, code, everything; he wanted to encrypt everything, network, at rest (TDE), backups, and in motion; i.e. data held in every table had to be encrypted at rest, in motion and in storage; so TDE was just the start, data actually held was then encrypted and the network was to use ASO or OpenSSH. There had to be the most locking of the database we could come up with; so much more than CIS; there had to be context based security around every user and every piece of data so Database Vault, VPD etc. Absolutely every action must be audited and the audit trails could not contain actual data; hmmm. There had to be no excess users or functionality at all in the database. The DBA mist not be able to see or change any data at all through any means; i.e. he cannot select data from v$sql or other views as well as of course from the actual tables. And many many more ideas...

This was an interesting project and in the end it went ahead in a much stripped down way from the initial discussions; for two reasons; It is hard to design and implement and second its hard to use the system after its in such a locked down state. Testing some things shows that absolute security like they imagined that they needed creates operational issues that must also be resolved. Also application development and deployment has issues; audit trails are difficult if we want to capture everything but not leak anything via the audit trail. There were many issues to cover and resolve and a truly truly locked database is hard to use. This is really like the idea that every single person in the organisation must have specific tasks and the data that they access and use has to be known fully and rules must be created that allow them to only use that data and no more and that data must only become visible at the right point in time exactly when they are supposed to see it.

If we wanted to create a super locked database we must start with the data and ignore Oracle and we must essentially make a matrix that shows for each user (real person) what exact data they are allowed to see and why and how and when and is it encrypted and is it masked and how is it audited. The n we must lay that over the Oracle database.

One of the most obvious problems with the Oracle database is its massive numbers of PUBLIC grants and the (tens of) thousands packages and procedures that are available either publicly direct or via other packages where the function is not directly accessed but is used via another public interface. No one knows what all this code does and whether its vulnerable to an exploit OR its use can abuse data in the database without an actual exploit such as SQL Injection. This is a major hurdle and is one that is a massive problem to solve.

We have two approaches (actually more approaches exist as well but...) ; the first is we de-install the default schemas that we don't use and which own some of this code; this works fine if we can de-install and if we don't need the code exposed by these users. This is a difficult problem as no one knows what these 30 to 50 default users do exactly in every case. (around 35 default accounts in a 10g, 11g, 12c, 18c legacy database and a cumulative total of more than 50 if we build a multi tenant database). Big issue. The second issue is that some of the functionally is installed into the SYS schema - i.e. packages owned by SYS so that the de-install of default schemas does not remove the risky code from the database.

Related to this issue is the PUBLIC grants. We could remove all public grants. There are a small number of posts online about people who have tried to do this and an unclear situation as to whether Oracle would support you and also what happens every time you install a patch or run catproc (i.e. a lot of the grants will be added back again). My view is that you can remove all the PUBLIC grants and it can be made to work; be aware that multienant makes this harder to do as a lot of it has to be done in the root container or the catcon.pl script has to be considered. This would only remove the PUBLIC grants and the invalid objects can be fixed by granting back to any remaining schema that needs the grant (if you already removed most default schemas then this is easier and also as part of that if you remove Java and XDB then again its easier). BUT this means any dynamic use of a PUBLIC grant is going to fail at run time and any patch will put the grants back so you need to script the removal.

Uf we truly want a super locked database then we must remove default schemas and PUBLIC grants BUT any inter-package exposure that is not PUBLIC would still be there and could potentially be used.

In principal for a super locked database we need to at a high level:


  • Block access to the database unless explicitly needed for each explicit action; this means any none explicit access can be blocked

  • Least privilege for all users

  • Data access controls around all data



These can be supplemented by hardening, patching, context based security around access, users and data, obfuscation and masking and encryption, audit trails, removal of public grants, removal of all defaults, network controls and much more. We should also add secure coding and security of all PL/SQL; i.e. we can use context based security again either by hand with who_called_me or accessible lists. We can include firewalls and intrusion detection or prevention and its possible to build that yourself into the database for free (at least in terms of license costs).

This subject of how far can we go in securing Oracle and what are the effects of that security intrigues me; i.e. does it make working harder; I have agreed to do two talks at the Belgian tech days conference next year in February and one of the talks I will give is on this very subject. How far can we go in securing data and what are the effects.

One last point of note is that 18c XE has just been released and it includes most of the security cost options for free such as database vault; fantastic!!

If anyone needs help to really secure their data either in terms of training; consulting or in terms of automated vulnerability scanning for their Oracle database talk to me on info@petefinnigan.com and we can help you protect your data.

Oracle Core Audit - Do you Audit your Core database engine for breach?

Oracles core database audit is a useful tool to monitor activity of the core database engine or applications and detect potential abuses. It seems to be a sad fact that with a lot of companies that i visit and from people i talk to at conferences very few have an audit trail in the database to monitor the database engine itself for possible unauthorised activity and even fewer of those that do actually use the audit trail that they have in a pro-active way - i.e. react to an event or alert detected by the audit trail in real or semi-real time.

I often take a show of hands at conference talks that I do or trainings and I ask the room - How many people have audit enabled in the database? - Usually between 10% and 30% of the room will put their hands up. I then say - Those of you with your hands up keep them up if you actually do anything with the audit trails except store it and never look at it? - Again 10% to 30% may keep their hands up. In a room of more than 100 people I have seen one or two people keep their hand up. I have asked these questions for years and in different settings; countries and more. The answer is always similar and a fact is that its not changing over the years. My guess (not a complete guess but based only on people I have had chance to ask!) is that 5% to maybe 15% may have auditing of the database engine itself.

This is not good enough. I spend quite a lot of my time helping companies create useful audit trails in their database so that they know whats happening and who is trying to do wrong to their data. I do this with a toolkit i have created called PFCLATK and this toolkit helps map a policy/event driven plan into settings in the actual databases and includes management and audit of audit and audit of security and even audit of security of audit. GDPR has created an interest in getting suitable audit trails up and running quickly as these are a core benefit to helping with GDPR; i.e. to know who accessed what and to know if you have been breached and more.

With GDPR and other existing data laws and data breach laws we need to rely more these days on audit trails than not. I said to someone in the last weeks; whats the point of planning for a database breach and creating an incident response process and a plan to deal with forensics to allow you to swiftly, calmly and objectively deal with a data breach if/when it happens IF you take no steps now to protect the data in your database. I went on to discuss that you can create an Oracle database security policy ( A document that describes what secure data in an Oracle database looks like to you) and implement it across all databases. You should still create your incident response process BUT also start to plan and implement Oracle security in your databases at the same time; no point as i say to plan for the breach if you do not bolt down the database.

As part of that bolting down of the database it makes absolute sense for one of the first countermeasures to be a rich and useful audit trail.

Audit trails in Oracle have a lot of nuances and issues and strange ways. I will cover some of these here in this blog over the coming weeks in details and show you what I mean; keep a watch for new posts.

You may say what about Unified Audit in 12c; its been around now since 2013 BUT; i have literally come across a small amount of people who use Unified audit and not the standard core audit - bear in mind the amount of people who actually have an audit trail set up and react to it; see above!!. As part of the room show of hands I always also ask if any of the people with their hand up use Unified audit; usually its a resounding no; or perhaps one person says they use it. On the face of it Unified Audit seems to offer advantages; policies; filtered audit BUT it also turns off some existing audit features if you enable in PURE mode; syslog, writing to the OS; it also seemed to have some teething issues in 12.1 but the move back to a real table instead of a secure file in 12.2 maybe makes that better.

PFCLATK also has policies and filtered results and it had them a few years before 12cR1 was released and i was not on the beta of that so i didn't know. I presented at UKOUG many years ago about audit trails and i mused there about policies and context based audit in the database for the core audit.

There are naysayers who say that auditing in the database with its core audit presents two main risks; 1) - The DBA or someone else with power can delete or turn off the audit trails and 2) - There is a massive performance impact to using database audit.

Both of these are not true (well a caveated not true). The attacker (the DBA) can turn off audit or delete or update audit records BUT as part of the audit trail we include audit triggers and also audit of audit so that we can react if that event happens. We can always get back deleted audit from the redo via flashback query or using LogMiner; If the DBA turned off audit trail creation we can also react swiftly and extract using the Incident process we created current actions and also use Flashback or LogMiner to see what changes he made. I believe the risk to having audit in the database (for a short time - I do advocate moving it to a central storage as quick as possible) is outweighed by the benefits of the use of SQL and PL/SQL to report and process the audit rather than terse file search tools and consolidating thousands of files. Also if we compare to the alternatives; network sniffer based technologies; then these also have issues, firstly they only see what flying past on the wire. If you decide to monitor the CREDIT_CARD table and its accessed in a SQL and this is in the TNS network packets then fine BUT if you access the table via a package called SCOTT.CARDS then either you have to know what SCOTT.CARDS does in advance and analyse all code to find access or you place a tap on the database server to try and get that detail from the database!. Secondly these network based technologies may syphon off details that could include data and now this data is stored in a box outside of the database. Database level audit can also include SQL that includes data BUT this is still stored in dictionary tables accessible to SYS and if deleted we can get to back from REDO.

The second issue people always site for not turning on audit in the database as i said is performance and again this can also be site as a reason to use a network blade / software TNS sniffer solution although if you have a lot of network traffic this causes different issues. My answer to the performance issue of enabling audit is that we should only audit that which should not happen; i.e. things that do not matter for performance; if someone accesses static configuration data then we audit DELETE and UPDATE (perhaps INSERT dependant on the design of the data) bit not SELECT as that is likely to be the major action performed thousands of times a day BUT delete and update should hardly ever happen.

We should start our audit trail design / policy with a list of events - "I want to know!" items and then flesh that out - a table is a good structure. The table should include a list of audit events and information on whether we just capture it and hold or we react. If we react how quickly and finally do we create a report (or include in a report) or do we raise and alert and escalate.

This design drives the technical solution; PFCLATK is deliberately written to encase these ideas and to use policy and event based audit that is easy to define and implement quickly in a database. That was the reason to create it; to allow customers to design their events and THEN to translate those into events and alerts in the toolkit.

Back to the performance issue for a second. Often people site performance as a major issue to not implement database level audit BUT at the same time they have triggers creating BEFORE and AFTER images of data changes at the application level. These can create 300% or even 500% impacts on the original action; this heavily depends on the code written into triggers. I have seen some monsters in my time!. Core database audit to audit things that should not happen has a tiny tiny impact.

OK, I will return to audit trail events and nuances and issues very soon as I have a big list of things I would like to talk about here.

Oracle Security Training by Pete Finnigan in 2018

The Merchant Adventurers Hall, York

Are you worried about the data in your databases being stolen? GDPR has just become law across the EU and the UK and affects business in other countries that process EU citizens data. Maybe you store and process credit card details and you fear card details being stolen and cloned. The core fear is that your business processing data in an Oracle database can be at the minimum tarnished by bad media over a breach or even completely ruined if the GDPR is brought to bear on a business.

Securing data in an Oracle database is not complex or rocket science if the task is approached in a methodical manner. You can get one over on the attackers and hackers by being one step ahead of them by finding out all of the key recommendations and pitfalls to fix in advance of an attack.

Oracle is a complex product; probably actually one of the most complex software packages available; it contains literally tens or even hundreds of thousands of objects and each can have their one security settings and parameters and techniques to secure them. It can, at first seem like a minefield BUT it doesn't have to be if you treat securing Oracle in a planned and structured way.

Often Oracle databases and the applications that they host are deployed in a completely insecure way; this is not usually intended but is usually a fact of performance and SLA's come first and security comes last; this hinders the process of securing data as adding security later is not actually so much harder than designing it in (after all Oracle has settings for security on most anything and they can be changed) its just a compromise or its a risk; in terms of perhaps breaking a working database.

Pete Finnigan (Me!) is a sought after speaker and trainer and of course consultant to companies world wide to help them secure their databases. Pete is a pragmatist and favours security solutions that are cost effective and work and do not break existing code. So you can learn from me how to prioritise your security plans to lock and harden your databases without risk. I love to focus on solutions that are possible to 100% implement and that are aimed at least cost for the best increase in security of your data in your Oracle databases. Pragmatic and cost effective.

I will be teaching a range of my companies Oracle Security training classes in the coming months both in-person and also online live teaching via webex. I have a range of Oracle Security classes that are available as public classes on these dates.

In particular I have just agreed to a 3 day training with Oracle University in Istanbul, Turkey on the 15th to 17th October. You can find details on the public training dates page. This event sees me teach my very popular 2 day class - How to perform a security audit of an Oracle database and also my one day class - Oracle Incident Response and Forensics. This is a unique combination of content for this event in Turkey.

I am also teaching my one day class - Secure Coding in PL/SQL in York, UK on the 28th August and also on the 29th my Oracle Incident Response and Forensics. This is my home city and its a great place to visit besides to learn about Oracle Security

I will also be adding dates in Prague, Holland, France and the UK as well for more classes later in the year.

Getting started with Oracle Security does not need to feel daunting and it is always possible to increase the security of your data no matter what you start with. Learning about Oracle security also gives you the insight to work in a better and more secure way going forwards.

To book your place on one of my classes to come and learn from my many years of practical experience in securing data in Oracle databases either visit the public training dates page and click the register links or send me an email and i will be glad to assist.

Oracle Can Generate 6 Password Hashes When a User is Added or Password Changed in 12.1.0.2 and Above

In a 12.2.0.2 database it's possible that Oracle generates 6 different password hashes for one password for one user under certain circumstances when a password is changed or created (user is created). I will layout the 6 different ones first and then for the rest of this post I will focus on one of them; i will tell you which one i want to focus on shortly after i have laid out all six generally.

The normal wisdom in a 10g database would have been that Oracle generates a single password hash for each user when a user is created or it would update that password hash when a users password was changed. This was the password hash stored in SYS.USER$.PASSWORD column and this uses the DES based HASH. The username is the SALT and is concatenated with the PASSWORD and then encrypted with DES CBC using a standard key and once the first round is complete the process starts again using the last input vector as the new key. This means that whilst an encryption algorithm is used (DES CBC) it is actually a one way HASH as there is no way to decrypt the password. The standard method to crack this is to use a cracker and either use a dictionary based approach - i.e. test each password generated from a dictionary of words and compare the generated hash with the one stored in the database. Another possibility is to use brute force; i.e. try a password of A, then B and then C and then AA, AB, AC... and AAA, AAB, AAC and so on until all possible passwords have been tried for the keyspace. A simple PL/SQL password cracker is available here. This is an older version of my PL/SQL cracker; version 1.5; if you have been on two day my training class - How to perform a security audit of an Oracle database - then you would have the latest version which is faster and has more default passwords and more features - A benefit of attending my classes is to get all the latest scripts and tools. I will be teaching this class in my home city of York at the beginning of July; come along! I may release the later version of this cracker on here soon; so watch out for a blog post on that.

So, 10g database, thats one password hash. When Oracle database 11.1 was released Oracle added case sensitive passwords and the new (At the time) SHA1 password hash. This is stored in SYS.USER$.SPARE4 and is identified as the "S:" string. This is just SHA1 on the actual password and with the addition of a SALT to make the password that is stored different to other hashes of the same password. So if i set the SYS password to TEST and then the SYSTEM password to TEST even though the hash is only of the password the hash is different as Oracle generates a random SALT each time a password is hashed. This SALT is also stored in SYS.USER$.SPARE4 with the hashed password. I created a simple SHA1 hash test back in 2007 when 11.1 was released. This script is sha1.sql and is here.

In 11.2 this situation continued; so that DES and the SHA1 hashes are available in SYS.USER$. When 12.1.0.1 was released an additional password hash was added in SYS.USER$.SPARE4 - this one identified by the "H:" - This is an HTTP Digest for use in web based applications authentication. In 12.1.0.1 this could not be turned off; so you got a HTTP digest whether you liked it or not. In 12.1.0.2 Oracle changed the HTTP digest slightly if a multi-tennent database were created. In 12.1.0.1 the hash for common users (default users as well as ones created by you) was visible in the PDB; in 12.1.0.2 this is now only visible in the CBD for common users; in a PDB the HTTP digest is still available for LOCAL users. In 12.2.0.2 the HTTP digest is not created by default anymore BUT can be created with a new additional syntax - I will come back to this in a later post just about this hash. So as of Oracle 12.1.0.1 there were 3 core password hashes.

In 12.1.0.2 added the new SHA2 password algorithm. This is a combination of the PBKDF2 multiple rounds and then a final SHA2 hash. As with the SHA1 in 11.1 this password hash is stored in SYS.USER$.SPARE4 and is the "T:" password hash. Also as with SHA1 in 11g the SALT used is also stored in the same column with the hashed password. More in a later post about this algorithm. So as of 12.2.0.1 whilst the HTTP Digest is normally turned off and the default is that the DES hash is not generated it is possible to get all 4; either through an upgrade or because you use the syntax to create the HTTP digest or because you have set the network authentication in the sqlnet.ora to default to an earlier setting to get the DES hash also.

OK, but thats just 4 password hashes what about the other two. Well the 5th is generated by Oracle internally and not stored in a public place where you can view it. If an account is a default account then every time a password changes (CREATE or ALTER) then Oracle creates an un-salted SHA1 hash and uses it to compare with an internal list to see if the account is a default and whether the password is a default password. Oracle does this to detect default passwords. The hash is not stored as far as I can see BUT the accounts status is updated to indicate that the password is a default. I will come back to this in a more detailed review in another post soon. So this is 5 password hashes generated in 12.1.0.2 and above.

The one i want to focus on for the rest of this blog is the 6th password hash created for a user. This hash is only created if you run a DDL trigger on CREATE and / or ALTER or generally an un-constrained DDL trigger. If you collect the ORA_DES_ENCRYPTED_PASSWORD trigger attribute in the DDL trigger for a password change then this trigger attribute is populated. As the name implies it should be a DES hash or DES encrypted password. If it is indeed encrypted then that also implies decryption. Lets have a look and test the ORA_DES_ENCRYPTED_PASSWORD trigger attribute.

First I installed PFCLATK our Oracle audit trail toolkit into my Oracle 12.2.0.1 database:


PFCLATK: Release 1.7.2.0 - Production on Wed Jun 13 18:07:26 2018
Copyright (c) 2009 - 2018 PeteFinnigan.com Limited. All rights reserved.

SECTION-[1] - Remove existing schemas and users
[1-1] Drop The ATK Admin user
[1-2] Drop the ATK Reports User
[1-3] Run schema drop script
SECTION-[2] - Create the Schema owner ATK (Functional owner)
[2-1] Create ATK Schema
[2-2] Perform ATK Grants
SECTION-[3] - Create schema owner ATKD (Data owner)
[3-1] Create ATKD schema
[3-2] Perform ATKD Grants
...
SECTION-[17] - Install the user policies
SECTION-[18] - Install the user factors
SECTION-[19] - Install the PUL jobs/policies
SECTION-[20] - Clean up...
[20-1] Purge the PFCLAudit Audit table
[20-2] Print out existing audit, except OLS and DV
SQL>

This gives me a very detailed policy driven audit trail in my database that allows certain actions to be raised as exceptions and alerts fired. We are not going to explore the audit trail in this blog post but i will come back and discuss this toolkit in a future blog post. We use it to help customers through consulting engagements develop and deploy a rich policy driven audit trail with alerts and escalations quickly and easily. Currently it's a PL/SQL based command line tool but a GUI is coming soon to allow customers to manage, choose and design their own policies or to amend or simply use ours. There will be an admin GUI and a dashboard to allow you to monitor multiple databases. The toolkit can be deployed to a target database to audit it or it can be be deployed to a central database so that all target databases can send their audit trails to it for centralised storage and reporting. More soon here in the blog on the toolkit; but if you are interested now to have us help you design and deploy very useful audit trails email info@petefinnigan.com for availability and costs.

We installed PFCLATK because it already knows how to collect the ORA_DES_ENCRYPTED_PASSWORD field when a user is created (password first set) or when the password is changed. So lets create a user called TEST and set his password to A:


SQL> create user TEST identified by A;

User created.

SQL> col err_sql for a40
SQL> col ora_dict_obj for a40
SQL> col ora_des for a32
SQL> set lines 220
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee

SQL>

This doesn't tell us much so far. Lets change the password to B, then C, then D and see what happens:

SQL> alter user TEST identified by B;

User altered.

SQL> alter user TEST identified by C;

User altered.

SQL> alter user TEST identified by D;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee

SQL>

Now we are getting somewhere. As you can see the first 16 characters of the DES encrypted password change as each new password is set BUT the last 16 characters do not. This implies that a short password is only encrypted into the first 16 characters (8 bytes) and a longer one into the next. But before we explore that rollover what if we set the same 4 passwords for the user ORABLOG:

SQL> alter user ORABLOG identified by A;

User altered.

SQL> alter user ORABLOG identified by B;

User altered.

SQL> alter user ORABLOG identified by C;

User altered.

SQL> alter user ORABLOG identified by D;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee

8 rows selected.

SQL>

The same 4 passwords were set for ORABLOG as i set for the user TEST. The same 4 DES encrypted passwords were created. This means that the encryption is just for the password and the username is not involved and also it means that there is no SALT. What if we now set the passwords using lower case a, b, c and d:

SQL> alter user TEST identified by a;

User altered.

SQL> alter user TEST identified by b;

User altered.

SQL> alter user TEST identified by c;

User altered.

SQL> alter user TEST identified by d;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee
alter user TEST identified by * TEST a6b09e2de7820bedaad3b435b51404ee
alter user TEST identified by * TEST 94326650a42f2bd8aad3b435b51404ee
alter user TEST identified by * TEST e6ea6b41dedd8560aad3b435b51404ee

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST cd961c7f88ea81c7aad3b435b51404ee

12 rows selected.

SQL>

This is interesting as the encrypted passwords are not the same as the upper case ones. This means that the encryption used must be CASE SENSITIVE unlike the DES algorithm used in the database password hash from Oracle 6 upwards; actually the algorithm supports upper and lower case and specials etc but the double quote syntax was needed to access that. You will have noticed that the last 16 characters (8 bytes) of the DES encrypted password is always the same; before we play with that lets test if this also works for SYS.

This is interesting as the reason I first looked into this area was to see if there was any way I could simulate password management in any other way than the password management built into Oracle; this built in password management does not work for SYS; so i was looking to see if i could simulate it in any other way. More on this in another blog post and i will show you what I worked out for SYS. So lets try this for SYS:

SQL> alter user sys identified by a;

User altered.

SQL> alter user sys identified by b;

User altered.

SQL> alter user sys identified by c;

User altered.

SQL> alter user sys identified by d;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee
alter user TEST identified by * TEST a6b09e2de7820bedaad3b435b51404ee
alter user TEST identified by * TEST 94326650a42f2bd8aad3b435b51404ee
alter user TEST identified by * TEST e6ea6b41dedd8560aad3b435b51404ee

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST cd961c7f88ea81c7aad3b435b51404ee
alter user sys identified by * SYS a6b09e2de7820bedaad3b435b51404ee
alter user sys identified by * SYS 94326650a42f2bd8aad3b435b51404ee
alter user sys identified by * SYS e6ea6b41dedd8560aad3b435b51404ee
alter user sys identified by * SYS cd961c7f88ea81c7aad3b435b51404ee

16 rows selected.

SQL>

So, yes it works for SYS and the results are the same as the passwords for TEST. So next, lets truncate the audit trail and lets try extending the password from A to 16 A's and see what happens:

SQL> truncate table atkd.pfclatk_audit;

Table truncated.

SQL> alter user TEST identified by A;

User altered.

SQL> alter user TEST identified by AA;

User altered.

SQL> alter user TEST identified by AAA;

User altered.

SQL> alter user TEST identified by AAAA;

User altered.

SQL> alter user TEST identified by AAAAA;

User altered.

SQL> alter user TEST identified by AAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAAAAA;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST e59f3bfac2082d77aad3b435b51404ee
alter user TEST identified by * TEST f984b70b43584870aad3b435b51404ee
alter user TEST identified by * TEST 00629bf7623f9c45aad3b435b51404ee
alter user TEST identified by * TEST 5a52d459aeb64441aad3b435b51404ee
alter user TEST identified by * TEST 8ee7deffee86298baad3b435b51404ee
alter user TEST identified by * TEST 1e3528942decf14daad3b435b51404ee
alter user TEST identified by * TEST 1e3528942decf14da0fe3035825f3637
alter user TEST identified by * TEST 1e3528942decf14de59f3bfac2082d77
alter user TEST identified by * TEST 1e3528942decf14df984b70b43584870
alter user TEST identified by * TEST 1e3528942decf14d00629bf7623f9c45

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST 1e3528942decf14d5a52d459aeb64441
alter user TEST identified by * TEST 1e3528942decf14d8ee7deffee86298b
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d

16 rows selected.

SQL>

This shows some interesting information and facts:

  • First the known string in the second 16 characters (8 bytes) aad3b435b51404ee remains the same until the password is 8 characters long. This means that all passwords 7 characters and less are encrypted into the first 16 characters (8 bytes) and the second 16 characters (8 bytes) remain the standard blank value. This is odd, why 7 characters and not 8 which is a standard input to DES? possibly the password is a standard C string with a NULL terminator and the terminator is also encrypted?

  • After and including the 8 character password the second 16 characters change. This is interesting as if we take the first 16 characters of the password A and the second 16 characters of the password AAAAAAAA (8 A's) the they are the same a0fe3035825f3637. So the second half of the algorithm used is the same as the first half. So it does not encrypt AAAAAAAA (8 A's) as one string; it takes the first 7 A's and encrypts to 1e3528942decf14 and then encrypts the 8th A on its own as a0fe3035825f3637 so is the same password DES string as a single A. So the password is definitely split before encryption and the same encryption is used on each half

  • Passwords 7 (7 A's) and onwards all have the same first half no matter what as they are all the same password half (AAAAAAA).

  • When we get to a 14 character password the second 16 characters of the encrypted password are the same as the first i.e. we have AAAAAAA + AAAAAAA = 1e3528942decf14d + 1e3528942decf14d

  • A password that is 15 characters or higher; its clear that the characters above 14 are ignored!!


If i repeat the output with the passwords also its a little clearer:

alter user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee 1 - (A)
alter user TEST identified by * TEST e59f3bfac2082d77aad3b435b51404ee 2 - (AA)
alter user TEST identified by * TEST f984b70b43584870aad3b435b51404ee 3 - (AAA)
alter user TEST identified by * TEST 00629bf7623f9c45aad3b435b51404ee 4 - (AAAA)
alter user TEST identified by * TEST 5a52d459aeb64441aad3b435b51404ee 5 - (AAAAA)
alter user TEST identified by * TEST 8ee7deffee86298baad3b435b51404ee 6 - (AAAAAA)
alter user TEST identified by * TEST 1e3528942decf14daad3b435b51404ee 7 - (AAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14da0fe3035825f3637 8 - (AAAAAAAA) (2nd 16 chars change)
alter user TEST identified by * TEST 1e3528942decf14de59f3bfac2082d77 9 - (AAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14df984b70b43584870 10 - (AAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d00629bf7623f9c45 11 - (AAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d5a52d459aeb64441 12 - (AAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d8ee7deffee86298b 13 - (AAAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d 14 - (AAAAAAAAAAAAAA) (2nd 16 = 1st 16 chars)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d (15 - (AAAAAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d (16 - (AAAAAAAAAAAAAAAA)

16 rows selected.

So the passwords seem to be split into 7 character strings and the separately DES encrypted or hashed? and then concatenated again as a HEX string. The testing that i have done above is in 12.2.0.1. Lets try the same 16 passwords in 11.2.0.4:

SQL> create user test identified by A;

User created.

SQL> col err_sql for a40
SQL> col ora_dict_obj for a30
SQL> col ora_des for a32
SQL> set lines 220
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ------------------------------ --------------------------------
create user test identified by * TEST a0fe3035825f3637aad3b435b51404ee

SQL>

In 11.2.0.4 the hash is exactly the same as in 12.2.0.1 for a password of A. We can therefore rely on the values to analyse the passwords of users as they are changed. As we can do this in a much better way with a password verify function and the password management settings this is sort of redundant but as i said the reason i started to look into this was to do password management of sorts for SYS; more of that in a later post.

In summary we could easily tell if a password is not at least 8 characters long as the last 16 characters of the ORA_DES_ENCRYPTED_PASSWORD are known if its length is seven or less. We can see that the password is split into 7 character blocks so in rare cases we might be able to detect repeat patterns or passwords all of the same character. There is no SALT but there is case sensitivity.

If we could test the actual algorithm and detect its implementation then we could perhaps decrypt a password up to 14 characters. But if it were decrypted at 14 characters but the password were 16 then it would be simple to test that the password is not cracked so therefore must be longer. Therefore we could brute force the extra characters easily - so a 16 character password would be like cracking a 2 character password if we knew the first 14 only.

The testing implies the same key is always used as the first 16 characters were the same as the second so it implies its not a random key used but a fixed key; knowing Oracle thats probably 0123456789ABCDEF. A bit of testing and trial and error could possibly reveal the algorithm used so that the password could be decrypted but i have not done this.

So, 6 password hashes for each user in some circumstances in 12.2.0.1. There are other cases as well of encrypted passwords such as LINKS.

Need Help with Oracle Security GDPR Training and Services

I talked here a few days ago about GDPR in general and I also published my slides from my talk GDPR for the Oracle DBA. We have been helping clients secure data in their Oracle databases and training people for many years.

GDPR does not call for much that is different than we have been helping people with for many years in terms of Oracle Security but it adds some new elements and also adds a focus to securing personal data. We have added a GDPR services page to our website that lists at a high level the services that we can help you with for GDPR. The services page is also available from the services menu on the home page of our website. As part of the services offering for GDPR we have also created a new one day training class called Oracle Security for GDPR. This is a one day class to get your Oracle staff and your security staff who need to understand Oracle Security up to speed with GDPR and Oracle security. If you would like to book us to come to your site to teach this please get in contact by emailing info@petefinnigan.com for more details or to book a class. Alternately watch out for public training dates on our site.

Grants WITH GRANT

The ability to make grants on objects in the database such as tables, views, procedures or others such as SELECT, DELETE, EXECUTE and more is the cornerstone of giving other users or schemas granular access to objects. I say granular as there is always the possibility to do context based security in many ways such as using products such as Virtual Private Database (VPD) or Oracle Label Security (OLS) or by hand coding into the packages or views; lots of options are possible to make access down to the row level, column level or even cell level. But the focus of this blog post is the ability to do GRANT {something} ON {something} TO {something} WITH GRANT OPTION; This has quite a few issues.

The biggest issue for me is when this is done the security moves out one layer from the owner of the object; so becomes harder to control and understand the true scope of the security. Unfortunately the reason often this syntax is used is to allow the creation of a view in SCHEMA_A with access to a table in SCHEMA_B and then with the requirement to grant access to the view in SCHEMA_A to USER_C. This cannot be done unless the access to the table in SCHEMA_B is granted WITH GRANT OPTION; Lets see an example:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL>
SQL>
SQL> create user schema_a identified by schema_a;

User created.

SQL> grant create session, create view to schema_a;

Grant succeeded.

SQL> create user schema_b identified by schema_b;

User created.

SQL> grant create session, create table to schema_b;

Grant succeeded.

SQL> create user user_c identified by user_c;

User created.

SQL> grant create session to user_c;

Grant succeeded.

SQL> alter user schema_a quota unlimited on users;

User altered.

SQL> alter user schema_b quota unlimited on users;

User altered.

SQL>


Now connect as SCHEMA_B and create the table that SCHEMA_B will create a view on and grant SELECT to SCHEMA_A:


SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> create table test01 (col_01 number);

Table created.

SQL> insert into test01 values ('1');

1 row created.

SQL> insert into test01 values ('2');

1 row created.

SQL> commit;

Commit complete.

SQL> grant select on test01 to schema_a;

Grant succeeded.

SQL>


Now we have a table in SCHEMA_B connect as SCHEMA_A and create the view and test it:


SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> create view view01 as select * from schema_b.test01;

View created.

SQL> select * from view01;

COL_01
----------
1
2

SQL>


Now the final step; grant SELECT on the view to USER_C and test access to the data is SCHEMA_B.TEST01 via SCHEMA_A.VIEW01:


SQL> grant select on view01 to user_c;
grant select on view01 to user_c
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SCHEMA_B.TEST01'


SQL>


This is where the problem lies; so in order to fix this we must instead grant select on SCHEMA_B.TEST01 to SCHEMA_A WITH GRANT OPTION and then it works:


SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;

Grant succeeded.

SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on view01 to user_c;

Grant succeeded.

SQL>


So now it works and USER_C can access the data in SCHEMA_B.TEST01 via SCHEMA_A.VIEW01:


SQL> connect user_c/user_c@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> select * from schema_a.view01;

COL_01
----------
1
2

SQL>


So this is how to have tables in one schema and access views in another and then grant select on the tables accessing the views to a third user. The problem now is that the security on SCHEMA_B.TEST01 table is no longer just controlled by SCHEMA_B but SCHEMA_A can also now make grants on SCHEMA_Bs table. First connect as SYS and create some more users to become GRANTEEs:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.

SQL> grant create session to ua identified by ua;

Grant succeeded.

SQL> grant create session to ub identified by ub;

Grant succeeded.

SQL> grant create session to uc identified by uc;

Grant succeeded.

SQL> grant create session to ud identified by ud;

Grant succeeded.

SQL>


Connect as SCHEMA_A (the grantee of the WITH GRANT OPTION) and pass on grants:


SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;

Grant succeeded.

SQL> grant select on schema_b.test01 to ub;

Grant succeeded.

SQL>


Now connect as user UA and grant WITH GRANT OPTION to user UC and then connect to user UC and make a grant to user UD:


SQL> connect ua/ua@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to uc with grant option;

Grant succeeded.

SQL> connect uc/uc@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ud with grant option;

Grant succeeded.

SQL>


OK, what a messy chain of grants that we have done starting as SCHEMA_A who was passed the right to GRANT on the privilege granted to it. Then UA can do the same and finally UC can also pass on the grant. So lets connect back as SYSDBA and check out the grants:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 16:00:51 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_A UB X
UC UD G
UA UC G
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>


This script shows the grants done above and shows the GRANTEE and GRANTOR and also whether the grant is normal "X" or is done WITH GRANT OPTION "G". What if you go full circle and log in as user UD and grant select back to SCHEMA_B WITH GRANT OPTION:


SQL> connect ud/ud@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to schema_b with grant option;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 16:08:43 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_A UB X
UC UD G
UA UC G
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>


Nothing changed so even though the GRANT from UD to SCHEMA_B said "Grant Succeeded" that clearly was not true. So what next, Oracle says that if you REVOKE a GRANT done WITH GRANT it should unravel all of the child grants to ensure that its all cleaned up. So log in to SCHEMA_B and revoke the original grant to SCHEMA_A with the WITH GRANT OPTION and does it unroll the other grants?


SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> revoke select on test01 from schema_a;

Revoke succeeded.

SQL>


Lets check the grants that still exist; if any:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 16:35:31 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>


All gone!!, very clever, Oracle even knew about all of the grants made by SCHEMA_A to other users and even the grants they then made to yet further users. As I like to know how things work; is there any clues as to how Oracle does the revokes? First lets put all of the grants WITH GRANT back again:


SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;

Grant succeeded.

SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;

Grant succeeded.

SQL> grant select on schema_b.test01 to ub;

Grant succeeded.

SQL> connect ua/ua@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to uc with grant option;

Grant succeeded.

SQL> connect uc/uc@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ud with grant option;

Grant succeeded.

SQL>


So if we check DBA_TAB_PRIVS is there any special marker to help it revoke recursively; no:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select * from dba_tab_privs where table_name=''''TEST01'''''
Executing Query [select * from dba_tab_privs where table_name='TEST01']
GRANTEE : UA
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : SCHEMA_A
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : UB
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : SCHEMA_A
PRIVILEGE : SELECT
GRANTABLE : NO
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : SCHEMA_A
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : SCHEMA_B
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : UC
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : UA
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : UD
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : UC
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>


Oracle doesn't need it as it could do a recursive query on the base tables of this view and look for grantor and grantee relationships and where the GRANT was WITH GRANT or GRANTABLE in DBA_TAB_PRIVS. So this option when doing granular grants on objects such as tables is a mess; it would be better to avoid this. There are two main reasons that WITH GRANT gets used; the first is to solve the view problem I showed at the start of this post and the second where a user becomes a "security user" and gets all grants WITH GRANT so that he can log in and then make grants to other users.

I would say that I don't like both of these options. Number 1 [the VIEW issue] can be solved by creating the view in the same schema as the table so that the GRANT with WITH GRANT is not needed; in fact no grant is needed as the OBJECT OWNER principal takes over and requires no grants. The security in having the data in one schema and the views in another (presumably with a limited view set on the data) is ruined by having to give WITH GRANT to that schema on the base table anyway so whilst if you block that schema and SQL injection is not possible then its OK BUT the fact still remains that this schema can now control the security of another schema; I don't like that; I prefer the risk that the view and table are in the same schema and no grants WITH GRANT are needed and the only grants made are those that expose the VIEW not the table and these are normal grants.

The Number 2 issue - the security user/schema is not a good way to manage grants. There are multiple options in this case to make grants :

  • Use a DBA %ANY% privilege to make grants

  • Use SYSDBA to make grants

  • Connect as the schema to make grants

  • Create a granting schema (number 2 above) to make grants

  • Connect to the schema using proxy and make grants


Personally I prefer the proxy approach as it allows access to the schema without needing to give out the schema password; We can also audit all actions as the proxy without affecting any schema level audit and we don't need to write schema.object in code. Using WITH GRANT simply increases the attack surface.

For extra credit; users can get the grants more than once:

SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;

Grant succeeded.

SQL> grant select on test01 to ua;

Grant succeeded.

SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 18:38:50 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_B UA X
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

So the user UA has the SELECT twice once WITH GRANT and once not; so if we revoke the grant to SCHEMA_A it will also remove one of these grants:

SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> revoke select on test01 from schema_a;

Revoke succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 18:52:47 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_B UA X

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

If we instead try a recursive grant WITH GRANT:

SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.

SQL> grant select on test01 to schema_a with grant option;

Grant succeeded.

SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;

Grant succeeded.

SQL> connect ua/ua@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to schema_a with grant option;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 18:56:30 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
UA SCHEMA_A G
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

So we granted select from SCHEMA_B to SCHEMA_A and then from SCHEMA_A to UA and then from UA to SCHEMA_A. So SCHEMA_A has the grant twice but from two users but the WITH GRANT has come full circle. What happens when we revoke?

SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> revoke select on test01 from schema_a;

Revoke succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 19:03:35 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

No matter how convoluted the grants WITH GRANT get Oracle unravels them; this is not really the point; that Oracle can unravel them. The point is not to get into this mess in the first place having grants allowed from a second schema; this is then uncontrollable by the original schema and its an increased attack surface. Try not to use these.

GDPR

I posted a couple of days ago my slides from the recent UKOUG Northern Technology day in Leeds where I spoke about GPPR for the Oracle DBA. I said then that i am also preparing a service line for helping customers specifically with the problem of Oracle Security for GPDR (compliance). I am working on releasing information for a new one day class that i will be teaching called Oracle Security for GDPR. A two page pdf flyer will be added to our training page in the next couple of days. I will also announce the GPDR service lines that we will be adding soon in the next few days. We have helped customers already with work towards GDPR compliance with work specifically in the areas of Oracle security that we can cover; these include:


  • Detailed security audits performed of customers Oracle database

  • We have used PFCLScan to audit a large number of customer databases

  • Help creating a database security policy for customers

  • Breach notice policy creation

  • We have been involved with a number of incident response and forensics

  • We have helped with audit trail design, creations and implementations

  • We have of course helped with training by teaching people on our classes about Oracle security

  • We have helped with masking of data

  • Many more...



As I said I will be publishing our range of oracle security services specific for GDPR in the next few days and also talking more in details here about that and also about our new one day training class Oracle Security for GDPR that we have created.

I wanted to talk a little here about GDPR in general as we have spent a lot of efforts in PeteFinnigan.com Limited to work towards GDPR compliance. We have done data assessments to locate all personal data they we hold and documented where we hold it and why and the reason for holding it. This was an interesting exercise. A lot of people state how GDPR is a good for business - For instance this article propounds the virtues of stronger security because of GDPR.

There is a flip side though. GDPR also can cause some security weakness in my opinion if you put the need to ensure that you are compliant with GDPR above the needs of collecting what is now classified as personal data. IP addresses and similar details are now classed as personal details. There is a lot written about this; some say that including IP Addresses in your Apache web logs and error logs is collecting personal data and therefore requires you to be GDPR compliant with this; so if someone were to breach your website and steal an apache weblog with tens of thousands of IP Addresses this would be a major data breach and much be reported and you would have to notify the owners of the IP address. If we argue that we keep the access_log for needs of security then maybe its OK and a valid reason to keep it and to keep thousands of what is personal data. What about running a webalizer report to check out how many people visit your website; thats not a reason to keep this data. You could argue that you never look at the access_log but you still collect that details. You could also argue that the IP address is not personal; problem is it is for some people. If you run a whois report or you combine the ip addresses in the access_log with personal search details from the google page that brought them to the site and maybe combine with a website account (http passwd) then its a problem. Some websites I looked at say its OK to keep this data and some say no its not and you run the risk of a 20M Euro fine.

This problem basically means that whilst some articles suggest that GDPR is a good thing for businesses its could also be a bad thing if you decide the risk of a GDPR breach and a 20M Euro fine is worse than the need to keep the server logs for webalizer then this can lead to less security - a breach that occurs with no logs is an undetected breach.

You may have noticed that at the top of most pages on this website there is a "number of visitors online" field. This is generated also by collecting every unique IP Address for a short period of time (30 minutes) and then checking how many unique IP addresses are on line; this is also a GDPR problem so we decided to use still collect IP addresses BUT to anonymise them as we dont care what the IP Address is we just care to count unique visits; So this took code changes to a number of pieces of software built into the website to achieve this. The same intermediate approach can be done with Apache logs or security logs - to anonymise IP Addresses and others data.

Also we have a forum on the website and this had many hundreds of entries that people have posted over the years; these are also personal details. So instead of trying to ask each person for permission again we went through and anonymised all of the entries to change all IP addresses to dummy, all emails to me, all names to me and so on. This had to be repeated for comments in blogs for the same reason and also in a number of other places.

We also conducted Cookie audits on all of our websites using PFCLCookie our tool that can be used to locate cookies in websites as they are also considered personal data. We have eliminated all cookies that our sites created.

We also never do email marketing so this is a big area that causes you to need to comply and register with the ICO; we don't do this so its not a problem.

We also updated our privacy policy and split it into a separate privacy policy, cookie policy and legal policy. We also updated our security policies and also worked to ensure we only collect personal data for business reasons of record keeping and accounts. This is an exception to GDPR. Actually trying to understand whether you need to comply with GDPR is not simple. The ICO in the UK has a tool on its website (a questionnaire) that allows you to see if you need to register. I went through this tool and was reasonably sure that we didn't need to register. I was sure we didn't need to comply with the old data protection act. In the end i had to call the ICO to be sure; no we didn't need to register but as suggested on the ICO website you can register anyway. So even if you are convinced that you do not need to register it makes sense to do so; to show that you are taking GDPR seriously BUT you still have to treat all data with GDPR in mind.

I have been watching others attempts at GDPR; here are some examples:


We currently store some of your personal data so that we can keep you informed of any new initiatives, information or opportunities, plus invitations to our events and updates on our upcoming programmes. The data we usually hold are your contact details.

We will always tell you how we use your data, and we will make sure we collect and store your data safely and securely. We will never pass your personal information to any third party without your specific consent.

If you are happy that we keep your details to enable us to contact you with relevant information you need to do nothing further. If you would like your details to be removed from our database, please do so by clicking...


The above is an email i received two days before GDOR became live. I never registered with this security company; I didn't consent to receive their marketing emails and they don't get consent; The idea is that it has to be conscious not opt in by default. because this was sent two days before GDPR its not strictly wrong in my opinion but as soon as they send me a marketing email it is wrong. Another email i received on the 1st of June, so after GDPR:


...
We still intend on meeting " and exceeding " those goals; however, with GDPR coming into effect, we want to make sure we are abiding by the law (and ensuring you continue to see our emails in your inbox).

If you’d like to continue receiving our emails, including the latest .... webinars (and we hope you will!), please...


After GDPR, a marketing email asking me to re-subscribe to their marketing!! There have been many, many more similar ones.

GDPR compliance is an on going process and its hard work and a lot of work.