Saturday, January 31, 2009

Security through obscurity - object names


This post was inspired by question in one of Latvian forums - can we use only numbers in column names. Of course we can do it using quotes but that's starting point into dangerous path of how to write unmaintainable code. But why stop only with tables like C34 and columns 1, 2, 3? There are ways beyond that!
So let's start with the same table names differing only in upper/lower case.
SQL> create table A (big number);
Table created.
SQL> create table "a" (small number);
Table created.

So what are our tables A and a:
SQL> desc A
Name Null? Type
----------------------- -------- -------
BIG NUMBER
SQL> desc a
Name Null? Type
----------------------- -------- -------
BIG NUMBER

A bit strange, isn't it? But that is because all nonquoted identifiers are converted to uppercase so we have to be precise:
SQL> desc "a"
Name Null? Type
----------------------- -------- ------
SMALL NUMBER

But we can make things even more obscure! For example how about table and/or column name as space?
SQL> create table " " (A number, "a" number);
Table created.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
a

A
3 rows selected.

Previous example with 2 rows instead of reported three and next with lost columns definitely could make some nervous people start searching for Oracle bugs.
SQL> create table b (" " number, "  " date);
Table created.
SQL> desc b
Name Null? Type
----------------------- -------- ------
NUMBER
DATE

But we can make things better. Why should we use only symbols on keybord? Remember the nice possibility of ALT key and numeric keypad keys. ALT + these keys gives us symbols according to their ASCII values, so just use your imagination. For example ALT+223 gives me this:
SQL> create table abnormal ("▀" number);
Table created.
SQL> desc abnormal
Name Null? Type
----------------------- -------- -------
▀ NUMBER

Nice little black square.
Next level might be making some columns a bit like hidden.
SQL> create table persons (name varchar2(10), "SYSDATE" date);
Table created.
SQL> insert into persons values ('John', sysdate-1);
1 row created.
SQL> select sysdate from dual;
SYSDATE
---------
31-JAN-09
SQL> select name, sysdate from persons;
NAME SYSDATE
---------- ---------
John 31-JAN-09

So where our column sysdate was gone? We inserted value sysdate-1 and got back the same sysdate? Although our column was in upper case, so our query column should be used? Naah, we should enclose it in quotes.
SQL> select name, "SYSDATE" from persons;
NAME SYSDATE
---------- ---------
John 30-JAN-09

So next time when you are starting to think about table name like C12, numeric column names, column names in quotes or something like that, don't be shy! Use the complete power of obscurity, so making your db more secure. If you'd try hard enough, you'd reach perfection when obscurity is so great that nobody, even you, won't be able to understand anything ;)
And I wasn't even suggesting to replace DUAL in your schema ;)

Friday, January 09, 2009

About experience

Just a few days ago Ryan Watson mentioned one of my articles in his blog post. It really wouldn't be anything important to write about but the first comment by Kevin really amused me and the comment was "Who is this guy and why is he qualified to comment on databases."

Blahhh... My answer was "[..] why does it matter and isn't better just to make reasonable pros and/or cons about the subject?" and Kevin answered with "Experts carry more weight than a novice; a senior Oracle admin with 20 years of experience has more experience to draw on. This is why a senior DBA will make $100 k and novice $20 k."

This was something so immediate and direct statement that let me started to think more about this subject. How much experience means to me? How much it means to other people (virtually) around me? Probably we all know one of the brightest stars in this area good old Don with one of his latest performances here, but undoubtely for all of us - if we don't know other people and they say something, their experience somehow matters. So how much does it matter for me?

1. I definitely have positive experience with some people and know their knowledge and writing style. They have credit in my eyes and I read each their article/statement/whatever else with more caution than on average. Even if the beginning of the message is somehow blurry or overall quality is not that perfect this time. They have to "apply efforts" to their articles to make me angry and sceptic.
2. I definitely have negative experience with a few people and know their usually blurry and washy posts without any resultant technical value. They have really minus credits in my eyes and even if this time they have written something technically sound I'm quite suspect about that. They have to write something really, really good to attract my attention other than sigh "usual crap".
3. I definitely have neutral experience with quite a lot of people. Let's say "nothing special" :) I read their articles only when they are directly related to my particular current needs.
4. People whom I've met the first time or haven't made any particular impression. These are the most complex part. What is my attitude to them? Analyzing my usual behaviour the conclusion is - I start reading the article with some interest and few starting paragraphs should attract my attention. They must be interesting or suitable for my needs otherwise I jump to next article. BUT. I've never (OK almost never :) read the author's CV, last rows about the author how cool he is or whatever else describing his experience. The content of the article is what matters for me.

So people I don't know win over people with negative credit (whose articles I usually even don't read), but lose to people with positive credit (whose articles I usually try to read until the and to find out something valuable).

And returning to the initial comments - what does 20 years of experience mean to me? Most probably nothing. At least initially. 20 years ago there was Oracle 6, how does it help for today's work? Most probably in almost no way. I'd say that a few years for an inquiring and smart mind is enough to deserve more than a person with 20 years experience stalled in Oracle 8. Actually he wins without any doubt at all.

The (quality of) content is what matters - this is the conclusion. At least for people I don't know :)