H.Tonguç Yılmaz - Oracle Blog

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

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