 |
|
Oracle Database Primer
Oracle Forensics tips by Paul Wright
|
If you already
have good Oracle DB/SQL skills, using SQL*PLUS at the command line
then you could save time by skipping this chapter and starting
Chapter 4 on Oracle security.
Oracle’s RDBMS
was based on SYSTEM-R from IBM which implemented an idea for
relational databases by Dr Edgar F. Codd.
Donald D. Chamberlin and Raymond F. Boyce also of IBM were the
authors of the SEQUEL language design. Oracle combined the
Structured English Query Language with the relational model to
deliver the first commercial SQL driven relational database to the
market in 1979, shortly ahead of IBM.
Understanding how
to use Oracle should start with how to find your way round.
Navigating Oracle’s data structures can be done using SQL*PLUS which
has the same advantage as Vi on UNIX in that it is ubiquitous,
scriptable and once you get the hang of it, quite effective. If you
learn commands using SQL*PLUS you should never be stuck as it is
nearly always on every server and Oracle client. The beauty of
SQL*PLUS is that it can be used for administration as well as
displaying data from a table.
First of all you
must logon to the Oracle server. If the $ORACLE_HOME/bin is not in
your path start from that directory:
OScommandline>./sqlplus /nolog
Sqlpluscommandline>conn
user/password@instancename
e.g. Sqlpluscommandline>conn system/manager@orcl
Help can be
accessed by entering the keywork “help”.
Then the help
topics for SQL*PLUS can be accessed by issuing the command
Sqlpluscommandline>help index
Each topic can be
further accessed by issuing the command
Help
<subject> e.g. help show -
show is an informative command in
SQL*PLUS.
If you have been
unable to connect to your Oracle server it may be because of your
Oracle network settings. From the client OS on Windows or *NIX there
is a text file called tnsnames.ora that includes mappings between
the instancenames and the IP address at which that instance lives.
You can think of tnsnames.ora as a hosts file like DNS. Mine is at
this file location.
E:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
Or on UNIX it is
here
$ORACLE_HOME/network/admin/tnsnames.ora
Edit this file to
include the server details taking care not to introduce additional
spacing. If this does not work you can use the syntax below to
bypass tnsnames.ora and pass the connection details directly to
SQL*PLUS on the command line.
sqlplussystem/manager@(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=orcl))(ADDRESS=(PROTOCOL=TCP)
(HOST=192.168.1.99)(PORT=1521)))
Or an easier to
remember syntax is:
sqlplus
user/password@<IP_ADDRESS>:<PORT>/<SID>
If the database
is local to the client OS you can access internally as OSDBA
(usually the OS administrator) by entering “sys as sysdba” for the
user and simply returning no password as below.
C:\Documents and Settings\Paul>sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 26 23:02:47
2006
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 -
Production
With the Partitioning, OLAP and Data Mining options
SQL>
So you should now
be able to connect to the database and as a DBA you will be able to
find out about objects are contained within. “Object” is a name
assigned to Tables, Packages, and Views etc which are “things” in
the database.
The database you
are connected to will have Objects in it which you will want to
access, so the first step is to list the object names. Querying the
Dictionary will provide this information. The Dictionary is an area
of the database which contains an overview of all the objects in the
database. In order to see what the Dictionary VIEW looks like then
we can ask Oracle to describe it as follows.
SQL>
describe dictionary
Name
Null? Type
------------------------- -------- -----------
TABLE_NAME
VARCHAR2(30)
COMMENTS
VARCHAR2(4000)
The above tells
us that the Dictionary VIEW contains two columns called TABLE_NAME
and COMMENTS both of which are character strings (Varchars). The
comments column is a description of what the table in column 1 does.
The query below
will return a list of all the Tables/VIEWS in the database.
SELECT table_name FROM DICTIONARY
ORDER BY table_name;
One of these
VIEWs is called DBA_USERS which we describe below.
SQL> desc dba_users;
Name
Null? Type
----------------------------------------- --------
----------------------------
USERNAME
NOT NULL VARCHAR2(30)
USER_ID
NOT NULL NUMBER
PASSWORD
VARCHAR2(30)
ACCOUNT_STATUS
NOT NULL VARCHAR2(32)
LOCK_DATE
DATE
EXPIRY_DATE
DATE
DEFAULT_TABLESPACE
NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE
NOT NULL VARCHAR2(30)
CREATED
NOT NULL DATE
PROFILE
NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP
VARCHAR2(30)
EXTERNAL_NAME
VARCHAR2(4000)
Plus we can
select the comments from the Dictionary regarding this VIEW.
SQL> select comments from
dictionary where table_name='DBA_USERS';
COMMENTS
------------------------------------------------------------------
Information about all users of the database
We can then find
out what the columns are in this table
SELECT column_name FROM
dict_columns WHERE table_name ='DBA_USERS' ORDER BY COLUMN_NAME;
Then “zooming”
into the password column we see:
SELECT comments FROM dict_columns
WHERE table_name ='DBA_USERS' and column_name=’PASSWORD’;
COMMENTS
-----------------------------------------------------------------
Encrypted password
This is an excerpt from the book "Oracle
Forensics: Oracle Security Best Practices", by Paul M. Wright,
the father of Oracle Forensics.
