Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

February 28, 2009

On some 11.1 security features and enhancements

Filed under: Oracle 11g New Features,Oracle Security — H.Tonguç Yılmaz @ 6:30 am

Last week security was one of the hot topics at my company, I found myself writing and talking with one of my DBA friends on Oracle’s security options and the history, we agreed that security is one of the areas where in time Oracle invested a lot but still there were important failures all around the software. Then we started discussing on 11.1 security features and enhancements and I remembered reading some cool options from Lutz Hartmann‘s 11g New Features book related chapter, like;

– Against brute force attacks, sec_ax_failed_login_attemps specifies the number of unsuccessful authentication attempts before the client is dropped,
– Against denial of service attacks, sec_protocol_error_further_action and sec_protocol_error_trace_action specify how the database should react, sec_protocol_error_further_action with DELAY,n option specify in seconds how long the server waits before it accepts further requests and sec_protocol_error_trace_action with LOG option creates a short audit log instead of creating huge trace files with TRACE option,
– 11g’s password verification function(utlpwdmg.sql) is enhanced,
– After 11g Audit is by default enabled and audit_trail is set to DB,
– sqlnet.allowed_logon_versions parameter defined the minimum client version that is allowed to connect.

And much more of them in the chapter, but they did not mention who wrote which chapters in the book. Where as it is so obvious that which chapters belong to Lutz in the book since he put a lot of efforts with chapters 8(security), 10(change management), 11(sql management), 12(Flashback) and 13(ASM) with examples they beautifully help understanding the new enhancements after 11.1 on these areas. Also I caught that using your name as a username at the SQL*Plus prompt is a good technique to claim your examples later on(Kyte and Foote uses this technique a lot) :)

I must of course mention that the other chapters of the other authors in this book were big disappointments for me, I thought what if Lutz didn’t write for the book will they still publish this book since there is nearly no value added to the already available text at Oracle’s documentation? Who knows. There is an important lesson for me here, if one day I decide to write a book I will choose to write alone or choose the co-authors and the press I will work with very carefully. Since I love reading Lutz and he is not blogging like the old days because he was angry to some people copying his blog posts and making money out of them, I hope he chooses to write alone for his future book projects and I can consume more of his quality Oracle readings.

Advertisements

August 24, 2008

The Rijndael(Advanced Encryption Standard-AES) algorithm to secure TBs of data

Filed under: Oracle How To,Oracle Security — H.Tonguç Yılmaz @ 10:28 am

The Need Definition –

There are some TBs of your Call Data Records(CDR) to be shared with an external company. Some of the columns in this dataset must be encrypted and this encryption method must guarantee that this columns only can be decrypted by you, the company who owns the data.

The Solution Advised –

After 10g we have DBMS_CRYPTO supplied package and inside this package there is the Rijndael(AES) algorithm which was selected by NIST in October 2000 to become the new official Advanced Encryption Standard(AES) for use within the US Government.

AES is available in two strengths; 128 and 256 bits, and of course the 256-bit version is approximately %50 slower than the 128-bit version, so for the optimum encryption performance 128 bit strength will be enough for this need since to break AES128 encryption one will need 2 ^ 100 amount of keys which will mean months of time even with a super-computer.

And why to do this inside the database but not with a custom C program on operating system for example, the answer is simple as usual; you have the PARALLEL QUERY, HASH JOIN, PARTITIONING, COMPRESSION type of VLDB options already available inside the database you paid for so for the other path you will most probably be re-inventing a dumper wheel within more time and this wheel will born with its maintanance costs on long-term.

A Simple Demostration based on the 10g EM Data Encryption Wizard –

AES128 demo with DBMS_CRYPTO

Some additional reading and references –

Advanced Encryption Standard

Encrypt Your Data Assets By Arup Nanda

How To Encrypt Data in Oracle Using PHP by Larry Ullman

Protect from Prying Eyes: Encryption in Oracle 10g by Arup Nanda

January 9, 2007

Project Lockdown by Arup Nanda

Filed under: Oracle Security — H.Tonguç Yılmaz @ 2:52 pm

A great article by Mr.Arup Nanda, as usual, also one of the best series on Oracle I have ever read. Mr.Arup Nanda was named “DBA of the Year” by Oracle Magazine in 2003 and he is an Oracle ACE.

This article discusses a phased approach to securing your database infrastructure, here are the highlights;

Phase 1 – Duration: One Day
1.1 Remove Default Passwords
1.2 Configure Oracle Binary Permissions
1.3 Secure Other Executables
1.4 Use umask
1.5 Limit SYSDBA Login
1.6 Create a Listener Password
1.7 Protect the Listener
1.8 Trim Sweeping Privileges
1.9 Change DBSNMP Password

Phase 2 – Duration: One Week
2.1 Remove utl_file_dir
2.2 Limit OS Authentication
2.3 Disable Remote OS Authentication
2.4 Secure SQL*Plus Using Product Profile
2.5 Rein In SQL*Plus
2.6 Wrap Sensitive Code
2.7 Convert Derived Grants to Direct Grants
2.8 Limit Tablespace Quotas
2.9 Monitor Listener Logs for Attempted Break-Ins
2.10 Audit and Analyze User Access

Phase 3 – Duration: One Month
3.1 Remove Passwords from Scripts
3.2 Remove Password from RMAN
3.3 Move DBA Scripts to Scheduler
3.4 Lock Down Objects
3.5 Create Profiles of Database Users
3.6 Create and Analyze Object Access Profiles
3.7 Enable Auditing for Future Objects
3.8 Restrict Access from Specific Nodes Only

Phase 4 – Duration: One Quarter
4.1 Enable Fine Grained Auditing
4.2 Activate a Virtual Private Database
4.3 Mask Sensitive Columns
4.4 Encrypt Sensitive Data
4.5 Secure Backups
4.6 Mine History from Archived Logs
4.7 Conclusion

Continue reading the whole article.

Also you may want to check Mr.Arup Nanda’s below two articles, one of the all time most read articles on Oracle Technology Network published articles;

Oracle Database 10g: The Top 20 Features for DBAs

Oracle Database 10g: Top Features for DBAs Release 2 Features Addendum

January 7, 2007

Without Bind Variables Your Code Is Also Less Secure

Filed under: Oracle Security — H.Tonguç Yılmaz @ 6:24 pm

So here are some security issue highlights;

1- Greatest Risk is from

– External hack
– Rogue employee; Is the DBA a threat?
– Social Engineering; Single sign on is dangerous (why?)
– Hackers choose the easiest method for getting in not the one that is the most challenging.
– If there is a deadbolt on the front door, just go in via the left open fly screen on the backdoor.

2- Methods of Theft

– Steal the data; Hack in
– Steal the database; Backups, Copy the files
– Ask for the data; Prevent social engineering

3- Common Hacks

– Social Engineering; “Can I have your password?”, “Where can I plug in my laptop?”
– Theoretical Hacks; Trojan, Java, Wrapped PL/SQL

4- Code Injection

– Add a command to the URL to correctly validate or view data one shouldn’t

http://www.site/code/myproc?pw=SMITH
http://www.site/code/myproc?pw=X’’+or+1=1+or+passw=‘’

‘select username from table where passw = ‘’’ || ‘X’’ or 1=1 or passw=‘’’ || ‘’’’
‘select username from table where passw = ‘X’ or 1=1 or passw=‘’

– Web App prompts for Product Code and Returns Product description;

sql_stmt = “select prod_desc from products where prod_code =‘ ”& input_str & “‘”
Select product_desc from products where product_code = ‘123’

User enters Product code as 123’ UNION select username, password from dba_users where ‘1’=‘1

Resulting SQL statement is now

Select product_desc from products where product_code = ‘123’ UNION select username, password from dba_users where ‘1’=‘1’

To understand how “Code Injection” works lets work on a simple example. Suppose we have an application that asks a code and returns the bank account information related to that code;

create table my_top_secrets (
PK_SECRET    VARCHAR2(30) PRIMARY KEY,
BANK_ACCOUNT VARCHAR2(30),
AMOUNT       NUMBER(10)
);

insert into my_top_secrets values('1', 'Malta Bank - 123456',  123000000);
insert into my_top_secrets values('2', 'Isviçre Bank - 7890', 4567000000);
commit;

create or replace function get_bankaccount(p_secret varchar2) return varchar2 is
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy varchar2(64) := 'Fail';
BEGIN
OPEN l_rc FOR ’select BANK_ACCOUNT
from my_top_secrets
where PK_SECRET = ‘ || p_secret;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
return l_dummy ;
END;
/

set serveroutput on
declare
l_dummy1 varchar2(64) := '-1'; -- since I dont know a primary key value in the table
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Fail

PL/SQL procedure successfully completed.

-- SQL modification involves deliberately altering a dynamic SQL statement so that it executes in a way unintended by the application developer.
declare
l_dummy1 varchar2(64) := '-1' || ' or 1=1';
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Malta Bank - 123456

PL/SQL procedure successfully completed.

-- Statement injection occurs when a user appends one or more new SQL statements to a dynamically generated SQL statement.
declare
l_dummy1 varchar2(64) := '-1' || ' union select user from dual';
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
HR

PL/SQL procedure successfully completed.

What about if we binded instead of concatenation;


create or replace function get_bankaccount(p_secret varchar2) return varchar2 is
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy varchar2(64) := 'Fail';
BEGIN
OPEN l_rc FOR ’select BANK_ACCOUNT
from my_top_secrets
where PK_SECRET = :x’ USING p_secret;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
return l_dummy ;
END;
/

declare
l_dummy1 varchar2(64) := '-1'; -- since I dont know a primary key value in the table
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Fail

PL/SQL procedure successfully completed.

declare
l_dummy1 varchar2(64) := '-1' || ' or 1=1';
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Fail

PL/SQL procedure successfully completed.

declare
l_dummy1 varchar2(64) := '-1' || ' union select user from dual';
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Fail

PL/SQL procedure successfully completed.

If you develop your application using string concatenation instead of using bind variables, side effects on your system will not only be poor scalability as I mentioned in one of my previous posts but especially applications open to the Internet have many hidden vulnerabilities. Be carefull and research on “SQL Injection” topic through Google..

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :

Oracle® Database Application Developer’s Guide – Fundamentals 10g Release 2 (10.2) Chapter 8 Coding Dynamic SQL Avoiding SQL Injection in PL/SQL

“Defending and detecting SQL injection” thread on Asktom
SQL Injection article by Hakkı Oktay

Create a free website or blog at WordPress.com.