As the calendar clicks over once more, it is time for some fun with date to character functions and function-based indexes.
This has been tested in XE (10gR2) and 11gR2 databases.
So nothing up my sleeves...
set echo on
start date_test.sql
clear screen
drop table date_format_test purge;
create table date_format_test (id number not null, val date not null);
create index date_format_test_ix1 on date_format_test (
id, to_char(val), to_char(val,'DD-Mon-YYYY'), to_char(val,'DD-fmMonth-YYYY'), to_char(val,'DD-MM-YYYY'));
Insert a bunch of date values as they might come in from different sessions from different countries in different languages
alter session set nls_date_format='DD/Mon/YYYY';
alter session set nls_calendar='Gregorian';
alter session set nls_language = 'English';
insert into date_format_test values (1, date '2009-01-20');
insert into date_format_test values (2, trunc(sysdate,'Mon'));
insert into date_format_test values (3, to_date('25122009','ddmmyyyy'));
alter session set nls_language = 'French';
insert into date_format_test values (11, date '2009-01-20');
insert into date_format_test values (12, trunc(sysdate,'Mon'));
insert into date_format_test values (13, to_date('25122009','ddmmyyyy'));
alter session set nls_language = 'German';
insert into date_format_test values (21, date '2009-01-20');
insert into date_format_test values (22, trunc(sysdate,'Mon'));
insert into date_format_test values (23, to_date('25122009','ddmmyyyy'));
alter session set nls_language = 'Spanish';
alter session set nls_date_format='DD/fmMonth/YYYY';
Now lets see what's in the database...
select /*+INDEX t*/ id, to_char(val,'DD-fmMonth-YYYY') from date_format_test t;
ID TO_CHAR(VAL,'DD-FMMONTH-YYYY')
----------- ------------------------------
1.00 20-January-2009
2.00 01-December-2009
3.00 25-December-2009
11.00 20-Janvier-2009
12.00 01-Décembre-2009
13.00 25-Décembre-2009
21.00 20-Januar-2009
22.00 01-Dezember-2009
23.00 25-Dezember-2009
select /*+INDEX t*/ id, to_char(val,'DD-Mon-YYYY') from date_format_test t;
ID TO_CHAR(VAL,'DD-MON-
----------- --------------------
1.00 20-Jan-2009
2.00 01-Dec-2009
3.00 25-Dec-2009
11.00 20-Janv.-2009
12.00 01-Déc. -2009
13.00 25-Déc. -2009
21.00 20-Jan-2009
22.00 01-Dez-2009
23.00 25-Dez-2009
In both of those cases, the value 'preserved' in the function based index is in the language of the inserting session, so the querying session sees the value in a variety of languages rather than their own.
select /*+INDEX t*/ id, to_char(val) from date_format_test t;
ID TO_CHAR(VAL)
----------- ------------------------------------------------
1.00 20/Enero/2009
2.00 01/Diciembre/2009
3.00 25/Diciembre/2009
11.00 20/Enero/2009
12.00 01/Diciembre/2009
13.00 25/Diciembre/2009
21.00 20/Enero/2009
22.00 01/Diciembre/2009
23.00 25/Diciembre/2009
In this cases, despite the hint, the data comes from the base table and is correctly shown in the language of the selecting session.
But this isn't just about display.
select id, to_char(val,'DD-Mon-YYYY'), val from date_format_test t where to_char(val,'DD-Mon-YYYY') = '20-Jan-2009';
no rows selected
select id, to_char(val,'DD-Mon-YYYY') from date_format_test where to_char(val,'DD-Mon-YYYY') = '20-Jan-2009';
ID TO_CHAR(VAL,'DD-MON-
----------- --------------------
1.00 20-Jan-2009
21.00 20-Jan-2009
In the first query, since VAL is selected, the data comes from the base table. No records are matched because, converting the date in the table to a character format in the current session language (Spanish) doesn't return any values matching the string '20-jan-2009'. In the second, it comes from the index and, because some inserting sessions were in English, we get some matches.
The problem is that function based indexes should only be used on deterministic functions and TO_CHAR(date,'format mask') doesn't necessarily meet the criteria. Being techies, we'd recognize that linguistic months might be a problem. We can always rely on numbers though, can't we ?
Well not quite. Did you notice my setup included setting NLS_CALENDAR ?
alter session set nls_language = 'English';
alter session set nls_calendar='Persian';
select /*+INDEX t*/ id, to_char(val,'DD-MM-YYYY') from date_format_test t;
ID TO_CHAR(VA
----------- ----------
1.00 20-01-2009
2.00 01-12-2009
3.00 25-12-2009
11.00 20-01-2009
12.00 01-12-2009
13.00 25-12-2009
21.00 20-01-2009
22.00 01-12-2009
23.00 25-12-2009
select id, val, to_char(val,'DD-MM-YYYY') from date_format_test t;
ID VAL TO_CHAR(VA
----------- ---------------------- ----------
1.00 01 Bahman 1387 01-11-1387
2.00 10 Azar 1388 10-09-1388
3.00 04 Dey 1388 04-10-1388
11.00 01 Bahman 1387 01-11-1387
12.00 10 Azar 1388 10-09-1388
13.00 04 Dey 1388 04-10-1388
21.00 01 Bahman 1387 01-11-1387
22.00 10 Azar 1388 10-09-1388
23.00 04 Dey 1388 04-10-1388
While you may think we are reaching the end of 2009, that isn't necessarily true.
But for those of us who are, then Happy New Year !
PS.
It is possible to specify the date language or the calendar as part of the to_char function. I suspect that would be sufficient to make these deterministic.
select to_char(sysdate,'DD-fmMonth-YYYY','nls_calendar=''Arabic Hijrah''') from dual;
select to_char(sysdate,'DD-fmMonth-YYYY','nls_date_language=''Italian''') from dual;
Sunday, December 27, 2009
Thursday, December 24, 2009
Autonomous SERVERERROR trigger
Last month I posted about the 'disappearing row trick'. In my response on stackoverflow I posted an additional demonstration of the issue using a SERVERERROR trigger. That was flawed because, as I just read in 5th Edition Oracle PL/SQL Programming, the AFTER SERVERERROR trigger fires as an autonomous transaction.
Take the following example:
drop table test_se_auto_tbl;
create table test_se_auto_tbl
Take the following example:
drop table test_se_auto_tbl;
create table test_se_auto_tbl
(id number(2) primary key, val varchar2(20));
create or replace trigger test_se_auto_trg
create or replace trigger test_se_auto_trg
after servererror on schema
begin
dbms_output.put_line('Trigger ran');
for c_rec in (select id, val from test_se_auto_tbl) loop
dbms_output.put_line(c_rec.id||':'||c_rec.val);
end loop;
end;
/
Running a transaction as three separate independent SQL statements shows that, when the third statement fails the SERVERERROR trigger fires but the two successful inserts are not visible (because the trigger is an autonomous transaction).
insert into test_se_auto_tbl values (1,'test ');
insert into test_se_auto_tbl values (10,'test 10');
insert into test_se_auto_tbl values (100,'test 100');
select id, val from test_se_auto_tbl;
rollback;
Revising the example (as I've done on stackoverflow), it is still possible to demonstrate the disappearing row trick however.
create or replace trigger test_se_auto_trg after servererror on schema
begin
dbms_output.put_line('Trigger ran');
insert into test_se_auto_tbl values (1,'test in trigger');
end;
/
begin
insert into test_se_auto_tbl values (1,'test 1');
insert into test_se_auto_tbl values (10,'test 10');
insert into test_se_auto_tbl values (100,'test 100');
end;
/
select id, val from test_se_auto_tbl;
In this case the trigger fires and is successfully able to insert the "test in trigger" row. This indicates the insert of the "test 1" row has been rolled back. If you run the three individual statements, the last insert errors but reports a deadlock as the autonomous transaction in the SERVERERROR trigger fails trying to violate the primary key on ID.
truncate table test_se_auto_tbl;
insert into test_se_auto_tbl values (1,'test ');
insert into test_se_auto_tbl values (10,'test 10');
insert into test_se_auto_tbl values (100,'test 100');
begin
dbms_output.put_line('Trigger ran');
for c_rec in (select id, val from test_se_auto_tbl) loop
dbms_output.put_line(c_rec.id||':'||c_rec.val);
end loop;
end;
/
Running a transaction as three separate independent SQL statements shows that, when the third statement fails the SERVERERROR trigger fires but the two successful inserts are not visible (because the trigger is an autonomous transaction).
insert into test_se_auto_tbl values (1,'test ');
insert into test_se_auto_tbl values (10,'test 10');
insert into test_se_auto_tbl values (100,'test 100');
select id, val from test_se_auto_tbl;
rollback;
Revising the example (as I've done on stackoverflow), it is still possible to demonstrate the disappearing row trick however.
create or replace trigger test_se_auto_trg after servererror on schema
begin
dbms_output.put_line('Trigger ran');
insert into test_se_auto_tbl values (1,'test in trigger');
end;
/
begin
insert into test_se_auto_tbl values (1,'test 1');
insert into test_se_auto_tbl values (10,'test 10');
insert into test_se_auto_tbl values (100,'test 100');
end;
/
select id, val from test_se_auto_tbl;
In this case the trigger fires and is successfully able to insert the "test in trigger" row. This indicates the insert of the "test 1" row has been rolled back. If you run the three individual statements, the last insert errors but reports a deadlock as the autonomous transaction in the SERVERERROR trigger fails trying to violate the primary key on ID.
truncate table test_se_auto_tbl;
insert into test_se_auto_tbl values (1,'test ');
insert into test_se_auto_tbl values (10,'test 10');
insert into test_se_auto_tbl values (100,'test 100');
The "test" row from the first insert is still there but is not directly visible to the autonomous transaction. It can be detected indirectly by trying to insert the duplicate row. This finds the uncommitted "test" row. Normally it would wait until a blocking transaction is committed or rolled-back and then either fail or allow the insert. But because the original transaction cannot complete until the trigger completes and the trigger can't complete until the original transaction completes, the deadlock situation arises.
Tuesday, December 22, 2009
Wide records in history tables - Squeezing the Juice
I had one of those WIDE tables, over a hundred a fifty columns which was a history style table, recording the state on particular days.
I've put together a PL/SQL routine that would dump out the columns that actually differed between successive entries.
The first parameter is the table name, or the target of the select statement. I have used DBMS_SQL.DESCRIBE_COLUMNS rather than parsing *_TAB_COLUMNS for this parameter for greater flexibility. It means I don't have to separate out the schema from the table in separate parameters. I can even pass a inline view as the target, such as '(select * from schema1.tbl2 where val !=123)'
The second parameter is the column(s) which identify the ordering, which will normally be a date or sequence. Again it is possible to have multiple values (eg 'batch_id, line').
The third parameter is optional, and identifies the partitioning column(s). If we were looking at an employee history, this would be the employee number.
A final optional parameter allows you to limit the query to a specific value for the identifier column.
Because it uses dynamic SQL, and I've opted for flexibility over restriction, there are obvious injection possibilities. It would be vulnerable if some-one specified a function name as part of the target [eg '(select bad_func from dual)'] or as the ordering column. It is invoker rights so it won't directly allow access to anything the user can't otherwise see or do, but if it gets called through a wrapper then it becomes dangerous. As a pipelined function it can only be called through SQL, so any data changes or DDL would have to be wrapped in an autonomous transaction. But that doesn't make it bullet-proof though.
I've put together a PL/SQL routine that would dump out the columns that actually differed between successive entries.
The first parameter is the table name, or the target of the select statement. I have used DBMS_SQL.DESCRIBE_COLUMNS rather than parsing *_TAB_COLUMNS for this parameter for greater flexibility. It means I don't have to separate out the schema from the table in separate parameters. I can even pass a inline view as the target, such as '(select * from schema1.tbl2 where val !=123)'
The second parameter is the column(s) which identify the ordering, which will normally be a date or sequence. Again it is possible to have multiple values (eg 'batch_id, line').
The third parameter is optional, and identifies the partitioning column(s). If we were looking at an employee history, this would be the employee number.
A final optional parameter allows you to limit the query to a specific value for the identifier column.
Because it uses dynamic SQL, and I've opted for flexibility over restriction, there are obvious injection possibilities. It would be vulnerable if some-one specified a function name as part of the target [eg '(select bad_func from dual)'] or as the ordering column. It is invoker rights so it won't directly allow access to anything the user can't otherwise see or do, but if it gets called through a wrapper then it becomes dangerous. As a pipelined function it can only be called through SQL, so any data changes or DDL would have to be wrapped in an autonomous transaction. But that doesn't make it bullet-proof though.
clear screen
create or replace function tab_diff
(p_tab_name in varchar2, p_ord_col in varchar2, p_id_col in varchar2 default null, p_id_val in varchar2 default null)
return tab_char_4000 AUTHID CURRENT_USER pipelined is
v_line varchar2(2000);
v_col_cnt INTEGER;
v_ind NUMBER;
rec_tab dbms_sql.desc_tab;
v_tab dbms_sql.varchar2a;
v_temp VARCHAR2(32000);
v_cursor NUMBER;
v_clause VARCHAR2(200);
begin
--
-- Basic parameter validation
--
if p_id_val is not null and p_id_col is null then
raise_application_error(-20001,'Cannot specify an id value without an associated column');
elsif p_tab_name is null or p_ord_col is null then
raise_application_error(-20001,'Table or query target and order column(s) must be specified');
end if;
--
-- Initial values
--
v_ind := 1;
IF p_id_col IS NOT NULL THEN
v_tab(v_ind) := 'select '||p_id_col||','||p_ord_col;
ELSE
v_tab(v_ind) := 'select '||p_ord_col;
END IF;
v_temp := 'select * from '||p_tab_name;
IF p_id_col IS NULL THEN
v_clause := ' over (order by '||p_ord_col||')';
ELSE
v_clause := ' over (partition by '||p_id_col||' order by '||p_ord_col||')';
END IF;
--
-- Identify the columns in the target and build the new query
--
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, v_temp, dbms_sql.native);
dbms_sql.describe_columns(v_cursor, v_col_cnt, rec_tab);
--
FOR v_pos in 1..rec_tab.LAST LOOP
v_line := rec_tab(v_pos).col_name;
IF v_line in (p_id_col, p_ord_col) THEN
null;
ELSE
-- When the new value doesn't match the 'lag' value, return the "new:old"
v_line := ', case when '||v_line|| ' = lag('||v_line||') '||v_clause||' then null '||
' else '||v_line||'||'':''||lag('||v_line||') '||v_clause||' end '||
v_line;
v_ind := v_ind + 1;
v_tab(v_ind) := v_line;
END IF;
END LOOP;
-- Add the FROM, WHERE and ORDER BY to the new query
v_ind := v_ind + 1;
IF p_id_val is null then
IF p_id_col is null then
v_tab(v_ind) := ' from '||p_tab_name||' order by '||p_ord_col;
ELSE
v_tab(v_ind) := ' from '||p_tab_name||' order by '||p_id_col||','||p_ord_col;
END IF;
ELSE
v_tab(v_ind) := ' from '||p_tab_name||' where '||p_id_col||' = '''||p_id_val||''' order by '||p_ord_col;
END IF;
--
-- parse, describe and execute the new query
--
BEGIN
dbms_sql.parse(v_cursor, v_tab, v_tab.first, v_tab.last, true, dbms_sql.native);
EXCEPTION
WHEN OTHERS THEN
FOR i in 1..v_tab.count LOOP
dbms_output.put_line('ERR:'||v_tab(i));
END LOOP;
RAISE;
END;
dbms_sql.describe_columns( v_cursor, v_col_cnt, rec_tab);
--
FOR i IN 1 .. rec_tab.COUNT LOOP
dbms_sql.define_column( v_cursor, i, v_line, 2000);
END LOOP;
v_ind := dbms_sql.execute( v_cursor );
--
-- Fetch each row from the result set
--
LOOP
v_ind := DBMS_SQL.FETCH_ROWS( v_cursor );
EXIT WHEN v_ind = 0;
pipe row( '===================================================================================================');
--
-- Go through each column and if the new:old value for the column is not null then display it
--
FOR v_col_seq IN 1 .. rec_tab.COUNT LOOP
-- Get the value
dbms_sql.column_value( v_cursor, v_col_seq, v_line );
IF NVL(v_line,':') != ':' THEN
pipe row( rpad(rec_tab(v_col_seq).col_name,35)||'>'||v_line);
ELSIF rec_tab(v_col_seq).col_name in (p_id_col, p_ord_col) THEN
--Also show the identifier and order columns
pipe row( rpad(rec_tab(v_col_seq).col_name,35)||'>'||v_line);
END IF;
END LOOP;
END LOOP;
return;
end tab_diff;
/
show errors function tab_diff
select * from table(tab_diff('TABLE_1','BATCH_ID','TAB_ID','TAB_ID_VAL'));
select * from table(tab_diff(
'(select * from TABLE_1 WHERE TAB_ID=1234)','BATCH_ID'));
Tuesday, December 15, 2009
Reading Matters
Chet asked, in a comment on my post, for the security blogs I read.
I was planning a 'reading matter' blog anyway, and there's a bit of crossover so here we go:
Security
I get the OraNA Security feed as my main source. Sometimes I'll add a new feed I come across, but they seem to get added to OraNA very quickly, and then I just remove the specific feed. The remaining ones aren't specific to Oracle (or even necessarily databases).
Imperva blog - Mostly database or data related
Securosis -Mostly computer related
Schneier on Security - A bit more high level
Risks Digest - Mostly reports on things that have gone wrong
A Day in the Life of an Information Security Investigator
This one is entertaining, but it has got quiet over recent months. He's either working hard, or its because he has cut down on his coffee intake.
I'm glad Chet asked since I found a couple of broken feeds while checking and had to re-point them to the new feed locations.
Software
I follow the blogs of Joel Spolsky and Jeff Atwood.
I've just finished "More Joel on Software" which I found very enjoyable. While it is 'just' a collection of his blog articles, it has been brought together in a very cohesive way, so works well as a book. I have been reading the 'Joel on Software' blog for a couple of years and had previously bought "User Interface Design for Programmers".
I suspect I got to Jeff Atwood's Coding Horror blog from Joel's blog, but it may have been the other way. Either way they got together to produce stackoverflow.
If you haven't already been, go take a look round stackoverflow.com. I used to hang around a couple of forums but this one just works better. OpenId authentication so I don't have to worry about ANOTHER password to remember. A whole bunch of 'badges' to earn and reputation to be garnered. Users with a high enough reputation get editing privileges on the question or answers (though there's a wiki style history to see who did what). As well as originators being able to mark answers as correct, others can vote answers up and down, and vote the question up and down. You can comment on the question and on answers and vote the comments up and down. Its not only free to use, but you can even download the whole Q&A database. If Oracle ever feel like ditching Jive, they can get this off the shelf [http://stackexchange.com/]. That said, I'm sure the thing could be spoiled by the wrong user base.
'Paper' reading
I've started on Oracle PL/SQL Programming, 5th Edition. I won this after Steven's visit to Sydney. I'd previously owned a much earlier edition (okay, it was my wife's but it sat on *our* bookshelf), but eventually got rid of it as it was too dated to be fully trusted. I need to catch up with best practices for the later versions of PL/SQL, and the latest edition goes all the way up to 11gR2. It's a big book, so it will take me a while.
On my non-work stuff, I've been re-reading my set of Jasper Fforde books. Very English and refreshingly different. I'd sort of class them as satire, but they are very tricky to categorise.
I was planning a 'reading matter' blog anyway, and there's a bit of crossover so here we go:
Security
I get the OraNA Security feed as my main source. Sometimes I'll add a new feed I come across, but they seem to get added to OraNA very quickly, and then I just remove the specific feed. The remaining ones aren't specific to Oracle (or even necessarily databases).
Imperva blog - Mostly database or data related
Securosis -Mostly computer related
Schneier on Security - A bit more high level
Risks Digest - Mostly reports on things that have gone wrong
A Day in the Life of an Information Security Investigator
This one is entertaining, but it has got quiet over recent months. He's either working hard, or its because he has cut down on his coffee intake.
I'm glad Chet asked since I found a couple of broken feeds while checking and had to re-point them to the new feed locations.
Software
I follow the blogs of Joel Spolsky and Jeff Atwood.
I've just finished "More Joel on Software" which I found very enjoyable. While it is 'just' a collection of his blog articles, it has been brought together in a very cohesive way, so works well as a book. I have been reading the 'Joel on Software' blog for a couple of years and had previously bought "User Interface Design for Programmers".
I suspect I got to Jeff Atwood's Coding Horror blog from Joel's blog, but it may have been the other way. Either way they got together to produce stackoverflow.
If you haven't already been, go take a look round stackoverflow.com. I used to hang around a couple of forums but this one just works better. OpenId authentication so I don't have to worry about ANOTHER password to remember. A whole bunch of 'badges' to earn and reputation to be garnered. Users with a high enough reputation get editing privileges on the question or answers (though there's a wiki style history to see who did what). As well as originators being able to mark answers as correct, others can vote answers up and down, and vote the question up and down. You can comment on the question and on answers and vote the comments up and down. Its not only free to use, but you can even download the whole Q&A database. If Oracle ever feel like ditching Jive, they can get this off the shelf [http://stackexchange.com/]. That said, I'm sure the thing could be spoiled by the wrong user base.
'Paper' reading
I've started on Oracle PL/SQL Programming, 5th Edition. I won this after Steven's visit to Sydney. I'd previously owned a much earlier edition (okay, it was my wife's but it sat on *our* bookshelf), but eventually got rid of it as it was too dated to be fully trusted. I need to catch up with best practices for the later versions of PL/SQL, and the latest edition goes all the way up to 11gR2. It's a big book, so it will take me a while.
On my non-work stuff, I've been re-reading my set of Jasper Fforde books. Very English and refreshingly different. I'd sort of class them as satire, but they are very tricky to categorise.
Monday, December 14, 2009
Who caught my error ?
This one is prompted by a Stackoverflow question here
DBMS_TRACE is a handy way to peek under the scenes in PL/SQL. The bad news is you'll need someone with SYSDBA privileges to install the required tables. I suggest you buy your DBA a box of chocolates for Christmas, and then suggest that you would be annoying them less often if they'd install the objects. Oh, and while they are there, could they give you some select privileges on some of the V$ views.
The required tables are in a script called tracetab.sql in $ORACLE_HOME/rdbms/admin. They get installed under SYS and you'll want some privileges to manage them too.
D:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 14 09:24:45 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> @tracetab.sql
SQL> CREATE PUBLIC SYNONYM plsql_trace_runs FOR plsql_trace_runs;
SQL> CREATE PUBLIC SYNONYM plsql_trace_events FOR plsql_trace_events;
SQL> CREATE PUBLIC SYNONYM plsql_trace_runnumber FOR plsql_trace_runnumber;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_runs TO PUBLIC;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_events TO PUBLIC;
SQL> GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;
Now for a demo :
create or replace
procedure test_trace is
v_test varchar2(3);
begin
select '12' into v_test from dual;
select '123' into v_test from dual;
select '1234' into v_test from dual;
select '12345' into v_test from dual;
exception
when value_error then
null;
end;
/
Somewhere in the mire of code, an exception is being raised. It could be a select or other SQL statement or just a PL/SQL assignment. The benefit of DBMS_TRACE over an SQL trace is that it catches PL/SQL exceptions.
Run the test by clearing out the table of any old junk, set the flag and execute the procedure
conn .../...@xe
delete from plsql_trace_events;
commit;
exec DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_exceptions);
exec test_trace;
Then query the results.
conn .../...@xe
select event_kind, event_unit, event_line, stack_depth, excp, event_comment, callstack, errorstack
from plsql_trace_events
where event_kind not in (38,40,43,44)
order by event_seq;
EVENT_KIND EVENT_UNIT EVENT_LINE STACK_DEPTH EXCP
----------- ------------------------------- ----------- ----------- -----------
EVENT_COMMENT
----------------------------------------------------------------------------------
CALLSTACK
----------------------------------------------------------------------------------
ERRORSTACK
----------------------------------------------------------------------------------
52.00 TEST_TRACE 6.00 2.00 6,502.00
Exception raised
----- PL/SQL Call Stack -----
object line object
handle number name
3BF0F6D4 6 procedure GARY.TEST_TRACE
3BDF1764 1 anonymous block
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
53.00 TEST_TRACE 11.00 2.00 6,502.00
Exception handled
We can see an exception was raised at line 6 and the fact that it was caught at line 11. The latter is pretty important too. If you have some complex code, it isn't impossible that an exception handler a couple of levels up the call hierarchy may 'handle' an exception for which it was never intended. You can even see the error number which is handy if it got caught by a WHEN OTHERS.
DBMS_TRACE is a handy way to peek under the scenes in PL/SQL. The bad news is you'll need someone with SYSDBA privileges to install the required tables. I suggest you buy your DBA a box of chocolates for Christmas, and then suggest that you would be annoying them less often if they'd install the objects. Oh, and while they are there, could they give you some select privileges on some of the V$ views.
The required tables are in a script called tracetab.sql in $ORACLE_HOME/rdbms/admin. They get installed under SYS and you'll want some privileges to manage them too.
D:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 14 09:24:45 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> @tracetab.sql
SQL> CREATE PUBLIC SYNONYM plsql_trace_runs FOR plsql_trace_runs;
SQL> CREATE PUBLIC SYNONYM plsql_trace_events FOR plsql_trace_events;
SQL> CREATE PUBLIC SYNONYM plsql_trace_runnumber FOR plsql_trace_runnumber;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_runs TO PUBLIC;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_events TO PUBLIC;
SQL> GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;
Now for a demo :
create or replace
procedure test_trace is
v_test varchar2(3);
begin
select '12' into v_test from dual;
select '123' into v_test from dual;
select '1234' into v_test from dual;
select '12345' into v_test from dual;
exception
when value_error then
null;
end;
/
Somewhere in the mire of code, an exception is being raised. It could be a select or other SQL statement or just a PL/SQL assignment. The benefit of DBMS_TRACE over an SQL trace is that it catches PL/SQL exceptions.
Run the test by clearing out the table of any old junk, set the flag and execute the procedure
conn .../...@xe
delete from plsql_trace_events;
commit;
exec DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_exceptions);
exec test_trace;
Then query the results.
conn .../...@xe
select event_kind, event_unit, event_line, stack_depth, excp, event_comment, callstack, errorstack
from plsql_trace_events
where event_kind not in (38,40,43,44)
order by event_seq;
EVENT_KIND EVENT_UNIT EVENT_LINE STACK_DEPTH EXCP
----------- ------------------------------- ----------- ----------- -----------
EVENT_COMMENT
----------------------------------------------------------------------------------
CALLSTACK
----------------------------------------------------------------------------------
ERRORSTACK
----------------------------------------------------------------------------------
52.00 TEST_TRACE 6.00 2.00 6,502.00
Exception raised
----- PL/SQL Call Stack -----
object line object
handle number name
3BF0F6D4 6 procedure GARY.TEST_TRACE
3BDF1764 1 anonymous block
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
53.00 TEST_TRACE 11.00 2.00 6,502.00
Exception handled
We can see an exception was raised at line 6 and the fact that it was caught at line 11. The latter is pretty important too. If you have some complex code, it isn't impossible that an exception handler a couple of levels up the call hierarchy may 'handle' an exception for which it was never intended. You can even see the error number which is handy if it got caught by a WHEN OTHERS.
Tuesday, December 08, 2009
The CREATE ANY TRIGGER three card trick.
I've got an interest in security, or at least in vulnerabilities. It isn't something I've really had as part of a job role but I like the tricky way of thinking it entails, and I follow a number of security blogs (both Oracle and generic).
During my degree course, when I was mooching around the Prime computers the students had accounts on I discovered some forgotten directories which allowed me to stop worrying about the number and size of files I wanted to store. My degree course was a 'sandwich' one, where the third year was spent working in the real world, and while there I discovered another 'hole'. In that case it was on a *nix system where, although the login account went straight to a menu rather than giving shell access, I could execute shell commands from within the text editor.
After graduating, I was working for a software provider. One client had an important batch job which failed to run at a weekend. Some clients had support accounts that we could access anytime, others required the account to be unlocked, and one site had to turn on the modem before we could connect. This site was quite secure and I couldn't get to the support account but it was local enough that I could drive to the site. The guy I was dealing with had a basic login with just a list of menu options so I still didn't have any command line or SQL level access to fix the problem. Remembering my previous adventures, I nipped into the MAIL option and from there to the text editor. [Hey, this was pre-internet and pretty much pre-Windows. You sent and read your mail through green-screen terminals.] This was VMS and the environment was smarter than the *nix environment had been and knew I was limited to a locked down account, so the editor wouldn't allow shell commands. But it would let me open up another file in the editor, and so I could edit a file that would be run by the batch job when it restarted. See what I mean about twisty thinking ?
DBAs need to understand the twisty thinking when it comes to protecting databases. So I though I'd push out an Oracle example of twisty thinking.
DBAs know that the *ANY* privileges are generally dangerous and shouldn't be handed out unless absolutely necessary (and even then, with lots of auditing and a large spiky stick). CREATE ANY TRIGGER is one of the trickier ones. On the face of it, it allows a user to create a trigger on anyone's table (or a DDL or event trigger). The trick is that, table triggers do NOT need to be owned by the same schema as that of the table.
So a user, DICK, with 'CREATE ANY TRIGGER' can create a trigger owned by TOM on a table owned by HARRY. That way anyone who inserts, updates or deletes a row in HARRY's table will fire the trigger that runs with TOM's privileges. And given DICK can create the trigger with any owner he wants, the TOM account will be the one with the most privileges.
Obviously, DBAs don't go around granting CREATE ANY TRIGGER to just anyone. A measly developer may just get an account with CREATE TABLE, but that's about it. Once you consider that the developer's account can be the table owner though (HARRY in the example), we have our first twist. A sneaky cracker will need a powerful DICK to create the trigger so he looks around for an account that already has that privilege, such as MDSYS (which owns the spatial datatypes, packages etc). That account is probably LOCKED and EXPIRED, but that doesn't make it unusable. A cracker will be studying the Oracle CPUs and security sites for SQL or PL/SQL injection vulnerabilities in those privileged (and locked down) accounts and those will be used to get access to the CREATE ANY TRIGGER privilege.
To summarise, cracker gets a lowly priviliged HARRY account, uses vulnerabilities in packages owned by default DICK users (despite them being locked) to create triggers owned by TOM users with very high privileges on HARRY's own tables. By inserting into those tables, the cracker can fire a trigger to grant himself DBA or whatever.
From there you can use CREATE ANY DIRECTORY to start hunting around the server file system, and write ssh or rhosts files to get to the oracle login. That may only be on a development system, but some snazzy rootkit style executables there could help the cracker make the jump to production. Or maybe, by pointing the CREATE ANY DIRECTORY cannon at the directory with the Oracle data files, they use UTL_FILE to read the files/blocks relating to C_USER# (the data segment underlying dba_users) and get the password hashes (which are no longer visible in DBA_USERS in 11g). There are some fun things being done to speed up cracking of Oracle passwords so anyone thinking the eight-character alphanumeric password they have been using for a couple of months is safe should think again. You don't use the same password for anything important do you ?
So, have a think about your password complexity and change requirements, applying those CPUs to those development and test databases as well as production, and actively auditing those more powerful privileges (by which I mean you READ about any use of them when they happen, and not six months later).
During my degree course, when I was mooching around the Prime computers the students had accounts on I discovered some forgotten directories which allowed me to stop worrying about the number and size of files I wanted to store. My degree course was a 'sandwich' one, where the third year was spent working in the real world, and while there I discovered another 'hole'. In that case it was on a *nix system where, although the login account went straight to a menu rather than giving shell access, I could execute shell commands from within the text editor.
After graduating, I was working for a software provider. One client had an important batch job which failed to run at a weekend. Some clients had support accounts that we could access anytime, others required the account to be unlocked, and one site had to turn on the modem before we could connect. This site was quite secure and I couldn't get to the support account but it was local enough that I could drive to the site. The guy I was dealing with had a basic login with just a list of menu options so I still didn't have any command line or SQL level access to fix the problem. Remembering my previous adventures, I nipped into the MAIL option and from there to the text editor. [Hey, this was pre-internet and pretty much pre-Windows. You sent and read your mail through green-screen terminals.] This was VMS and the environment was smarter than the *nix environment had been and knew I was limited to a locked down account, so the editor wouldn't allow shell commands. But it would let me open up another file in the editor, and so I could edit a file that would be run by the batch job when it restarted. See what I mean about twisty thinking ?
DBAs need to understand the twisty thinking when it comes to protecting databases. So I though I'd push out an Oracle example of twisty thinking.
DBAs know that the *ANY* privileges are generally dangerous and shouldn't be handed out unless absolutely necessary (and even then, with lots of auditing and a large spiky stick). CREATE ANY TRIGGER is one of the trickier ones. On the face of it, it allows a user to create a trigger on anyone's table (or a DDL or event trigger). The trick is that, table triggers do NOT need to be owned by the same schema as that of the table.
So a user, DICK, with 'CREATE ANY TRIGGER' can create a trigger owned by TOM on a table owned by HARRY. That way anyone who inserts, updates or deletes a row in HARRY's table will fire the trigger that runs with TOM's privileges. And given DICK can create the trigger with any owner he wants, the TOM account will be the one with the most privileges.
Obviously, DBAs don't go around granting CREATE ANY TRIGGER to just anyone. A measly developer may just get an account with CREATE TABLE, but that's about it. Once you consider that the developer's account can be the table owner though (HARRY in the example), we have our first twist. A sneaky cracker will need a powerful DICK to create the trigger so he looks around for an account that already has that privilege, such as MDSYS (which owns the spatial datatypes, packages etc). That account is probably LOCKED and EXPIRED, but that doesn't make it unusable. A cracker will be studying the Oracle CPUs and security sites for SQL or PL/SQL injection vulnerabilities in those privileged (and locked down) accounts and those will be used to get access to the CREATE ANY TRIGGER privilege.
To summarise, cracker gets a lowly priviliged HARRY account, uses vulnerabilities in packages owned by default DICK users (despite them being locked) to create triggers owned by TOM users with very high privileges on HARRY's own tables. By inserting into those tables, the cracker can fire a trigger to grant himself DBA or whatever.
From there you can use CREATE ANY DIRECTORY to start hunting around the server file system, and write ssh or rhosts files to get to the oracle login. That may only be on a development system, but some snazzy rootkit style executables there could help the cracker make the jump to production. Or maybe, by pointing the CREATE ANY DIRECTORY cannon at the directory with the Oracle data files, they use UTL_FILE to read the files/blocks relating to C_USER# (the data segment underlying dba_users) and get the password hashes (which are no longer visible in DBA_USERS in 11g). There are some fun things being done to speed up cracking of Oracle passwords so anyone thinking the eight-character alphanumeric password they have been using for a couple of months is safe should think again. You don't use the same password for anything important do you ?
So, have a think about your password complexity and change requirements, applying those CPUs to those development and test databases as well as production, and actively auditing those more powerful privileges (by which I mean you READ about any use of them when they happen, and not six months later).
Subscribe to:
Posts (Atom)