Search

Top 60 Oracle Blogs

Recent comments

February 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...)

Error message of the day: OUI-25023 and the FQDN

It’s been a long day with many problems around a Grid Control installation, including (but not limited to) corruption of the repository database, bugs in OUI when it comes to deinstalling the Oracle Management Server, lots of files left over by the weblogic “uninstall.sh” script and many more. Some of the error messages were quite misleading, and OUI-25023 just was one too many. What happened?

Earlier today I was trying to install the 64bit 11.1.0.1 agent on an 8 node cluster. After an initial headache (see below) it worked ok. However, I couldn’t resist mentioning OUI-25023. Here’s the complete story.

I downloaded the 11.1 agent for linux x86-64 as per the GC 11.1 documentation and deployed it to my fresh-installed management server. The OMS is on Solaris SPARC, and Grid Control doesn’t supply agents for a different platform. However, the security experts have locked the oracle account down on the cluster which ruled out the “agent push” scenario. I then opted for the installation via a response file, as described in the documentation.

The idea is that you set a number of variables in a response file “additional_agent.rsp” to inform the installer about your desired configuration. If memory serves me right then there was no way to run OUI in GUI mode, it had to be a silent installation.

Amongst the variables you need to specify the cluster nodes. I dutifully filled that information in, using the {“node1.example.com”, “node2.example.com”, …, “node8.example.com”} – i.e. the fully qualified domain name as per the documentation.

Starting the installer with the -silent and -responseFile /path/to/additional_agent.rsp options failed:

$ ./linux_x64/agent/runInstaller -silent -responseFile /u01/app/oracle/stage/agent_11.1.0.1/linux....
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 150 MB.   Actual 1662 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 16415 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-02-15_03-15-10PM. Please wait ...
*** Check for updates ***
*** Select Installation Type ***
*** Check Prerequisites ***
*** Specify Oracle Management Service Location ***
*** Customize Ports ***
*** Review ***

ERROR: OUI-25023: The local node is not selected for installing this product. Include the local node in the cluster list or perform the installation on the nodes on which the install is to be performed.Also note that the cluster nodes should be specified in non FQDN format.

$

I was pretty sure that my node_list included my first node… Pinging the node and DNS access also worked (I wrote about that in a previous post). So a little research on Metalink revealed this gem:

OUI-25023 When Trying To Install A Patchset On RAC [ID 394868.1]

So contradicting the error message (…FQDN format…) I needed to specify the node_list as in the inventory: without the “example.com” bit. Once that was changed in the response file, the installation completed ok.

HTML5 heading for an early grave?

I was very dismayed when I read this yesterday.

A number of news services have done the rounds since, including this today on Slashdot.

I think it’s all a bit depressing really. So we have another 3-4 years to wait for the final spec to be sorted. Then of course we have to wait for the spec to be implemented (badly) by all the necessary browsers. Of course they are already supporting various bits of it, but their implementations will no doubt change along the way and cause untold numbers of apps to break with them. Then we have to wait for everyone to upgrade their browsers. So what is the real date that HTML5 is go? 2020?

All of a sudden native apps seem kinda appealing and Flash doesn’t seem quite as evil as it used to be. :(

Cheers

Tim…

ORA-29913

External tables as a substitute of loading tables through SQL*Loader have become more popular over the last couple of years – which means questions about Oracle error ORA-29913 have also become more popular. So what do you do about something like this:


SQL> select * from extern;
select * from extern
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXTERN_17063.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_LOADER", line 19

The commonest reason for ORA-29913 is simply that Oracle can’t open the operating system files it’s supposed to use. This may be the input data files or the various output files (the “bad”, “discard” and “log” files) that it needs to produce. The reason for failure may be that the directory doesn’t exist, or that the Oracle executable doesn’t have permission to access the directory appropriately, or that the file itself doesn’t have the necessary permissions.

In the example above it looks as if Oracle can’t write to the required log file which, as the “OS error” line suggests, may be a problem with the (unspecified) directory or with the named file (EXTERN_17063.log) in that directory.

So, a few quick checks when you see this error when trying to work with external tables:

    Check your ‘create directory … ‘ statements
    Check that the directories have appropriate permission for Oracle to read and write to them
    Check that there isn’t a problem with existing file permissions

I have a piece of framework code for external tables, and its most important feature is catering for all the output files (that I am aware of), and separating the directories for the inputs and the outputs.

define m_filename = 'd11g_ora_1820.trc'
define m_file_dir = 'C:\oracle\diag\rdbms\d11g\d11g\trace'
define m_log_dir  = 'c:\working'

drop table ext;

create or replace directory ext_tab as '&m_file_dir';
create or replace directory log_dir as '&m_log_dir';

create table ext(text_line varchar(4000))
organization external
(
	type oracle_loader
	default directory ext_tab
	access parameters (
		records delimited by newline
		discardfile	log_dir:'read_trace_dis.txt'
		badfile		log_dir:'read_trace_bad.txt'
		logfile		log_dir:'read_trace_log.txt'
		characterset us7ascii
		fields
			rtrim
			reject rows with all null fields
		(
			text_line	(1:4000)	char
		)
	)
	location ('&m_filename')
)	reject limit unlimited
;

As it stands this has been set up to define a standard trace file on my laptop as an external table; it’s a framework I use sometimes on customer sites where I have lots of database privileges but don’t have any rights to see the server: sometimes you really need to see the trace files. (A nice little feature that’s appeared in 11g, by the way, is that you don’t need to work out names for trace files any more, you just need to get at column v$process.tracefile)

A couple of footnotes:

  • If you don’t specifiy a “log” directory as I have then all the output files go into the “default” directory which, for many people, is likely to be the directory where you had your datafiles. (You could have different directories for the “bad” and “discard” files, of course.)
  • The various log files are appended to (not replaced) every time you query the external table – and it’s very easy to forget this and let them become very large (which means that you could get ORA-29913 because the size of an output file exceeds the O/S limit, or has filled the device).
  • If you don’t specify names for the output files then they will contain the name of the external table and the process ID of the session’s shadow process. The presence of the proces ID does reduce the risk of a single output file getting too big, of course. (I haven’t checked what this does with shared servers, but the typical use of external tables means you probably shouldn’t be querying them through shared servers)
  • The default names of the output files varies with version – until 11g I think it was always {table_name}_{process_id}.{type}, but my 11g sandbox seems to introduce another number in the middle of the name to give:  {table_name}_3168_{process_id}.{type}

Got to stop there, the plane’s about to push back – and it’s “all electronics off”. Spelling and grammar may be corrected this evening.

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).

UnConference - Jonathan Lewis

Media: 
See video

 How to become an Oracle expert.

Furies of Calderon…

I was getting a bit of Dresden Files withdrawal, so I decided to give Jim Butcher‘s other series (Codex Alera) a go, starting with Furies of Calderon.

It’s very different to the Dresden Files style. For a start, it’s not in first-person, so you don’t feel like you are in the head of any specific character, but you get a more rounded view of the other characters. Second, there is no magic, although I guess you could call the furies a type of magic. Third, the books are big. Speaking as a born-again-reader, I’m still a bit daunted by large books.

Despite the obvious differences, I really enjoyed it. I will no doubt start on the second book in the series some time today.

Cheers

Tim…

Calculating Overlapping Hours for Labor Transactions

February 15, 2011 There is an interesting problem in the ERP platform that I support.  When production floor workers begin working on a particular job, the employee “clocks into” the job in the computer system.  When production floor workers stop working on a particular job, the employee “clock out of” the job in the computer [...]

It’s Conference Season!

My favorite mode of life is being busy doing something that I enjoy and that I know, beyond a doubt, is the Right Thing to be doing. Any hour I get to spend in that zone is a precious gift.

I’ve been in that zone nearly continuously for the past three weeks. I’ve been doing two of my favorite things: lots of consulting work (helping, earning, and learning), and lots of software development work (which helps me help, earn, and learn even faster).

I’m looking forward to the next four weeks, too, because another Right Thing that I love to do is talk with people about software performance, and three of my favorite events where I can do that are coming right up: