June 1st, 2009 by Pete
We are holding a public training event in my home city of York on July 13th and July 14th 2009. The two day seminar is aimed at getting anyone who needs to perform a security audit of an Oracle database up to speed with the whole process (cradle to grave - planning to completion). The class is valuable to many different kinds of people, from developers, security staff, DBA's, managers... as the core lessons are taught; why data is insecure, how to plan for an audit; how to conduct an audit; how to appraise the data collected and what to do next.
The class works even if you are not going to perform the audit yourself but are going to hire someone; securing complex software such as Oracle's database is often a complex task in-itself and understanding the process is very important.
The class is structured around a "mock" database security audit but the end goal is to indentify the data, who uses it (authorised or not) and how that data "flows" throughout the database. So whilst the focus is on a security audit;
the real story; is how to secure the data in your Oracle databases.
This is a unique oppertunity to be taught by Pete Finnigan about how to secure an Oracle database.
The details of the class are on a new page -
Oracle Security training York - including location, course registration and price.
I am really excited to be teaching this class in my home Roman and Viking city of York and I am planning to show delegates around the city whilst they are here [ if they want to of course!]. The places are very limited so please hurry to confirm your place on this seminar.
[
No Comments]
May 21st, 2009 by Pete
I have just uploaded the slides from two recent talks. I gave my Oracle security masterclass in Helsinki for the OUGF last week which went really well and also yesterday I gave my talk "the right way to secure a database" for the UKOUG which also was very well received. The slides are updates of previous talks and as usual are available from my
Oracle Security white papers page.
[
No Comments]
May 11th, 2009 by Pete
A question was posted on the Oak table mailing list some time back asking if its possible to validate a users password from within the database without creating a session. One of the replies suggested looking at my PL/SQL based password cracker. I didn't have the time to go further with it at the time as I was very busy. Then two or three of weeks ago someone else posted a question to my Oracle security forum "
Verify oracle username and password using sql" that is very similar to the oaktable question.
So last weekend I spent 15 minutes extracting the code from the PL/SQL cracker and making it into an installable function. I have created a function called "testpwd" and its available in a file called
testpwd.sql and also available via my
Oracle security tools page. The function is easy to use. Simply create a user, grant CREATE SESSION, CREATE PROCEDURE and as SYS GRANT SELECT ON SYS.USER$ to the user and install the function. Then test it. The set up is shown here:
SQL> grant create session, create procedure to testpwd identified by testpwd;
Grant succeeded.
SQL> connect sys/oracle1 as sysdba
Connected.
SQL> grant select on sys.user$ to testpwd;
Grant succeeded.
SQL> connect testpwd/testpwd
Connected.
SQL> @testpwd
Function created.
SQL>
|
Then its simple to test the function. Imagine you want to verify that SCOTT's password is TIGER - which it is in this database then the function simply returns "Y" and then if a wrong password, in this case BLOB is passed the function returns "N". This means that the function can be used to verify passwords. Here is the sample test:
SQL> select testpwd('SCOTT','TIGER') from dual;
TESTPWD('SCOTT','TIGER')
---------------------------------------------------------------
Y
SQL> select testpwd('SCOTT','BLOB') from dual;
TESTPWD('SCOTT','BLOB')
---------------------------------------------------------------
N
SQL>
|
We can now do what is possible inside the password verification function but outside that function. If you change passwords and want to check whether the same password is reused in a password verification function this is done because both the old and new passwords are available to test. This is not the case anywhere else and sometimes it is desirable to be able to verify old and new passwords.
Hopefully this function is useful?
[
6 Comments]
May 1st, 2009 by Pete
I have just posted the slides to my recent talk (
The Right Method To Secure An Oracle Database) at the UKOUG Northern Server technology day held in my home city of York to my Oracle security white papers page in the usual single slide to one page and size slides to one page format.
This was an interesting talk and I wanted to focus just on one thing; this is the importance to start any security project to secure an Oracle database with the "data" itself not simply follow a checklist. Checklists are fine but they should not be a starting point if you want to "secure data". A checklist in this case focuses on the Oracle software settings not your data so, for instance if you followed every step in the CIS benchmark and it would take a long time, would the key data (say your credit cards) you want to protect be protected? - in general the base level of security has definitely risen in the database, there is no doubt, but the specific data you want to secure has not had any specifc security settings applied to it.
This is the point, checklists will help with general hardening but they wont specifically - specifically is the keyword - help with securing your key identified data. So the message is start with the data, understand where it it, how it "flows", how its used, who uses it and then formulate a plan to secure it; checklists can be part of the plan but start with the data.
[
No Comments]
April 15th, 2009 by Pete
Oracle Corp. issued 43 fixes Tuesday as part of its quarterly Critical Patch Update, repairing flaws in its database management system, application server and application product lines.
"
Oracle issues 43 updates, fixes serious database flaws"
Oracle's advisory is
here, there are 16 new fixes for the database and two of them can be remotely exploited without authentication (no username or password required) over the network.
[
No Comments]
April 14th, 2009 by Pete
I was asked by a colleague a couple of weeks ago if it was possible to create ENUM's in PL/SQL like its possible to create in languages such as C. The actual example the person emailed me is too business/market specific for his company and I don't want to repeat it here as I don't want to give away who he works for without his permission so I will use a simpler examples.
Because I have some knowledge of PL/SQL my thoughts turned to TYPEs as it should be possible to create a TYPE that is in effect a constrained TYPE or an enumerated type. A simple example would be the BOOLEAN type in PL/SQL that is limited to values of TRUE and FALSE:
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=TRUE;
5 pv_var:=FALSE;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
|
That works as designed but what if we tried to assign a different value?
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=7;
5 end;
6 /
pv_var:=7;
*
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:='NOT';
5 end;
6 /
pv_var:='NOT';
*
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
SQL>
|
That doesn't work as the values are constrained - great. What is interesting is that we are always allowed to set the variable to NULL so in essence the TWO value ENUM has a three value set of possible values, TRUE, FALSE and NULL:
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=NULL;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
|
The BOOLEAN data value is defined in the standard.sql (stdspec.sql and stdbody.sql) files as:
type BOOLEAN is (FALSE, TRUE);
|
Now that looks exactly like whats needed for my colleague. Indeed the ADA language that PL/SQL is based on supports
enumerations in the same format as the BOOLEAN type is supported in PL/SQL in the STANDARD PACKAGE. Can we then use this same syntax in PL/SQL for instance:
SQL> declare
2 type colors is (RED, GREEN, BLUE, YELLOW);
3 begin
4 null;
5 end;
6 /
type colors is (RED, GREEN, BLUE, YELLOW);
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00505: User Defined Types may only be defined as PLSQL Tables or Records
ORA-06550: line 2, column 1:
PL/SQL: Item ignored
SQL>
|
The above simple test shows that its not possible for us to create ENUMs in PL/SQL like we can do in ADA. i.e. we can only create TYPEs that are tables or records. This is annoying but also an illustration of undocumented Oracle as Oracle themselves use the TYPE syntax to create an enumeration type in the STANDARD package but dont allow us to do the same. Clearly this syntax does compile if the code is within the standard package but not anywhere else so it is possible (but clearly not recommended or advised as it would affect support/warranty) to add new enumerations to the standard package. Oracle must have a check in the compiler that forces error PLS-00505 if the TYPE is an enumerator and not in the STANDARD package. I am also logged in AS SYSDBA above so its not the user that allows this but the location (STANDARD PACKAGE).
Why do Oracle use syntax available to them only in the STANDARD package and not available to us? - well, my educated guess would be that they have only implemented this syntax in a very narrow way, i.e. to fulfill a particular case and not much more. They must have made sure it compiles the BOOLEAN correctly but not tested or implemented much else hence we cannot use it. This is also a reason not to simply add your own enums to the standard package as they may compile but the results are likely to be undefined in some cases where the testing didn't iron out the rules properly.
It is quite interesting that Oracle constrain (or create an enumeration) a TYPE using the TYPE syntax as ADA does when Oracle also supports the SUBTYPE syntax to provide constrained and un-constrained types. An unconstrained type is really just an ALIAS for another type. See the STANDARD package for some examples. A constrained type is a type that limits the numeric values (which are allowed in user PL/SQL space; what is not allowed it seems is the connection between ENUM identifiers which in the C language would be numercially associated and indeed C implements the enum values as integers) and numeric constrains. This leads me to the idea that an ENUM can be emulated via CONSTANTS and SUBTYPES and this idea is what I have passed on to my colleague:
SQL> declare
2 RED constant number(1):=1;
3 GREEN constant number(1):=2;
4 BLUE constant number(1):=3;
5 YELLOW constant number(1):=4;
6 --
7 VIOLET constant number(1):=7;
8 --
9 subtype colors is binary_integer range 1..4;
10 --
11 pv_var colors;
12 begin
13 pv_var:=YELLOW;
14 end;
SQL> /
PL/SQL procedure successfully completed.
SQL> 13
13* pv_var:=YELLOW;
SQL> c/YELLOW/VIOLET/
13* pv_var:=VIOLET;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13
SQL>
|
As you can see we create a set of CONSTANT values that represent the values of the ENUM and then create a SUBTYPE that constrains the values of any variable of this SUBTYPE to these values.
This is not a perfect solution as we cannot do TYPE BOOLEAN IS (TRUE,FALSE) as Oracle does in the standard package or indeed TYPE COLORS IS (RED,GREEN,BLUE,YELLOW) as we can in ADA but its close; there are also issues around using this across multiple peices of code as ideally we would write this once which would inevitably cause us to need to do PACKAGE.CONSTANT. What is missing is the tie between the words (enums) and the type but what we have here is almost what C does, except C does the mapping between say YELLOW and 4 in the bacground for us and allows us to write YELLOW without seperately defining it. To illustrate the use of this idea to my colleague I created a simple example program that allows the use of the "enum" as a parameter, return type of a function etc, basically in a similar context as I would use an enum in my C programs.
SQL> declare
2 RED constant number(1):=1;
3 GREEN constant number(1):=2;
4 BLUE constant number(1):=3;
5 YELLOW constant number(1):=4;
6 --
7 VIOLET constant number(1):=7;
8 --
9 subtype colors is binary_integer range 1..4;
10 --
11 pv_var colors;
12 --
13 function test_a (pv_var1 in colors) return colors
14 is
15 begin
16 if(pv_var1 = YELLOW) then
17 return(BLUE);
18 else
19 return(RED);
20 end if;
21 end;
22 --
23 begin
24 pv_var:=test_a(YELLOW);
25 if (pv_var=YELLOW) then
26 dbms_output.put_line('YELLOW');
27 elsif(pv_var=RED) then
28 dbms_output.put_line('RED');
29 elsif(pv_var=BLUE) then
30 dbms_output.put_line('BLUE');
31 elsif(pv_var=GREEN) then
32 dbms_output.put_line('GREEN');
33 else
34 dbms_output.put_line('UN-KNOWN');
35 end if;
36 end;
37 /
BLUE
PL/SQL procedure successfully completed.
SQL>
|
OK, enough of ENUM's in PL/SQL for now. I like to look into the internals of Oracle and in particular PL/SQL and in the standard package there are lots of other gems such as the definition of VARCHAR2 which follows the similar syntax used in ADA to define a new type. In ADA we can do:
TYPE BOOL IS NEW BOOLEAN;
mybool: BOOL
|
Which is similar syntax to PL/SQL but again we only find this syntax used in the standard package. There are 10 occurances of this syntax in the shipped SQL code of the 11g database all of which use new occurances of CHAR_BASE or DATE_BASE. If we try and use the same syntax in user space code as follows we get:
SQL> declare
2 type mychar is new char_base;
3 begin
4 null;
5 end;
SQL> /
type mychar is new char_base;
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00504: type CHAR_BASE may not be used outside of package STANDARD
ORA-06550: line 2, column 1:
PL/SQL: Item ignored
SQL>
|
Interesting, these are undocumented language features of PL/SQL that come as standard from ADA but we are not allowed to use them ourselves in our PL/SQL code. It is interesting as I said to delve into the code shipped by Oracle to see what they use and do that we are not allowed to, because in some cases we may actually extend our knowledge and maybe we will find a feature that does work and is useful in some cases. For me it's definetely about the learning more about how Oracle works. Have fun!
[
No Comments]
April 14th, 2009 by Pete
I received a copy of Ron Ben Natan's new book "
How To Secure and audit Oracle 10g and 11g" (The link is Amazon.co.uk because I am in the UK, you can find the book on Amazon.com from the author name or ISBN) last week. I wanted to find time to mention Ron's book last week but travelling for work meant I had little spare time at all.
This is a good book, I like the format of "howtos", i like the way it concentrates particularly on the audit trails sections (note the audit in the title refers to audit trail rather than security auditing) on the functionallity available from Oracle. This is important as still most sites I visit do not employ audit trails in the database itself. Often sites use auditing in the database but for application level activities. It is important that sites audit database activity with the same vigour as application level auditing and understanding whats available with the database is a good start to set up and run and use something.
The book is focused at a hardening level and covers various privilege and access issues. It also covers encryption and authentication and of course auditing solutions. It also covers the new technologies/products from Oracle; Audit vault and Database vault.
This is a good book.
[
No Comments]
April 2nd, 2009 by Pete
I am going to be in Edinburgh on April 21st and April 22nd to teach my popular two day class "
How to perform a security audit of an Oracle database" with PiSec Limited. This class is great for all types of skills from Security auditors to DBA's, to CISO's to developers. I have taught all of these types of people in many countries around the world and all have enthused about the skills learned and developed. The class takes you through the process of performing a security audit of an Oracle database from threats, exploits, planning, building toolkits, through a complete althrough of a sample audit on a real Oracle database, finally through to analysis, write up and next steps. The course is filled with real world experience from someone with real world experience in this field. The key focus is on understanding the data and flow of the data so that its possible to develop a policy and implement it to secure all of your databases.
You can register for this course with
PiSec Limited Registration page. Places are limited so please hurry to show your interest. I look forward to seeing you all in Edinburgh.
[
No Comments]
